Help in User Roles report

[Pallavi65]

 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

[Brian Lancaster]

You might be better off writing a report on the sys_user table. With a condition of roles contains itil.

[Pallavi65]

Hi Brian,

Thanks for your response. I have understand that but this report is created and it is followed since years. So, can you please help me how to avoid duplicate User IDs while searching for users having ITIL roles?

I am writing a script in scripts background and not getting exact results.

var gr = new GlideRecord('sys_user+has_role');

gr.addEncodedQuery('role=20981678992002845'); //sys_id of the ITIL role

gr.groupBy('user_name');

gr.query();

while(gr.next()){

gs.print(gr.getRowCount());

}

Please help to get unique records

Regards,

pallavi

[Brian Lancaster]

Try using GlideAggregate instead of GlideRecord. The main problem is every time a user get put in additional group that have roles they will get put into the has roles table multiple times. I'm surprised that you are only seeing duplicates and not triplicates.

[Uday_Kumar · Accepted solution]

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