Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Deleted user
0 / 0 points
Next:
badges earned

Your Points Tracker
Challenges
Leaderboard
  • Global
  • Feed

Badge for your thoughts?

You're enrolled in our new beta rewards program. Join our group to get the inside scoop and share your feedback.

Join group
Recognition
Give the gift of kudos
You have 0 kudos available to give
Who do you want to recognize?
Why do you want to recognize them?
Kudos
Great job appreciating your peers!
Check back soon to give more kudos.

Past Kudos Given
No kudos given
You haven't given any kudos yet. Share the love above and you'll see it here.

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How can I get list of all confluence spaces, assigned users and groups using MS SQL query?

How can I get list of all confluence spaces, their assigned users and groups using MS SQL query?

1 answer

0 votes
Diego Atlassian Team Jan 23, 2020

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:

After that, we need to look into the Database Schema. You can take a look at Atlassian own Confluence Schema here:

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:

 

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:

  1. Always be extremely careful with any query you want to send to your database
  2. If you are learning, do it in a testing environment. Never try new things on your production
  3. Understand the database relationship
  4. Check for similar queries
  5. If you plan on changing something on your database, create a backup before
  6. 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:

 

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_address
FROM SPACES AS s
JOIN SPACEPERMISSIONS AS sp ON s.spaceid = sp.spaceid
LEFT JOIN user_mapping AS u ON sp.permusername = u.user_key
LEFT JOIN cwd_user AS c ON c.lower_user_name = u.lower_username
LEFT JOIN cwd_group AS cg ON sp.permgroupname = cg.group_name
LEFT JOIN cwd_membership AS cm ON cg.id = cm.parent_id
LEFT JOIN cwd_user AS cu ON cu.id = cm.child_user_id
WHERE cu.lower_email_address is not null
ORDER BY s.SPACENAME, cu.lower_email_address;

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

🥓🙅🏻‍♀️ Meet-less May Badge!

Hello Confluence Community!  What if i told you that you could have a healthier life and be 100% meet-less? This month, we're promoting a healthy, balanced work diet with Confluence. (Read m...

539 views 3 23
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you