8 Replies Latest reply on Dec 5, 2018 8:34 AM by brian lilley-longville Branched to a new discussion.

    How to see who deleted a workbook?

    Jeff Krieg

      Just wanted to share my knowledge. This can be done by accessing the Postgres database of Tableau Server. The following query will show which users have deleted workbooks in the past 24 hours. Please note, this is not supported by Tableau, they will not be able to help modify or write queries against Postgres.

       

      SELECT 'User named: ' || a.name || ',   Deleted workbook: '|| b.name as WhoDidIt

        FROM historical_events c,

        hist_users a,

        hist_workbooks b

        where c.historical_event_type_id in

        (select type_id from historical_event_types

      where name = 'Delete Workbook')

      and c.created_at > (now() - interval '24 hour')

      and c.hist_workbook_id = b.id

      and c.hist_actor_user_id = a.id

        • 1. Re: How to see who deleted a workbook?
          Jeff Krieg

          If you need to see who deleted a view:

           

          SELECT 'User named: ' || a.name || ',   Deleted view: '|| b.name as Who_did_it

            FROM historical_events c,

            hist_users a,

            hist_views b

            where c.historical_event_type_id in

            (select type_id from historical_event_types

          where name = 'Delete View')

          and c.created_at > (now() - interval '24 hour')

          and c.hist_view_id = b.id

          and c.hist_actor_user_id = a.id

          2 of 2 people found this helpful
          • 2. Re: How to see who deleted a workbook?
            Jeff Krieg

            Who deleted a project?

             

            SELECT 'User named: ' || a.name || ',   Deleted project: '|| b.name as WhoDidIt

              FROM historical_events c,

              hist_users a,

              hist_projects b

              where c.historical_event_type_id in

              (select type_id from historical_event_types

            where name = 'Delete Project')

            and c.created_at > (now() - interval '24 hour')

            and c.hist_project_id = b.id

            and c.hist_actor_user_id = a.id

            3 of 3 people found this helpful
            • 3. Re: How to see who deleted a workbook?
              jagruthi.mothukuri

              Hi Jeff,

               

              Thank you so much for these scripts, very helpful. Is there a similar way to indentify which site admin deleted which user.

               

              Appreciate your Help!!

               

              Thanks,

              Jagruthi

              • 4. Re: How to see who deleted a workbook?
                Jeff Krieg

                Hi Jagruthi!!

                 

                Very sorry for the delay! I didn't see your update.

                 

                The following query should get what you're looking for. Please note, I removed the 24 hour filter via a comment. This can be added back if you'd like. Also note the Delete Type, you could create a filter on this to determine what kind of delete was done

                 

                 

                Who deleted a user and when?!

                 

                SELECT 'User named: ' || a.name || ',   Deleted user: '|| b.name as WhoDidIt, d.name Delete_Type, c.created_at as Action_Date

                  FROM historical_events c,

                  hist_users a,

                  hist_users b,

                  historical_event_types d

                where c.historical_event_type_id in

                (select type_id from historical_event_types

                where name = 'Delete Site User'

                union

                select type_id from historical_event_types

                where name = 'Delete System User'

                union

                select type_id from historical_event_types

                where name = 'Delete User From Group')

                and c.hist_target_user_id = b.id

                and c.hist_actor_user_id = a.id

                and c.historical_event_type_id = d.type_id

                --and c.created_at > (now() - interval '24 hour')

                • 5. Re: How to see who deleted a workbook?
                  Graham Murray

                  Hi Jeff,

                   

                  There are great - thanks.

                   

                  So I ran this and got something like:

                   

                  User named: TPA17,   Deleted workbook: ... comScore Digital Dashboard’

                   

                  Is there any way to show when the workbook was deleted at all?

                   

                  Thanks,

                   

                  Graham

                  • 6. Re: How to see who deleted a workbook?
                    Hilary Hastings

                    This was really helpful thank you. I used it as a base to create an audit report of workbook events. No doubt there are other ways but posting here as it's another tweak to this particular SQL:

                     

                    SELECT a.name as User,

                      d.name as Event,

                      b.name as Workbook,

                      c.created_at as Date

                      FROM historical_events c,

                      historical_event_types d,

                      hist_users a,

                      hist_workbooks b

                    where c.hist_workbook_id = b.id

                    and c.hist_actor_user_id = a.id

                    and c.historical_event_type_id=d.type_id

                    order by b.name, d.name

                    • 7. Re: How to see who deleted a workbook?
                      Srini Ramanathan

                      Jeff,

                      Thanks for sharing. Works for most cases.

                       

                      All,

                      I did notice a bug here though. If the view deleted from the workbook on the server was the last view of the workbook then the workbook gets dropped but this does NOT result in a 'Delete Workbook' event record. Any suggestions on how we could report this? I am guessing there is a long form way of keeping track of the count of views on the workbook before and after Delete View but that still is a function of investigation and not a reporting duty. Your thoughts...

                      • 8. Re: How to see who deleted a workbook?
                        brian lilley-longville

                        3 years late to the party here but Jeff THANK YOU SO MUCH! This was so clutch for us this morning and we have archived the scripts for future use.

                        Thanks again!!!!!!!!!!!!!!!!