This article provides information on tracking changes to a document or a blog post in Jive using SQL query.
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.
To track changes made to a document or blog post:
- To populate changes in the
jivedw_activity_facttable, we need to run the ETL job under System > Settings > Analytics > Data Load.
jivedw_activity_facttable needs to be queried with the
dw_object_idpassed 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 tablefor this.
SELECT dw_object_id from jivedw_object WHERE object_id=1001 and object_type=102
- You can find the correct
object_typein the "Object Type Reference" table of the Analytics DB Data Model reference.
- You can then use the above-found
dw_object_idto query the
- 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
102- document Type
1001- the document ID
40- Modify Activity Type
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.
- You need to run the above query against the analytics database.
- In Hosted instances, navigate to the instance page on JCA > Analytics DB > Magic Query.
- In On-Premise instances, you will have to run this using psql or your other DB query tool.
- Below is a screenshot of results in a Hosted instance.
The query result brings up all the activities for the document or blog post with type, timestamp, activity performer, and other fields.