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
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share