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!

[robhaas]

I was attempting to you what you provided.

[Chuck Tomasi]

I'm sorry, I'm working on several community script related questions at once. From what I read...

The idea here is to find all users with a specific department number and then count the amount of incidents open/closed/etc...

You want to save the incident count in to the u_stats table?

Your original script assumes there is already an entry for the analyst in the u_stats table. Did you want to add a check to add a record if not found?

[robhaas]

No problemt at all! I greatly appreciate the help. The original script uses a single user just for my purposes of trying to get more than 1 incident stat saved to the u_stats table. Here is where I am at. Instead of using a single user, I want to find all users where the department_number = 1222 and store those users.

I was thinking they would be stored in the 'person' variable? Maybe they need to be in an array? Either way, once we have the specific users, we want to run the first aggregate function where we count the incidents closed yesterday that were assigned to any of the specific users. Once we have that data, we want to query the u_stats table for each user and if they are found, we update the corresponding stat in the u_stats table for each user.

I have been able to do this much on my own using the top portion of my original script in this thread. Where I am losing it is attempting to run additional stats on the same script using the already specified list of users.

When I look at what you provided, we declare person and just pass that to multiple functions, each of which will update a separate stat field on the u_stats table. However, I need to past a list of users instead of just one. Does this help?

[Chuck Tomasi · Accepted solution]

Hi Robert,

Thanks for the clarification. To find all users in a specific department...

// Get all users with that department...

var user = new GlideRecord('sys_user');

user.addQuery('department.name', '2112');

user.query();

while (user.next()) {

        // call your function here, passing the user's sys_id

        function2(user.getValue('sys_id'));

}

Does that help?

[robhaas]

I feel pretty useless here because that confused me more...