Miscellaneous : Trying to send an email on a Comment in a ticket

Trying to send an email on a Comment in a ticket - not working!

I’ve been trying to use a slightly modified KACE KB ticket rule to notify a ticket owner when a ticket has been assigned to them. Why, because when a ticket rule assigns the ticket to an owner the built-in emailing mechanism (email owner on every change) doesn’t work. I took the canned rule and changed the C.DESCRIPTION LIKE to C.COMMENT LIKE and that’s it. When I test this query, by adding an appropriate Change ID in place of <CHANGE_ID> the query returns the desired results. I need to use the COMMENT because there is no DESCRIPTION added to the history of the ticket for the actions of the rule, so each rule I create adds a comment.

I have this canned email rule in use throughout the kbox queues but they are all based on C.DESCRIPTION or some other data set (CATEGORY). I’ve also ordered this rule to the very bottom of the list so it is the last rule to run.

Any suggestions would be most appreciated as I have a queue owner whining loudly about his team’s SLAs because they don’t get notified when a ticket is assigned to them.

Thank you

***********query**************

SELECT
-- 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 https://aerocare.panasonic.aero/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
-- 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
-- -- example of static distribution list
'aerocare@panasonic.aero' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
AND C.ID=<CHANGE_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
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE
C.COMMENT LIKE 'Owner Assigned:%'
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1

0 Comments [ + ] Show comments

Answers (3)

Answer Summary:

Posted by: GeekSoldier 11 years ago

You could also use one of the checkboxes to notify the owner, submitter, or approver on any ticket change.

Comments:

The queue is set to Notify Owner on any change but when a ticket rule sets the owner the kbox doesn't see a "user" making the change and it doesn't send the email. It's been like that since we started with it on 5.2 - jmarotto 11 years ago
- I follow you now. Sorry for the misunderstanding. SQL away! - GeekSoldier 11 years ago

Posted by: chucksteel 11 years ago

Instead of looking for the comment on the change you could also look for the owner changing. You would need to add the following join:

 JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> AND CF.FIELD_CHANGED = OWNER_ID

Then your where statement becomes

WHERE CF.BEFORE_VALUE != CF.AFTER_VALUE

That should only match tickets where the owner has changed.

Comments:

We though about this Chuck but the queue is set to Notify Owner on Any Change and we kind of need it like that. Setting the rule like this would send two emails. - jmarotto 11 years ago
I too am trying to use this rule. I get:
12/20/2013 13:13:16> Starting: 12/20/2013 13:13:16
12/20/2013 13:13:16> Executing Select Query...
12/20/2013 13:13:16> selected 0 rows

when I try to run this rule based on chucksteel's suggestions. Does anyone have anymore information on this? I was using OWNER_EMAIL for the email column. - Kevino2010 11 years ago
- Can you post your select query? - chucksteel 11 years ago
  - SELECT
    -- 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://helpdesk/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
    
    -- 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
    -- -- example of static distribution list
    'xx@xx.com' AS NEWTICKETEMAIL -- $newticketemail
    
    FROM HD_TICKET
    JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> AND CF.FIELD_CHANGED = OWNER_ID
    /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
    AND C.ID=<CHANGE_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
    /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
    /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
    CF.BEFORE_VALUE != CF.AFTER_VALUE
    /* this is necessary when using group by functions */
    GROUP BY HD_TICKET.ID
    HAVING 1=1 - Kevino2010 11 years ago
  - The code looks fine. I'd recommend turning on the option to send the query results to email and change the owner on a ticket to see what it shows you. - chucksteel 11 years ago
  - If I change the owner from unassigned to an owner, I get:
    'Executing Select Query' and then 'selected 0 rows' and I dont get a "send the query results" email.
    
    On the other hand, if I change the ticket from an Owner to Unassigned, I get a "send the query results" email with all of the ticket information for that one ticket that I made the change on, but the email is not working because there is no email designated for the 'Owner User Unassigned'.
    13:47:56> Sending ticket notifications...
    12/23/2013 13:47:56> sent mail to 0 of 1
    12/23/2013 13:47:56> Ending: 12/23/2013 13:47:56
    It's like I typed a value backwards or something. I'm using the select query posted above. - Kevino2010 10 years ago
  - Thank you so much Chuck! - Kevino2010 10 years ago
Chuck,
I was able to get this to work using the information that you posted in: http://www.itninja.com/question/custom-email-to-submitter-on-owner-change
Thanks for your help.
How difficult would it to have a similar rule which emails the Before owner and lets them know that the ticket is no longer assigned to them? - Kevino2010 10 years ago
- Here's my select statement for a rule which does just that:
  
  select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
  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,
  U4.EMAIL as PREVIOUS_OWNER_EMAIL,
  U5.EMAIL as NEW_OWNER_EMAIL
  from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
  JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
  and HD_TICKET_CHANGE.ID=<CHANGE_ID>
  JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
  and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_ID'
  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 USER U4 on U4.ID = HD_TICKET_CHANGE_FIELD.BEFORE_VALUE
  LEFT JOIN USER U5 on U5.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
  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 (( (1 in (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)) ) and HD_TICKET.HD_QUEUE_ID = 2 and HD_TICKET_CHANGE.USER_ID != U4.ID)
  
  Note that the HD_QUEUE_ID needs to be changed from 2 to match your queue ID. Also, this rule won't match tickets if you change the owner to someone else, only if another technician makes the change so keep that in mind when testing.
  
  I have the rule send an email with the following options:
  
  Subject: Ticket $id has been transferred to $new_owner_email
  Email Column: PREVIOUS_OWNER_EMAIL
  Email Body: Ticket $id, $title, has been transferred to $new_owner_email. - chucksteel 10 years ago
  - Thanks Chuck!! That is exactly what I was looking for. You the man - Kevino2010 10 years ago
  - Hey Chuck, How would I pull the updater email in this query? I have been trying for a couple of days to figure it out, but I am stuck. Any advice? - Kevino2010 10 years ago
  - You need to add two things:
    Add this in the joins section, beneath the U5 join (I personally wouldn't use these generic U1, U2, etc aliases but that's what the wizard used so I'm sticking with that convention).
    LEFT JOIN USER U6 on U6.ID = HD_TICKET_CHANGE.USER_ID
    
    Further up in the select fields add this below the U5.EMAIL line, be sure to add at the end of U5.EMAIL as NEW_OWNER_EMAIL or the syntax will break:
    U6.EMAIL as UPDATER_EMAIL - chucksteel 10 years ago

Posted by: Wildwolfay 11 years ago

When you set this canned ticket rule, you set NEWTICKETEMAIL as the alias for that particular email. You understand that the EMAIL column of the ticket rule SEND RESULTS FOR EACH ROW is hard coded with a $?

When you do a RUN NOW, what does the run log say for your rule?

Comments:

Side note: If you have your KACE set to auto assign, it doesn't place a comment in the ticket comment section that says: Owner Assigned: so your where statement would find nothing unless it's manually assigned to someone. - Wildwolfay 11 years ago
The Run Log shows -
05/01/2013 08:13:41> Starting: 05/01/2013 08:13:41
05/01/2013 08:13:41> Executing Select Query...
05/01/2013 08:13:41> selected 0 rows - jmarotto 11 years ago
Every rule we run (100s in 11 queues) adds an Owners Only Comment to the ticket so we know what rule made a change. - jmarotto 11 years ago
This has more to do (I think) with the System making the change and not a logged in user. I just can't figure out what portion of the query to modify so that it's not checking for the User who made the change. I don't really care who made the change, the rule simply needs to recognize a change has been made and that change resulted in the COMMENT being added. - jmarotto 11 years ago

Answer this question

Posted by:

View more:

Miscellaneous Questions

question