/build/static/layout/Breadcrumb_cap_w.png

Kace HelpDesk queue report help

Hello all.  We pulled a custom script from IT Ninja that creates a nice little report of our ITHelpDesk queue..  It includes fields like Ticket Title, Time closed, Time to Close, Category, Priority, etc....  We've since added a custom "check box" to the queue that we would like to include on this report and we're trying to figure out how to modify the SQL query to do so.

Custom check box info:

Name: CUSTOM_3

Label: After Hours

 

Existing SQL Query:

 

select HD_TICKET.ID,
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       DATE_FORMAT(HD_TICKET.TIME_CLOSED, '%m-%d %H:%i') as TIME_CLOSED,
       CONCAT(IF(TIME_TO_SEC(HD_TICKET.TIME_CLOSED) >= TIME_TO_SEC(HD_TICKET.TIME_OPENED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED),
              TO_DAYS(HD_TICKET.TIME_CLOSED) - TO_DAYS(HD_TICKET.TIME_OPENED) - 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.TIME_OPENED))),
              '%kh %im')) AS TIME_TO_CLOSE,
       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 = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL


0 Comments   [ + ] Show comments

Answers (2)

Posted by: Druis 8 years ago
Third Degree Green Belt
3
Add 'HD_TICKET.CUSTOM_FIELD_VALUE2 as AFTER_HOURS' anywhere between the 'Select' and 'from HD_TICKET'. 
Posted by: dhavalp@temptimecorp.com 6 years ago
White Belt
0
I like this report, how can I just get a report for a certain queue and the ticket number as well?

Comments:
  • The ticket number is already included as ID. To limit this to a specific queue add:
    and HD_TICKET.HD_QUEUE_ID = 1
    after this:
    HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)

    Be sure to use the ID for the queue you want. - chucksteel 6 years ago
 
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