KACE Product Support

Here is the select statement for a rule which can be used to notify the user on comments:

 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'

The owner's email is returned as OWNER_EMAIL and the most recent comment is $comment for use in the email message.

You should only have to put a check box under the column for comment for Owner. It is found under Email on Events in the queue you are working in.

That should send an email to the owner of the ticket any time a comment is entered in the ticket. Even their own comment. 

Also check out

http://www.itninja.com/question/custom-ticket-rule-email-comment-1

http://www.itninja.com/question/trying-to-send-an-email-on-a-comment-in-a-ticket-not-working

Nshah,

Thanks for the response! I would use the built-in rules, but they only run on 15 min intervals and that doesn't always work out.

I've read the two links you sent, but they don't really seem to accomplish what I need to happen. What I'm trying to do is create a rule that will notice when "new" comments are added to a ticket and send them out to the owner. 

Writing the query to read the comments is something I can do, but I'm not sure how I can create a variable that will notice only when new comments are added to the ticket.  On ticket save, I want the query to look for any new comments added to the ticket.

Hopefully that makes more sense than the first post!