How to get the record count in the while loop?

[Rick54]

Hello,

I am trying to get the count of all record that matches the query condition. When I use the below script, the count is always being '1' even though there are 1+ records that matches the condition. I am I missing anything? Please suggest.

var count = 0;
var gr= new GlideAggregate('cmdb_ci_db_mssql_instance');
gr.addAggregate('COUNT');
gr.addQuery('sys_id', 'IN', instances);//This variable holds 1 or more record sys ids
gr.addQuery('install_status',7);
gr.query();
while(gr.next()){
	instanceCount = gr.getAggregate('COUNT');
	gs.print(instanceCount);
	
}

This time I have 2 records that matched the condition
Output:
*** Script: 0
*** Script: 1
*** Script: 1
*** Script: 0

Expected Ouput: 2

My expectation is that these records are queried one after one that's the reason I am getting individual record's count. Is there any way to count all looped records and combine and output as 2? Thanks

[Brian Lancaster]

I don't have anything in that CMDB table so I tried it with incident looking for assigned_to in list of sys_id and it works fine.  Try this:

var gr= new GlideAggregate('cmdb_ci_db_mssql_instance');
gr.addAggregate('COUNT');
gr.addQuery('sys_id', 'IN', instances);//This variable holds 1 or more record sys ids
gr.addQuery('install_status',7);
gr.query();
if(gr.next()){
	gs.print(gr.getAggregate('COUNT'));
}

[Rick54]

Thanks for looking into this Brian. Instances variable is key here. Values in this variable comes from another glide record. Below is the complete script.

var rel2 = new GlideRecord('cmdb_rel_ci');
rel2.addEncodedQuery("type=55c95bf6c0a8010e0118ec7056ebc54d^child.sys_class_name=cmdb_ci_db_mssql_instance^parent.sys_class_name=cmdb_ci_database^parent.sys_id="+db+"");//db variable holds one database record 
rel2.query();
while(rel2.next()){
	var instances = rel2.child;
	var instCount = rel2.getRowCount();//this is giving me the correct count in this case 3 count
	var inst = new GlideRecord('cmdb_ci_db_mssql_instance');
	inst.addQuery('sys_id', 'IN', instances);
	inst.addQuery('install_status', '7');
	inst.query();
	while(inst.next()){
		var instanceCount = inst.getRowCount();//always gives me 1 as count 
		if(instCount == instanceCount){
			
		//do something in this loop
		}
		
	}
}

[Brian Lancaster]

So you said instance was going to be a list of sys_id but because it is in a while loop you will only get one sys_id.  I'm not 100% sure what you are trying to do but maybe in your first while loop you need to make you instances an array (declare var instances = [] before the while loop) and then do instances.push(rel2.child);.  I would then end this while loop and do you second glide record query where inst.addQuery('sys_id, 'IN', instances);

you may need to do instances.toString() or instances.toString().split(','); 

[Rick54]

I tried this approach and it just gives me just one record.

var db = "981bba741b4437889ed265b82d4bcbba";
var instances = [];
var rel2 = new GlideRecord('cmdb_rel_ci');
rel2.addEncodedQuery("type=55c95bf6c0a8010e0118ec7056ebc54d^child.sys_class_name=cmdb_ci_db_mssql_instance^parent.sys_class_name=cmdb_ci_database^parent.sys_id="+db+"");//db variable holds one database record 
rel2.query();
while(rel2.next()){
	instances.push(rel2.child);
}
	//gs.print(instances);
	var instCount = rel2.getRowCount();//this is giving me the correct count in this case 3 count
	var inst = new GlideRecord('cmdb_ci_db_mssql_instance');
	inst.addQuery('sys_id', 'IN', instances.toString().split(','));
	inst.addQuery('install_status', '7');
	inst.query();
	while(inst.next()){
		var instanceCount = inst.getRowCount();//always gives me 1 as count 
		gs.print(instanceCount);
		
	}

[Brian Lancaster]

Then you query is only returning one record.  Try going to to cmdb_ci_db_mssql_instance list or records and doing a lookup of sys_id start with 1st sys_id or sys_id strat with 2nd sys_id and install_state = what ever 7 is equal to and see how many record you get back.