/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Would like to create a Montly report based total close tickets based on AD Location

10/21/2014 1549 views

Hello,

We have several remote offices and I am trying to capture a total closed ticket monthly report based on their AD location which is poplulated into KACE. I am unsure of which paramters to use. I believe I just beed to tweak the below highlighted in yellow


select COALESCE(NULLIF(U.CUSTOM_2, ''), 'EXTERNAL') as SUBMITTER_DEPT,
count(HD_TICKET.ID) AS Total_Requests
from HD_TICKET
left join USER U on U.ID=HD_TICKET.SUBMITTER_ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
group by SUBMITTER_DEPT
order by Total_Requests DESC

0 Comments   [ + ] Show comments

Comments


All Answers

0
Yes, you need to adjust the field from U.CUSTOM_2 to U.LOCATION.

select COALESCE(NULLIF(U.LOCATION, ''), 'EXTERNAL') as SUBMITTER_LOCATION,
count(HD_TICKET.ID) AS Total_Requests
from HD_TICKET
left join USER U on U.ID=HD_TICKET.SUBMITTER_ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
group by SUBMITTER_LOCATION
order by Total_Requests DESC

Answered 10/22/2014 by: chucksteel
Red Belt

  • Awesome. Thank you!
0
select U.LOCATION as OFFICE,
count(HD_TICKET.ID) AS Total_Requests
from HD_TICKET
left join USER U on U.ID=HD_TICKET.SUBMITTER_ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
where HD_STATUS.STATE = 'closed' and HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
group by OFFICE
order by Total_Requests DESC


-- Something like this should work. Change OFFICE to whatever name you want to show up in the report. You can also do a IFNULL(U.LOCATION, 'No Office') as OFFICE to show the text "No Office" if the field is Null.
Answered 10/22/2014 by: h2opolo25
Red Belt

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