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

[dianacuceu]

Hi,

I've noticed some issues for the first script regarding the new changes. I couldn't figure out why it's happening but the scheduled report is sent multiple times even though there are no new changes since the last report. To give you an example: I create a change on Monday and I expect to receive a report with it on Tuesday. However, what happens is that I receive the same report on Tuesday, Wednesday, Thursday etc and at some points I no longer receive it.

Another thing - is there any information with regards to change created date withit the sys_audit table so that we use something like:

1. var gr = new GlideRecord('change_request');  
2. gr.addActiveQuery();  
3. gr.query();  
4.  
5. while (gr.next()){  
6.       var audit = new GlideRecord('sys_audit');  
7.       audit.addQuery('documentkey', gr.sys_id);  
8.       audit.query();  
9.  
10.  
11.       while (audit.next() && ((audit.fieldname == 'start_date') || (audit.fieldname = 'end_date') || (audit.fieldname = 'created_on'))){  
12.               if (audit.sys_created_on >= gs.daysAgo(1)){  
13.                       gs.print('We have the a change with start_date or end_date updated and change record has number: ' + gr.number);  
14.               }  
15.       }  
16. };  

And the last question: is the ";" at the end of the script mandatory?

Thank you in advance,

Diana

[sergiu_panaite]

Hi Diana,

I am not sure why you're receiving the report multiple times. This might be related to the way report was created.

As for your code you mentioned, sys_audit.fieldname == 'sys_created_on', this won't help, as sys_created_on doesn't have a previous value to be tracked.

On the other hand I see an issue with this condition in the script:

while (audit.next() && ((audit.fieldname == 'start_date') || (audit.fieldname = 'end_date')

First, second condition should be (audit.fieldname == 'end_date') (double ==, not single)

and

Secondly, it doesn't evaluates correctly on my own instance (not sure why at this stage).

Instead, I would use something like this where I put the condition on sys_created_on directly in the change_request GlideRecord call:

var gr = new GlideRecord('change_request'); 
gr.addActiveQuery(); 
gr.addQuery('sys_created_on', '>=', gs.daysAgo(1));
gr.query(); 

gs.print('Changes created since yestearday: ' + gr.getRowCount());

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

       gs.print('We have ' + audit.getRowCount() + ' records in sys_audit for change: ' + gr.number);

       while (audit.next()){ 

           if (audit.fieldname == 'start_date'){
               gs.print('Change ' + gr.number + ' has start_date changed from: ' + audit.oldvalue + ' to ' + audit.newvalue);
           }   
           if (audit.fieldname == 'end_date') {
               gs.print('Change ' + gr.number + ' has end_date changed from: ' + audit.oldvalue + ' to ' + audit.newvalue);
           } 
       } 
}

Running this on my own instance gives me:

[0:00:00.041] Script completed in scope global: script

---

*** Script: Changes created since yestearday: 8
*** Script: CHG0000010
*** Script: We have 0 records in sys_audit for change: CHG0000010
*** Script: CHG0000011
*** Script: We have 0 records in sys_audit for change: CHG0000011
*** Script: CHG0000012
*** Script: We have 0 records in sys_audit for change: CHG0000012
*** Script: CHG0000013
*** Script: We have 0 records in sys_audit for change: CHG0000013
*** Script: CHG0000014
*** Script: We have 0 records in sys_audit for change: CHG0000014
*** Script: CHG0000015
*** Script: We have 0 records in sys_audit for change: CHG0000015
*** Script: CHG0030001
*** Script: We have 4 records in sys_audit for change: CHG0030001
*** Script: CHG0030002
*** Script: We have 8 records in sys_audit for change: CHG0030002
*** Script: Change CHG0030002 has end_date changed from:   to 2016-02-19 10:20:23
*** Script: Change CHG0030002 has start_date changed from:   to 2016-02-18 10:20:20

Hope this helps.

Regards,

Sergiu

[folu]

Hi Sergiu,

I want to schedule a report to send only if there was a change in the records count. How can I do this?

Thanks.