/build/static/layout/Breadcrumb_cap_w.png

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.


1 Comment   [ + ] Show comment
  • This is what Kace came up with for tickets submited today for all queues. I want to modify it to show last 7 days but only for queue ID=0. Also would like to know if this can break it down by category (ie Network Support, PC Support, Application support...)

    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 11 months ago

Answers (1)

Posted by: Hobbsy 10 months ago
Red Belt
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


 
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