/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Custom report for all ticket touches

11/21/2018 470 views

I'm trying to create a custom report that will tell me the number or times a User made any change to any ticket within a time frame. Essentially get work hours for an entire Queue based on ticket touches... However, I'm trying to do this by blending other posts, which has led to a great deal of failure on my part. 


Anyone done something similar they might share? 

Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • SELECT HD_QUEUE.NAME as Queue, HD_CATEGORY.NAME AS Category,
    (SELECT COUNT(ID) FROM HD_TICKET where
    HD_CATEGORY_ID = HD_CATEGORY.ID
    and HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)GROUP BY HD_CATEGORY.ID) as Tickets,
    USER.FULL_NAME as "Default Owner"
    FROM HD_CATEGORY
    LEFT JOIN HD_QUEUE on HD_QUEUE.ID = HD_CATEGORY.HD_QUEUE_ID
    LEFT JOIN USER on USER.ID = HD_CATEGORY.DEFAULT_OWNER_ID

    GROUP BY HD_CATEGORY.ID
    ORDER BY HD_QUEUE.NAME, HD_CATEGORY.NAME


    This is somewhat what I want, and mostly what I was basing my guesses off of, but I don't actually care for Category info. All attempts at editing this to show Count if tickets edited by a user in a particular Queue fail to even Save.

Answer Chosen by the Author

1

This should work for you:

SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as '# Changed',
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as'Tickets'
FROM USER
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
and USER.USER_NAME = "username"
GROUP BY USER.FULL_NAME

This uses username for the query instead of user full name, just set it to the appropriate username. It also gives a comma separated list of the tickets that the user changed.

Answered 11/26/2018 by: chucksteel
Red Belt

  • This should do quite nicely. I greatly appreciate it, Sir.
  • I have added

    || HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
    and USER.USER_NAME = "2nd username"

    Following the initial user to expand to multiple people. Seems to be working well. Thanks again.

All Answers

0

If you are reporting on multiple users (which is what your comment suggests), then this query reports on users in a given label (User Services, in this example):

SELECT USER.FULL_NAME, COUNT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID)) as "Tickets Changed",
GROUP_CONCAT(DISTINCT(HD_TICKET_CHANGE.HD_TICKET_ID))
FROM USER
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.USER_ID = USER.ID
WHERE LABEL.NAME = "User Services"
and HD_TICKET_CHANGE.TIMESTAMP > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY USER.FULL_NAME
ORDER BY USER.FULL_NAME


Answered 11/26/2018 by: chucksteel
Red Belt

  • That is much more efficient. Thanks again hahah

Don't be a Stranger!

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

Sign up! or login

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