Send Email if a ticket is not opened.
Anyone know how to get a "new ticket" to send an email to a supervisor,
if it hasn't been opened by its "owner" for a specified amount of time ?
Is this done via a ticket rule? Not sure here?
Maybe you're looking for something like that?
This select statement should identify tickets with a status of "New" that have not been updated by their owner in the past four hours:
SELECT T.ID, T.TITLE, T.CREATED, LAST_CHANGE.TIMESTAMP,
MANAGER.EMAIL as MANAGER_EMAIL,
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = T.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID
and HD_TICKET_CHANGE.USER_ID = T.OWNER_ID)
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join USER OWNER on OWNER.ID = T.OWNER_ID
left join USER MANAGER on MANAGER.ID = OWNER.MANAGER_ID
OWNER.ID != 0
and HD_STATUS.NAME = 'New'
and LAST_CHANGE.TIMESTAMP < NOW() - INTERVAL 4 HOUR
It includes a column for MANAGER_EMAIL which will be used for the Email each recipient in query results option. There are some other columns selected that can be used as variables in the email to be sent.
You can change the interval by modifying the last line "INTERVAL 4 HOUR" (and yes, it is supposed to be singular for the unit).
Set the rule to run on a schedule and the manager will start receiving notifications. If you don't want the manager to receive multiple notifications for a ticket, then you will need to establish a way to know if the notice has already been sent, perhaps with a custom field that is updated.