Percent of Tickets Closed
Hi. I am looking for a report from Kace 1000 V 7 out of service desk that gives me the percent of tickets closed within an hour and I am not great with SQL and I see no way to do this using the wizard at all. Any one have any ideas I would greatly appreciate it.
2 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
7 years ago
Top Answer
[posting as a new answer for better formatting]
1. How can I ignore previous years and just look at the current year?
To limit by year you can add a statement to the where clause:
YEAR(TIME_CLOSED) = YEAR(NOW())
2. How do I get rid of where it says still open?
That is one of the sub select statements, just remove it from query (don't forget to take away the comma in front of it).
3. How do I look at total closed instead of total open?
Change the references to CREATED to TIME_CLOSED (except when calculating the difference between created and closed).
The resulting query looks like this:
SELECT YEAR(TIME_CLOSED), WEEK(TIME_CLOSED), P.NAME,
COUNT(T.ID) AS "Total Closed",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 3600
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "1 hour",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 7200 and 14400
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "2 to 4 hours",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 14400 and 86400
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "4 hr to 1 day"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 2
and YEAR(TIME_CLOSED) = YEAR(NOW())
GROUP BY YEAR(TIME_CLOSED), WEEK(TIME_CLOSED), P.ID
I think the second script is only looking at tickets opened as well in a two week time period.
Yes, I had assumed you wanted to report on how quickly tickets created in the past two weeks were closed, not how quickly tickets closed in the past two weeks were closed. Again, we need to change most of the references to CREATED to TIME_CLOSED:
SELECT P.NAME,
COUNT(T.ID) AS "Total Closed",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 3600
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "1 hour",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 7200 and 14400
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "2 to 4 hours",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 14400 and 86400
AND YEAR(TIME_CLOSED) = YEAR(T.TIME_CLOSED) and WEEK(TIME_CLOSED) = WEEK(T.TIME_CLOSED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "4 hr to 1 day"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 2
AND DATE(TIME_CLOSED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
GROUP BY YEAR(TIME_CLOSED), WEEK(TIME_CLOSED), P.ID
Comments:
-
Hi Chuck - I submitted a K1000 question but I wasn't sure how to CC you on it. My ITNinja company\username is AEM.
Ticket subject is: K1000 (Version: 6.4.120822) - Apple\Mac Inventory Report
Could you please look into and offer any assistance? Thanks in advance! - AEM 7 years ago
Posted by:
chucksteel
7 years ago
This is a big messy query but it seems to work. Note that it shows number of tickets in the groups, but percentages. Also, grouping by two week periods is a bit more complicated so this is grouped by week.
SELECT YEAR(CREATED), WEEK(CREATED), P.NAME,
COUNT(T.ID) AS "Total Opened",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 3600
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "1 hour",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 7200 and 14400
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "2 to 4 hours",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 14400 and 86400
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "4 hr to 1 day",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_CLOSED = '0000-00-00 00:00:00'
AND YEAR(CREATED) = YEAR(T.CREATED) and WEEK(CREATED) = WEEK(T.CREATED)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "Still Open"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 2
GROUP BY YEAR(CREATED), WEEK(CREATED), P.ID
You can add intervals by copying the existing sub select statements and changing the TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN x and y
Where x and y and the times in seconds.
Here is another query that shows you the tickets for just the past two weeks, it's similar but grouped just by priority.
SELECT P.NAME,
COUNT(T.ID) AS "Total Opened",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) < 3600
AND DATE(CREATED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "1 hour",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 7200 and 14400
AND DATE(CREATED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "2 to 4 hours",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_TO_SEC(TIMEDIFF(TIME_CLOSED, CREATED)) BETWEEN 14400 and 86400
AND DATE(CREATED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "4 hr to 1 day",
(SELECT COUNT(ID)
FROM ORG1.HD_TICKET
WHERE TIME_CLOSED = '0000-00-00 00:00:00'
AND DATE(CREATED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
AND HD_TICKET.HD_PRIORITY_ID = P.ID
AND HD_TICKET.HD_QUEUE_ID = P.HD_QUEUE_ID) "Still Open"
FROM HD_TICKET T
JOIN HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
WHERE P.HD_QUEUE_ID = 2
AND DATE(CREATED) > DATE_SUB(NOW(), INTERVAL 2 WEEK)
GROUP BY P.ID
Comments:
-
Note, these reports are both for queue 2, you will most likely need to change that for your environment. - chucksteel 7 years ago
-
This is fantastic, thank you. Will be testing it shortly, might have a few questions after that but this is really great. I will let you know how it goes. - anonymous_135716 7 years ago
-
Hi. These are great. After testing have a question regarding the first more complicated script. I tried to change it to ignore previous years and I got an error. Then I tried a few other things but it does not seem to like me. So, these are my questions for the first script. 1. How can I ignore previous years and just look at the current year? 2. How do I get rid of where it says still open? 3. How do I look at total closed instead of total open?
Thank you very much. - anonymous_135716 7 years ago-
Oh also need to make sure the first script is only looking at tickets that are closed in those time periods. I think the second script is only looking at tickets opened as well in a two week time period. Not sure, I am getting confused. - anonymous_135716 7 years ago
-
Hi. Thank you for helping me, with a little more tweaking your scripts worked beautifully and they are saving hours and hours of work within Kace with reports. - anonymous_135716 7 years ago
-
Hi. These reports are fantastic. they have helped a lot and are saving a lot of time. I have one more question to go along with these scripts you wrote.
The script which generates the ticket count by priority by time to close is great. However, we would like to be able to identify the tickets which failed to meet our SLA targets. Ideally, we would love to a script which showed ticket ID, Priority, and time to close (excluding time stalled and time outside normal business hours). With such a listing we could easily identify the tickets for each priority which violated the SLA and then open it in Kace to figure out what caused the failure. - anonymous_135716 7 years ago-
Unfortunately, I have never found a good way to determine the amount of time a ticket has been stalled and to exclude time outside of working hours. - chucksteel 7 years ago
1. By Priority
2. Closed within 1 hour
3. Closed within 2 to 4 hours
4. Closed within 2 hours - 1 day
5. Closed within 2-29 days
6. Open 30 days or more
Want to track tickets through 3 milestones kind of
1. Time Created
2. Time Opened - like put into work or progress
3. Time Closed
Is it possible to subtract the time a ticket is marked as being "on hold" or "awaiting customer response" as well?
I am sure these are possible but after going through all the reports and looking at the sql side and trying the report wizard and trying to utilize the filters in version 7.0 I just am not seeing it. I have not tried Dell's Toad yet or maybe there is something better out there that can help me create the script. So any suggestions are greatly appreciated. Thank you - anonymous_135716 7 years ago
Ticket subject is: K1000 (Version: 6.4.120822) - Apple\Mac Inventory Report
Could you please look into it and offer any assistance? Thanks in advance! - AEM 7 years ago