Compare "Last Scanned" and "Install Date" on cmdb_sam_sw_install table

othomas1
Kilo Guru

Hello everyone!

I am trying to create a business rule that compares the "last scanned" and "install date" fields, whatever records that are found to be 90 days older than the newest date in the install column, those should be deleted.

 

find_real_file.png

 

Script that i started:

 


var gr = new GlideRecord('cmdb_sam_sw_install');
gr.query();

while(gr.next()) {


var gdt1 = new GlideDateTime("last_scanned");
var gdt2 = new GlideDateTime("install_date");
var dur = new GlideDuration();

var dur = GlideDateTime.subtract(gdt1, gdt2); //the difference between gdt1 and gdt2
gs.print(dur.getDisplayValue());


}

 

Is it possible to compare a date field with a date/time field? If so, is there another thread in the community i can reference?

 

Any information is appreciated!

26 REPLIES 26

Hope you will mark my answer as correct. it really takes efforts to do so.

Thank you,
Abhishek Gardade

ah ok, when i change the table name i get some errors, i will keep working on this and update the thread. But i will most definitely acknowledge your efforts with my question.

 

*** Script: Software Display NameMicrosoft Windows Server 2008 Enterprise x64 6.1 || Created ON: 2018-11-10 00:47:41
*** Script: Created Date: null
*** Script: updated:2018-07-01
Evaluator: org.mozilla.javascript.EcmaError: Cannot convert null to an object. Caused by error in script at line 19 16: var startDate = gdt1.getDate(); // Replace u_start_date with your field name of Start Date 17: var currentDate = gdt.getDate(); 18: ==> 19: var diffDays = gs.dateDiff(startDate.getDisplayValue(),currentDate.getDisplayValue(), true); 20: var daysIn = (diffDays/(60*60*24)); 21: 22: gs.print(daysIn );

You are getting error because created date is NULL, and you are passing same value to dateDiff Function.

Check if you have the value for Created date on the record.

Thanks,

Abhishek 

Thank you,
Abhishek Gardade

these are the two fields im trying to compare

 

find_real_file.png

I changed the code to:

 

var sftw = new GlideRecord('cmdb_sam_sw_install');
//sftw.addQuery('sys_id','b4fd7c8437201000deeabfc8bcbe5dc1');
sftw.query();

if(sftw.next()){

gs.print('Software Display Name'+ sftw.display_name +" || Created ON: "+ sftw.sys_created_on);

var gdt1 = new GlideDateTime(sftw.install_date);

gs.print("Installation Date: "+ gdt1.getDate());

var gdt = new GlideDateTime(sftw.last_scanned);

gs.print("Last Scanned Date:"+ gdt.getDate());

var installDate = gdt1.getDate(); // Replace u_start_date with your field name of Start Date
var scanDate = gdt.getDate();

var diffDays = gs.dateDiff(installDate.getDisplayValue(),scanDate.getDisplayValue(), true);
var daysIn = (diffDays/(60*60*24));

gs.print(daysIn );

}

 

 

 

 

 

 

Results:

 

*** Script: Software Display NameMicrosoft Windows Server 2008 Enterprise x64 6.1 || Created ON: 2018-11-10 00:47:41
*** Script: Installation Date: null
*** Script: Last Scanned Date:2018-07-01
Evaluator: org.mozilla.javascript.EcmaError: Cannot convert null to an object. Caused by error in script at line 20 17: var installDate = gdt1.getDate(); // Replace u_start_date with your field name of Start Date 18: var scanDate = gdt.getDate(); 19: ==> 20: var diffDays = gs.dateDiff(installDate.getDisplayValue(),scanDate.getDisplayValue(), true); 21: var daysIn = (diffDays/(60*60*24)); 22: 23: gs.print(daysIn );