/bundles/itninjaweb/img/Breadcrumb_cap_w.png
I am looking to create a report that tells me how long it took for a ticket owner to be changed from "unassigned" to someone else, on the previous days tickets.
We have an SLA that emailed tickets (which automatically log in the unassigned queue) must be picked up and assigned to an individual/team within 2 hours, but we don't have a report that can monitor this. 
It will need to be a daily report, running on the previous days logged tickets.
We need the report to show, Ticket ID, time created, time Owner was changed from "unassigned"

Ideally, we would like the report to only show tickets where the time owner was changed from unassigned to something else breached the 2 hour SLA
2 Comments   [ + ] Show comments

Comments

  • Try this:-

    select C.HD_TICKET_ID,
    H.CREATED,
    O.FULL_NAME,
    H.TITLE,
    TIMEDIFF(C.TIMESTAMP,H.CREATED) as TIME_TAKEN,
    C.TIMESTAMP as TIME_REASSIGNED from HD_TICKET_CHANGE C
    JOIN HD_TICKET H ON (H.ID = C.HD_TICKET_ID)
    JOIN USER O ON (O.ID = C.USER_ID)
    where description LIKE '%from "unassigned"%'
  • That didnt work. the report errored.
Please log in to comment


Answers

0
This should work:
SELECT T.ID, O.FULL_NAME as "Owner", T.TITLE, T.CREATED, 
C.TIMESTAMP as "Ticket Created", 
TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) as "Owner Changed (Minutes)"
FROM HD_TICKET T
LEFT JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID
LEFT JOIN USER O on O.ID = T.OWNER_ID
WHERE 
CF.FIELD_CHANGED = "OWNER_ID"
and CF.BEFORE_VALUE = 0
and TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) > 120
and DATE(T.CREATED) > DATE_SUB(NOW(), INTERVAL 1 DAY)
That will return for all tickets. If you just want tickets created via an email use this:
SELECT T.ID, O.FULL_NAME as "Owner", T.TITLE, T.CREATED, 
C.TIMESTAMP as "Ticket Changed", 
TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) as "Owner Changed (Minutes)"
FROM HD_TICKET T
JOIN HD_TICKET_CHANGE IC on IC.HD_TICKET_ID = T.ID and IC.ID = (select min(ID) from HD_TICKET_CHANGE where HD_TICKET_ID = T.ID)
LEFT JOIN HD_TICKET_CHANGE C on C.HD_TICKET_ID = T.ID
LEFT JOIN HD_TICKET_CHANGE_FIELD CF on CF.HD_TICKET_CHANGE_ID = C.ID
LEFT JOIN USER O on O.ID = T.OWNER_ID
WHERE 
IC.VIA_EMAIL != ""
and CF.FIELD_CHANGED = "OWNER_ID"
and CF.BEFORE_VALUE = 0
and TIMESTAMPDIFF(MINUTE, T.CREATED, C.TIMESTAMP) > 120
and DATE(T.CREATED) > DATE_SUB(NOW(), INTERVAL 1 DAY)

Answered 07/19/2018 by: chucksteel
Red Belt

Please log in to comment
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share