Asistance with Service Desk Reports
I am trying to generate a report for tickets opened in the last 7 days (regardless of current state) for specific queue as well as a report of tickets closed in the last 7 days. I have looked into creating the report using the wizard but haven't been very successful and I am not much of a SQL person. Any assistance would be greatly appreciate it.
Answers (1)
Here you go, I created these quickly using the reporting wizard and then cracked open the SQL to add in the Queue ID, so just replace the '1' in the WHERE statement with your Queue ID
So for your logged last 7 days
SELECT Q.NAME AS QUEUE_NAME, HD_TICKET.ID, IF(HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.IS_PARENT, HD_SERVICE_STATUS.NAME, HD_STATUS.NAME) AS STATUS_NAME, HD_TICKET.CREATED, HD_TICKET.TIME_CLOSED FROM HD_TICKET JOIN HD_QUEUE Q ON Q.ID = HD_TICKET.HD_QUEUE_ID LEFT JOIN HD_SERVICE_STATUS ON HD_TICKET.HD_USE_PROCESS_STATUS and HD_TICKET.HD_SERVICE_STATUS_ID and HD_SERVICE_STATUS.ID = HD_TICKET.HD_SERVICE_STATUS_ID LEFT JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (((TIMESTAMP(HD_TICKET.CREATED) <= NOW() AND TIMESTAMP(HD_TICKET.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY)))) AND HD_TICKET.HD_QUEUE_ID = 1 ORDER BY ID, STATUS_NAME
And for closed I did the same, so again, just change the Queue ID in the where statement
select HD_TICKET.ID,
HD_TICKET.TITLE ,
HD_TICKET.DUE_DATE ,
HD_TICKET.TIME_CLOSED 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 7 DAY) and HD_TICKET.HD_QUEUE_ID = 1
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
select DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as 'Created' ,HD_PRIORITY.NAME as 'Priority' ,CONCAT(IF(Q.TICKET_PREFIX = 'TICK:','TICK:', Q.TICKET_PREFIX),LPAD(HD_TICKET.ID, 4, '0')) as 'Number' ,HD_TICKET.TITLE as 'Title' ,HD_CATEGORY.NAME as 'Category' ,HD_STATUS.NAME as 'Status' ,if((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as 'Submitter' ,Q.NAME as 'Queue' ,if((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as 'Owner' ,HD_TICKET.DUE_DATE as 'Due' ,DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as 'Modified' ,M1.NAME as 'Device' from (HD_TICKET)
LEFT JOIN HD_STATUS on (HD_TICKET.HD_STATUS_ID = HD_STATUS.ID)
LEFT JOIN HD_IMPACT on (HD_TICKET.HD_IMPACT_ID = HD_IMPACT.ID)
LEFT JOIN HD_CATEGORY on (HD_TICKET.HD_CATEGORY_ID = HD_CATEGORY.ID)
LEFT JOIN HD_PRIORITY on (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
LEFT JOIN HD_TICKET_APPROVAL_PARENT HTAP on HTAP.HD_TICKET_ID = HD_TICKET.ID
LEFT JOIN HD_SERVICE_STATUS HSS on HD_TICKET.HD_SERVICE_STATUS_ID = HSS.ID
LEFT JOIN HD_SERVICE_TICKET HST on HST.ID = HD_TICKET.SERVICE_TICKET_ID
LEFT JOIN HD_SERVICE HS on HS.ID = HST.HD_SERVICE_ID
LEFT JOIN HD_SERVICE_TYPE HSTT on HSTT.ID = HS.HD_SERVICE_TYPE_ID
LEFT JOIN HD_TICKET_TEMPLATE on HD_TICKET_TEMPLATE.ID = HD_TICKET.TICKET_TEMPLATE_ID and HD_TICKET_TEMPLATE.QUEUE_ID = HD_TICKET.HD_QUEUE_ID where 1 = 1 AND (( HD_TICKET.HD_QUEUE_ID in (2,13,4,5,14,3,9,15,12,11,1) ) OR ( (HD_TICKET.OWNER_ID = 4060) or (HD_TICKET.APPROVER_ID = 4060) or (HD_TICKET.SUBMITTER_ID = 4060) or
EXISTS (
select *
from HD_TICKET_APPROVAL HTA,
HD_TICKET_APPROVAL_STAGE HTAS
where HTA.HD_TICKET_ID = HD_TICKET.ID
and HTA.HD_TICKET_ID = HTAS.HD_TICKET_ID
and HTA.USER_ID = 4060
and HTA.APPROVAL = 'info'
and HTAS.APPROVAL_DATE = '0000-00-00 00:00:00'
) )) and HD_TICKET.CREATED > '2023-08-28'
and HD_TICKET.CREATED < '2023-08-29' order by HD_TICKET.CREATED desc - raul102801 8 months ago