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

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?
    • 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
  • 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.
    • I would like them to receive the email straight away on ticket save, so that we can action a resolution to the issue quickly.
  • Can you post your full SQL query?
  • 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")
    • 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,
  • 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.
Please log in to comment

Answers

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


.
Answered 11/11/2014 by: chris.poston
Senior Purple Belt

Please log in to comment
Answer this question or Comment on this question for clarity