1 person found this helpful
The analytics Db and new analytics service (for Fall Cloud and Jive 7) both capture this information. In fact in Fall Cloud and Jive 7 it is exposed in the UI as Impact Metrics.
But to get it from an analytics standpoint in version 6 and earlier, you would look at the jivedw_activity_fact table - particularly at the VIEW events (event code 10 - more info here: Analytics DB Data Model) or at one of the _agg_ tables that roll up activity by day, week, or month...
Thanks for the quick response Josh! Agg tables were exactly what I needed.
If you have the system database and analytics database .dmp files (you will need both) loaded into a PostgreSQL database... (to load them into a PostgreSQL database follow these instructions: How to import the analytics database into your local Postgres install )
This SQL statement will provide you what you're looking for...
WHEN 1 THEN 'Thread'
WHEN 18 THEN 'Poll'
WHEN 3 THEN 'User'
WHEN 13 THEN 'Attachment'
WHEN 14 THEN 'Community Space'
WHEN 20 THEN 'Private Message'
WHEN 38 THEN 'BlogPost'
WHEN 102 THEN 'Document'
WHEN 1100 THEN 'Video'
WHEN 96891546 THEN 'Event'
WHEN 1464927464 THEN 'Wall Entry'
WHEN 3227383 THEN 'Idea'
END AS object_description
,o.name as object_name
,dc.name as community_name
,CASE WHEN ju.name IS NOT NULL THEN ju.name
WHEN ju.name IS NULL THEN (ju.firstname||' '||ju.lastname)
ELSE ju.email END AS user_disp_name
FROM jivedw_activity_fact daa
LEFT JOIN jivedw_object as o on o.dw_object_id = daa.direct_dw_object_id
LEFT JOIN jivedw_container as dc on daa.dw_container_id = dc.dw_container_id
LEFT JOIN jiveuser as ju on ju.userid = daa.user_id
AND daa.activity_type IN (10)
AND (daa.activity_ts BETWEEN date_trunc('hour', timestamp '2013-01-01 00:00:00') and date_trunc('hour', timestamp '2014-01-01 00:00:00'))