ITNinja

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.

*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 */

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).

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.

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.

I'm definitely interested in testing it for you.

email my profile if your interested. Others please do as well.