1 Reply Latest reply on Aug 1, 2016 11:27 AM by Caton Guilbault

    How can I get user details for specific pieces of content accessed?

    storilynn

      Hello community. I would like to be able to have a report that contains the specific user id's of the individuals who have accessed specific pieces of content (mainly documents) in Jive. Ideally, I would like to see the details of that activity - if they viewed only or also downloaded the file.

       

      Can someone assist?

       

      Thank you

      Stori

        • Re: How can I get user details for specific pieces of content accessed?
          Caton Guilbault

          Hi Storilynn,

           

          If you have access to the Analytics database this query will pull all activities or can be constrained to specific ones.

           

          select 
              jivedw_day.begin_ts,
              jivedw_activity_fact.activity_ts,
              jivedw_activity_fact.user_id,
              jivedw_user.username, 
              jivedw_activity_fact.activity_type,
              jivedw_activity_meta.activity_name,
              jivedw_object.object_id
          
          from     jivedw_activity_fact
          
          join         jivedw_object on jivedw_object.dw_object_id = jivedw_activity_fact.direct_dw_object_id
          left join     jivedw_day on jivedw_day.day_id = jivedw_activity_fact.day_id
          left join     jivedw_user on jivedw_user.user_id = jivedw_activity_fact.user_id
          left join     jivedw_activity_meta on jivedw_activity_meta.activity_type = jivedw_activity_fact.activity_type
          
          where 
              jivedw_activity_fact.activity_ts > '7/28/15' -- Start Date
          and     jivedw_activity_fact.activity_ts <= '8/1/15' -- End Date
          --and     jivedw_activity_fact.activity_type = 10 -- Activity type (reference Activity and Object Type Definitions)
          and           jivedw_object.object_type = 102 -- Object type (reference Activity and Object Type Definitions)
          and     jivedw_object.object_id = 72657 -- Document ID