Custom Ticket Rule Help!
In a recent employee engagement survey we found out that most of our employees are ignoring the emails generated by KACE. My CIO has good intentions to remedy this, but we know of some limitations with KACE. His first recommendation won't work because KACE only generates plain text emails. He wanted to bold the comments so they would stand out from the other ticket change "garble." The next option was to engage professional support and have them make changes in the backend of the system. For what we were looking at, it didn't seem to make sense, especially with v6.0 around the corner. Just so all of you know, professional services won't stand by their work if it breaks as part of a version change. Even looking at v5.5 they couldn't guarantee that the +$500 config would stick. This leaves me with my third and favorite option, all of you here in the IT Ninja community.
Do you already have a ticket rule in place that can do the following or know how to make a rule that will perform the following?
1.) Email end users whenever a new comment is posted in a ticket.
2.) Exclude all other aspects of a typical email except for the comment text.
3.) Include a link to the ticket
If it cannot be done or you don't want to donate the time, it's all good. This is a last ditch effort before we wait it out to see if future versions will help. Thank you!
Community Chosen Answer
You can use a select statement like the following to trigger an email based on new comments on a ticket:
select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, HD_TICKET.TITLE, U1.USER_NAME as OWNER_NAME, U3.USER_NAME as LASTINPUTNAME, 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, STATE, U1.FULL_NAME as OWNER_FULLNAME, U1.EMAIL as OWNER_EMAIL, U2.USER_NAME as SUBMITTER_NAME, U2.FULL_NAME as SUBMITTER_FULLNAME, U2.EMAIL as SUBMITTER_EMAIL, U3.EMAIL as UPDATEREMAIL, U3.FULL_NAME as UPDATERNAME, UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP), TICKETCHANGE.COMMENT as COMMENT, TICKETINITIAL.COMMENT as INITIAL_COMMENT, TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION, HD_CATEGORY.CC_LIST AS CATEGORYCC, HD_CATEGORY.NAME AS CATEGORY_NAME, U2.LOCATION AS SUBMITTER_LOCATION, U2.WORK_PHONE AS SUBMITTER_WORK_PHONE, HD_PRIORITY.NAME AS TICKET_PRIORITY, HD_QUEUE.NAME AS QUEUE_NAME from ( HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY) JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID and TICKETCHANGE.ID=<CHANGE_ID> JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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 TICKETCHANGE.COMMENT != '' and TICKETCHANGE.DESCRIPTION not like "Changed ticket Machine from%" and TICKETCHANGE.DESCRIPTION not like "%Ticket Created%" and HD_TICKET.HD_QUEUE_ID = 9 and HD_STATUS.NAME != 'Closed'
For Email column use SUBMITTER_EMAIL
For the email portion you can constuct a link to the ticket with:
Note that we have SSL enabled on our KBOX, if you don't then use http instead of https.