Aging reports on Incident

SNOW46 · ‎08-17-2018

Hi All,

I want to schedule an Aging Report on Incident in which it will display in tabular format like in Pivot Table format.

Can anyone share me some ideas as well as help on the same so that I will configure and implement the same as well.

Like the same as above image.

Mark Stanger · ‎08-20-2018

Here's what you'll need to do...

1) Create a new 'Choice' field on the 'incident' table named 'Aging category'

2) Right-click that field label from your form and select 'Configure choices' to add the following choices. COPY THESE VALUES EXACTLY!

3-7 Days

8-14 Days

15-21 Days

22-28 Days

> 28 Days

3) Navigate to 'System Definition -> Scheduled Jobs' and select 'Automatically run a script of your choosing'. Here you'll create a scheduled job to run a script to get you accurate counts for your report. You can run this as often as you want, but I'd probably only run it every day or so you don't negatively impact your instance performance. You should use this in the 'Script' field.

u_updateIncidentAging();

function u_updateIncidentAging() {
	var elapsedTime = 0;
	var aging = '';
	var currentTimeNow = gs.nowDateTime();
	var gr = new GlideRecord('incident');
	gr.addEncodedQuery('u_aging_category!=>28^ORu_aging_category=');
	gr.query();
	while(gr.next()) {
		elapsedTime = (gs.dateDiff(gr.opened_at, currentTimeNow, true))/60/60/24;
		
		// Check to see when the item was created
		if (elapsedTime <= 2) aging = '0-2 Days';
			if (elapsedTime > 2)  aging = '3-7 Days';
			if (elapsedTime > 7)  aging = '8-14 Days';
			if (elapsedTime > 14) aging = '15-21 Days';
			if (elapsedTime > 21) aging = '22-28 Days';
			if (elapsedTime > 28) aging = '> 28 Days';
			
		gr.setWorkflow(false); // Skip any Business Rules
		gr.autoSysFields(false); // Do not update system fields
		gr.u_aging_category = aging;
		gr.update();
	}
}

Your scheduled job should look like this...

Once this is in place, you can click the 'Execute now' button to run the script and populate your new field. You should be able to go to any incident record and see this value populated correctly.

4) Create your report. The report should look like this...

View solution in original post

gyedwab · ‎08-20-2018

Another alternative to creating a custom field and writing script to populate it, would be to use Explore Analytics to create those reports:

Incident Aging Video

This approach has a few advantages:

Won't require development/maintenance of code if changes are required in the future
Different reports can use different aging buckets as desired
You can use the aging capability globally, on any table or any field (e.g. time since incident was last updated, average age of assets, time before planned start of changes waiting approval)

CL2 · ‎11-13-2019

This is a great solution.

There is a complication that my colleague (EP @ NYCHHC) pointed out.

This solution would updated every incident on the table and this would end up throwing off your last updated reports . As every time the scheduled job runs you would be updating the aging category field which lives on every ticket.

Moop · ‎12-10-2019

This should be alleviated with

		gr.autoSysFields(false); // Do not update system fields

SRS · ‎02-11-2022

This thread is brilliant but experiencing some issues on the output, any feedback would be good:

Report is showing like this for some reason.

Other details:

----

AMercier · ‎08-14-2023

I've tested this solution out and it does exactly what I need. A current report where I can Count currently open incidents Grouped by Assignment Group, Stacked by Age Buckets. One Question I have is how would I update the script to account for resolved time.

Something along these lines
var currentTimeNow = IF incident.resolved_at ISEMPTY THEN gs.nowDateTime ELSE incident.resolved_at