I have recently been asked to create reports that have the following information and am not able to get the info needed with the report wizard in Kace Reporting.  Any help is appreciated very much.

1) Number of Tickets Opened in a Month(for all queues)/Queue name/Who submitted ticket/Title of ticket/Category of Ticket

2) Number of Tickets Closed in a Month(for all queues)/Queue name/Who submitted ticket/Title of ticket/Category of Ticket

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

I think reports like this have been posted here before. Have you tried searching for them?

Answered 03/01/2013 by: chucksteel
Red Belt

  • I've been looking for about two days and so far have only found one "sample" report. :(
Please log in to comment
0

The report you are requesting lists two different kinds of information. If you want a report of all submitters for the past month you should be able to use the reports wizard to generate that easily enough. 

The number of tickets opened per month for all queues looks like this:

 SELECT count(HD_TICKET.id), HD_TICKET.HD_QUEUE_ID, HD_QUEUE.NAME FROM ORG1.HD_TICKET
JOIN HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
WHERE HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY)
GROUP BY HD_QUEUE_ID;

Tickets closed:

 SELECT count(HD_TICKET.id), HD_TICKET.HD_QUEUE_ID, HD_QUEUE.NAME FROM ORG1.HD_TICKET
JOIN HD_QUEUE on HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
WHERE HD_TICKET.TIME_CLOSED > DATE_SUB(NOW(), INTERVAL 31 DAY)
and HD_STATUS.STATE = 'closed'
GROUP BY HD_QUEUE_ID;

Adding who submitted the ticket to a report like the one above wouldn't make sense because you would just have a long list of names that contained all of the submitters in that queue. It should really be a separate report.

Answered 05/08/2013 by: chucksteel
Red Belt

  • Hey, that is neat! Thanks. :)
Please log in to comment
Answer this question or Comment on this question for clarity