Scheduled report conditional script for new changes or start_date/end_date updates

[dianacuceu]

Hello,

I'm working on scheduling a change_request report - daily but only if there are new changes created 1 day ago or if any of planned start date or

planned end date have been updated since yesterday.

I have prepared the following script for the first part (new changes created) but I don't know how to do the second part (start_date / end_date updates).

// Only run this Scheduled Job if there are active Changes created 1 day ago

var ga = new GlideAggregate('change_request');

ga.addAggregate('COUNT');

ga.addQuery('created', '>=', ga.daysAgo(1));

ga.query();

ga.next();

ga.getAggregate('COUNT') !== '0'

Could you please help finalizing this script?

Thank you,

Diana

[sergiu_panaite · Accepted solution]

Hi Diana,

I modified your script into this:

// Only run this Scheduled Job if there are active Changes created 1 day ago
var ga = new GlideAggregate('change_request');
ga.addActiveQuery();
ga.addAggregate('COUNT');
ga.addQuery('sys_created_on', '>=', gs.daysAgo(1));
ga.query();

var changes = 0;
if (ga.next()){
   changes = ga.getAggregate('COUNT');
}
gs.print('There are: ' + changes + ' changes created 1 day ago');

var gr = new GlideRecord('change_request');
gr.addActiveQuery();
gr.query();

while (gr.next()){
       var audit = new GlideRecord('sys_audit');
       audit.addQuery('documentkey', gr.sys_id);
       audit.query();


       while (audit.next() && ((audit.fieldname == 'start_date') || (audit.fieldname = 'end_date'))){
               if (audit.sys_created_on >= gs.daysAgo(1)){
                       gs.print('We have the a change with start_date or end_date updated and change record has number: ' + gr.number);
               }
       }
};

I used 2 parts based on your OR condition:

- one to get a count on all active change requests created since yesterday

- one part to check all active change requests and see if start_date OR end_date have been modified since yesterday (modified means a record has been inserted into sys_audit table). Now this only works if start_date/end_date are audited (by default they are). If they are not audited, then you won't know if they were or not updated since yesterday, so I am not going to talk about that case.

I run the script via Background scripts on my own instance where I modified start_date for two changes and I get:

[0:00:00.042] Script completed in scope global: script
*** Script: There are: 7 changes created 1 day ago
*** Script: We have the a change with start_date or end_date updated and change record has number: CHG0000001
*** Script: We have the a change with start_date or end_date updated and change record has number: CHG0030002

Hope this helps.

Let me know if you need anything extra.

Regards,

Sergiu

[dianacuceu]

Many thanks Sergiu! Will give it a try.

Diana

[dianacuceu]

Hi Sergiu,

Apologies for the late feedback... I just wanted to thank you for the help. It is working as expected!

Thanks again ,

Diana

[sergiu_panaite]

Glad that I could help and to hear it's working as expected Diana!

Regards,

Sergiu