I would like to create a report on which permission groups each user has been given. The report would be something along the lines of:
| SPACEKEY | SPACENAME | Group Name | User Name | E-mail |
Does anyone have the SQL that would provide that or something similar? I'm not familiar enough with the database layout to know how to obtain such a report. We are currently running Confluence 5.7.1.
Thanks,
Tom Hanstra
hanstra@nd.edu
I wrote these recently, it's close to what you need.
Group Membership:
select cwd_group.group_name, cwd_user.user_name, cwd_user.display_name, cwd_user.email_address, (select attribute_value from cwd_user_attribute where cwd_user_attribute.user_id = cwd_user.id and cwd_user_attribute.attribute_name = "lastAuthenticated") as lastAuthenticated from cwd_membership left join cwd_user on cwd_user.id = cwd_membership.child_user_id left join cwd_group on cwd_membership.parent_id = cwd_group.id order by user_name asc;
And this one is a list of all groups and individual users granted View privileges on a space:
SELECT sp.permid, sp.permtype, s.spacekey, s.spacename, sp.permgroupname, um.lower_username FROM SPACEPERMISSIONS sp JOIN SPACES s ON sp.spaceid = s.spaceid LEFT JOIN user_mapping um ON sp.permusername = um.user_key where sp.permtype = "VIEWSPACE" order by spacekey asc;
Tested on MySQL
Its a good way to make reports using sql or buying and add-on ?
I've been asked this question a lot around here and Nic helped me out, but I need more opinions in this.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.