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)
Please log in to answer
Posted by:
h2opolo25
9 years ago
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.
