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

Please log in to comment

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!
Please log in to comment
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

Please log in to comment
Answer this question or Comment on this question for clarity