SAM PRO: Deleting outdated records in Software Installation table

[MD_1308]

Hi,

In our organization we are feeding data into Software Installation table via a third party interface. We wish to keep only the latest records based on Last scanned date on a installed on device and delete all the older records.

We have set below provided scheduled job to run weekly, this works fine if we test it as the background script limiting to some individual installed on devices but if we run the script as a scheduled job it deletes records up to some point and breaks afterwards and if we run again it doesn't delete anything further:

Please consider: there are around 8.4 million records  in our SI table

var src=new GlideAggregate('cmdb_sam_sw_install');
src.addAggregate('COUNT');
src.addQuery('discovery_source', 'IN','nxt,avc_ad_grp');
src.addNotNullQuery('last_scanned');
src.addQuery('installed_on.install_status', '!=', '7');  //retired
src.addQuery('installed_on.install_status', '!=', '11'); // pending retire
src.groupBy('installed_on');
src.query();

while (src.next()) {
var aggrsrc=new GlideAggregate('cmdb_sam_sw_install');
aggrSrc.addAggregate('COUNT(DISTINCT', 'last_scanned');
aggrSrc.addQuery('installed_on', src.installed_on);
aggrSrc.addQuery('discovery_source', 'IN','nxt,avc_ad_grp');
aggrSrc.groupBy('discovery_source');
aggrSrc.query();

while (aggrSrc.next() && aggrSrc.getAggregate('COUNT(DISTINCT', 'last_scanned') > 1) {
var aggrMax = new GlideAggregate('cmdb_sam_sw_install');
aggrMax.addQuery('installed_on', src.installed_on);
aggrMax.addQuery('discovery_source', aggrSrc.discovery_source);
aggrMax.addNotNullQuery('last_scanned');
aggrMax.addAggregate('MAX', 'last_scanned');
aggrMax.setGroup(false);
aggrMax.query();

while (aggrMax.next()) {
var instDel = new GlideRecord('cmdb_sam_sw_install');
instDel.addQuery('installed_on', src.installed_on);
instDel.addQuery('discovery_source', aggrSrc.discovery_source);
instDel.addNotNullQuery('last_scanned');
instDel.addQuery('last_scanned', '!=', aggrMax.getAggregate('MAX', 'last_scanned'));
instDel.deleteMultiple();
}
}
}

Please provide guidance, how we can optimize the script so that it can run end to end or if there are any other possibilities.

Regards,

Mohit

[Aleksey4]

Hi,

if you haven't fixed it yet, just an idea - maybe use Auto Flush instead of the script, something like on a screenshot, just adjust the Age and conditions to your needs?

It will take few days to to clean up the old records (in our case ~1mln per day), but you'll get a clean list eventually

[MS25]

Do you have a resolution yet on this? 

[Aditya Dogra]

I am also looking for a solution. In SAMPro deleting old records should have been part of the solution.