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
Thanks in advance.
Sam
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
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.
