/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Stalled tickets by owners report - editing.

06/24/2015 667 views
Hi,

In Kace there is a "stalled tickets by owners" report, we currently have two queues in kace for the servicedesk. This report is bringing back both queues. Is there a way to filter it so it only brings back the IT queue.

My Sql isn't the greatest....so any help is welcome


Thanks

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Hi Hobbsy, Thanks that worked perfectly.

Answer Chosen by the Author

1
Open your report "Stalled Tickets by Owner" and duplicate the report.

Open your "Copy of Stalled Tickets by Owner" report (feel free to rename)

Into the report SQL text add the highlighted lines:

 select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE,
       HD_TICKET.HD_QUEUE_ID,
       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 = 'stalled' and HD_TICKET.HD_QUEUE_ID = '1'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
The top insert will select the field in the ticket table that holds the Queue Identifier and the second insert in the "where" section holds the numeric identifier.

To find your queue numeric identifier go to Service Desk>Configuration and Queues

Hover over your queue name in the list shown and in the bottom left hand corner text will appear giving the ID number

for example

Answered 06/24/2015 by: Hobbsy
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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