/build/static/layout/Breadcrumb_cap_w.png

Report for tickets closed within same queue (First Level Resolution)

Hi, could some one assist us with creating an sql report? We have multiple queues in Kace representing different levels of IT L1, L2, L3. Our default queue where all tickets begin is the L1 queue. We are looking for a way to report tickets that were created in the L1 queue, were never transferred, and subsequently closed under the same L1 queue (First Level Resolution).  How would an sql query check that a ticket was never transferred.

0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 5 years ago
Red Belt
1

You can use a sub-select statement:

SELECT HD_TICKET.ID, TITLE, CREATED, TIME_CLOSED
FROM HD_TICKET
WHERE CREATED < date_sub(NOW(), INTERVAL 1 MONTH)
AND HD_QUEUE_ID = 2
AND (SELECT COUNT(ID) FROM HD_TICKET_CHANGE 
WHERE DESCRIPTION like "%Changed ticket Queue%"
AND HD_TICKET_ID = HD_TICKET.ID
GROUP BY HD_TICKET_ID) is null
GROUP BY HD_TICKET.ID

That query will show the tickets opened in a specific queue in the past month that were not transferred to another queue.


 
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