/build/static/layout/Breadcrumb_cap_w.png

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!


SELECT 'it@abc.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




0 Comments   [ + ] Show comments

Answers (3)

Answer Summary:
Posted by: Michelle.ashby 1 week ago
Orange Belt
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.


Comments:
  • This worked perfectly, I like your idea. I ended up using T.TIME_CLOSED = ''. Thank you! - rbaranowicz 4 days ago
Posted by: Hobbsy 1 week ago
Red Belt
0

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 1 week ago
Posted by: Hobbsy 1 week ago
Red Belt
0

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 4 days ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ