Here are some queries to get you started:
select idea.ideaid, idea.userid, person.username, idea.containertype, idea.containerid, idea.status, to_timestamp(idea.creationdate/1000) as "creationdate", to_timestamp(idea.modificationdate/1000) as "modificationdate", idea.ideasubject from jiveidea idea left join jiveuser person on person.userid = idea.userid
select jivemessage.threadid, jivemessage.userid, jivecommunity.name as "Space", to_timestamp(jivemessage.creationdate/1000) as "creationdate",jivethreadlatest.messagecount, jiveviewcount.viewcount, jivemessage.subject from jivemessage left join jivethreadlatest on jivethreadlatest.threadid = jivemessage.threadid left join jiveviewcount on jiveviewcount.objectid = jivemessage.threadid left join jivecommunity on jivecommunity.communityid = jivemessage.containerid where jivemessage.parentmessageid is null and jiveviewcount.objecttype = '1' and jivemessage.containertype = '14' order by jiveviewcount.viewcount desc
All Messages Posted
select message.messageid, juser.username, answer.helpfulanswer, answer.correctanswer, to_timestamp(message.creationdate/1000) as "creationdate", community.name as "Space" from jivemessage message left join jiveanswer answer on answer.messageid = message.messageid left join jivecommunity community on community.communityid = message.containerid left join jiveuser juser on juser.userid = message.userid where message.containertype = 14 -- Limited to only Spaces ord er by message.messageid asc
Thanks this was a good start. Do you know what the query is for a commenter, as opposed to the original poster? That's really key for the desired output. I'm trying to identify the commentators in the threads.
The All Messages Posted query contains that information (all posts on all threads). You can add the threadid column from the jivemessage table to see this relationship in the results. If you are trying to figure out who the 'answerer' of questions are then I would recommended this query:
select question.threadid, answer.messageid, question.userid as "Asker", answer.userid as "Answerer", to_timestamp(question.creationdate/1000) as "creationdate", to_timestamp(question.resolutiondate/1000) as "resolutiondate", question.resolutionstate, answer.helpfulanswer, answer.correctanswer from jivequestion question left join jiveanswer answer on answer.threadid = question.threadid order by question.threadid asc