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

othomas1 · ‎08-01-2019

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!

AbhishekGardade · ‎08-01-2019

I can see you are getting output. For all the records who have Last Scanned Date available.

As i can see some of the records who don't have Last Scanned Date or Installation date.

Add one more filter condition

Installation date IS NOT EMPTY

AND

Last Scanned Date IS NOT EMPTY

Thanks,

Abhishek

Thank you,
Abhishek Gardade

othomas1 · ‎08-01-2019

Hi Abhishek,

I updated my script to:

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

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 2014 Setup (English) || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display Name%SQL_PRODUCT_SHORT_NAME% SSIS 64Bit For SSDTBI || Created ON: 2018-11-11 22:20:23
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameSQL Server 2012 Client Tools || Created ON: 2018-11-11 22:20:12
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameMicrosoft SQL Server 2012 Transact-SQL Compiler Service || Created ON: 2018-11-11 22:20:12
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameMicrosoft SQL Server 2012 Management Objects || Created ON: 2018-11-11 22:21:27
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2014 Transact-SQL Compiler Service || Created ON: 2018-11-11 22:20:25
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display NameMicrosoft SQL Server 2014 Management Objects || 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 System CLR Types for SQL Server 2012 || Created ON: 2018-11-11 22:21:28
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2008 Setup Support Files || Created ON: 2018-11-11 22:21:48
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server Compact 3.5 SP2 ENU || Created ON: 2018-11-11 22:20:11
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server 2008 R2 Setup (English) || Created ON: 2018-11-11 22:19:51
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameMicrosoft AS OLE DB Provider for SQL Server 2014 || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameSQL Server 2014 Data Tools - BI for Visual Studio 2013 || Created ON: 2018-11-11 22:20:24
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2008 R2 Native Client || Created ON: 2018-05-26 13:47:54
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameMicrosoft SQL Server System CLR Types (x64) || Created ON: 2018-11-11 22:20:12
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameSQL Server 2014 Common Files || Created ON: 2018-11-11 22:20:12
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display NameMicrosoft SQL Server 2012 Native Client || Created ON: 2018-11-11 22:20:37
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameTeradata SQL Assistant 15.10.1.4 || Created ON: 2018-11-11 22:20:37
*** Script: Installation Date: 2017-08-01
*** Script: Last Scanned Date:2019-06-22
*** Script: 690
*** Script: Software Display NameSQL Server 2008 R2 SP2 Client Tools || Created ON: 2018-11-11 22:21:48
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameSQL Server 2012 Management Studio || Created ON: 2018-11-11 22:20:22
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameService Pack 3 for SQL Server 2012 (KB3072779) (64-bit) || Created ON: 2018-11-11 22:21:48
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameMicrosoft System CLR Types for SQL Server 2012 (x64) || Created ON: 2018-11-11 22:19:51
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameMicrosoft SQL Server 2008 R2 Management Objects || Created ON: 2018-11-11 22:19:51
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server 2012 PowerPivot for Excel 32-bit || Created ON: 2018-11-11 22:20:24
*** Script: Installation Date: 2015-07-29
*** Script: Last Scanned Date:2019-06-22
*** Script: 1424
*** Script: Software Display NameSQL Server 2012 Common Files || Created ON: 2018-11-11 22:20:24
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display Name%SQL_PRODUCT_SHORT_NAME% Data Tools - BI for Visual Studio 2013 || Created ON: 2018-11-11 22:20:37
*** Script: Installation Date: 2017-05-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 780
*** Script: Software Display NameMicrosoft SQL Server 2012 Setup (English) || Created ON: 2018-11-11 22:21:47
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameSQL Server 2014 SQL Data Quality Common || Created ON: 2018-11-11 22:20:26
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display NameMicrosoft SQL Server 2014 Transact-SQL ScriptDom || Created ON: 2018-11-11 22:21:48
*** Script: Installation Date: 2017-05-05
*** Script: Last Scanned Date:2019-06-22
*** Script: 778
*** Script: Software Display NameMicrosoft SQL Server 2012 Policies || Created ON: 2018-11-11 22:21:48
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server System CLR Types || Created ON: 2018-11-11 22:21:08
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server 2008 R2 Policies || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2016-02-04
*** Script: Last Scanned Date:2019-06-22
*** Script: 1233.9583333333333
*** Script: Software Display NameMicrosoft SQL Server 2012 Transact-SQL ScriptDom || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-02-02
*** Script: Last Scanned Date:2019-06-22
*** Script: 869.9583333333334
*** Script: Software Display NameSQL Server 2008 R2 SP2 Management Studio || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameSQL Server 2008 R2 SP2 Common Files || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334
*** Script: Software Display NameService Pack 3 for SQL Server 2008 R2 (KB2979597) (64-bit) || Created ON: 2018-11-11 22:20:36
*** Script: Installation Date: 2017-02-03
*** Script: Last Scanned Date:2019-06-22
*** Script: 868.9583333333334

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

My only concern is how will i be able to do this for the entire table? Adding the filters reduced it to 41 records, i would need to scan the entire table. I cant thank you enough for your assistance.

AbhishekGardade · ‎08-01-2019

Hello Othomas,

It wont make any sense to find out the records whose having empty dates. It will give you an error which you shown previously.

One more thing I want to check in encoded query what is the below query for which is marked in bold? can you share me a screenshot of filter query?

sftw.addEncodedQuery('GOTOdisplay_nameLIKESQL^installed_on=49a0769a6f7fc2000dd741dc5d3ee4cb^last_scannedISNOTEMPTY^install_dateISNOTEMPTY')

Thanks,
Abhishek

Thank you,
Abhishek Gardade

AbhishekGardade · ‎08-03-2019

If I have answered your question, please mark my response as correct so that others with the same question in the future can find it quickly and that it gets removed from the Unanswered list.
Thank you,Abhishek

Thank you,
Abhishek Gardade

othomas1 · ‎08-06-2019

There is an out of the box solution that addressing the issue, here is the link to the doc site:

https://docs.servicenow.com/bundle/madrid-it-service-management/page/product/asset-management/concep...