scheduled job: set closure(closed_at) for closed items

[chrisn_]

Hello everyone,

I am faced with the unique problem of having a large number of RITM records not having a closure or (closed_at) value set. This was caused due to a known issue in SN where if a workflow does not have a timer, task or approval in it then certain business rules do not have time to run and complete the overall request process. This includes not changing the active flag for RITM's and as I have learned the closed_at date. I have a business rule that has fixed this from now on, but I am now faced with fixing the existing records. I need to run a scheduled job that will look at closed RITM's that has no closed_at value in it and set it to the updated field value as their final update was the closed_at date. Any help with this would be greatly appreciated as I am still quite a novice to background scripting like this. This is what I am attempting but I am not getting a list of the records back like I'd hoped before moving on to attempting to set the closed_at field.

var count = 0;
var gr = new GlideRecord('sc_req_item');
gr.addQuery('active','false');
gr.addQuery('closed_at','');
gr.query();
while(gr.next()){
count++;
// gr.closed_at = gr.sys_updated_on;
// gr.update();
}
gs.print(count + ' records updated');

[Ahmmed Ali]

Hello,

the best way to have his worked would be, open sc_req_item table in list view. Then apply the filter condition you want to apply.

then right click on the filer condition and click on copy query.

then you can use the same query in below script. 

var count = 0;
var gr = new GlideRecord('sc_req_item');

gr.addEncodedQuery("paste the copied query here");
gr.query();
while(gr.next()){
count++;
// gr.closed_at = gr.sys_updated_on;
// gr.update();
}
gs.log(count + ' records updated',"record count"); //this will create a log entry with source as record count.

if you are seeing records in the list view after applying filter then you should see same records returned from the script.

Thanks,

Ali

[sathish639]

Hi,

We faced a similar issue where some RITMs did not have closed_at value populated after closure. We have scheduled a job in place to fix those RITMs. What it does is that it looks for the RITM's corresponding metric data and for the definition: Request Item State Duration with value: "Closed - Complete" and updates the RITM Closed Date with this particular metric's start date. By this way, the correct closure date/time will be updated. Hope this helps!

Below is the scheduled job. 

Schedule Job:

//To fix the RITMs that are Closed but the Closed date is empty.

var i = 0;
var j = 0;

var ritm = new GlideRecord('sc_req_item');
ritm.addQuery('state','3');
ritm.addNullQuery('closed_at');
ritm.query();
while(ritm.next())
{
var rmet = new GlideRecord('metric_instance');
rmet.addQuery('table','sc_req_item');
rmet.addQuery('definition','64d462970f291a00e5ea05cce1050e6c'); //Request Item State Duration
rmet.addQuery('value','Closed - Complete');
rmet.addQuery('id',ritm.sys_id);
rmet.query();
if(rmet.next())
{
ritm.active = 'false';
ritm.closed_at = rmet.start;
ritm.setWorkflow(false); //Do not run business rules
ritm.update();
// gs.log ('RITM updated : '+ritm.number,'src1');
j++;
}
else
gs.log ('RITM not updated (without "Closed - Complete" metrics entry) : '+ritm.number,'src1');
i++;
}

gs.log ('Total no. of RITMs with empty Closed Date : '+i, 'src1');
gs.log ('Total no. of RITMs updated with Closed Date from Metrics Data: '+j, 'src1_1');