reporting on response time
I'm trying in replicate features/reports we use in our existing service desk software.
Response time is one of our key SLAs i.e. the time it takes from a ticket being submitted by a user to it being opened, we actually use the time when assigned to IT staff as the trigger for the response time, that's just the way our existing software works. I suppose what I really want is the time between the ticket being created and the first action on the ticket by IT staff, when status change, assigned - basically that we've looked at it and responsed. We report on the average response time each month against our target.
Looking at the built in reports and the options in custom reports I can't see how we can do this. There is a Time Opened field which I guess could be used to calculate the response this but that relies on a ticket being set to opened, it is possible to go from New straight to Closed (or any other state).
Is it possible to do this as a SQL report? Is there info on the structure of the tables so more complex reports like this can be created?
thanks,
Brian
Response time is one of our key SLAs i.e. the time it takes from a ticket being submitted by a user to it being opened, we actually use the time when assigned to IT staff as the trigger for the response time, that's just the way our existing software works. I suppose what I really want is the time between the ticket being created and the first action on the ticket by IT staff, when status change, assigned - basically that we've looked at it and responsed. We report on the average response time each month against our target.
Looking at the built in reports and the options in custom reports I can't see how we can do this. There is a Time Opened field which I guess could be used to calculate the response this but that relies on a ticket being set to opened, it is possible to go from New straight to Closed (or any other state).
Is it possible to do this as a SQL report? Is there info on the structure of the tables so more complex reports like this can be created?
thanks,
Brian
0 Comments
[ + ] Show comments
Answers (4)
Please log in to answer
Posted by:
GillySpy
13 years ago
TIME_OPENED is the last time that a ticket entered and opened state. If a ticket went directly from New (stalled state) to Closed (Closed state) then there would be no TIME_OPENED. What would you do if you wanted to close a ticket -- e.g. a duplicate or irrelevant ticket -- without going through an opened state? Would you also want that ticket measured according to this standard? There is no wrong answer, but defining on these processes will allow you to create accurate reports.
Let's assume that you have a special state called "Admin closed" that you would use to force close an irrelevant ticket. We now have the freedom to handle your scenario with a condition in the query:
Let's assume that you have a special state called "Admin closed" that you would use to force close an irrelevant ticket. We now have the freedom to handle your scenario with a condition in the query:
select T.ID, TITLE,
case when TIME_OPENED <> '0000-00-00 00:00:00' then TIMEDIFF(TIME_OPENED,T.CREATED)
when S.STATE='Stalled' then TIMEDIFF(NOW(), T.CREATED)
else TIMEDIFF(TIME_CLOSED,T.CREATED) END SLA_ELAPSED_TIME,
TIME_OPENED, TIME_CLOSED
FROM HD_TICKET T JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
WHERE S.NAME<>'Admin Closed'
ORDER BY 3 DESC
Posted by:
technobri
13 years ago
Hi Gerald,
Thanks for the reply.
In our current helpdesk we include duplicate or irrelevant tickets in the reports but that's because there's no easy way to filter them. It makes sense to do so though and I can see that an 'admin closed' state would work for us.
Your SQL using the case clause makes sense to and I can see that working. I would mean a discipline of always setting the state which is something we don't currently have to do, there is no New state in our current helpdesk, all tickets are Open by default. My initial thoughts were to pick up the first entry in the ticket history after Ticket Created. I've copied a couple of lines from a sample ticket - in the case below the first action is changed ticket owner but could be anything, comment added, priority set etc.
admin on Mar 11 2011 09:30:41 AM
admin on Mar 07 2011 02:39:34 PM
In that scenario any action on a ticket would count as a response but without knowing the structure of the tables it's hard to know if it's possible to create the SQL to do that. Is that info published anywhere? i assume it must be as there's the ability to create SQL reports.
thanks
Brian
Thanks for the reply.
In our current helpdesk we include duplicate or irrelevant tickets in the reports but that's because there's no easy way to filter them. It makes sense to do so though and I can see that an 'admin closed' state would work for us.
Your SQL using the case clause makes sense to and I can see that working. I would mean a discipline of always setting the state which is something we don't currently have to do, there is no New state in our current helpdesk, all tickets are Open by default. My initial thoughts were to pick up the first entry in the ticket history after Ticket Created. I've copied a couple of lines from a sample ticket - in the case below the first action is changed ticket owner but could be anything, comment added, priority set etc.
In that scenario any action on a ticket would count as a response but without knowing the structure of the tables it's hard to know if it's possible to create the SQL to do that. Is that info published anywhere? i assume it must be as there's the ability to create SQL reports.
thanks
Brian
Posted by:
technobri
13 years ago
Had a play around with ticket rules - Wow, very powerful :)
Created one that writes the time into a custom field when the ticket is first changed and saved. It works a treat. Gives me exactly what I want.
Would still be great to get the table definitions, I guess I can muddle through by looking at the SQL in existing reports but it would be great to have the official documentation.
Have been using this as a bit of a test case as the built in reports don't give us exactly what we want. But with a bit of customisation using ticket rules and SQL reports I think we can achieve pretty much everything we need.
Brian
Created one that writes the time into a custom field when the ticket is first changed and saved. It works a treat. Gives me exactly what I want.
Would still be great to get the table definitions, I guess I can muddle through by looking at the SQL in existing reports but it would be great to have the official documentation.
Have been using this as a bit of a test case as the built in reports don't give us exactly what we want. But with a bit of customisation using ticket rules and SQL reports I think we can achieve pretty much everything we need.
Brian
Posted by:
GillySpy
13 years ago
That would work. You could alternatively manipulate the TIME_OPENED field in your rule instead of a custom field. The custom field is good because you can set it as read only and display that in the ticket.
Rating comments in this legacy AppDeploy message board thread won't reorder them,so that the conversation will remain readable.