Version 1

    Here is a query I built that displays all content that was viewed in the last month.  This needs to be run against the analytics database.  It includes the time stamp, who the user was, what content was viewed, and the place the content lives.

     

    SELECT

    jivedw_activity_fact.activity_ts, jivedw_activity_fact.user_id, jivedw_activity_fact.activity_type, jivedw_object.dw_object_id, jivedw_object.object_type, jivedw_object.object_id, jivedw_object.name, jivedw_container.dw_container_id, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name, jivedw_user.username, jivedw_user.email, jivedw_user.firstname, jivedw_user.lastname

    FROM

    jivedw_activity_fact, jivedw_user, jivedw_object, jivedw_container

    WHERE jivedw_activity_fact.activity_type = 10 AND jivedw_activity_fact.user_id != -1 AND jivedw_activity_fact.user_id = jivedw_user.user_id AND jivedw_activity_fact.direct_dw_object_id = jivedw_object.dw_object_id  AND jivedw_activity_fact.dw_container_id= jivedw_container.dw_container_id

    AND jivedw_activity_fact.activity_ts between date_trunc('month',current_date) - interval '1 month'

    AND date_trunc('month',current_date) - interval '1 day'

     

    It can probably be cleaned up some but is hopefully useful to somebody out there.

     

    Here is an example of the output:

    activity_tsuser_idactivity_typedw_object_idobject_typeobject_idnamedw_container_idcontainer_typecontainer_idnameusernameemailfirstnamelastname
    8/1/20137389101611811408Test Thread 1172142101Posting Space 1joesmithjoe.smith@example.comJoeSmith
    8/1/20135174102800111713Example Discussion190142119Water Coolerchrischris.jones@example.comChrisJones
    8/1/20137391106871021115Document 19142003Documentation Spacebjacksonbob.jackson@example.comBobJackson

     

    The object_type column can be referenced with the Object Type Reference section of this guide; Analytics Data Model. In the example above 1 = thread and 102 = document.