Script to find any RITM's that have no catalog tasks

[Harry Campbell2]

Hello All,

I need a background script that will find any active approved RITM records that have no catalog tasks.

In our system, all approved requested items should have a catalog task but there are a lot of historical records that have none. I need to find all of these records.

I think a background script is the best way to achieve this but I'm not sure how it would work. I have a script that will find and print any RITM numbers that are active and approved:

var gr = new GlideRecord('sc_req_item');
gr.addEncodedQuery('active=true^approval=approved');
gr.query();
while (gr.next()) {
gs.print(gr.number);
}

Is there anything I can add to this script to return the RITM numbers that have no catalog tasks?

Many Thanks

Harry

[Brad Tilton]

Hi Harry,

What you're probably going to need to do is write a second query inside of your while loop that queries the sc_task table for any records where the request_item is that item. If there aren't any you could print the number.

This approach will work ok if you're just running this once to find a total number, but if you were going to be running it on a schedule or in a business rule you'd probably want to make it more efficient than that by pushing sys_ids to an array, etc.

[Ankur Bawiskar]

Hi Harry,

Please find the below script

var encodedQuery = 'request_item.active=true^request_item.approval=approved';

This query will be applied on "sc_task" table

var gr = new GlideRecord('sc_task');  

gr.addEncodedQuery(encodedQuery);  

gr.query();  

gs.print(gr.getRowCount());

while (gr.next()) {  

}  

Mark Correct if this solves your issue and also hit Like and Helpful if you find my response worthy based on the impact.

Thanks

Ankur

[Patrick DeCarl1 · Accepted solution]

Harry,

Try below script.

var noTaskCounter = 0;
var gr = new GlideRecord('sc_req_item');
gr.addEncodedQuery('active=true^approval=approved'); //<-- whatever you want to filter on
gr.query();
while (gr.next()) {
       //you could do a GlideAggregate here also. 
       var tsk = new GlideRecord('sc_task');
       tsk.addQuery('request_item', gr.sys_id);
       tsk.query();
       if (!tsk.next()) {
               gs.info('RITM == {0} has Zero Tasks',gr.number);
               noTaskCounter++;
       }
}
gs.info('Out of {0} RITMs, only {1} had Zero Tasks', gr.getRowCount(), noTaskCounter);   

Output ex:

*** Script: RITM == RITM0000012 has Zero Tasks

*** Script: Out of 8 RITMs, only 1 had Zero Tasks

[Harry Campbell2]

This works a treat! Exactly what I was after, many thanks Patrick