Custom Ticket Rule - Email Comment
I am working on a custom task that will email a customer if a ticket is not closed and over 7 days old.
I want the email to have the current status and last comment added to the ticket.
When I tested the sql in workbench it seemed to work correctly. When I ran it in the system to test it I received an email but it only contained the comment. I also noticed the run log is showing “> Executing Select Query...” but nothing else.
Below is the query I am testing this with, I was wondering if anyone sees anything wrong with it?
HD_TICKET.ID as TICKNUM,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
U1.FULL_NAME as OWNER_FULLNAME,
U2.USER_NAME as SUBMITTER_NAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
HD_TICKET.CREATED as TICKETCREATED,
from ( HD_TICKET,
JOIN HD_TICKET_CHANGE ON 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 = HD_TICKET_CHANGE.USER_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
HD_STATUS.NAME != 'Closed'
HD_TICKET_CHANGE.DESCRIPTION LIKE '%"Status Update"%'
For testing purposes I created a status called “Status Update” to email the ticket instead of a date range. I planned to add “and HD_TICKET.CREATED < DATE_SUB(NOW(), INTERVAL 7 DAY)” instead of the status if I can get this working correctly.
In the email body I was looking to include: