COUNT DISTINCT by dot-walking filed in PA script

[Yanhong Huang]

Hi All,

I created the PA indicator, need to COUNT DISTINCT by Change request which associate from Outage --> Incident -->Cause by Change.

I tested the script can get the correct change count

var ovt = new GlideAggregate('cmdb_ci_outage');

ovt.addAggregate('COUNT(DISTINCT', 'task_number.ref_incident.caused_by');

ovt.setGroup(false);

ovt.query();

ovt.next();

//return  (ovt.getAggregate('COUNT(DISTINCT', 'task_number.ref_incident.caused_by'));

gs.print(ovt.getAggregate('COUNT(DISTINCT', 'task_number.ref_incident.caused_by'));

However, I cannot use the PA script to collect the right count

Would you please advise?

[Prataps135]

Hi @Yanhong Huang,

(function executeRule() {
    var uniqueChanges = new Set(); // Store unique Change Request numbers

    var ga = new GlideAggregate('cmdb_ci_outage');
    ga.addAggregate('COUNT'); // Dummy count, we will use our Set for distinct values
    ga.addNotNullQuery('task_number.ref_incident.caused_by'); // Ensure there's a related Change Request
    ga.query();

    while (ga.next()) {
        var changeNumber = ga.getValue('task_number.ref_incident.caused_by');
        uniqueChanges.add(changeNumber);
    }

    return uniqueChanges.size; // Return distinct count
})();

Please try this script.

Thanks and Regards,
Pratap Singh Sisodia

[Bert_c1]

Testing the above, it fails at line 1. for GlideAggregate reference see:

https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server_legacy/c_GlideAggregateAPI

"COUNT('DISTINCT" is not valid.

Naviaget to Platform Analytics Administration -> Indicators -> Scripts to see OOB examples.

the 'ICenter.ActiveUsers' script is one example, based on that the following may work:

getCount();

function getCount() {
	var ovt = new GlideRecord('cmdb_ci_outage');
	//ovt.addAggregate('COUNT(DISTINCT', 'task_number.ref_incident.caused_by');
	//ovt.setGroup(false);
	ovt.addExtraField('task_number.ref_incident.caused_by');
	ovt.addQuery('task_number.ref_incident.caused_by', '!=', '');
	ovt.query();
	var totalCount = ovt.getRowCount();
//	gs.info(totalCount);
	return totalCount;
}

I don't believe GlideAggregate supports the '.addExtraField()' method.

Testing requires knowing the Indicator definition and Indicator Source definition.