SQL needed for User/Group report

Question

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:

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

Jurgen Sërbo · Answer

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.

Josh Steckler · Answer

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