How can I get list of all confluence spaces, their assigned users and groups using MS SQL query?
Hello @Nirmalkumar Shete .
As I understand, you need to create a SQL Query to list some specific data about your Confluence Spaces.
I would recommend you to start by looking into how to connect and run SQL queries on Microsoft SQL Server. Here we have their page on how to do so:
[Microsoft] Connect to and query a SQL Server instance by using SQL Server Management Studio (SSMS)
After that, we need to look into the Database Schema. You can take a look at Atlassian own Confluence Schema here:
[Confluence Server] Confluence Data Model
Keep in mind that your database tool most likely has a feature that allows you to create your own visualization.
After that, you can get a taste on how to work with your database with some already well-established Knowledge bases for Confluence Server and how to extract data directly from your database. One such example is this:
[Confluence Server] How do I view a list of all space administrators for all spaces
Fetching and analyzing data from your database requires some prior knowledge of the database itself and also the tool and methods being used.
A few notes for anyone who needs or wants to dive deeper into SQL territory:
Always be extremely careful with any query you want to send to your database If you are learning, do it in a testing environment. Never try new things on your production Understand the database relationship Check for similar queries If you plan on changing something on your database, create a backup before Updating the database requires Confluence to be down. Touching the database while Confluence runs will most likely break your instance
Some threads here in the community prove to be useful while learning:
[Atlassian Community] Need a SQL statement to find a list of all spaces not updated in X amount of time and the corresponding space administrators [Atlassian Community] Confluence Query [Atlassian Community] SQL query to get all Confluence spaces with anonymous enabled
I hope this guides you somewhere. Looking forward to your reply!
I was looking for query to be executed, thanks for pointers though.
This query gives list of all spaces and associated users in every space
SELECT distinct s.SPACENAME, cu.lower_email_addressFROM SPACES AS sJOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceidLEFT JOIN user_mapping AS u ON sp.permusername = u.user_keyLEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_usernameLEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_nameLEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_idLEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_idWHERE cu.lower_email_address is not null ORDER BY s.SPACENAME, cu.lower_email_address;
I started this query in 2020 and it's still running
It looks like you're new here. Sign in or register to get started.
