What are the SQL queries used to get the numbers for "Confluence Usage" in "View System Information"
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Purpose
To understand what SQL queries are used to get the numbers for "Confluence Usage" section in "View System Information" (<BASE_URL>/admin/systeminfo.action)
Active Users
Number of users
User groups
Total Spaces
Global Spaces
Personal Spaces
Content Count
Environment
Confluence 6.x and above
Solution
Active Users
See How to get a list of active users counting towards the Confluence license
Number of users
SELECT COUNT(*) FROM cwd_user; -- for all users
SELECT COUNT(*) FROM cwd_user WHERE directory_id = '<local dir_ID from cwd_directory>'; -- for all local users User groups
SELECT COUNT(*) FROM cwd_group; -- for all groups
SELECT COUNT(*) FROM cwd_group WHERE directory_id = '<local dir_ID from cwd_directory>'; -- for all local groups Total Spaces, Global Spaces and Personal Spaces
SELECT COUNT(*) totalSpace,
sum(case when SPACETYPE = 'global' then 1 else 0 end) globalSpaces,
sum(case when SPACETYPE = 'personal' then 1 else 0 end) personalSpaces
FROM SPACES;Content Count
SELECT COUNT(*) allContent,
sum(case when PREVVER IS NULL then 1 else 0 end) currentContents
FROM CONTENT;