What are the SQL queries used to get the numbers for "Confluence Usage" in "View System Information"
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
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
1
2
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
1
2
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
1
2
3
4
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
1
2
3
SELECT COUNT(*) allContent,
sum(case when PREVVER IS NULL then 1 else 0 end) currentContents
FROM CONTENT;Was this helpful?