26 Replies Latest reply on May 16, 2014 10:10 AM by mnevill

    Using Postgres SQL queries and Excel to determine non-employee answers

    mnevill

      Here is a video that covers how to get a Postgres SQL dump of all questions from Jive as well as who answered them.  The data from this query is combined with profile data you can get from CMR as well as space data that you can classify many ways.

       

       

       

      Instructions for installing Postgres, creating databases, and restoring databases from Jive cloud .dmp files:

      Installing Postgres for Jive DWH Implementation

       

      Query to get answers (Jive 5 and 6):

      SELECT  jivequestion.threadid,

              jiveanswer.messageid,

              jivequestion.userid as "asker",

              jiveanswer.userid as "answerer",

      to_timestamp(jivequestion.creationdate/1000) as "creationdate",

              to_timestamp(jivequestion.resolutiondate/1000) as "resolutiondate",

      jivequestion.resolutionstate,

      jiveanswer.helpfulanswer as "helpful",

      jiveanswer.correctanswer as "correct",

              jiveuser.email as "answerer_e-mail",

            jivequestion.containerid as "space_id",

              jivecommunity.name as "space"

      FROM    jivequestion

      LEFT JOIN jiveanswer ON jiveanswer.threadid = jivequestion.threadid

      LEFT JOIN jiveuser ON jiveuser.userid = jiveanswer.userid

      LEFT JOIN jivecommunity ON jivecommunity.communityid = jivequestion.containerid

      ORDER BY jivequestion.threadid asc

      **NOTE: This query has been updated to include the e-mail address of the answerer without needing a VLOOKUP so some of the steps in the video can be omitted.

       

      Query to get answers (Jive 7):

      SELECT  jivequestion.threadid,

              jiveanswer.messageid,

              jivequestion.userid as "asker",

              jiveanswer.userid as "answerer",

              to_timestamp(jivemessage.creationdate/1000) as "answerdate",

              jivequestion.resolutionstate,

              jiveanswer.correctanswer as "correct",

              jiveuser.email as "answerer_e-mail",

              jivequestion.containerid as "space_id",

              jivecommunity.name as "space"

      FROM    jivequestion

      LEFT JOIN jiveanswer ON jiveanswer.threadid = jivequestion.threadid

      LEFT JOIN jivemessage ON jivemessage.messageid = jiveanswer.messageid

      LEFT JOIN jiveoutcome ON  jiveoutcome.childobjectid = jiveanswer.messageid

      LEFT JOIN jiveuser ON jiveuser.userid = jiveanswer.userid

      LEFT JOIN jivecommunity ON jivecommunity.communityid = jivequestion.containerid

      WHERE jiveanswer.correctanswer = 1 OR jiveoutcome.parentobjecttype = 1 AND jiveoutcome.outcometypeid = 4  AND jiveoutcome.containertype = 14

      ORDER BY jivemessage.creationdate asc

      **NOTE: Starting in Jive 7 any future Helpful answers are no longer logged in the jiveanswer table, so this query pulls from the Structured Outcomes table (jiveoutcome). If you upgrade all pre-Jive 7 Helpful answers are still present in the jiveanswer table but they are also converted to Helpful Structured Outcomes, and then logged in the jiveoutcome table going forward.  I don't know if things are changing again in Jive 8 but there are going to be new ways to mark content as helpful.  This query also gives the date the reply was supplied instead of the creation date of the thread or the date when an answer was marked.

       

      Excel formula to distinguish employees from external users:

      =IF(ISNUMBER(SEARCH("@domain.com",H2)),"EMPLOYEE","USER")

       

      NOTE: replace "@domain.com" with the proper e-mail string and replace H2 with the cell containing e-mail addresses of the person that supplied the answer.

       

      Message was edited by: Matt Nevill - changed answer.containerid to question.containerid since it was wrong initially

       

      Message was edited by: Matt Nevill - updated query to include answerer e-mail address (1/9/2014)

       

      Message was edited by: Matt Nevill - added query for Jive 7 to account for the conversion of Helpful answers to Structured Outcomes (11/11/2014)