/build/static/layout/Breadcrumb_cap_w.png

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 6 years ago

Answers (1)

Posted by: Druis 6 years ago
Third Degree Green Belt
0
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 6 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 6 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 6 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 6 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 6 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 6 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 6 years ago

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