/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


How could I change this report to report by ticket owner instead of queue?

10/03/2012 2734 views

Hello,

 

I am using the following report as a template for this...I have pulled it from the KACE KB. This works great as a way to track average ticket time by queue however for incentives I need this to track by ticket owner.

 

Any help on this would be great!

 

SELECT

(case when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),
INTERVAL 1 HOUR) then '0-1 hour'
when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL
24 HOUR) then '1-24 hours'
when
TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL
24 HOUR) then '>24 hours'
else 'error' end ) as CLOSE_GROUP,

count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET,
HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP

UNION
select 'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE from HD_TICKET
,HD_STATUS
where
HD_TICKET.HD_STATUS_ID=HD_STATUS.ID
and HD_STATUS.NAME='closed'
and TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
group by CLOSE_GROUP


Thanks,

 

Anthony

0 Comments   [ + ] Show comments

Comments


All Answers

0

I get "incorrect datetime value" and "truncated incorrect time value" when I run the query above, but in case this might help (i.e. give you something to work with) - here's the query with a column added for ticket owners and the results grouped on the owners.  Something to play with, I guess.

Hope that helps!

John

SELECT USER.USER_NAME as OWNER,
(case when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),
INTERVAL 1 HOUR) then '0-1 hour'
when
TIME_CLOSED<DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED), INTERVAL
24 HOUR) then '1-24 hours'
when
TIME_CLOSED>DATE_ADD(IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED),INTERVAL
24 HOUR) then '>24 hours'
else 'error' end ) as CLOSE_GROUP,
count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id) mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
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 TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here*/
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
UNION
select
USER.USER_NAME as OWNER,
'all' as CLOSE_GROUP,count(HD_TICKET.ID) as NUMBER_OF_TICKETS,
CONCAT((sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
div 86400),'d ',
TIME_FORMAT(SEC_TO_TIME(sum(TIME_TO_SEC(TIMEDIFF(TIME_CLOSED,IF(TIME_OPENED='0',TIME_CLOSED,TIME_OPENED))))/count(HD_TICKET.id)
mod 86400 ), '%kh %im %ss')) as AVG_TIME_TO_CLOSE
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 TIME_CLOSED<>0
and TIME_CLOSED>'2012-07-01' /*change the start date here*/
and TIME_CLOSED<'2012-09-30' /*change the end date here */
and HD_TICKET.HD_QUEUE_ID in (1) /*add queue numbers here*/
GROUP BY OWNER
Answered 10/08/2012 by: jverbosk
Red Belt

  • Confirmed working by vmescall per:

    http://www.itninja.com/question/custom-report-average-initial-response-time-grouped-by-technician-for-last-28-days

    John
  • Thanks!
 
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