Service Desk Rule to assign Owner on Close
Hello,
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!
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
Answers (3)
Top Answer
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.
Comments:
-
This worked perfectly, I like your idea. I ended up using T.TIME_CLOSED = ''. Thank you! - rbaranowicz 3 years ago
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
Comments:
-
Thanks for the suggestion. I wanted to try this method first since I've never ran an SQL Report. I did a dump of the status table and found that the ID I need is 2. I added AND HD_TICKET.HD_STATUS_ID != 2 but this generates the error below.
mysqli error: [1054: Unknown column 'HD_TICKET.HD_STATUS_ID' in 'where clause']
My rule now is as:
SELECT 'it@company.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 168 HOUR)
AND T.OWNER_ID = 0
AND HD_TICKET.HD_STATUS_ID != 2
AND T.HD_QUEUE_ID = 1 - rbaranowicz 3 years ago
You will also need to add the field in your SELECT statement
SELECT 'it@abc.com' 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
Comments:
-
I might be doing something else wrong because this doesn't work for me, the other method did so I am good now. Thanks for your time! - rbaranowicz 3 years ago
