Need to update the install count field

[suuriyas]

HI Community,

I have a requirement, where i need to update the install count details in software package table.

This data is depend on the number of records present in software instances table with install on being not empty.

For example in software instances table there are 234 records with product name as google 13 out of which install on field is empty for 4 records then in software package table 230 needs to be updated in install count field

Created a scheduled job which run on daily basics

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

    }

}

But it is not working, can you please let me know what i'm doing wrong 

Thanks in advance

[Chaitanya ILCR · Accepted solution]

Hi @suuriyas ,

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

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

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

Try this

Please mark my answer as helpful/correct if it resolves your query.

Regards,
Chaitanya

[shantanu_patel8]

Hi @suuriyas 

Looks like the issue is with the encoded Query. You can use the following 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.addQuery('installed_on','!=','');

    rec.addQuery('software',prod.sys_id.toString());

    rec.groupBy('software');

    rec.addAggregate('COUNT');

    rec.query();

    if (rec.next()) {

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

        prod.install_count = count;

        prod.update();

    }

}

Mark the answer as correct and helpful if it resolves your issue. Happy Scripting 

-Shantanu