Custom Report in Kace
Hello,
I am looking for some help on a report. I am trying to create a report of tickets by team that are not closed, which will show the ticket number, ticket owner and the date and time they were put in a status of “Assigned” then the date and time they were placed in a status of “Active”. Any help would be appreciated.
0 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
chucksteel
7 years ago
This query uses the HD_TICKET_CHANGE table to find when the status changes were logged:
SELECT T.ID, O.FULL_NAME, ASSIGNED.TIMESTAMP as "Assigned", ACTIVE.TIMESTAMP as "Active"
FROM HD_TICKET T
JOIN USER O on O.ID = T.OWNER_ID
JOIN HD_TICKET_CHANGE ASSIGNED on ASSIGNED.HD_TICKET_ID = T.ID and ASSIGNED.DESCRIPTION like "%Status%to%Assigned%"
JOIN HD_TICKET_CHANGE ACTIVE on ACTIVE.HD_TICKET_ID = T.ID and ACTIVE.DESCRIPTION like "%Status%to%Active%"
JOIN HD_STATUS on HD_STATUS.ID = T.HD_STATUS_ID
WHERE T.HD_QUEUE_ID = 2
and HD_STATUS.STATE != 'closed'
Note that this is for tickets in queue 2, you will most likely need to change that for your queue.
Posted by:
DirtySoc
7 years ago
Hey! You should be able to get what you want using the create report Wizard. I looked through the reports that I have and noticed this one listed below. It shows tickets with the opened status across all queues in the Service Desk.
**************************************************************************
Category: Service Desk
Break on Columns: OWNER_NAME
SQL:
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE,
CONCAT(IF(TIME_TO_SEC(NOW()) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED) - 1), 'd ',
DATE_FORMAT(ADDTIME("2000-01-01 00:00:00",
SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
'%kh %im')) AS TIME_OPEN,
HD_PRIORITY.NAME as PRIORITY,
HD_CATEGORY.NAME as CATEGORY,
HD_STATUS.NAME as STATUS,
HD_IMPACT.NAME as IMPACT,
MACHINE.NAME as MACHINE_NAME,
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
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_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
