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

[J Siva]

Hi @suuriyas 
Create another scheduled job and set it to run after the first job. Try below script.

var prod = new GlideRecord('< software package table>');
prod.query();
while (prod.next()) {

    var rec = new GlideAggregate('<software instances table >');
    rec.addEncodedQuery('<BUILD QUERY WHERE INSTALLED STATUS UPDATED in LAST 7 DAYS> and PRODUCT NAME IS CURRENT PRODUCT PACKAGE RECORD: PROD'); 
    rec.addAggregate('COUNT');
    rec.query();

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

    }
}

Regards,
Siva

[suuriyas]

HI @J Siva ,

Thanks for the response,

Can you please elaborate more on add encoded query part i did not understand this

and PRODUCT NAME IS CURRENT PRODUCT PACKAGE RECORD: PROD'); 

[J Siva]

@suuriyas 

Product name field is the ref field in the "Software instance" table.

Go to Software instance table and build the query as per your requirement, then copy that query and use it in the script.

[suuriyas]

HI @J Siva ,

Thanks for the response i tried it but it did not work

For testing i filter with just one particular product name and checked example 

var prod = new GlideRecord('cmdb_ci_spkg');

prod.query();

while (prod.next()) {

    var rec = new GlideAggregate('cmdb_software_instance');

    rec.addEncodedQuery('software=d3d2a1be87a65250da3ecbbc8bbb3500');

    rec.addAggregate('COUNT');

    rec.query();

    if (rec.next()) {

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

        prod.install_count = count;

        prod.update();

    }

}

for this particular product,  we have 4675 records in software instances table out which 2902 records has install on field value( not empty) so in software package table it should have updated as 2902 but still it is showing as 4675 as a install count