How to count pages in a whole Confluence instance?

[Alex Bobrovski]

Hi,

who can help - I'd like to know how many pages are on my Confluence? The total number mustn't include deleted articles (from the recycle bin).

[Adam Laskowski]

The best way to get an accurate count of the number of pages in the instance (excluding their previous versions) is to run this query against your database:

select count(*) from content where contenttype='PAGE' and prevver is null and content_status='current';

Limiting the contenttype to PAGE removes space and global descriptions, user info, drafts, and blog posts; only returning rows with a null previous version ensures that only the current version of the page is counted; lastly, content that has been deleted will have a status of "deleted," so you only want the rows that are still in the "current" status.

[Alex Bobrovski]

If I use the {sql} macro, is it dangerous in terms of security? Is it true that any user can run the macro and get any data?

[Penny Wyatt (On Leave to July 2021)]

Yes, the {sql} macro opens up your whole database to any user who can edit Confluence content.

[K_ Yamamoto]

For Confluence Cloud users, consider using REST API as below because Confluence Cloud doesn't support SQL, Macro, and some Add-ons for stats.

A_USER="admin"
A_PASSWD="__YOUR_PASSWORD_HERE__"
INSTANCE_HOST="__YOUR_SUBDOMAIN_HERE__.atlassian.net"
 
# Total number of pages
curl -sGLu ${A_USER}:${A_PASSWD} "https://${INSTANCE_HOST}/wiki/rest/api/search" --data-urlencode 'cql=type IN (blogpost, page)' --data-urlencode 'limit=0'

Refer my related answer at For Confluence Cloud, how do I find the total number of pages? for more detail if you needed.

[K_ Yamamoto]

As of 2022, use the API token instead of the account password https://id.atlassian.com/manage-profile/security/api-tokens