2 Replies Latest reply on Oct 1, 2014 9:16 AM by erinclements

    Query for Activity on Specific piece of content?

    erinclements

      Hi,

      I'm wondering if anyone has a query that will give me the activity for a specific piece of content or object ID?  I can't seem to find anything that will narrow down the activity to just one document.  Is this even possible?  I've tried a few things, but haven't really been able to get any user data out of any of the databases.  Thanks!

        • Re: Query for Activity on Specific piece of content?
          Caton Guilbault

          Hi Erin,

           

          Here is a forensics query I use for this purpose:

           

          select
              jivedw_activity_fact.day_id,
              jivedw_day.begin_ts,
              jivedw_activity_fact.activity_ts,
              jivedw_activity_fact.user_id,
              jivedw_user.username,
              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
          
          where
                  jivedw_activity_fact.activity_ts > '9/21/14'
          and     jivedw_activity_fact.activity_ts <= '9/25/14'
          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 = 1430 -- Document ID
          
          

           

          You can swap out values in lines 18-20 for different content types or use an IN ( ) but you might get a ton of data back if you have a lot of activity.