Overview

This article provides information on tracking changes to a document or a blog post in Jive using SQL query. 

Prerequisites

The information provided in this article applies to Jive Hosted and On-Premise instances. For Hosted instances, customers will need to engage customer support to perform these steps. For On-Premise instances, customers can perform these steps on their own.

Note:  Analytics must be enabled.

Solution

To track changes made to a document or blog post:

1. To populate changes in the jivedw_activity_fact table, we need to run the ETL job under System > Settings > Analytics > Data Load.
2. The jivedw_activity_fact table needs to be queried with the dw_object_id passed as a parameter. This is different from the document or blog post’s object ID (that you see in the URL). You have to query the jivedw_object table for this.
   1. SELECT dw_object_id from jivedw_object WHERE  object_id=1001 and object_type=102
   2. You can find the correct object_type in the "Object Type Reference" table of the Analytics DB Data Model reference.
3. You can then use the above-found dw_object_id to query the jivedw_activity_fact table.
4. The final query might look like:SELECT * FROM jivedw_activity_fact WHERE direct_dw_object_id = (SELECT dw_object_id FROM jivedw_object WHERE object_type = 102 AND object_id = 1001) AND activity_type = 40 AND user_id = 1
   1. 102 - document Type
   2. 1001 - the document ID
   3. 40 - Modify Activity Type
   4. 1 - This is the userID - in the example, it is the userID of the admin user. Replace this with the ID of the user whose activity you want to query.
5. You need to run the above query against the analytics database.
   1. In Hosted instances, navigate to the instance page on JCA > Analytics DB > Magic Query.
   2. In On-Premise instances, you will have to run this using psql or your other DB query tool.
6. Below is a screenshot of results in a Hosted instance.

Please see the schema for the jivedw_activity_fact table for column details or the Analytics DB Data Model documentation for details on 'Activity Facts'.

Confirmation

The query result brings up all the activities for the document or blog post with type, timestamp, activity performer, and other fields.