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

Comments

Please log in to comment

Answers

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.
Answered 02/16/2015 by: h2opolo25
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity