Hello,

I am looking for some help on a report.  I am trying to create a report of tickets by team that are not closed, which will show the ticket number, ticket owner and the date and time they were put in a status of “Assigned” then the date and time they were placed in a status of “Active”. Any help would be appreciated. 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1
This query uses the HD_TICKET_CHANGE table to find when the status changes were logged:
SELECT T.ID, O.FULL_NAME, ASSIGNED.TIMESTAMP as "Assigned", ACTIVE.TIMESTAMP as "Active"
FROM HD_TICKET T
JOIN USER O on O.ID = T.OWNER_ID
JOIN HD_TICKET_CHANGE ASSIGNED on ASSIGNED.HD_TICKET_ID = T.ID and ASSIGNED.DESCRIPTION like "%Status%to%Assigned%"
JOIN HD_TICKET_CHANGE ACTIVE on ACTIVE.HD_TICKET_ID = T.ID and ACTIVE.DESCRIPTION like "%Status%to%Active%"
JOIN HD_STATUS on HD_STATUS.ID = T.HD_STATUS_ID
WHERE T.HD_QUEUE_ID = 2
and HD_STATUS.STATE != 'closed'
Note that this is for tickets in queue 2, you will most likely need to change that for your queue.

Answered 10/09/2017 by: chucksteel
Red Belt

Please log in to comment

Answers

0
Hey! You should be able to get what you want using the create report Wizard. I looked through the reports that I have and noticed this one listed below. It shows tickets with the opened status across all queues in the Service Desk.

**************************************************************************

Title: Open Tickets by Owner
Category: Service Desk
Break on Columns: OWNER_NAME
SQL:
select HD_TICKET.ID, 
       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((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 = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL

Answered 10/06/2017 by: DirtySoc
Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share