Reporting on users with roles in groups

[stephenrodgers]

Hi folks,

I'm trying to make a list report that gets at user data (name, manager, last login, etc.), their roles (or at least the ability to exclude users that have no roles), and assignment group data (pretty much just group name and group manager). Ideally it would be something like the following:

• List Report
• Include fields: Assignment group name, user name, assignment group.manager, user.manager <<< Easy if I can get at User data
• Filter 1: Last Login is <WHATEVER> (empty, more than 90 days ago, etc.) <<< Easy if I can get at User data
• Filter 2: Only show users who have at least one role <<< Easy if I can get at sys_user_has_role table

When I try to see what tables might work, I get the following:

• sys_user gets me user data, but not groups or roles
• sys_user_group gets me group data, but not users or roles
• sys_group_has_role gets me group and role data, but not users
• sys_user_has_role gets me role data and lets me dotwalk to user data, but not groups
• sys_user_grmember gets me group data lets me dotwalk to user data, but not roles

Is there some clever way to do this that I'm just not seeing? It seems like the best I can do is 2 out of 3. Thanks!

[montanadawgz]

In Istanbul (Personal Instance) and Helsinki (Prod and Dev) - Looked up System Definitions --> Database views, and there is nothing. Another rabbit hole, to go search for?

[Rahul165]

did you get a solution? I am looking for the same

[Navneet3]

Hi, I am looking for same solution. We are on Orlando version.

A report which will show user data (name, manager, last login, etc.), their roles (or at least the ability to exclude users that have no roles), and assignment group data (pretty much just group name and group manager). 

I don't want to go the database view. Is there an easy way?

Thank you

[Sujit4]

Hi,

I need to create a report Users without Roles in a Group. I was trying database views by using sys_user, sys_user_has_role and sys_user_group. Needs some pointers for creating the join query.

Can we create, without database views or scripting.

thanks,

Sujit