Ticket Status History

RT Users
1

Hello everyone.
I need to create a report which shows the average length of time a ticket is in each status. I tried adapting the ‘include ticket history’ template in a perl script but I cannot seem to get it to include status changes. I’ve dug through the wiki some and the list archive but haven’t come across anything which helps.
Any ideas on how I can pull this info?

Thanks
Eric

2

Hi

we do something similar here. The idea is :
define Tickets attributes (you could use CFs)
one for each Status called TimeSpentInOpen/TimeSpentInNew/…
plus one called TimeSinceLastStatusChange
Then each time the status changes,
add to the timer associated with the old value the time since
TimeSinceLastStatusChange
then update TimeSinceLastStatusChange to the current time.

To display the values, I added a box between Dates and Links called Metrics.
I also modified the columnMap to be able to display the values in search
results.

It is still a recent change but it seems to work.
It will not work with old tickets though.

GerardOn 2011-03-31 16:19, Eric Stoycon wrote:

Hello everyone.
I need to create a report which shows the average length of time a ticket is in each status. I tried adapting the ‘include ticket history’ template in a perl script but I cannot seem to get it to include status changes. I’ve dug through the wiki some and the list archive but haven’t come across anything which helps.
Any ideas on how I can pull this info?

Thanks
Eric

3

Something like this:

SELECT t1.OldValue status, AVG(TIMESTAMPDIFF(SECOND, t2.Created, t1.Created))

FROM Transactions AS t1
CROSS JOIN Transactions AS t2
LEFT JOIN Transactions AS t3
ON t3.id < t1.id AND t3.id > t2.id
AND t3.ObjectType = t1.ObjectType AND t3.ObjectId = t1.ObjectType
AND (t3.Type = ‘Status’ OR (t3.Type = ‘Set’ AND t3.Field = ‘Status’))

WHERE
t1.ObjectType = ‘RT::Ticket’
AND (t1.Type = ‘Status’ OR (t1.Type = ‘Set’ AND t1.Field = ‘Status’))

AND t2.ObjectType = t1.ObjectType AND t2.ObjectId = t1.ObjectId
AND (t2.Type = 'Status' OR (t2.Type = 'Set' AND t2.Field =

‘Status’) OR t2.Type = ‘Create’)
AND t2.id < t1.id

AND t3.id IS NULL

GROUP BY t1.OldValue
;On Thu, Mar 31, 2011 at 6:19 PM, Eric Stoycon estoycon@gmail.com wrote:

Hello everyone.
I need to create a report which shows the average length of time a ticket is in each status. I tried adapting the ‘include ticket history’ template in a perl script but I cannot seem to get it to include status changes. I’ve dug through the wiki some and the list archive but haven’t come across anything which helps.
Any ideas on how I can pull this info?

Thanks
Eric

Best regards, Ruslan.