/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Report to list % of tickets closed within 24 hours of being created

01/20/2015 2049 views

I am needing a report that will show tickets with a specific title and the % of those tickets that have been closed within 24 hours of being created for the previous quarter only.  The tickets are part of a process so the title of the tickets will always be the same.

 

Thank you.

0 Comments   [ + ] Show comments

Comments


All Answers

0
This worked for me.  It results in a report showing the last 90 days, and each column is divided by the total tickets for the queue.  You can change the interval to anything you want.  180 for six months and so on. I run it from MySQL Workbench. After you run it, export it to Excel, then format fields for percentages.  

If you want to use it within KACE you need to remove the SET variable at the top and define @days as 90 through the script.

Good luck.

SET @days = 180;Select a.QueueName as 'Queue Name', (c.4Tickets/b.AllTickets) as 'Within 4 Hours', 	(d.8Tickets/b.AllTickets) as '4 to 8 Hours', (e.24Tickets/b.AllTickets) as '8 to 24 Hours', (f.48Tickets/b.AllTickets) as '24 to 48 Hours', 	(g.72Tickets/b.AllTickets) as '48 to 72 Hours', (h.1WeekTickets/b.AllTickets) as '72 Hours to 1 Week', 	(i.RemainingTickets/b.AllTickets) as 'Greater than 1 Week', b.AllTickets as 'Total Tickets'from-- Select Queues(select     HQ.ID, HQ.NAME as QueueNameFROM HD_QUEUE HQWHERE HQ.NAME != 'Project' AND HQ.NAME != 'Change Control' AND HQ.NAME != 'Help Desk (obsolete)' AND HQ.NAME != 'Development' AND HQ.NAME != 'Accela Change Control') aleft join-- All tickets during period(select HD_QUEUE_ID, count(HD_TICKET.ID) as AllTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY))GROUP BY HD_QUEUE_ID) bon a.ID = b.HD_QUEUE_IDleft join-- Tickets closed within 4 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 4TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 0 and 8)GROUP BY HD_QUEUE_ID) con a.ID = c.HD_QUEUE_IDleft join-- Tickets closed within 8 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 8TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 0 and 8)GROUP BY HD_QUEUE_ID) don a.ID = d.HD_QUEUE_IDleft join-- Tickets closed within 24 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 24TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 8 and 24)GROUP BY HD_QUEUE_ID) eon a.ID = e.HD_QUEUE_IDleft join-- Tickets closed between 24 and 48 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 48TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 24 and 48)GROUP BY HD_QUEUE_ID) fon a.ID = f.HD_QUEUE_IDleft join-- Tickets closed between 48 and 72 hours(select HD_QUEUE_ID, count(HD_TICKET.ID) as 72TicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 48 and 72)GROUP BY HD_QUEUE_ID) gon a.ID = g.HD_QUEUE_IDleft join-- Tickets closed between 72 hours and 1 week(select HD_QUEUE_ID, count(HD_TICKET.ID) as 1WeekTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 BETWEEN 72 and 168)GROUP BY HD_QUEUE_ID) hon a.ID = h.HD_QUEUE_IDleft join-- Tickets closed greater than 1 week(select HD_QUEUE_ID, count(HD_TICKET.ID) as RemainingTicketsFROM HD_TICKETWHERE (HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL @days DAY)) AND (select time_to_sec(timediff(HD_TICKET.TIME_CLOSED, HD_TICKET.TIME_OPENED)) / 3600 > 168)GROUP BY HD_QUEUE_ID) ion a.ID = i.HD_QUEUE_IDOrder By a.QueueName;

Answered 01/26/2015 by: Krexin
White Belt

Don't be a Stranger!

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

Sign up! or login

View more:

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