Skip navigation
brennels posted 8 years ago
 Does anyone have or know how to create a databae query to pull community name and last message posted within that community. I am trying to pull some metrics to generate a community health scorecard, rating thos communities that aren't frequently updated. Any help would be appreciated.


  • Ryan Rutan 8 years ago

    Does it have to be just "last message"?  I believe the "last modified" for a space/group is updated every time a message is posted in it.  I could be mistaken, but that might help if it holds up.
  • brennels 8 years ago

    Hi Ryan, Thanks for the reply. It appears the lastmodifieddate in the jivecommunity table is the last time the community was updated and published. I think what I need is something like this. Select name from jivecommunity where lastmodifieddata > #### and < #### from jivemessage... The date formats have to be converted but I am having a hard time linking the data from the two tables to pull similar information. Let me know if you have any other thoughts and or suggestions...
  • Ryan Rutan 8 years ago

    If you wanted to tie it down to just messages, then could do something like this:
    select communityID, name, (select to_timestamp(max(creationdate)/1000) from jivemessage where containertype=14 and containerId=c.communityID) as last_message_date from jiveCommunity c; 
    similarly, you can run queries for Jive Groups and Projects:
    select groupID, name, (select to_timestamp(max(creationdate)/1000) from jivemessage where containertype=700 and containerId=g.groupID) as last_message_date from jiveSGroup g;
    select projectID, name, (select to_timestamp(max(creationdate)/1000) from jivemessage where containertype=600 and containerId=p.projectID) as last_message_date from jiveProject p;

    This limits the results to just messages, but should get you what you want.  Hope this helps.  
  • brennels 8 years ago

    Nice... Thanks so much, I'll see if this does the trick