How to filter non-itil users

[shaik_irfan]

How to take report of users who dont have itil role but has certificaton role, when i filter as role is not itil & role is certification i can see both since 1 condition is matching

[SanjivMeher]

You can run a report on sys_user_has_role and extract user with itil and then extract users with certification role. and Do a vlookup in excel and you will get the list.

[bernyalvarado · Accepted solution]

Hi Shaik shaik.irfan

Run the following script as a background script and you will get a list of all names of folks that have the certification role but not the itil role.

var gr = new GlideRecord('sys_user_has_role');
gr.addEncodedQuery('role.name=certification');
gr.query();
while(gr.next()){
   var gr2 = new GlideRecord('sys_user_has_role');
   gr2.addEncodedQuery('user=' + gr.user.sys_id + '^role.name=itil');
   gr2.query();
   if (!gr2.hasNext()){
       gs.print(gr.user.getDisplayValue() + ' ' + gr.role.getDisplayValue());
   }
}

disclaimer: i did this script while in meetings It's accurate but is not intended to be elegant

Thanks,

Berny

[andymcdonald]

I also wrote a script that does this, but yours is better   ... Nice.

[bernyalvarado]

lol