/build/static/layout/Breadcrumb_cap_w.png
10/05/2018 194 views
Hello, is it possible to send an additional survey after 90 days of ticket closure?

I was thinking of creating a ticket rule and was going to select ticket status = closed then set survey satisfaction = 0, so that an email gets sent out

Not sure how to implement this though. Anyone tried it before and would share that would be helpful
2 Comments   [ + ] Show comments

Comments

  • Thanks ondrar for the quick reply. I am testing this now and will update
  • Hi Ondrar, the query works well and sends out the email. I am trying to add a message and link to the ticket as the email that gets sent out is blank. What would the variables need to be for specifying the ticket and URL? Thanks

All Answers

1
I have a rule similar to this in that it works on closed tickets, so I quick whipped this select statement up for you:


SELECT DISTINCT
  HD_TICKET.ID AS TICKET_ID, 
  HD_TICKET.TITLE AS TICKET_TITLE,
  HD_STATUS.NAME AS STATUS_NAME,
  HD_STATUS.STATE AS STATE,
  SUBMITTER.FULL_NAME AS SUBMITTER_FULLNAME,
  SUBMITTER.EMAIL AS SUBMITTER_EMAIL,
  HD_TICKET.TIME_CLOSED,
  HD_TICKET.SATISFACTION_RATING AS RATING,
  HD_TICKET.SATISFACTION_COMMENT AS COMMENT
  

FROM (HD_TICKET, HD_STATUS)
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

         
WHERE HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
  AND HD_STATUS.STATE = 'closed' -- ticket is still closed
  AND -- no survey rating or comment
  (SATISFACTION_COMMENT IS NULL
  OR SATISFACTION_COMMENT LIKE ''
  OR SATISFACTION_RATING IS NULL
  OR SATISFACTION_RATING LIKE ''
  )
  AND HD_TICKET.HD_QUEUE_ID = -- insert your queue id here
  AND DATE(TIME_CLOSED) = DATE(NOW())-90 -- closed 90 days ago


You can start with that and create your own email to go along with it, using submitter_email for the Column containing email addresses.  Note that this reports tickets without a survey rating or comment, so if they filled out only one of those, they'd still get an email.  You may or may not want to change that.
Answered 10/05/2018 by: ondrar
Second Degree Brown Belt