Does anyone have a query written that will show when new comments are added to a ticket? I'm looking to have an email sent to the owner if the submitter adds new comments. I've ran into an issue that my end users are sometime going back into the ticket and adding more comments, but the tech doesn't know that if he's not paying attention to the comments. I know the KACE system has one built into it, but the 15 min waiting around to see if it’s going to work is too slow IMO. BTW, those built in rules should be modifiable, just saying J!
Having a rule that will auto notify the owner of the ticket should resolve the issue for me.
Community Chosen Answer
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.