5 Replies Latest reply on Mar 3, 2016 2:04 AM by lei.chen.0

    How do I create a viz showing hits on Tableau Server per Site?

    Vineet Gordhandas
    Vineet Gordhandas Feb 24, 2016 2:26 PM

      Hello,

      I have been researching various SQL queries and looking at many workbooks especially this one: Custom Tableau Server Admin Views

      but have been unsuccessful in displaying the correct data.

       

      Using the same query form the Custom Tableau Server Admin Views workbook, I adjusted it to include _sites and did a left join on 'hist_actor_site_id' and 'Id(sites)' and many of the Sites are not appearing or are appearing with the wrong Id number. Also I have noticed that many of the projects are not associating with the correct sites in the historical data.

       

      The ultimate goal is to create a dashboard for our Director that shows all activity occurring on Tableau Server per Site over the last 6 months

       

      Looking forward to the community's responses!

       

      Thank you!

        • 1. Re: How do I create a viz showing hits on Tableau Server per Site?
          lei.chen.0
          lei.chen.0 Ambassador Feb 24, 2016 7:11 PM (in response to Vineet Gordhandas)

          Hello Vineet,

           

          Please use hist_sites instead of _sites, joining by

           

          historical_event.hist_actor_site_id = hist_sites.id

           

          More information about the tables be found here.

          Workgroups Database

           

           

          Regards.

          2 of 2 people found this helpful
          • 2. Re: How do I create a viz showing hits on Tableau Server per Site?
            Vineet Gordhandas
            Vineet Gordhandas Feb 25, 2016 8:50 AM (in response to lei.chen.0)

            Thank you Lei Chen! This seems to work!

             

            I also added 'hist_projects' and things are lining up fairly well. There is some strangeness where a few projects are appearing under a "Null" site that I need to investigate further but I believe this is the correct answer!

             

            Thanks again,

            Vineet

            • 3. Re: How do I create a viz showing hits on Tableau Server per Site?
              lei.chen.0
              lei.chen.0 Ambassador Feb 28, 2016 8:21 PM (in response to Vineet Gordhandas)

              Hello Vineet,

               

              I also added 'hist_projects' and things are lining up fairly well. There is some strangeness where a few projects are appearing under a "Null" site that I need to investigate further but I believe this is the correct answer!

               

              Actually this is the question I've been investigating too.

               

              Depends on the action name, some operations don't have target_site.

              The following is the result I found on our server,

               

              target_site doesn't contain any null:

              • Access Data Source
              • Add Comment
              • Add User To Group
              • Create Project
              • Create Site
              • Create Site User
              • Delete Comment
              • Delete Data Source
              • Delete Project
              • Delete Site
              • Delete Site User
              • Delete View
              • Delete Workbook
              • Download Data Source
              • Download Workbook
              • Increment Data Source Extract
              • Lock Site
              • Publish Data Source
              • Publish View
              • Publish Workbook
              • Refresh Data Source Extract
              • Refresh Workbook Extract
              • Send Subscription E-Mail For View
              • Send Subscription E-Mail For Workbook
              • Update Data Source
              • Update Project
              • Update Site
              • Update User Admin Level
              • Update User Licensing Role
              • Update User Publisher State
              • Update Workbook

               

              target_site is always null:

              • Change Project Ownership From
              • Change Project Ownership To
              • Change Workbook Ownerhsip To
              • Change Workbook Ownership From
              • Create Data Source Task
              • Create Group
              • Create Schedule
              • Create System User
              • Create Workbook Task
              • Delete Data Source Task
              • Delete Group
              • Delete Schedule
              • Delete System User
              • Delete Workbook Task
              • Disable Schedule
              • Enable Schedule
              • Issue Refresh Token
              • Login
              • Logout
              • Move Datasource From
              • Move Datasource To
              • Move Workbook From
              • Move Workbook To
              • Redeem Refresh Token
              • Revoke Refresh Token
              • Update Schedule
              • Update System User Email

               

              The above looks quite reasonable.

               

              What is tricky to me is that, one action mostly is not null, but sometimes has null.

              • Access View

               

              Till now, I still haven't figured out why...

               

               

              Regards.

              • 4. Re: How do I create a viz showing hits on Tableau Server per Site?
                Vineet Gordhandas
                Vineet Gordhandas Mar 2, 2016 12:08 PM (in response to lei.chen.0)

                Can you help explain what the difference between Target and Actor sites are? I am so confused as to why those items would have different IDs.

                • 5. Re: How do I create a viz showing hits on Tableau Server per Site?
                  lei.chen.0
                  lei.chen.0 Ambassador Mar 3, 2016 2:04 AM (in response to Vineet Gordhandas)

                  According to Workgroups Database,

                   

                  hist_actor_user_idintegerA foreign key reference to the user_id column for the record in the hist_users table of the user who initiated the event.
                  hist_target_user_idintegerA foreign key reference to the user_id column for the record in the hist_users table of the user who was the target of the event.
                  hist_actor_site_idintegerA foreign key reference to the site_id column for the record in the hist_sites table where the event was initiated.
                  hist_target_site_idintegerA foreign key reference to the site_id column for the record in the hist_sites table which was the target of the event.

                   

                  Also depending on the action name, actor_site and target_site have different patterns.

                  The attache image shows the patterns found on our server,

                  - flag_same_site: if actor_site and target_site are the same

                  - flag_same_user: if actor_user and target_user are the same

                   

                  Basically target_site information is valuable to me.

                  But for certain actions, target_site is null, then I'll refer to actor_site.

                  For example, Create Group, I guess actor_site means the site that group is to be created on.

                  Right, it's a little confusing.

                  test.png

                  1 of 1 people found this helpful