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!

[stephenrodgers]

Thanks Paul, that sounds promising.

Quick following up...I don't see that database view in our instance (Helsinki Patch 2). Is that supposed to be in the baseline instance, or does it come along with a plugin, or...?

[Chuck Tomasi]

Hi Steve,

Check the two XML files I attached here... They came from Helsinki P1, but haven't changed in a while. Not sure why you're not seeing them on your instance.

[stephenrodgers]

Thanks very much both of you. I'll dig into it.

FWIW, the "Database view in the base system" doc makes no mention of it either, (although it calls out 25 database views and we seem to have 37...not your department I'm sure, just passing it along). Hopefully this'll work out...even denormalized data would be perfectly fine for my purposes.

In any event, I'll get into this and see how it goes. You've given me plenty to chew on for today. Thanks!

[Laurie Marlowe1]

I'm not seeing the database view either!   Nor is it showing up in my developer instance.   Strange...

Thanks, for the files, Chuck!

Laurie

[montanadawgz]

Looking for this view to see if we can create several reports.  

Do we have to manually create this view or should it be with the system?