Service Desk Rule to assign Owner on Close
We have a custom service desk rule which checks for any tickets that are not assigned to anyone in 48 hours of creation and emails IT team to take action. The problem is that the rule does not check if the ticket is already in Closed state. At first I tried to have the rule check for ticket state before checking if the ticket is assigned but after few hours of failure, I decided to create another rule that would assign the user closing the ticket as the owner. This rule would help with "closed" tickets that were never assigned to anyone (on ticket close). I will post my "unassigned" rule here if anyone is interested. Can anyone help with the new rule or modifying existing? Thank you!
SELECT 'email@example.com' AS SUPPORT, T.ID AS ID, T.TITLE AS ISSUE, S.FULL_NAME AS SUBMITTER, T.CREATED AS Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 48 HOUR)
AND T.CREATED > SUBDATE(NOW(), INTERVAL 49 HOUR)
AND T.OWNER_ID = 0
AND T.HD_QUEUE_ID = 1
I am thinking if this is working for you well otherwise, all you need is to add the line "AND T.TIME_CLOSED is null" in your WHERE clause (at the end should be fine)
I believe this will ensure is it only selecting tickets where the time closed is empty. If it still does not work though you could try T.TIME_CLOSED = ''.
One of those should get you hat you are after.
If you check the HD_TICKET table there is probably a field HD_TICKET.HD_STATUS_ID that holds the ID that says if the ticket is closed. If you check the HD_STATUS table, in there you will find which status is is used for that queue.
Then just add to your WHERE statement HD_TICKET.HD_STATUS_ID != 8 for example, that will stop the rule running on closed tickets.
To do a dump of a table like the status table setup an SQL report
SELECT * FROM HD_STATUS
You will also need to add the field in your SELECT statement
SELECT 'firstname.lastname@example.org' AS SUPPORT, T.ID AS ID, T.TITLE AS ISSUE, S.FULL_NAME AS SUBMITTER, HD_TICKET.HD_STATUS_ID, T.CREATED AS Created FROM HD_TICKET