We use two separate queries for this information and combine with other internal data sources to provide a content inventory.
Document Table Query:
select doc.internaldocid, replace(docversion.title,',','') as title, doc.userid as creatorID, person.username as OriginalAuthor, doc.documentid, doc.containertype, objtype.code, doc.containerid, community.name, to_timestamp(doc.creationdate/1000) as creationdate, to_timestamp(docversion.modificationdate/1000) as Modificationdate, to_timestamp(doc.expirationdate/1000) as expirationdate, doc.recommended, doc.meanrating, doc.readcount, doc.commentstatus, doc.versionid 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 where docversion.state = 'published' -- constrains to viewable docs and objtype.code = 'community' -- constrainsts to docs in spaces order by internaldocid asc
All Tags for Each Doc Query:
select jiveobjecttag.objectid, string_agg(jivetag.tagname, '| ') from jiveobjecttag left join jivetag on jivetag.tagid=jiveobjecttag.tagid where jiveobjecttag.objecttype=102 --constrains to tags on docs group by jiveobjecttag.objectid
You also might want to think about adding in the Categories for each doc if you leverage them for content filtering or grouping.
Categories for Each Doc Query:
select doc.internaldocid, string_agg (replace(tagset.name,',',''), ' | ') as Categories from jivedocument doc 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 jiveobjecttagset objtagset on objtagset.objectid = doc.internaldocid left join jivetagset tagset on tagset.tagsetid = objtagset.tagsetid where docversion.state = 'published' -- constrains to viewable docs and objtype.code = 'community' -- constrainsts to docs in spaces and objtagset.objecttype = '102' -- constrainsts to doc object group by doc.internaldocid
I hope that helps and let me know if you figure out a way to tie all this together into one query. I've tried a couple of times but keep running into conflicting constraints that end up excluding the subset of docs that don't have a category or a tag.
I don't think Cloud has direct database access you will need to file a case with Support asking for access to find out. OOTB you can get some of this information from the CMR Content report but if you have a large instance that can get icky fast. You might be able to tease some of these details out of the DES (Using the V2 Jive Data Export Service (Analytics)) but I haven't tried yet.
+ Ted Hopton the guy who has posted the most about the DES so far to see if he has any ideas.
thanks Caton! we will check it out.