How to build a report to find which groups have members with the role B (knowing the group has role A and not all members of the group have role B, that was givent to them individually)

[Bernard12]

Hi Everybody,

Context: no Performance Analytics, no admin rights (just report admin), no coding.

I already know:

• [sys_group_has_role] 
• [sys_user_has_role]
• [sys_user_grmember]

I'd like to make a report to find which groups have members with the role B (knowing the group has role A and not all members of the group have role B, that was givent to them individually).

Many thanks for any idea.

Kind regards.

Bernard

[andersbogsjo]

Hi!

I think you will have to build a Database view for that.

I will look in to that, but not today - sorry

Regards

Anders

[andersbogsjo]

Hi Bernhard!

I did build a database view that might solve your case (in my dev instance)

TThis combines the sys:group_has_role with sys_user_has_role.

Then if you open that list (in this case u_sys_user_has_role_group.list) and then the columns from both tables are visible an in reporting you can filtera that out.

Aexample of the list

Hope that what the way you wanted it. If you wanna join other tables in a database view see documentation.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/use/reporting/concept/c_DatabaseViews.html

Regards

Anders

[andersbogsjo]

Hi Bernard!

Did you get any help from my last reply?

regards

Anders

[Bernard12]

Hi Anders,

Many thanks for your kind help.

I was on holidays, so I'm replying a bit late to your message.

In fact, I've visited the new Paris release and the feature is now OOTB. I mean you can easily find groups that have members that have whatever role you are looking for, even if the group doesn't have the role.

So, we've decided to wait a little bit to get this feature without spending time & money.

Many thanks for having proposed something.

Kind regards.

Bernard