I want a ticket rule to email results when someone completes a satisfaction survey to the supervisor. I don't know the variable to add the survey results to the email. Is there a list of variables or does someone know how to set that in the SQL? Here's my current query:

SELECT
'lucas.land@wacoisd.org' AS EMAIL_LIST,
      -- ticket fields
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
    
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
    
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
    
       -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      OWNER_LABEL.LABEL_ID,
    
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
    
      -- about status
      S.NAME AS STATUS,   -- $status
    
      -- about impact
      I.NAME AS IMPACT,   -- $impact
    
      -- about category
      CAT.NAME AS CATEGORY, -- $category
    
      -- other fields
      HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
      HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
      HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisd_num
      HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
      HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
      HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email

     -- Satisfaction Survey
     HD_TICKET.SATISFACTION_RATING as 'Satisfaction Rating', -- $satrating
     HD_TICKET.SATISFACTION_COMMENT as 'Satisfaction Comments' -- $satcomment
   
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            /*AND C.ID=<CHANGE_ID>*/
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* labels ***********/ RIGHT JOIN USER_LABEL_JT OWNER_LABEL ON OWNER_LABEL.USER_ID = OWNER.ID

     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

     WHERE
SATISFACTION_RATING > 0 and C.TIMESTAMP BETWEEN (DATE_SUB(NOW(), INTERVAL 5 SECOND)) AND NOW()

      /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
The variables available for custom rules are determined by what fields are selected in the select statement. The values for the satisfaction survey are stored in HD_TICKET.SATISFACTION_RATING and HD_TICKET.SATISFACTION_COMMENT. If you haven't already setup a tool like MySQL WorkBench to look at the database then I highly recommend it.

Answered 04/27/2015 by: chucksteel
Red Belt

  • Right. Those colums are selected in the above SQL query. The problem I'm having is sending those values in an email. In the select statement above there are commented variables (e.g. $ticknum and $created) which when added to the email body add the info from that column. How can I add the data from the satisfaction survey and comment columns to the email? Are these variables preset in K1000 or can they be created?
    • Sorry, I missed that. The comments don't have any bearing on the creation of the variables, the KB documents just put them there to make it easier to understand. You need to either use the column name or the aliased name. Since your alias "Satisfaction Rating" has a space the variable probably isn't getting created. Try aliasing as SAT_RATING or something similar and then you can use $sat_rating.
Please log in to comment
Answer this question or Comment on this question for clarity