10 Replies Latest reply on Feb 6, 2019 6:09 AM by john penna

    Who viewed which view with history timestamp

    kbj3030

      Hi All,

       

      I'm using the 'Workbook and view hits' dashboard which tells us which user viewed which view how many times and what is the last view time stamp.

      The last view time stamp we are getting it from 'view_stats or _views_status table with 'time' or 'last-view_time' dimensions. Now, my requirement is to track all the user details who viewed which view at different times with time stamp. (like per day, per last 7days or per month)

       

      The 'last_view_time' or 'time' dimension stores only recent view timestamp but I want all its history. Is there any way can we get it or track the history of view timestamps.

      Let me know if you require any info from my end. Thanks!

        • 1. Re: Who viewed which view with history timestamp
          Jeff Strauss

          Try having a look at the historical_events table for type 84 which is access rendering of views.  See the data dictionary for more info.  Workgroups Database

          • 2. Re: Who viewed which view with history timestamp
            kbj3030

            Hi Jeff,

             

            Thanks for your response. I have tried type id 84 but its not working :-(

            • 3. Re: Who viewed which view with history timestamp
              Matt Coles

              Try using the pre-built data source I authored for this for this, TS Events. The connections and fields are already curated for use in answering this, and other questions.

              • 4. Re: Who viewed which view with history timestamp
                kbj3030

                Hi Matt,

                 

                I tried TS Events and I'm getting nulls under Historic View  and Workbook even If I have data.

                • 5. Re: Who viewed which view with history timestamp
                  Matt Coles

                  That's because not all actions recorded in historical_events, the table that data source is primarily based on, have to do with Views and Workbooks. You can see all the event types by dragging out "Event Name". Some are login/logout, for example, in which case, the information about workbooks or views will be left NULL.

                  • 6. Re: Who viewed which view with history timestamp
                    kbj3030

                    Hi Matt,

                     

                    As you said, I filtered with Event Name but what you said is right. Its not recording all the actions in historical_events. Is there any way can we get this missing data?

                    • 7. Re: Who viewed which view with history timestamp
                      Matt Coles

                      So, let's be precise on what we're doing here. I filtered Event Name to "Access View". Then I dragged "Historical Item Name" to Rows. There are no NULL values showing. What did you do to create your viz?

                       

                      The thing with that data source that you have to keep in mind is that some of the fields refer to what was, and some refer to what is. If the view was moved or deleted, the fields that reflect the current situation may have nulls, but the historical ones should still have the data.

                       

                      I did find a bug in my data source with the Historical Repository URL though, that I need to think about how to fix.

                      • 8. Re: Who viewed which view with history timestamp
                        kbj3030

                        I dragged Historical Item Name , Actor User Name and Event Date (tried with local too) in rows

                        As advised, Filtered Event Name with Access View but still not seeing any data. I validated against my name as I accessed few views yesterday and today but no luck

                        • 9. Re: Who viewed which view with history timestamp
                          Matt Coles

                          No data at all?? That's very strange. Could you confirm that there are no (1) extract filters, (2) data source filters, and (3) sheet filters in play on your viz? If there are none, then if it were me, I'd check to make sure I was pointing to the correct Server repository. If you are, then I'd edit the connection, and navigate to the historical_events table on the list on the left pane, and just make sure it actually has some rows in it.

                          • 10. Re: Who viewed which view with history timestamp
                            john penna

                            I have some questions (I know it not supported reporting off the audit tables) - But have found 1 issue on the historical event table where the siteid is not correctly populated so you get the wrong site which I have logged with tableau.

                             

                            The main issue is the logon time and who accessed a view.

                             

                            Tableau is not working correctly as we know people have not logged on but Tableau is saying they have.

                             

                            If  there is a extract and that extract refreshes at 7am its saying users have logged on at 3.am which is roughly when the warehouse
                            has refreshed (which the extract points to)

                             

                            we think that if you lock your machine and stay logged into Tableau server overnight with the dashboard open on your screen when the DW refreshed (even if there is an extract) the user gets temporary disconnected and logs on again when the warehouse
                            finishes which is wholly inaccurate.

                             

                            I dont have the readonly pw and the business wont reset or run the command to see it so I cant use matt reports so I am querying the postres db using the Tableau documentation online which is not bad.

                             

                            The below is filtered to catch log on's from 11pm to 7am but users are appearing as logon and access a view when the warehouse refreshes at 3am (even though the dashboards are extracts to be run at 7am),.

                             

                             

                            select * from (

                            select

                            ht.name as "Event_Type",

                            ht.action_type,

                            he.id,

                            he.is_failure,

                            he.details,

                            he.hist_workbook_id,

                            hb.workbook_id,

                            hb.name AS "WB Name",

                            hsu.name,

                            hsu.email,

                            he.created_at AS "Logged Time",

                            Case

                            WHEN datepart(hh, he.created_at) >=7 and datepart(hh, he.created_at)< 18 THEN 1

                            Else 0

                            END AS "login_normal_work_hrs_between_7am_and_before_6pm",

                            Case

                            WHEN datepart(hh, he.created_at) > 18 and datepart(hh, he.created_at)<= 23 THEN 1

                            Else 0

                            END AS "login_between_6pm_and_at 11pm",

                            Case

                            WHEN datepart(hh, he.created_at) >= 23 or datepart(hh, he.created_at)< 7 THEN 1

                            Else 0

                            END AS "login_between_11pm_and_before_7am",

                            case

                            when DATEPART(WEEKDAY, he.created_at) = 1 Then 'Monday'

                            when DATEPART(WEEKDAY, he.created_at) = 2 Then 'Tuesday'

                            when DATEPART(WEEKDAY, he.created_at) = 3 Then 'Wednesday'

                            when DATEPART(WEEKDAY, he.created_at) = 4 Then 'Thursday'

                            when DATEPART(WEEKDAY, he.created_at) = 5 Then 'Friday'

                            when DATEPART(WEEKDAY, he.created_at) = 6 Then 'Saturday'

                            when DATEPART(WEEKDAY, he.created_at) = 7 Then 'Sunday'

                            Else NULL

                            END AS "Day_Of_Week_Name"

                            from OPENQUERY(DATABASE_NAME_GOES_HERE,'select * from
                            historical_events')he

                            left join OPENQUERY(DATABASE_NAME_GOES_HERE,'select * from
                            historical_event_types')ht on ht.type_id=he.historical_event_type_id

                             

                            ) d

                            where d.login_between_11pm_and_before_7am = 1

                            and d.Day_Of_Week_Name in ('Saturday','Sunday')

                            order by [Logged Time] desc