We use the following query for docs but it could be modified to include other pieces of content.
select doc.internaldocid,doc.documentid, person.username as "Original Author", community.name as "Space Name",to_timestamp(doc.creationdate/1000) as "creationdate",to_timestamp(docversion.creationdate/1000) as "VersionCreatedate", to_timestamp(docversion.modificationdate/1000) as "Modificationdate",docversion.userid as "Last Modified UserID", jcount.viewcount,docversion.title
from jivedocument doc
left join jiveuser person on person.userid = doc.userid
left join jiveobjecttype objtype on objtype.typeid = doc.containertype
left join jivecommunity community on community.communityid = doc.containerid
left join jivedocversion docversion on docversion.internaldocid = doc.internaldocid
left join jiveviewcount jcount on jcount.objectid = doc.internaldocid
where docversion.state = 'published' and objtype.code = 'community' and jcount.objecttype = '102'
order by jcount.viewcount desc
This a query we use for Threads:
select msg.threadid, question.threadid as "QuestionID",msg.userid, community.name as "Space",to_timestamp(msg.creationdate/1000) as "creationdate",latest.messagecount, jcount.viewcount, question.resolutionstate,
date_part('year',to_timestamp(msg.creationdate/1000)) as "Year",
date_part('month',to_timestamp(msg.creationdate/1000)) as "Month",
date_part('day',to_timestamp(msg.creationdate/1000)) as "Day",
date_part('week',to_timestamp(msg.creationdate/1000)) as "Week",
from jivemessage msg
left join jivethreadlatest latest on latest.threadid = msg.threadid
left join jiveviewcount jcount on jcount.objectid = msg.threadid
left join jivecommunity community on community.communityid = msg.containerid
left join jivequestion question on question.threadid = msg.threadid
where msg.parentmessageid is null and
jcount.objecttype = '1' and
msg.containertype = '14'AND
to_timestamp(msg.creationdate/1000) >= '9/1/2012 00:0:0'
order by jcount.viewcount desc
The query below is what I ended up using. I haven't had a lot of time to tweak it, so you have to know the right month id to input vs being able to specify a date range. Also, this does not distinguish a Question from a Discussion, so both are classified as a Thread. Once I get this output, I relabel the object_type into something readable (1 = Thread, 102 = Document, etc), then I make a pivot table to get the total view count for each piece of content, and finally use VLOOKUPs to get the space id, space name, and content type all in one table. This replicates the CMR report for everything I use except as I mentioned earlier both Discussions and Questions are now Threads. I also mix in our company division based on space id from another VLOOKUP to help us track how much content from each division is consumed. From there I can do another pivot table to lay everything out very nicely in a summary of each content type and the number of views for each by division.
jivedw_activity_agg_month.month_id, jivedw_activity_agg_month.user_id, jivedw_user.email, jivedw_object.object_type, jivedw_object.name, jivedw_container.container_type, jivedw_container.container_id, jivedw_container.name, jivedw_activity_agg_month.activity_count
LEFT JOIN jivedw_user ON jivedw_user.user_id = jivedw_activity_agg_month.user_id
LEFT JOIN jivedw_object ON jivedw_object.dw_object_id = jivedw_activity_agg_month.direct_dw_object_id
LEFT JOIN jivedw_container ON jivedw_container.dw_container_id = jivedw_activity_agg_month.dw_container_id
LEFT JOIN jivedw_month ON jivedw_activity_agg_month.month_id=jivedw_month.month_id
WHERE jivedw_month.month_of_year = '4' AND jivedw_month.year = '2014' AND jivedw_activity_agg_month.activity_type = 10 AND jivedw_object.object_type != '14' AND jivedw_object.object_type != '3'
NOTE: replace the value for month_of_year and year appropriately
If anyone figures out how to accurately break Threads up in to Questions and Discussions please comment with an updated query.