/build/static/layout/Breadcrumb_cap_w.png

Custom report showing count by owner for picked up "unassigned" tickets

Hello,
What I want to accomplish is a report that will show me a count for each owner based on the number of "Unassigned" tickets that they pick up in a given time period.  I already have a total count by owner.  The problem is that sometimes the helpdesk will create their own tickets based on phone calls and I need to weed those out from the report.  I need to see who is picking up helpdesk tickets that are sent in via email and list the owner initially as "Unassigned".  I would assume that I would need to use HD_TICKET_CHANGE.OWNER_NAME variable.  I've spent a little over a day on this and getting nowhere.  Any help would be appreciated.

0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 10 years ago
Red Belt
1
I would start with the HD_TICKET_CHANGE_FIELD table and look for changes where the changed field is OWNER_ID and the previous value was 0 (for unassigned). This query pulls in the username and timestamp for the change:

SELECT HD_TICKET_CHANGE_FIELD.*, USER.USER_NAME, HD_TICKET_CHANGE.TIMESTAMP
FROM ORG1.HD_TICKET_CHANGE_FIELD
JOIN USER on USER.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID
WHERE FIELD_CHANGED = "OWNER_ID"
AND BEFORE_VALUE = 0;

Changing that to get a count and grouping by the after value (the new owner) gets you this:
SELECT COUNT(HD_TICKET_CHANGE_FIELD.ID), USER.USER_NAME
FROM ORG1.HD_TICKET_CHANGE_FIELD
JOIN USER on USER.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID
WHERE FIELD_CHANGED = "OWNER_ID"
AND BEFORE_VALUE = 0
AND TIMESTAMP > NOW() - INTERVAL 30 DAY
GROUP BY AFTER_VALUE

That query is also limited to the previous 30 days.

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