Custom KACE Report
I have a custom report that I run against a specific set of users in our helpdesk team. I've tried to get the report to run like the canned report that kicks out open tickets by owner for the last 7 days, but the code I'm adding keeps giving an error. Can someone please look at my code here and propse a solution that would have it run for a 7 day cycle only as opposed to all open tickets?
SELECT HD_TICKET.ID,
HD_QUEUE.NAME,
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(OWN.FULL_NAME, ' 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
LEFT JOIN USER OWN
ON HD_TICKET.OWNER_ID = OWN.ID
JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID WHERE HD_STATUS.STATE = 'Open' OR HD_STATUS.STATE = 'Stalled'
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe','Darrell Dubois','Devon Rylski','Edward Abbott','Joe Vermeulen','Scott Legros')
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
Answers (1)
Is this what your looking for?
SELECT HD_TICKET.ID,
HD_QUEUE.NAME,
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(OWN.FULL_NAME, ' 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
LEFT JOIN USER OWN
ON HD_TICKET.OWNER_ID = OWN.ID
JOIN HD_QUEUE
ON HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
WHERE ( HD_STATUS.STATE = 'Open'
OR HD_STATUS.STATE = 'Stalled' )
AND OWN.FULL_NAME IN ( 'David Andreason', 'Tim Rowe', 'Darrell Dubois', 'Devon Rylski',
'Edward Abbott', 'Joe Vermeulen', 'Scott Legros' )
AND HD_TICKET.CREATED > DATE_SUB(Current_date(), INTERVAL 7 DAY)
ORDER BY OWNER_NAME,
HD_PRIORITY.ORDINAL,
HD_CATEGORY.ORDINAL,
HD_STATUS.ORDINAL,
HD_IMPACT.ORDINAL
