/build/static/layout/Breadcrumb_cap_w.png

Kace Service Desk - SQL Query

I am trying to create a report that shows the total count of tickets submitted to a queue via email vs portal. Also, I want to filter out tickets that starts with title "Account Creation -", "Account Termination -" or "Visitor -" since they are auto generated by an external form. I tried this with canned reports and via wizard but wasn't successful. Anybody out there who could help me with a SQL query that does this?

Thanks in advance.
Sam


0 Comments   [ + ] Show comments

Answers (1)

Answer Summary:
Posted by: chucksteel 6 years ago
Red Belt
0

Top Answer

This should do the trick:
SELECT concat(month(CREATED), "/", year(CREATED)) as "Month/Year",
COUNT(CASE 
WHEN HD_TICKET_CHANGE.VIA_EMAIL = "" THEN 1
END) AS PORTAL,
COUNT(CASE
WHEN HD_TICKET_CHANGE.VIA_EMAIL != "" THEN 1
END) AS EMAIL,
COUNT(HD_TICKET_CHANGE.ID) as TOTAL
FROM HD_TICKET
JOIN HD_TICKET_CHANGE on HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID 
and HD_TICKET_CHANGE.ID = (SELECT MIN(ID) FROM HD_TICKET_CHANGE WHERE HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET.HD_QUEUE_ID = 2
and HD_TICKET.TITLE not like "Account Creation -%"
and HD_TICKET.TITLE not like "Account Termination -%"
and HD_TICKET.TITLE not like "Visitor -%"
GROUP BY YEAR(CREATED),MONTH(CREATED)
ORDER BY YEAR(CREATED),MONTH(CREATED)

Note that this will only return tickets with queue ID of 2, you will most likely need to change that to match the ID of the queue where these tickets are. It also breaks it down by month/year and lists the total.    

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