KACE Product Support

ORIGINAL: dave1kelsey

Hello all,
Ive been asked to create a report that provides us with a total number of support tickets that have come through the helpdesk in the past 31 days to compare it against our closed tickets in past 31 days report, and for the life of me cant figure out a way of creating this.
Can anybody provide any suggestions



The key here is to use the wizard to create the initial report, and then just modify the SQL to include your date range function. Give this a whirl:

SELECT
HD_TICKET.CREATED,HD_CATEGORY.NAME AS CATEGORY,
HD_TICKET.TITLE,S.FULL_NAME AS SUBMITTER_NAME,
O.FULL_NAME AS OWNER_NAME
FROM HD_TICKET
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND (HD_TICKET.CREATED > DATE_SUB(NOW(), INTERVAL 31 DAY) )
ORDER BY CREATED desc,CATEGORY

Brilliant, I gave scottlutz a +1. (sorry with IT ninja coming I think it's important to start doing this so i'm going to shamelessly plug ratings).

dave1kelsey, however you define your business needs it is very likely we can create a report to reflect it. Sometimes the question is whether or not we can create the report in the wizard, tweak it or start from scratch. If you have some exotic needs or find the vanilla isn't meeting your needs please ask away. I want to illuminate the edge cases so you can make the call. For many customers the edge cases are few and far between and the cost of measuring them doesn't equal the risk they carry.

If it's just a KPI then you're probably good. KPI's are mostly about establishing a baseline and comparing it. But I have seen helpdesks where they close tickets on every response -- so basically the "responded" status is a "closed" state. For those they would really need to consider how that will affect reporting.

Being "on the front lines" for years I have seen people take advantage of loopholes. For example, techs that will reopen old tickets and then re-close them to bump their #'s. In the reporting above this might be hard to detect if you are purely looking at these numbers. But if you were also looking at another report that showed the time difference from open to close then that same person would be penalized for doing that.

When using the report wizard, select ticket, ticket created, within the last 31 days.

I admit there is now a lot of work to provide an overview of how well the helpdesk is hitting KPI's

Speaking of KPIs/metrics, here is an interesting article to read through at your leisure:
http://it.slashdot.org/story/11/12/14/225204/the-four-fallacies-of-it-metrics
Your mileage may vary ;)

Brilliant thanks for the replies works perfectly once edited the layout in SQL editor

twaller is correct and you can do the same for "time closed".

Dave1kelsey -- do you care about tickets that were reopened in the last 31 days?

For example, if every ticket was reopened after 31 days then over the course of N time you would see twice as many tickets close as tickets open. Which of course its technically impossible. Is that okay? How do you interpret those?

GillySpy, used Scottlutz answer to create the SQL report.
Currently there are reports that run on the same day to provide: tickets created, tickets closed and tickets still open for past 31 days.
I admit there is now a lot of work to provide an overview of how well the helpdesk is hitting KPI's and if anyone has any better way of compiling this information then i would be grateful, but essentially i need to provide how many tickets have been closed with references to how many were actually created and how many are still outstanding.

FTR, I want to change my vote for scott's to a +2 but it won't let me :(

ORIGINAL: GillySpy

If you have some exotic needs or find the vanilla isn't meeting your needs please ask away.



GillySpy, Thanks for this ive spoken to KACE about other bespoke changes to a report that we need, to allow us to properly implement a new ticket/stalled/closed time report to allow better identification of how well we are "performing" and this will happen in the new year now and should apparently only cost 1hr of time which is good.

BTW how do i +1 people?

At the bottom right of each post is a "rate post" link.

If you're interested in precisely measuring the hours / seconds that a ticket is in a state then I do have a rule "framework" for that. PM me so I can email it.

Thanks Scottlutz, will spend my free time reading this!!

So true. Many moons ago I was majoring in statistics before I realized that you have to be a pure idealist to stick with it and job satisfaction seemed like an oxymoron at that point.