/build/static/layout/Breadcrumb_cap_w.png

Create report to show tickets resolved by specific person or group

Is it possible to create a report that will show all tickets resolved (or last modified) by a certain person? Currently have reports to show resolved tickets by technician but a ticket can be resolved (or last updated) by someone but technician name is left under another person.


0 Comments   [ + ] Show comments

Answers (1)

Posted by: grayematter 9 years ago
5th Degree Black Belt
0

The following should get you started.  The limitation on date ranges and queue are arbitrary and only used to limit the result set.  This gives you the last user to touch a ticket.  So if a ticket is assigned to Jack but Jill adds the last comment, this will report Jill.

SELECT
    HD_TICKET.ID,
    HD_STATUS.NAME AS STATUS,
    HD_CATEGORY.NAME AS CATEGORY,
    S.FULL_NAME AS SUBMITTER_NAME,
    HD_TICKET.CREATED,
    HD_TICKET.TITLE,
    HD_TICKET_CHANGE.ID,
    HD_TICKET_CHANGE.COMMENT,
    HD_TICKET_CHANGE.USER_ID,
    LT.USER_NAME as 'Last touched by'
FROM
    HD_TICKET
        JOIN
    HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
        JOIN
    HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
        LEFT JOIN
    USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
        LEFT JOIN
    HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
        LEFT JOIN
    USER LT ON (LT.ID = HD_TICKET_CHANGE.USER_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_CHANGE.ID = (SELECT MAX(TC.ID)
FROM HD_TICKET_CHANGE TC
WHERE TC.HD_TICKET_ID = HD_TICKET.ID)
GROUP BY HD_TICKET.ID
ORDER BY HD_TICKET.ID

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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