How to get the Count of all Confluence Pages created by a User
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
This KB will help to get the Count of the Pages created by a User in Confluence
Solution
Please run the below SQL queries to pull the data.
To get the count of Pages
MySQL and PostGres SQL syntax
1 2 3 4 5 6 7 8 9SELECT lower_username as Username , display_name as DisplayName , count(DISTINCT CONTENT.title) as PagesCreated FROM CONTENT LEFT JOIN USER_MAPPING ON CONTENT.creator = USER_MAPPING.user_key LEFT JOIN CWD_USER ON USER_MAPPING.lower_username = CWD_USER.lower_user_name WHERE creator IS NOT NULL AND CONTENTTYPE IN ('PAGE') AND CONTENT_STATUS='current' group by lower_username , display_name ORDER BY PagesCreated DESC;
If you want the Pages created with the Page URLs and creation Date as well, please run the below query. Replace 'https://localhost:8443/c720/' with your base URL
MySQL and PostGres SQL syntax
1 2 3 4 5 6 7 8 9 10 11 12SELECT c.title AS PageName, u.username AS Creator, c.creationdate AS CreationDate, CONCAT ('https://localhost:8443/c720/','/pages/viewpage.action?pageId=', c.contentid) AS "PAGE URL" FROM content c JOIN user_mapping u ON c.creator = u.user_key JOIN user_mapping um ON c.lastmodifier = um.user_key WHERE c.prevver IS NULL AND c.contenttype = 'PAGE' AND c.content_status = 'current';
Was this helpful?