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?

[Lavanya34]

Hi Jim,

Thanks for the quick response. This solves my issue 🙂 

Thanks,

Lavanya

[John Vo1]

Jim,

Here is my schedule job script:

When I change opened at dates on the incident to anything over 30 days or even 60 days it always defaults to <91 days.  

[kvillalobos]

This is great! Just what I needed!

[attanhes]

Try this....

gs.include('DurationCalculator');

TestUpdateIncidentAgening();

function TestUpdateIncidentAgening(){

        var dc = new DurationCalculator();

          var ageing = '';

        // var currentTimeNow = gs.nowDateTime();

          var gr = new GlideRecord('incident');

            gr.addQuery('active', true);

            //Run this only for one job for testing

          // gr.addQuery('number','INC652712');

          //gr.addEncodedQuery('u_ageing_category!=>28^OR u_ageing_category=');

          gr.query();

          while(gr.next()) {

              addSchedule(dc);

            var now = new Date();

            var elapsedTime = dc.calcScheduleDuration(gr.opened_at, new GlideDateTime()) /60/60;    

              //elapsedTime value returned is in seconds so diving by 60 twice will give hours.

              //check to see when the item was created

              gs.log('Elapsed Time ' + elapsedTime);

              gs.log('Opened Time ' + gr.opened_at);

              gs.log('Now Time ' + new GlideDateTime());

              if (elapsedTime <= 16){ ageing = '0_2';}         // 1 days is equal to 8hr as per schedule

              if (elapsedTime > 16){ ageing = '3_5';}           // 2 days is eqial to 16hr as per schedule

              if (elapsedTime > 40){ ageing = '6_10';}      

              if (elapsedTime > 80){ ageing = '11_28';}

              if (elapsedTime > 224){ ageing = '>28';}

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

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

              gr.u_ageing_category = ageing;

              gr.update();  

            }

}

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;

      }

      durationCalculator.setSchedule(grSched.getUniqueValue());

}

[Jim Coyne]

So is that working for you then?