/build/static/layout/Breadcrumb_cap_w.png

Reporting Question dealing with Service Desk...

I am new to the the KACE world.  I came from ServicePro and used to have a SQL report that showed me the following:

Total number of Opened Requests, Closed Requests for a Given Time period and by Tech Support person and a total of the time period.

As well as...

A separate report that showed all open tickets for each Tech Support person.

Can anyone help me out with this?  Thanks

0 Comments   [ + ] Show comments

Answers (1)

Posted by: h2opolo25 9 years ago
Red Belt
1
Open Tickets for each tech...

select HD_TICKET.ID,
  HD_QUEUE.NAME as Team,
       HD_TICKET.TITLE ,
       HD_CATEGORY.NAME as CATEGORY,
  HD_PRIORITY.NAME as PRIORITY, 
  HD_STATUS.NAME as STATUS,
  ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
       USER.LOCATION as SUBMITTER_LOCATION,
  HD_TICKET.CC_LIST,
       HD_TICKET.DUE_DATE,
  DATE_FORMAT(HD_TICKET.CREATED, '%m-%d %H:%i') as OPENED_ON,
  DATE_FORMAT(HD_TICKET.MODIFIED, '%m-%d %H:%i') as LAST_UPDATED,
       CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.CREATED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.CREATED),
              TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.CREATED) - 1), ' days' 
              ) AS TIME_OPEN
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
left join USER on USER.ID = HD_TICKET.SUBMITTER_ID

where HD_STATUS.STATE rlike 'opened|stalled'

ORDER BY SUBMITTER_NAME

Closed Tickets past month by Queue...

select HD_TICKET.ID,
  HD_QUEUE.NAME as Queue,
       HD_TICKET.TITLE ,
       HD_CATEGORY.NAME as CATEGORY,
       HD_PRIORITY.NAME as PRIORITY, 
  ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME,
  HD_TICKET.CC_LIST,
  HD_TICKET.DUE_DATE ,
  DATE_FORMAT(HD_TICKET.CREATED, '%m-%d %H:%i') as OPENED_ON,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as CLOSED_ON,
       CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.CREATED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.CREATED) - 1), 'd ', 
              DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
              SEC_TO_TIME(TIME_TO_SEC(HD_TICKET.TIME_CLOSED)-TIME_TO_SEC(HD_TICKET.CREATED))),
              '%kh %im')) AS TIME_TO_CLOSE,
  HD_TICKET.SATISFACTION_RATING as SURVEY_RATING,
  HD_TICKET.SATISFACTION_COMMENT as SURVEY_COMMENT
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
ORDER BY Queue


Feel free to edit these to your liking.

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