/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Custom Report in Kace

10/06/2017 1029 views

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

Comments



Community Chosen Answer

1
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.

Answered 10/09/2017 by: chucksteel
Red Belt

All Answers

0
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.

**************************************************************************

Title: Open Tickets by Owner
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

Answered 10/06/2017 by: DirtySoc
Senior Yellow Belt

 
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