check this script

[1dusjhyahnt]

I want to fetch sys_id only once at a time but it fetches sys_id almost in repetition manner please fix this issue?

var last15days = Class.create();
last15days.prototype = {
    initialize: function() {},
    report: function() {
        var result = [];
        var grCCC = new GlideRecord('cmdb_ci_computer');
        grCCC.addEncodedQuery("u_latest_communication_date<=javascript&colon;gs.daysAgo(7)");
        grCCC.setLimit(1130);
        grCCC.query();
        while (grCCC.next()) {
            var auditquery = 'documentkey=' + grCCC.sys_id + '^fieldname=u_latest_communication_date';
            var grSysAudit = new GlideRecord('sys_audit');
            grSysAudit.addEncodedQuery(auditquery);
            grSysAudit.query();
            var count = grSysAudit.getRowCount();
            if (count > 5)
                result.push(grCCC.sys_id);


        }
        return result;
    },

    type: 'last15days'
};

[chetanb]

Hello @1dusjhyahnt 

Just store sysid outside audit query and use getrowcount() instead of count vr

Can you please check below script.

var last15days = Class.create();
last15days.prototype = {
initialize: function() {},
report: function() {
var result = [];
var grCCC = new GlideRecord('cmdb_ci_computer');
grCCC.addEncodedQuery("u_latest_communication_date<=javascript&colon;gs.daysAgo(7)");
grCCC.setLimit(1130);
grCCC.query();

while (grCCC.next()) {
var sysId = grCCC.sys_id; // Get sys_id once
var auditquery = 'documentkey=' + sysId + '^fieldname=u_latest_communication_date';
var grSysAudit = new GlideRecord('sys_audit');
grSysAudit.addEncodedQuery(auditquery);
grSysAudit.query();

if (grSysAudit.getRowCount() > 5) { // Check count directly
result.push(sysId);
}
}

return result;
},
type: 'last15days'
};

If my answer solves your query, don't hesitate to make as accepted and helpful.

Regards,

CB

[Sohithanjan G]

Hi @1dusjhyahnt ,

Updated code

var last15days = Class.create();
last15days.prototype = {
    initialize: function() {},
    report: function() {
        var result = [];
        var grCCC = new GlideRecord('cmdb_ci_computer');
        grCCC.addEncodedQuery("u_latest_communication_date<=javascript&colon;gs.daysAgo(7)");
        grCCC.setLimit(1130);
        grCCC.query();
        
        var sysIds = [];
        while (grCCC.next()) {
            sysIds.push(grCCC.sys_id);
        }
        
        var auditquery = 'documentkeyIN' + sysIds.join(',');
        var grSysAudit = new GlideRecord('sys_audit');
        grSysAudit.addEncodedQuery(auditquery);
        grSysAudit.addQuery('fieldname', 'u_latest_communication_date');
        grSysAudit.query();
        
        var counts = {};
        while (grSysAudit.next()) {
            var computerSysId = grSysAudit.documentkey.toString();
            counts[computerSysId] = (counts[computerSysId] || 0) + 1;
        }
        
        for (var sysId in counts) {
            if (counts.hasOwnProperty(sysId) && counts[sysId] > 5) {
                result.push(sysId);
            }
        }
        
        return result;
    },

    type: 'last15days'
};

Please mark as Accepted Solution & Hit helpful !!!