CODE WRITEN BY KACE SUPPORT FOR DEMONSTRATION PURPOSES ONLY. CLIENT CONFIRMS THE NEED FOR TESTING AND ACCEPTS ALL RESPONSABILITY FOR USE AND MISUSE OF CODE. KACE SHALL NOT BE HELD LIABLE FOR DAMAGE OR DOWN TIME RESULTING FROM USE OF THIS OR ANY CODE PRESENTED FOR PURPOSES OF TROUBLESHOOTING OR DEMONSTRATION.
This report organizes the tickets by owner and then by date range (i.e. 7-13 days of age, 14-20 days of age and 21+ days in age) with totals for each date range. For example...
Owner - John Doe
Tickets open between 7 and 13 days ID Title Created Submitter Status Date of Last Comment Last Comment ================================================== Total Tickets open between 7 and 13 days .... Tickets open between 14 and 20 days ID Title Created Submitter Status Date of Last Comment Last Comment ================================================== Total Tickets open between 14 and 20 days .... Tickets open 21+ days ID Title Created Submitter Status Date of Last Comment Last Comment ================================================== Total Tickets open 21+ days ....
To create this report open a SQL blank report from the Choose Action menu, then select New (SQL). Label this report anyting you wish.
Set the Break on Columns to:OWNER,TIME_OPENED
If you want place the checkbox next to Show Line Numbers.
Copy and past the following information in the SQL box.
select HD_TICKET.ID "Ticket", HD_TICKET.TITLE "Title", ifnull(O.FULL_NAME,' Unassigned') as "Owner", Ifnull(USER.FULL_NAME,'Unassigned') as "Submitter", HD_STATUS.NAME as "Status", DATE(HD_TICKET.CREATED) "Created", C.COMMENT "Comment", C.TIMESTAMP "Comment Date", CASE WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >21 THEN 'Tickets Open 21+ days' WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >14 THEN 'Tickets Open between 14 and 20 days' WHEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED))/60/60/24 >7 THEN 'Tickets Open between 7 and 13 days' END TIME_OPENED from HD_TICKET JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID=HD_TICKET.ID JOIN (Select MAX(ID) MAXID FROM HD_TICKET_CHANGE GROUP BY HD_TICKET_ID) MAXC ON C.ID=MAXID LEFT JOIN USER ON SUBMITTER_ID=USER.ID LEFT JOIN USER O ON OWNER_ID=O.ID 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 WHERE UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED)>(7*24*60*60) and HD_STATUS.STATE<>'Closed' ORDER BY O.USER_NAME ASC,(UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(HD_TICKET.CREATED)) ASC
