/build/static/layout/Breadcrumb_cap_w.png

Satisfaction Survey to e-mail Support Manager when a user rate the ticket 3 or below

I have created a custom rule in MySQL Workbench that shows a list of tickets that have a rating of 3 or under.


When I bring the query into KACE and setup "Email each recipient in query results" I don't receive an email when a user selects a satisfaction rating of 3 or below.


I was wondering if I have to update the ticket with something for it to trigger the email?


Chris,


5 Comments   [ + ] Show comments
  • I have added this to the Select SQL: and ((SATISFACTION_RATING < "3") AND SATISFACTION_RATING <> "0")

    And added my email address to the email results.

    But i'm getting a big mess of a report, is there a way to make this simpler? - chris.poston 9 years ago
    • Email Each Recipient in Query results is expecting that the Select SQL statement is going to return a list of Email Addresses; I think this would be your first issue. There should be a way to do this as a report, and schedule it for a daily run, no? Maybe someone else can guide better - anonymous_112053 9 years ago
  • Do you want the supervisor to be emailed as soon as someone submits a satisfaction survey or would you prefer them to receive a daily report? If you want a daily report of all tickets submitted with less than 3 then you create a report and then have the report scheduled to run once a day. - chucksteel 9 years ago
    • I would like them to receive the email straight away on ticket save, so that we can action a resolution to the issue quickly. - chris.poston 9 years ago
  • Can you post your full SQL query? - chucksteel 9 years ago
  • select HD_TICKET.*,
    HD_STATUS.NAME AS STATUS_NAME,
    HD_STATUS.ORDINAL as STATUS_ORDINAL,
    HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
    HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
    HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
    STATE,
    if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
    if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
    if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
    if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
    if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
    if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
    if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
    if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
    case upper(STATE)
    when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
    when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
    else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
    if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
    U1.FULL_NAME as OWNER_FULLNAME,
    U1.EMAIL as OWNER_EMAIL,
    if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
    if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    U2.EMAIL as SUBMITTER_EMAIL,
    if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
    if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
    if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
    Q.NAME as QUEUE_NAME
    from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
    LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
    LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
    LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
    LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
    LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
    where HD_PRIORITY.ID = HD_PRIORITY_ID
    and HD_STATUS.ID = HD_STATUS_ID
    and HD_IMPACT.ID = HD_IMPACT_ID
    and HD_CATEGORY.ID = HD_CATEGORY_ID
    and ((SATISFACTION_RATING < "3") AND SATISFACTION_RATING <> "0") - chris.poston 9 years ago
    • in the select statement add...
      'email address or addresses' as SUPPORT

      then enter SUPPORT as the column containing email addresses.

      select HD_TICKET.*,
      'myboss@mycompany.com' AS SUPPORT,
      HD_STATUS.NAME AS STATUS_NAME, HD_STATUS.ORDINAL as STATUS_ORDINAL, HD_IMPACT.ORDINAL as IMPACT_ORDINAL, - h2opolo25 9 years ago
  • I have tried some like this using the CC_List column, on ticket save it does enter in the email address but won't send an email to that address until it is saved again. Which the user won't need to do. - chris.poston 9 years ago

Answers (1)

Posted by: chris.poston 9 years ago
Blue Belt
1
I have managed to work this out and make a nice report that goes to our Support Manager on ticket save.


SELECT
                HD_TICKET.ID,
                HD_TICKET.TITLE as TICKET_TITLE,
HD_TICKET.SATISFACTION_RATING,
HD_TICKET.SATISFACTION_COMMENT,
                SUBMITTER.FULL_NAME as SUBMITTER_NAME,
                SUBMITTER.EMAIL as SUBMITTER_EMAIL,
SUBMITTER.WORK_PHONE as SUBMITTER_PHONE

FROM 
HD_TICKET
/* Joing the user table as submitter so we can use the submitter name and email address in a email */
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

WHERE
HD_TICKET.SATISFACTION_RATING <= 3 AND
HD_TICKET.SATISFACTION_RATING != 0 AND
HD_TICKET.HD_QUEUE_ID = 13


Tick Email results and add Managers email address

Email will look like:

Satisfaction Rating below 3


#

Id

Ticket Title

Satisfaction Rating

Satisfaction Comment

Submitter Name

Submitter Email

Submitter Phone

1

1234

Test

2

 Unhappy

Username

Usermane@company.co.uk

0123456789


.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ