-
Title
Report for tickets created in the last 7 days that are now closed for a specific label -
Description
This is a custom report that shows all of the tickets that have been opened and closed in a 7 day period, but only for specific offices, where the offices are represented by labels on the Machine and the Machine field in the ticket is populated.
-
Resolution
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.
To personalize this for your environment, look at the line that reads L.NAME='Florida' and change "Florida" to the label that you want to use.
Also, when using the K1000 appliance to run this report put the LABEL_NAME in the break on columns section.
select HD_TICKET.ID, HD_TICKET.TITLE , HD_TICKET.DUE_DATE , CONCAT(TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED), 'd ', DATE_FORMAT(ADDTIME("2000-00-00 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, L.NAME LABEL_NAME from HD_TICKET JOIN MACHINE ON HD_TICKET.MACHINE_ID=MACHINE.ID JOIN MACHINE_LABEL_JT ML ON MACHINE.ID=ML.MACHINE_ID JOIN LABEL L ON L.ID=ML.LABEL_ID and L.NAME='Florida' LEFT JOIN USER ON SUBMITTER_ID=USER.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 HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY L.NAME,SUBMITTER_NAME,HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL