Is there a way to create a report that tells you how long each ticket was stalled? I see where you can pull the time that the ticket went into stalled status but not how to pull the amount of time the ticket was stalled.

Thanks!
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
No. You can tell the moment in time a ticket was stalled and the current time. The difference is the time it has been in its current stalled state.

Additionally you can tell the moment in time a ticket was created and you can tell all the different times that the ticket was put into or out of a stalled state. The math on those gaps would give you the total time that a ticket is stalled.

The latter would not be an easy query and would only work on tickets that have existed after 5.1 was installed. Also it would require that you have not modified your status values in any significant way.
Answered 10/07/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
*IF* you don't use ticket rules to automatically change states then here is a report that will give you the total seconds a ticket has been in a stalled state. You could easily modify it for other states
Note: note I said states which is not necessarily status, but usually implies status

select T.ID,
sum(case when TIME_STALLED=C.TIMESTAMP AND
TIME_STALLED>TIME_OPENED and TIME_STALLED>TIME_CLOSED
THEN (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(TIMESTAMP)) /*currently stalled */
when AFTER_S.STATE='Stalled' AND BEFORE_S.STATE='Stalled' then 0 /* change in status but not state */
WHEN AFTER_S.STATE='Stalled' then (UNIX_TIMESTAMP(TIMESTAMP)-UNIX_TIMESTAMP(T.CREATED))*-1 /* move into stalled */
WHEN BEFORE_S.STATE='STALLED' THEN (UNIX_TIMESTAMP(TIMESTAMP)-UNIX_TIMESTAMP(T.CREATED)) /* move out of stalled */
WHEN C.DESCRIPTION LIKE 'Ticket Created%' THEN 0 /*first created */
ELSE 0 END ) SECS_STALLED,
S.STATE CUR_STATE
from HD_TICKET T
JOIN HD_TICKET_CHANGE C ON T.ID=C.HD_TICKET_ID
JOIN HD_STATUS S ON S.ID=T.HD_STATUS_ID
LEFT JOIN HD_TICKET_CHANGE_FIELD F ON C.ID=F.HD_TICKET_CHANGE_ID and FIELD_CHANGED='STATUS_NAME'
LEFT JOIN HD_STATUS AFTER_S ON AFTER_S.NAME=AFTER_VALUE and AFTER_S.HD_QUEUE_ID=T.HD_QUEUE_ID
LEFT JOIN HD_STATUS BEFORE_S ON BEFORE_S.NAME=BEFORE_VALUE and BEFORE_S.HD_QUEUE_ID=T.HD_QUEUE_ID
WHERE
T.HD_QUEUE_ID >0 /* can specify a queue here */
and (AFTER_S.NAME IS NOT NULL OR C.DESCRIPTION LIKE 'Ticket Created%')
GROUP BY T.ID
ORDER BY 1
/* question is how long was it in a stalled state */
Answered 10/08/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
If you do use ticket rules to change state then you'll have to implement a timer with rules to keep track of each state. A timer would also have the advantage of being able to exclude down time (evenings and weekends).
Answered 10/08/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Thanks GillySpy,

I'll take a swing at the report above. The way we have our system setup is that we have 4 different status'.

1. New = Open
2. Work In Progress = Open
3. Pending = Stalled
4. Closed = Closed

You mentioned using ticket rules and schedules pertaining to the helpdesk hours (7a - 5p). That is what I'm attempting as well along with cutting out the stalled time. My thought was to create a rule that would automatically change all New and Work In Progress to a status that is considered stalled at 5p, then changing them back at 7a. New would go to a specific status and WIP would have its own status as well. Anyway, that's my goal...

Thanks, again, for answering the question. I'm going to work on it now.
Answered 10/10/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
If you are going to use the report I posted above I don't see how your rule would give you an accurate total for the time opened because your rule's changes are not going to have a corresponding entry in the HD_TICKET_CHANGE_FIELD table.

I had been working on a set of rules that one could import into their helpdesk to accurately track total time a ticket is opened or stalled. It incorporates business hours as well. If anyone wants to be a guinea pig let me know.
Answered 10/10/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
I'm definitely interested in testing it for you.
Answered 10/11/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
email my profile if your interested. Others please do as well.
Answered 10/11/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity