Need help with aggregate script

[robhaas]

I am attempting to populate a field fields on a table using GlideAggregate. I have successfully managed to populate 1 field, but am not looking to populate others. The idea here is to find all users with a specific department number and then count the amount of incidents open/closed/etc... Here is what I have to calculate closed yesterday. I am only using 1 specific user right now for testing.

**********************************************************************************

var usr = new GlideRecord('sys_user');

usr.addQuery('sys_id', '0a9e1a1a4fc89e00c0cba3928110c7a8');

usr.query();

var person = usr.sys_id;

while (usr.next()){

var inc = new GlideAggregate('incident');

inc.addQuery('closed_at', '>=', gs.beginningOfYesterday());

inc.addQuery('closed_at', '<', gs.beginningOfToday());

inc.addQuery('assigned_to', person);

inc.addAggregate('COUNT');

inc.query();

var num = 0;

if(inc.next()){

num = inc.getAggregate('COUNT');

var stats = new GlideRecord('u_stats');

stats.addQuery('u_analyst.sys_id', person);

stats.query();

if (stats.next()){

stats.u_closed_yesterday = num;

stats.update();

}

}

};

**********************************************************************************

I want to also count open incidents and put that in the field u_open on the stats table. So what I need help with, is how do I cleanly pass the variable 'person' to a new function and run an aggregate? For instance, if this is the script to count open incidents, I just want to set the 'person' query as global and use it in multiple functions.

**********************************************************************************

	var inc = new GlideAggregate('incident');
	inc.addQuery('state', 'IN', '1,10,11,12,20');
	inc.addQuery('assigned_to', person);
	inc.addAggregate('COUNT');
	inc.query();

var open = 0;

	if(inc.next()){
	open = inc.getAggregate('COUNT');

	var stats = new GlideRecord('u_stats');
	stats.addQuery('u_analyst.sys_id', person);
	stats.query();

	if (stats.next()){
	stats.u_open = open;
	stats.update();
			}
		}
	}

}

**********************************************************************************

Hopefully my question makes sense. If not, I will do my best to explain it better. Thanks for all help in advance!

[Chuck Tomasi]

Hi Robert,

I cleaned things up a bit, but it would look something like this. If you already have the sys_id, then the first usr query to get a sys_id doesn't buy you anything. You also don't need to dot-walk to a sys_id on a reference field. The reference is already a sys_id.

var person = '0a9e1a1a4fc89e00c0cba3928110c7a8';

if (usr.get(person)) {

   var inc = new GlideAggregate('incident');
   inc.addQuery('closed_at', '>=', gs.beginningOfYesterday());
   inc.addQuery('closed_at', '<', gs.beginningOfToday());
   inc.addQuery('assigned_to', person);
   inc.addAggregate('COUNT');
   inc.query();

   var num = 0;

   if(inc.next()){
   num = inc.getAggregate('COUNT');

   var stats = new GlideRecord('u_stats');

   if (stats.get('u_analyst', person)){
   stats.u_closed_yesterday = num;
   stats.update();
   }
   }
}


function2(''0a9e1a1a4fc89e00c0cba3928110c7a8');

function2(person) {
   var inc = new GlideAggregate('incident');
   inc.addQuery('state', 'IN', '1,10,11,12,20');
   inc.addQuery('assigned_to', person);
   inc.addAggregate('COUNT');
   inc.query();

   var open = 0;

   if(inc.next()) {
   open = inc.getAggregate('COUNT');

   var stats = new GlideRecord('u_stats');
   stats.addQuery('u_analyst', person);
   stats.query();

   if (stats.next()){
   stats.u_open = open;
   stats.update();
   }
   }
}

[robhaas]

Thanks Chuck. Question...what about the case where I do not have have the sys id for the user. So back to the original thought of pulling all users that have a specific department number. Such as:

var usr = new GlideRecord('sys_user');
usr.addActiveQuery();
usr.addQuery('department_number', '1222');
usr.query();

I need to pull all users and run the above aggregate functions.

[robhaas]

ctomasi Also, I get this error on the code you provided.

Javascript compiler exception: missing ; before statement (null.null; line 29) in:
var person = '0a9e1a1a4fc89e00c0cba3928110c7a8';

[Chuck Tomasi]

Can you share your entire script? I don't know what line 29 refers to at this point. Thanks.