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.  
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • Do you want percent closed per week, per month? Unless you want all time it would be helpful to know how often you want the report and for what interval you want the percentage calculated.
    • So it would be a bi-weekly metrics report that I am looking for. A report to show ticket close time for:
      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
    • 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 it and offer any assistance? Thanks in advance!
  • Sorry, I would like to have these reports run based on two weeks of data. I do not think I was clear on that.
Please log in to comment

Answer Chosen by the Author

1
[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

Answered 02/16/2017 by: chucksteel
Red Belt

  • 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!
Please log in to comment

Community Chosen Answer

1
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






Answered 01/31/2017 by: chucksteel
Red Belt

  • Note, these reports are both for queue 2, you will most likely need to change that for your environment.
    • 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.
    • 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.
      • 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.
    • 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.
    • 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.
      • 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.
Please log in to comment

Answers

This content is currently hidden from public view.
Reason: Determined to be spam through moderation
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share