Version 2

    Here is a query that displays all content that was viewed by anyone with a specified e-mail address domain (@company123.com) while they were logged in. 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_user.username, jivedw_user.email, jivedw_user.firstname, jivedw_user.lastname, jivedw_object.object_type, jivedw_object.name, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name

    FROM

    jivedw_activity_fact, jivedw_user, jivedw_object, jivedw_container

    WHERE jivedw_user.email LIKE '%@company123.com' AND 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

     

    Here is an example of the output:

     

    activity_tsuser_idusernameemailfirstnamelastnameobject_typenamecontainer_typecontainer_idname
    2/14/20134694joesmithjsmith@example.comJoeSmith102Client List.PDF142180Sales Documents
    6/20/20134694joesmithjsmith@example.comJoeSmith1Does anyone remember HE-MAN?142003Water Cooler
    6/20/20137292bcarlsonbcarlson@example.comBobCarlson102Test Document142160Help Space

     

    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.