Help in User Roles report

Go to solution
Pallavi65
Tera Contributor

‎03-15-2023 07:44 AM

 Hello all,

I have created a report on "User Roles" table and applied a filter condition to get the records on number of users having ITIL role.

So, I am getting duplicate user IDs also in that report. Like for example, we have a user id, USR105892 and it is showing up twice in that report. So no matter how it is coming over there, if I do group by User ID, i want to get only unique records but not with duplicate User id numbers.

Can someone help me how to write a script and add that in filter condition?

 

Thanks, in advance.

 

 

Regards,

Pallavi

0 Helpfuls
  • 729 Views
1 ACCEPTED SOLUTION

Go to solution
Uday_Kumar
Giga Guru

‎03-15-2023 08:21 AM

Hi @Pallavi65 How are You?

if you want to get the no of users having itil role use GlideAggregate instead of GlideRecord with aggregate of Count.

I am pasting the script run it in script background and check, if you have any problem please ask me.

var gr=new GlideAggregate('sys_user');
gr.addEncodedQuery('roles=itil');
gr.addAggregate('COUNT');
gr.query();
while(gr.next())
{
    gs.print(gr.getAggregate('COUNT'));
}

Please mark it as helpful if it helps you.

Thanks and Regards

Uday Kumar Valapudasu

 

 

View solution in original post

0 Helpfuls
6 REPLIES 6

Go to solution
Bert_c1
Kilo Patron

‎03-15-2023 08:46 AM - edited ‎03-15-2023 11:14 AM

var gr = new GlideRecord('sys_user_has_role');

gr.addEncodedQuery('role=282bf1fac6112285017366cb5f867469'); //sys_id of the ITIL role
gr.orderBy('user');
gr.query();
var userRoleCount = 0;
var curUser = '';
gs.info("Found " + gr.getRowCount() + " records to process.");
while(gr.next()){
if (userRoleCount == 0) {
curUser = gr.user.toString();
}
// gs.info("Processing user: " + gr.user.toString() + ", curUser = " + curUser + ", userRoleCount = " + userRoleCount + ".");
if (gr.user.toString() != curUser) {
var usergr = new GlideRecord('sys_user');
usergr.addQuery('sys_id', curUser);
usergr.query();
usergr.next();
gs.info('User: ' + usergr.user_name + ' has ' + userRoleCount + ' records.');
curUser = gr.user.toString();
userRoleCount = 0;
}
userRoleCount++;
}

// Show last user
var usergr = new GlideRecord('sys_user');
usergr.addQuery('sys_id', curUser);
usergr.query();
usergr.next();
gs.info('Last user: ' + usergr.user_name + ' has ' + userRoleCount + ' records.');

0 Helpfuls

Go to solution
Bert_c1
Kilo Patron

‎03-16-2023 07:14 AM - edited ‎03-16-2023 11:46 AM

@Pallavi65 

Here's a GlideAggregate version:

 

 

var suhrGA = new GlideAggregate('sys_user_has_role');
suhrGA.addQuery('role','282bf1fac6112285017366cb5f867469');
suhrGA.groupBy('user');
suhrGA.orderByAggregate('COUNT', 'role');
suhrGA.addAggregate('COUNT', 'role');

suhrGA.query();

while (suhrGA.next()) {
	var usergr = new GlideRecord('sys_user');
	usergr.addQuery('sys_id', suhrGA.getValue('user'));
	usergr.query();
	usergr.next();
	gs.info('User: ' + usergr.name);
	gs.info('COUNT: ' + suhrGA.getAggregate('COUNT', 'role'));
	gs.info(' ');
}