Would like to create a Montly report based total close tickets based on AD Location
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
Answers (2)
Please log in to answer
Posted by:
chucksteel
10 years ago
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
Comments:
-
Awesome. Thank you! - gambitz 10 years ago
Posted by:
h2opolo25
10 years ago
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.
Comments:
-
thank you! - gambitz 10 years ago
