KACE Report on tickets with a specific subject
I'm attempting to create a report that will show the total number of tickets with a specified subject (They should all be identical), as well as the name of the person who closed them. What I have below is giving me a syntax error:
SELECT O.FULL_NAME, COUNT(HD_TICKET.TIME_OPENED) FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.TIME_OPENED like '%2016%') AND (HD_TICKET_SUBJECT like %Additional request form and New Hire process (child)%) GROUP BY O.FULL_NAME
If I remove the AND (HD_TICKET_SUBJECT like %Additional request form and New Hire process (child)%) portion, the report will spit out total tickets in 2016, closed by owner.
My goal is to get more specific and see how many of those have the specific subject I'm looking for.
1 Comment
[ + ] Show comment
-
Have you tried to build this report using the wizard? - Hobbsy 7 years ago
Answers (1)
Please log in to answer
Posted by:
Druis
7 years ago
There is no table called HD_TICKET_SUBJECT. Try HD_TICKET.TITLE
Comments:
-
When I use your suggested replacement, I still have an error:
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '%Additional request form and New Hire process (child)%) GROUP BY O.FULL_NAME LI' at line 1] in EXECUTE( "SELECT O.FULL_NAME, COUNT(HD_TICKET.TIME_OPENED) FROM HD_TICKET LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_TICKET.TIME_OPENED like '%2016%') AND (HD_TICKET.TITLE like %Additional request form and New Hire process (child)%) GROUP BY O.FULL_NAME LIMIT 0") - NJ@V 7 years ago-
Tyr this:-
SELECT O.FULL_NAME,
COUNT(HD_TICKET.TIME_OPENED)
FROM HD_TICKET
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 1)
AND
(YEAR(HD_TICKET.CREATED) = 2017)
AND (HD_TICKET.TITLE like '%Additional request form and New Hire process (child)%')
GROUP BY O.FULL_NAME - Druis 7 years ago-
That's some progress at least. I can save the report without errors now, but it's not retrieving any results. The ticket title I have should be exact (they're auto generated child tickets, so there shouldn't be any variation) - NJ@V 7 years ago
-
Are you sure of your queue ID? 1 is usually the default but if there are multiple Queues they will all have different numbers - Druis 7 years ago
-
Yes I'm positive. If I remove
AND (HD_TICKET.TITLE like '%Additional request form and New Hire process (child)%')
it spits out total number of tickets closed per user. - NJ@V 7 years ago
-
I'm not sure where your issue is. When I run this query with my own key text I get a return. You may benefit from using a tool like MySQL workbench or HeidiSQL here to interrogate your tables - Druis 7 years ago
-
I just tried again with only the word Additional as a qualifier and it worked. I'm thinking that maybe it can't handle the extra set of parenthesis - NJ@V 7 years ago
