How to get a list of all the active personal spaces
This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Compare Atlassian cloud vs server, the contents of this article cannot be applied to Atlassian Cloud applications.
To get a list of all personal spaces bound to active users.
This can be done by running the following SQL query directly against the database:
SELECT DISTINCT s.spaceid, c.user_name, s.spacename, s.spacekey, c.active FROM SPACES AS s JOIN user_mapping AS u ON s.creator = u.user_key JOIN cwd_user AS c ON c.lower_user_name = u.lower_username WHERE spacekey LIKE '~%' AND c.active = 'T';