Yes you can tie an object (content) to where it lives (container). We usually do this in separate queries but you could combine into one with some intricate where statements.
Here are some examples for the production database: SchemaSpy - Table db_700clean.public.jivecommunity
Note: You can do this from the Analytics DB as well: SchemaSpy - Table dba8c1cmrana.public.jivedw_container
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 -- This join brings in the where docs live 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 -- This where limits the results to space containers jcount.objecttype = '102' -- This where limits the results to docs order by jcount.viewcount desc
select jivecommunity.lft, jiveblog.name as "Blog Name", jiveblogpost.userid, jiveuser.username, jivecommunity.name as "Space Name", jiveblogpost.blogpostid, jiveblogpost.permalink, to_timestamp(jiveblogpost.creationdate/1000) as "creationdate", to_timestamp(jiveblogpost.publishdate/1000) as "publishdate", jiveviewcount.viewcount from jiveblogpost join jiveblog on jiveblog.blogid = jiveblogpost.blogid join jiveuser on jiveuser.userid = jiveblogpost.userid join jivecommunity on jivecommunity.communityid = jiveblog.containerid -- This join brings in the where blogs live join jiveviewcount on jiveviewcount.objectid = jiveblogpost.blogpostid where jiveblog.containertype = 14 and -- This where limits the results to space containers jiveviewcount.objecttype = 38 -- This where limits the results to blogposts
select poll.pollid, sgroup.name as "Group", juser.username, jgroup.name, to_timestamp(poll.creationdate/1000) as "creationdate", poll.name from jivepoll poll left join jiveuser juser on juser.userid = poll.userid left join jivesgroup sgroup on sgroup.groupid = poll.objectid left join jivegroupuser guser on guser.userid = poll.userid left join jivegroup jgroup on jgroup.groupid = guser.groupid -- This join brings in the where polls live where poll.objecttype = 700 and -- This where limits the results to group containers sgroup.name like '%MBB%' order by "creationdate" asc
This looks promising! We will try your suggestion and get back to you!
Thank you Caton! Looks like our database structures are a little bit different, but your idea really helped us a lot! We have figured this out, thank you so much!