How to query/report on incidents for knowing the age of ticket?

[pavankum]

Hi,

i would like to report/ query incidents of all active incidents so that it should give me the incidents based on age. like incidents count and age.

For example like below

Incident Count                 Age

              25                                 3 days

              30                                 5 days

              50+                             10 days

[ranjith_j]

Hi Pavan,

Found a simples way -

Create a new field of type duration on incident table. You may name it as 'Age'.

Write an After-Update Business Rule with condition 'When State Changes To Resolved'

Script:

calculateAge();

function calculateAge(){

var datedif = gs.dateDiff(current.sys_created_on,current.resolved_at);

current.u_age = datedif;

current.update();

}

This Works. I have tried it myself. If you have any queries, please let me know.

Thanks.

[kavinhappy]

Hi Ranjith,

Could you please share the script to calculate age days for all open tickets as well.

i.e. date diff should be the current date - created date.

Also how to use this query in SNOW reports.

Thanks

[marcguy]

Age of open tickets is very tricky because it's not stored on the record for obvious reasons, it would be constantly changing that's why we calculate at point in time, i.e. resolved/closed.

You would need to run a custom script to calculate the age, maybe in a scheduled job and then build up a html/type list or something like that, but you won't get the age from a field on a record.

Marc

[kavinhappy]

Thanks Marc.

Is there a option to add a column with formula or user defined query to get diff of system date and incident created date?

[marcguy]

Calculated fields are not recommended because of the heavy load they put on an instance.

one thing I have seen done in the past, which is not an ideal solution but helps somewhat, is to add a integer field called Age(in days) and run a scheduled script once a day to update that field silently in a scheduled job to keep a track of incident age. I didn't particularly like that solution but that was what was wanted.

Another way would be to have a trend job (sys_trend) of open incidents and keep an eye on backlog growth that way.

I would not recommend a calculated (formula) field though, especially on a table as large as incident.