/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


SQL Reporting - Average Ticket Time Opened

03/24/2015 1912 views
Hi,

 I found this report in another thread and it does exactly what I want it to do, except I don't have anything in the Custom Field 13 so the "Average Time Opened" part of this report does not work. What do I need to do/add to the Custom Field 13 to make this report work correctly? Thanks in advance!

SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS,  AVG(HD_TICKET.CUSTOM_FIELD_VALUE13) as 'Average Time Opened'
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
Answer Summary:
0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

1
This query gets the time a ticket is open by subtracting the TIME_OPENED from TIME_CLOSED and then formats it in a human readable format:

SELECT USER.USER_NAME as OWNER, count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
TIME_FORMAT(SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, 
                          TIME_OPENED, 
                          TIME_CLOSED)
           )),'%Hh %im') AS "Average Time"
from HD_TICKET
JOIN HD_STATUS on (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN USER on (USER.ID = HD_TICKET.OWNER_ID)
where HD_STATUS.NAME='closed'
and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 30 DAY)
and HD_TICKET.HD_QUEUE_ID in (2) /*add queue numbers here*/
GROUP BY OWNER

Answered 03/25/2015 by: chucksteel
Red Belt

  • That worked perfectly, Exactly what i needed!! Thank you!

All Answers

0
Whoever created this report must have had a custom rule in place to keep the time opened in the custom field. I believe you can use AVG(HD_TICKET.TIME_OPENED) instead but I don't have access to the database to check that at the moment.

Answered 03/25/2015 by: chucksteel
Red Belt

  • Using that does return a value for Average Time, but it looks like this: 17050262763383.0000

    Is there a way to put that in a Minute/Day/Hour format?

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