Reports that show ticket response time, & length of time it took to close them.
Hi Everyone,
My supervisor is passing along a task of hers to me. Every quarter, she has to generate reports to show how well our helpdesk is meeting our SLA's. I know next to nothing about SQL so hoping someone here can help. I read through all the posts in this thread and only saw one similar question. It says it was answered but I can't find the answer.
There are 5 reports she has to gather data for:
1. Response time greater than 4 hours for priority 3 tickets.
2. Response time greater than 2 hours for priority 1 & 2 tickets.
3. Time from ticket open to close for priority 1 & 2 tickets that exceeds 2 days.
4. Number of tickets opened each month in the previous quarter.
5. Number of tickets closed each month in the previous quarter.
She used to just export all the tickets closed & opened in the previous quarter. Easy enough to do. But then she goes into Excel and uses a custom macro made by an employee to determine the other info. Alot of manual work, and in my opinion unneccesary, and it doesn't work well. This should be able to all be done by Kace reports.
I tried to tackle the first one with reports but since the "Responded to" field in our tickets is a custom date/time field, and there's no option to choose to determine how long it took to respond, i'm stuck. Here's what I got from the wizard (I removed the bogus criteria i picked in the wizard just to have the custom field appear in the sql query).
SELECT HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_PRIORITY.NAME AS PRIORITY,
O.FULL_NAME AS OWNER_NAME,
S.FULL_NAME AS SUBMITTER_NAME
FROM HD_TICKET
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT
JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_PRIORITY.NAME = '3 - As Time Permits')
AND (date(HD_TICKET.TIME_OPENED) >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month)
and date(HD_TICKET.TIME_OPENED) < date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))
(HD_TICKET.CUSTOM_FIELD_VALUE0)
ORDER BY TIME_OPENED
Basics of what i'd like to do.
All reports should show the following columns - ticket time opened, ticket time closed, ticket description, ticket priority, ticket response time, ticket owner, ticket submitter. Timeframe of tickets should be monthly or by the previous quarter. Ordered by time opened. I would have no problem going in and manually putting in dates each time I generated the report to show tickets from 10/1/2015 to 10/31/2015 for example to show me October.
Report #1 would show any tickets of priority 3 that EXCEED a response time of 4 hours.
Report #2 would show any tickets of priority 1 or 2 that EXCEED a response time of 2 hours.
Report #3 would show any tickets of priority 1 or 2 that are not closed within 2 days.
Reports #4 & 5 I think I can generate with built in reports or just make one, but basically just need a total # of tickets opened and closed during the previous quarter.
It would be BRILLIANT if I could get a percentage on the report of the # of violations. If not, I can manually calculate that.
PRIORITY 1 tickets are named "1 - Work Prohibited", 2 are "2 - Work Possible But Hindered", 3 are "3 - As Time Permits". The custom date/time field for response time is labeled HD_TICKET.CUSTOM_FIELD_VALUE0 in our db.
Hopefully that's enough info, sorry for the length... just tried to give as much detail as possible.
My supervisor is passing along a task of hers to me. Every quarter, she has to generate reports to show how well our helpdesk is meeting our SLA's. I know next to nothing about SQL so hoping someone here can help. I read through all the posts in this thread and only saw one similar question. It says it was answered but I can't find the answer.
There are 5 reports she has to gather data for:
1. Response time greater than 4 hours for priority 3 tickets.
2. Response time greater than 2 hours for priority 1 & 2 tickets.
3. Time from ticket open to close for priority 1 & 2 tickets that exceeds 2 days.
4. Number of tickets opened each month in the previous quarter.
5. Number of tickets closed each month in the previous quarter.
She used to just export all the tickets closed & opened in the previous quarter. Easy enough to do. But then she goes into Excel and uses a custom macro made by an employee to determine the other info. Alot of manual work, and in my opinion unneccesary, and it doesn't work well. This should be able to all be done by Kace reports.
I tried to tackle the first one with reports but since the "Responded to" field in our tickets is a custom date/time field, and there's no option to choose to determine how long it took to respond, i'm stuck. Here's what I got from the wizard (I removed the bogus criteria i picked in the wizard just to have the custom field appear in the sql query).
SELECT HD_TICKET.TIME_OPENED,
HD_TICKET.TIME_CLOSED,
HD_TICKET.TITLE,
HD_TICKET.CUSTOM_FIELD_VALUE0,
HD_PRIORITY.NAME AS PRIORITY,
O.FULL_NAME AS OWNER_NAME,
S.FULL_NAME AS SUBMITTER_NAME
FROM HD_TICKET
JOIN HD_PRIORITY ON (HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID) LEFT
JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT
JOIN USER S ON (S.ID = HD_TICKET.SUBMITTER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1) AND ((HD_PRIORITY.NAME = '3 - As Time Permits')
AND (date(HD_TICKET.TIME_OPENED) >= date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3)-(3*1) month)
and date(HD_TICKET.TIME_OPENED) < date_add( date_sub( curdate(), interval dayofyear(curdate())-1 day), interval ((quarter(curdate())-1)*3) month)))
(HD_TICKET.CUSTOM_FIELD_VALUE0)
ORDER BY TIME_OPENED
Basics of what i'd like to do.
All reports should show the following columns - ticket time opened, ticket time closed, ticket description, ticket priority, ticket response time, ticket owner, ticket submitter. Timeframe of tickets should be monthly or by the previous quarter. Ordered by time opened. I would have no problem going in and manually putting in dates each time I generated the report to show tickets from 10/1/2015 to 10/31/2015 for example to show me October.
Report #1 would show any tickets of priority 3 that EXCEED a response time of 4 hours.
Report #2 would show any tickets of priority 1 or 2 that EXCEED a response time of 2 hours.
Report #3 would show any tickets of priority 1 or 2 that are not closed within 2 days.
Reports #4 & 5 I think I can generate with built in reports or just make one, but basically just need a total # of tickets opened and closed during the previous quarter.
It would be BRILLIANT if I could get a percentage on the report of the # of violations. If not, I can manually calculate that.
PRIORITY 1 tickets are named "1 - Work Prohibited", 2 are "2 - Work Possible But Hindered", 3 are "3 - As Time Permits". The custom date/time field for response time is labeled HD_TICKET.CUSTOM_FIELD_VALUE0 in our db.
Hopefully that's enough info, sorry for the length... just tried to give as much detail as possible.
3 Comments
[ + ] Show comments
-
All of this can be done, but my first question is what constitutes a response in terms of your Service Desk Queue? To measure response we need to attach it to a timestamp when a specific action is completed - Hobbsy 8 years ago
-
All the techs have to do when they get a ticket is mark the status as "Acknowledged" and set the date & time (CUSTOM_FIELD_VALUE0). The only part we're looking for in the report is what date & time they responded to the user, and whether or not it was within the 2 or 4 hour time limit based on the priority. - j.hough_FNP 8 years ago
-
Hopefully that answered your question :) Thanks for the response Hobbsy. - j.hough_FNP 8 years ago
Answers (3)
Please log in to answer
Posted by:
chucksteel
8 years ago
Here is a report that shows tickets not closed in two days including the average time to close those tickets. They are grouped by Priority, month and date. This is for a queue of ID = 2.
SELECT concat(month(TIME_CLOSED), "/", year(TIME_CLOSED)) as "Month/Year", HD_PRIORITY.NAME as "Priority",
count(HD_TICKET.ID) as "Tickets Closed",
round(AVG(TIMESTAMPDIFF(SECOND,
CREATED,
TIME_CLOSED)
)/3600, 2) AS AverageDaysToClose
FROM ORG1.HD_TICKET
JOIN HD_PRIORITY on HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
and TIME_CLOSED != '0000-00-00 00:00:00'
and TIME_TO_SEC(TIMEDIFF(HD_TICKET.TIME_CLOSED, HD_TICKET.CREATED)) > 172800
GROUP BY HD_PRIORITY_ID, YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
ORDER BY YEAR(TIME_CLOSED),MONTH(TIME_CLOSED)
Posted by:
j.hough_FNP
8 years ago
Sorry for the new section, there wasn't a reply option on your last post.
I believe so, if I run a report - SELECT * FROM HD_PRIORITY ID
The 3 I want to report on are the first 3 listed:
I believe so, if I run a report - SELECT * FROM HD_PRIORITY ID
The 3 I want to report on are the first 3 listed:
| # | ID | HD Queue ID | Name |
| 1 | 1 | 1 | 2 - Work Possible But Hindered |
| 2 | 2 | 1 | 1 - Work Prohibited |
| 3 | 3 | 1 | 3 - As Time Permits |
Comments:
-
Yes, then the ID values are 1, 2, and 3. Try running this query:
SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP, FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAME, HD_PRIORITY_ID
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID
and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_ID
WHERE
TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400
and DATE(T.CREATED) BETWEEN 2016-01-01 and 2016-02-04
That will include a column for the priority so you can see what values are included.
As a side question, are you using a tool like MySQL Workbench to test your queries and look at the database tables. It is very helpful when creating custom reports like this. - chucksteel 8 years ago-
Yes, I actually just downloaded it before I posted that to see if I could find the values in there.
When I run the above I just get a header row in the results
-- ID TITLE CREATED TIMESTAMP ...etc etc. - j.hough_FNP 8 years ago-
D'OH. The dates need to be in quotes.
and DATE(T.CREATED) BETWEEN "2016-01-01" and "2016-02-04" - chucksteel 8 years ago -
Don't understand why i can't reply to your comment. That worked. I want the HD_PRIORITY_ID column to show the actual name of the priority. I pulled "HD_PRIORITY.NAME AS Priority" off another reports query, it works just fine on that, but if I add it, or change HD_PRIORITY_ID to HD_PRIORITY.NAME AS PRIORITY I get a message that says Error Code: 1054. Unknown column 'HD_PRIORITY.NAME' in 'field list' - j.hough_FNP 8 years ago
-
Replies on ITNinja can only go three deep.
You need to join to the HD_PRIORITY table in order to select a column from it. Add this to the other join statements:
LEFT JOIN HD_PRIORITY on HD_PRIORITY.ID = T.HD_PRIORITY_ID - chucksteel 8 years ago -
Perfect, that worked just fine. Any thoughts on item #3 in my original post? Report that would show any tickets Priority 1 & 2 that were NOT closed within 2 days, also utilizing the same date created between line? - j.hough_FNP 8 years ago
-
For number three you could basically use the same query as above but select a column that calculates the difference between the created and closed timestamps:
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff is the reference for timestampdiff. This would return the value in seconds:
TIMESTAMPDIFF(SECOND,
CREATED,
TIME_CLOSED)
But you probably want days or hours. - chucksteel 8 years ago -
Man SQL makes me feel stupid. lol. I'm assuming when you say "select a column" that I need to change something in the first one, probably removing FIRST_CHANGE.TIMESTAMP, FIRST_CHANGE.DESCRIPTION at minimum and adding something but i'm not sure what.
I changed TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400 to TIMESTAMPDIFF(DAYS, CREATED, TIME_CLOSED) > 2 but i doubt that's right. I looked at the link you sent me but it didn't help me much.
I'm going to make a point to learn some SQL after all this. It's fun but i don't understand it. - j.hough_FNP 8 years ago -
You don't need to remove any columns from the list that are already there unless you don't want them included in the report. The first part of the SQL statement uses the select statement to choose which columns will be included in the output. They are a comma separated list of column names which frequently include the table name as well in the format table.column. So, if you want to add the ticket ID that would be HD_TICKET.ID (or T.ID because we used an alias to make the query shorter). You can perform some basic functions on the data when selecting columns, so to get the difference between two columns (like created and closed) you can do that. Just add the column to the comma separated list of items in the SELECT portion of the query. In the query above the last item is HD_PRIORITY_ID so add , TIMESTAMPDIFF(DAY,
CREATED,
TIME_CLOSED)
The line you changed is in the where clause which filters the results being selected. It should be functionally equivalent to use 14400 seconds or 2 days. - chucksteel 8 years ago -
took me a bit but managed to get it working. thank you so much chuck!!! - j.hough_FNP 8 years ago
Posted by:
chucksteel
8 years ago
This query might help:
SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP, FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID
and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_ID
WHERE
TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400
and DATE(T.CREATED) = DATE(NOW()) - INTERVAL 7 DAY
Adjust the 14400 value to match your SLA, it is in seconds. The interval value matches tickets created in the past seven days. This will find tickets based on the first change found in the HD_TICKET_CHANGE table, excluding ticket creation and system change (USER_ID != 0).
Comments:
-
Thanks Chucksteel. I kinda like the First_Change.timestamp... since the field we have to mark for when we contacted the user is required, it marks a change in the ticket... also helps since the custom 'contacted user' field is date & time based, it only requires the date... the techs don't always put in the right time... sometimes leave the time out completely. A couple of changes I'd like to make though, if you don't mind.
1. Can I have it only include HD_PRIORITY ID 1, 2, & 3?
2. How would I adjust it to show the previous quarter... or if I wanted to do say October, November, and December of last year?
Any ideas for a report to show the # of tickets of PRIORITY ID 1 & 2 that were not closed within 2 days? Same concept as the ticket.change, quarter or by specific months.
Thanks!!!!!! - j.hough_FNP 8 years ago-
any thoughts on this chucksteel? thanks! - j.hough_FNP 8 years ago
-
To include only certain priorities based on ID you can add the following to the where clause:
and HD_PRIORITY_ID in (1, 2, 3)
This post on StackExchange should help with previous quarter:
http://stackoverflow.com/questions/29136188/returning-start-and-end-dates-of-previous-quarter-in-mysql
If you want to just hard code the dates you can change the DATE(T.CREATED) = DATE(NOW()) - INTERVAL 7 DAY to:
DATE(T.CREATED) BETWEEN 2015-10-01 and 2015-12-31 - chucksteel 8 years ago-
hmm, i made those changes and now it shows no results. Here's what I have...
SELECT T.ID, T.TITLE, T.CREATED, FIRST_CHANGE.TIMESTAMP, FIRST_CHANGE.DESCRIPTION, T.TIME_CLOSED, FIRST_CHANGE.USER_ID, USER.FULL_NAME
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE FIRST_CHANGE ON FIRST_CHANGE.HD_TICKET_ID = T.ID
and FIRST_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = T.ID and HD_TICKET_CHANGE.USER_ID !=0 and HD_TICKET_CHANGE.DESCRIPTION not like "Ticket Created%")
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
LEFT JOIN USER on USER.ID = FIRST_CHANGE.USER_ID
WHERE
TIME_TO_SEC(TIMEDIFF(FIRST_CHANGE.TIMESTAMP, T.CREATED)) > 14400
and DATE(T.CREATED) BETWEEN 2016-01-01 and 2016-02-04
and HD_PRIORITY_ID in (1, 2, 3) - j.hough_FNP 8 years ago -
Are you sure that the priority IDs are actually 1, 2, and 3? Keep in mind that priorities are queue specific so the ID values in the database should be verified. - chucksteel 8 years ago