Need to update the record count in software package table

[suuriyas]

HI Community,

I have a requirement, we have schedule job which run daily in software instances table if record is not updated for more than 7 days then install on field is made empty. now in software package table the count needs to be reflected.

For example there is" x " software record in software instances table and there are 12 records and 3 of them are not updated for more than 7 days so for 3 records  install on is made empty now in the software package table the install count should be display as 9 instead of 12.

How can we achieve this?

Thanks in advance

[suuriyas]

HI @J Siva ,

I tried but unfortunately it didn't even execute, this is the script written and 

var prod = new GlideRecord('cmdb_ci_spkg');

prod.query();

while (prod.next()) {

    var rec = new GlideAggregate('cmdb_software_instance');

    rec.addEncodedQuery('installed_on!=NULL^software=+prod.sys_id');

    rec.addAggregate('COUNT');

    rec.query();

    if (rec.next()) {

        var count = rec.getAggregate('COUNT');

        prod.install_count = count;

        prod.update();

    }

}

No update has been done on package table

[J Siva]

@suuriyas Update your encoded query. PFB  the script.

var prod = new GlideRecord('cmdb_ci_spkg');
prod.query();
while (prod.next()) {

    var rec = new GlideAggregate('cmdb_software_instance');
    rec.addEncodedQuery('installed_on!=NULL^software='+prod.sys_id);
    rec.addAggregate('COUNT');
    rec.query();

    if (rec.next()) {
        var count = rec.getAggregate('COUNT');
        prod.install_count = count;
        prod.update();

    }
}

[suuriyas]

HI @J Siva ,

I tried this but unfortunately it did not worked, no update has been done in the table

var prod = new GlideRecord('cmdb_ci_spkg');

prod.query();

while (prod.next()) {

    var rec = new GlideAggregate('cmdb_software_instance');

    rec.addEncodedQuery('installed_on!=NULL^software=+prod.sys_id');

    rec.addAggregate('COUNT');

    rec.query();

    if (rec.next()) {

        var count = rec.getAggregate('COUNT');

        prod.install_count = count;

        prod.update();

    }

}

[Abhijit4]

Hi @suuriyas 

I would suggest to not touch anything on scheduled job. Its better to create BR on Software Instance table which will trigger when Installed On changes to empty. 

In script, you can get related previous package gliderecord and either reduce count by 1 or recalculate ( using glideaggregate )how many software instances are available with package and set that value.

[Abhijit4]

@suuriyas if you still want to go with scheduled job approach then while resetting installed on, you would need to maintain JSON array which will hold package sys_id and software instances removed count.

At the end of scheduled job, you can use above json array to go through all software package and reduce its count based your stored count value.