/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Custom report for hours worked by adjustments

06/22/2020 68 views

I need to create a report that presents the hours worked on each ticket, by adjustments as this is how we enter time. I need to display the ticket ID, the username, the category and subcategories, and a custom field that is a select box. Here is what I have been able to piece together so far.


SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(W.ADJUSTMENT_HOURS)  as "Work Hours"

FROM ORG1.HD_WORK W

JOIN USER on W.USER_ID = USER.ID

WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)

GROUP BY W.HD_TICKET_ID


Any help would be appreciated.

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

0
SELECT USER.USER_NAME, W.HD_TICKET_ID, SUM(W.ADJUSTMENT_HOURS)  as "Work Hours",
HD_CATEGORY.NAME, HD_TICKET.CUSTOM_FIELD_VALUE0
FROM ORG1.HD_WORK W
JOIN HD_TICKET on HD_TICKET.ID = W.HD_TICKET_ID
JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
JOIN USER on W.USER_ID = USER.ID
WHERE W.STOP > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY W.HD_TICKET_ID

In the query above I am selecting the first custom field, which is stored in CUSTOM_FIELD_VALUE0 (the db columns are 0 based). You will need to adjust that to match your custom field.


Answered 06/24/2020 by: chucksteel
Red Belt

  • This is extremely helpful!

    I ran this query and wasn't getting the custom field I was looking for and realized that it's the custom_2 under the ticket layout fields that I'm trying to grab. I tried to just change the value to 2 instead of 0 but that created an error. Also is there a way to grab the Title of the ticket as well?
    • Custom field 2 will be CUSTOM_FIELD_VALUE1.
      The title of the ticket is stored in the TITLE column, so you can add HD_TICKET.TITLE to the list of columns selected.
      • That did the trick. I appreciate all you help, thank you!

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