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

[othomas1]

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.

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!

[othomas1]

The value for that field which i change the name to installation date is a date/time field.

[AbhishekGardade]

Thats Okay,

Cam you select the another record who having Installation date and Last Scanned date? Till then, I will try to check the possible issues/ feasibility.

Thanks

[othomas1]

Do you want me to single out one record from the table instead of trying to query all of them?

[AbhishekGardade]

Yes try out with single record.If it is successful then you can run it for all.

Thanks,
Abhishek

[othomas1]

I tried it out with an encoded query:

var sftw = new GlideRecord('cmdb_sam_sw_install');
sftw.addEncodedQuery('GOTOdisplay_nameLIKESQL^installed_on=49a0769a6f7fc2000dd741dc5d3ee4cb')

sftw.query();

while(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 );

}

Output

****************

*** Script: Software Display NameMicrosoft SQL Server Compact 3.5 SP2 Query Tools ENU || Created ON: 2018-11-11 22:20:12
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server 2008 Setup Support Files || Created ON: 2018-11-11 22:21:17
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft System CLR Types for SQL Server 2014 || Created ON: 2018-11-11 22:20:37
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display NameMicrosoft SQL Server Data-Tier Application Framework (x64) || Created ON: 2018-11-11 22:21:47
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2014 T-SQL Language Service || Created ON: 2018-11-11 22:20:22
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2014 Management Objects  (x64) || Created ON: 2018-11-11 22:21:08
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server || Created ON: 2018-11-11 22:19:41
*** Script: Installation Date: null
*** Script: Last Scanned Date:2019-06-22
Evaluator: org.mozilla.javascript.EcmaError: Cannot convert null to an object.
   Caused by error in script at line 21

     18: var installDate = gdt1.getDate(); // Replace u_start_date with your field name of Start Date
     19: var scanDate = gdt.getDate();
     20: 
==>  21: var diffDays = gs.dateDiff(installDate.getDisplayValue(),scanDate.getDisplayValue(), true);
     22: var daysIn = (diffDays/(60*60*24));
     23: 
     24: gs.print(daysIn );