/build/static/layout/Breadcrumb_cap_w.png
05/14/2019 158 views
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

Comments


All Answers

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.


Answered 05/15/2019 by: chucksteel
Red Belt