Trying to create an aging report

[Conan_Lloyd]

Bear with me, I'm pretty new to SN and very new to scripting. I'm pretty good at reverse engineering things but not as great at creating from scratch. After searching the forums I noticed that there didn't seem to be a way to get an aging report for incidents. (How many open are 0-2 days old, 3-7 days old, etc.)

Below is what I have tried and it's not quite working yet, anyone wanna point out where I screwed up?

I created a new field called Aging Category (u_aging_category) on the incident table.

I wrote the following script as a scheduled job:

//************************ Set Aging Category **************************************

var optime = current.opened_at.getGlideObject().getNumericValue();
var nowtime = gs.nowDateTime().getGlideObject().getNumericValue();
var totalTime = nowtime - optime ;
var calculation = totalTime/1000/60/60/24;

if (calculation <= 2){
current.u_aging_category = '0-2 Days';
}
if (calculation <= 7 && calculation > 2){
current.u_aging_category = '3-7 Days';
}
if (calculation <= 14 && calculation > 7){
current.u_aging_category = '8-14 Days';
}
if (calculation <= 21 && calculation > 14){
current.u_aging_category = '15-21 Days';
}
if (calculation <= 28 && calculation > 21){
current.u_aging_category = '22-28 Days';
}
if (calculation > 28){
current.u_aging_category = '> 28 Days';
}


In theory this should update the Aging Category field when I run the job and then I can report on it using a Pivot table. Unfortunately, the field is not updating. Any quick thoughts?

[Jim Coyne]

Have you been able to exclude weekends and holidays?   I do have an idea that I could expand on once I get a bit of free time.

[attanhes]

Hi Jim,

I tried with below script but still no luck...

getDuration:function addSchedule(durationCalculator) {

// Load the "8-5 weekdays excluding holidays" schedule into our duration calculator.

var scheduleName = "8-11 WeekDays SLA schedule excluding holidays";

var grSched = new GlideRecord('cmn_schedule');

grSched.addQuery('name', scheduleName);

grSched.query();

if (!grSched.next()) {

gs.log('*** Could not find schedule "' + scheduleName + '"');

return;

[tsoct]

Hi Jim,

Hope to get some advice from you on how to exclude weekend from the calculation.

I tried to exclude weekend by using this script below. However, the aging category for all incidents become default 0_2.

Could you help you advice on this?

Many thanks!

(function(){

                            var elapsedTime = 0;

                            var dc = new DurationCalculator();

                            dc.setSchedule('sys_id of weekdays 8x5 exclude holiday schedule');

                            var aging = '';

                            var currentTimeNow = gs.nowDateTime();

                            var gr = new GlideRecord('incident');

                            gr.addEncodedQuery('state!=7^u_aging_category!=>28^ORu_aging_category=');

                            gr.query();

                            while(gr.next()) {

                                  elapsedTime = dc.calcScheduleDuration(gr.opened_at.getDisplayValue(),currentTimeNow)/60/60/24; //convert seconds to day

                                                          if (elapsedTime <= 2) aging = '0_2';

                                                                                        if (elapsedTime > 2)   aging = '3_7';

                                                                                        if (elapsedTime > 7)   aging = '8_14';

                                                                                        if (elapsedTime > 14)   aging = '15_21';

                                                                                        if (elapsedTime > 21)   aging = '22_28';

                                                                                        if (elapsedTime > 28)   aging = '>28';

                                                          gr.setWorkflow(false);       //skip any Business Rules

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

                                                          gr.u_aging_category = aging;

                                                          gr.update();

                            }

})();

[stephaniet]

Hi James,

I am very new to ServiceNow, so please pardon my ignorance if it is something simple.

I am having the opposite problem with this.   I was able to set up the scheduled job with the script and set up the dictionary with all the correct choices.   When I executed the job it set everything to the default value (0_2).   Not sure what I'm doing wrong.   Can you help?

I am trying to run it on my sc_task table instead of my incident table (I changed the reference to it in line 5):

[Jim Coyne]

Actually, what I think happened is the new column values were set to "0_2" when you created the column because of the default value and the script itself is NOT really running.   If you are going to have the script right in the Scheduled Script Execution record, which is fine, you need to call the function in order for it to run.   All it is doing is defining the function but not actually running the code.

To fix it, replace line 1 with:

(function(){

...and line 24 with:

})();

That will create a Self-Executing Anonymous Function which will define the function and run it right away.   You can read more about them here - Self-Executing Anonymous Functions.

Let me know if that fixes your issue.