Confluence Content Audit: 10 Queries, All From Atlassian’s Own Documentation

Confluence gives you no stale-content report, no orphaned-pages screen, and no way to see what your attachment history actually costs. Here is what it does give you — verified line by line against Atlassian’s own documentation.

Confluence 9.0 reached end of support today, 30 July 2026. Confluence 9.1 follows on 3 October 2026, and 9.2 LTS on 10 December 2026. Those dates come straight from Atlassian’s end-of-life policy table.

That’s the visible clock. The quieter one matters more. Per Atlassian’s Data Center licensing page:

“End of life for impacted Data Center products will take place on March 28, 2029 at 23:59 PST. Data Center subscriptions and any associated Marketplace apps will expire on this date, making Data Center products and apps read-only.”

And before that, on 30 March 2028 at 23:59 PST, existing customers lose the ability to buy new subscriptions, Marketplace apps, or expansions.

Every migration between now and then gets priced by content volume. Deleting 30% of your pages first costs you one afternoon.

Why There’s No Button For This

The Orphaned Pages screen — the discovery tool most veteran admins remember — was removed in Confluence 7.0. CONFSERVER-57601, summary “Plans to hide the orphaned pages screen,” closed as Fixed on 10 September 2019, fix version 7.0.1, removed from the default theme.

Atlassian’s cleanup guide frames the problem honestly:

“As your site grows beyond a certain size, search becomes essential. One way you can improve the relevance of search results is to archive spaces that are no longer needed on a day to day basis.”

Archiving, it adds, gives you “more relevant search results because archived content is excluded by default.”

Good advice. Very little tooling attached. So here are the queries.

Before You Run Anything

• Read-only. Every query here is a SELECT or a search. Nothing writes.
• Run the SQL on a clone. Not because SELECT is dangerous, but because several of these hit BODYCONTENT, the largest table in most Confluence databases.
• Mind the dialect. Atlassian tested these on different engines and I’ve noted which for each. On Oracle you’ll need dbms_lob.getlength() in place of LENGTH().
• On formatting. Atlassian publishes several of these as single unbroken lines. I’ve added line breaks for readability — the logic is identical. Verify against the linked source if that matters to you.

Part 1: Finding Stale Content

Tier 1: CQL in the URL bar (no special access)

Confluence Data Center accepts raw CQL as a URL parameter. No app, no API client, no database access.

The syntax below uses documented features: lastmodified is a valid field, and the CQL field reference documents startOfDay(), startOfWeek(), startOfMonth(), startOfYear() and their endOf*() counterparts, plus now() with a negative offset — Atlassian’s own example is created >= now(“-4w”).

Query 1 — every page untouched for a year

https://YOUR-BASE/dosearchsite.action?cql=type=page AND lastmodified < now("-52w")

Query 2 — two years stale in one space, with an escape hatch

https://YOUR-BASE/dosearchsite.action?cql=space=OPS AND type=page AND lastmodified < now("-104w") AND NOT label="keep"

Query 3 — untouched since before last year, including archived spaces

https://YOUR-BASE/dosearchsite.action?cql=type=page AND lastmodified < startOfYear("-2y")&includeArchivedSpaces=true

Tier 2: REST — and the ceiling that will fool you

Query 4 — the same search through REST, for a spreadsheet

GET /rest/api/content/search
    ?cql=type=page AND lastmodified < now("-52w")
    &limit=100
    &expand=version,space,history.lastUpdated

Notice the limit=100. You will not get 100 results. And Confluence will not tell you that.

Atlassian’s KB on this is titled “Searching for content with the REST API and CQL always limits results to 50” — a title that overstates its own article body. The real behaviour is conditional on what you expand:

Query 4 above expands version,space,history.lastUpdated but not body, so it tops out at 200 — not the 100 it asks for, and not 50. Add body.view to get page content into your CSV and you silently drop to 50.

Atlassian’s stated workaround is exactly that trade: drop the body expansion to get back to 200. Which is right for an audit anyway — you want titles, dates and owners in a spreadsheet, not page bodies.

Either way, page through the results. A single call is a sample, not an inventory, and the number it returns looks plausible enough that nobody questions it.

This KB is documented for Confluence Data Center. Atlassian notes that Data Center KBs for non-DC-specific features may also apply to Server, but were not tested there.

Cross-reference: old is not the same as dead

A page can be four years old and load fifty times a week. Deleting it makes someone’s day worse.

Analytics requires Confluence Data Center 7.11 or later and a Data Center license. Atlassian’s cleanup guide gives the exact steps:

Go to Analytics in the Confluence header. Select the Spaces tab. Set your date range. Sort the table by views (ascending).

Ninety seconds, and you have the other half of the picture. Old AND unviewed is a criterion you can defend in a meeting. Old alone is not.

Part 2: Attachment Bloat, and the Number That Funds the Cleanup

Attachments live on the filesystem, not the database. Per Atlassian’s storage configuration doc: “By default, Confluence stores attachments in the attachments directory within the configured Confluence home folder.” Storing them in the database was an option only in Confluence 5.4 and earlier.

Which is why attachments never appear in database size reports — and why admins discover the problem when the shared home mount fills up, or when a migration gets quoted by the gigabyte.

The version delta — and Atlassian hands you both halves

This is the best-documented finding in the whole article, and almost nobody knows the page exists.

Query 5 — attachment bytes per space, everything included

SELECT sum(cp.LONGVAL), s.spacename
FROM spaces s
INNER JOIN content c            ON c.spaceid = s.spaceid
INNER JOIN contentproperties cp ON cp.contentid = c.contentid
WHERE c.contenttype = 'ATTACHMENT'
  AND cp.propertyname = 'FILESIZE'
GROUP BY s.spacename;

Source: How to find the total size of attachments per space

Atlassian states explicitly what this counts: it sums the FILESIZE property “for every attachment row in the space, including older versions of attachments and attachments on trashed pages.”

Query 6 — current versions only

Atlassian documents the exact modification: add AND c.prevver IS NULL AND c.content_status = ‘current’ to the WHERE clause.

SELECT sum(cp.LONGVAL), s.spacename
FROM spaces s
INNER JOIN content c            ON c.spaceid = s.spaceid
INNER JOIN contentproperties cp ON cp.contentid = c.contentid
WHERE c.contenttype = 'ATTACHMENT'
  AND cp.propertyname = 'FILESIZE'
  AND c.prevver IS NULL
  AND c.content_status = 'current'
GROUP BY s.spacename;

Atlassian’s page doesn’t state which database dialect these were tested on.

Query 7 — the instance-wide total

SELECT sum(LONGVAL) AS size_bytes
FROM CONTENTPROPERTIES
WHERE CONTENTID IN (
    SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT'
)
AND PROPERTYNAME = 'FILESIZE'
ORDER BY sum(LONGVAL) DESC;

Source: How to find the total size of all attachments in Confluence — tested on PostgreSQL and MSSQL, Confluence 5.7.x and above

Atlassian’s page doesn’t say whether this total covers historical versions — which is one more reason to trust the Query 5 / Query 6 pair instead.

Query 8 — the largest individual files, with the person who uploaded them

SELECT DISTINCT c.contentid, c.spaceid,
       c.title    AS attachmentTitle,
       u.username AS uploadedBy,
       co.title   AS pageTitle,
       cn.longval AS bytes,
       s.spacekey AS spacekey,
       s.spacename AS spacename
FROM CONTENT AS c
JOIN USER_MAPPING AS u       ON u.user_key = c.creator
JOIN CONTENT AS co           ON c.pageid = co.contentid
JOIN spaces AS s             ON c.spaceid = s.spaceid
JOIN CONTENTPROPERTIES AS cn ON cn.contentid = c.contentid
WHERE c.contenttype = 'ATTACHMENT'
  AND cn.longval IS NOT NULL
  AND cn.propertyname = 'FILESIZE'
ORDER BY cn.longval DESC;

Source: How to find the largest attachment files in your Confluence instance — Atlassian’s examples are for PostgreSQL

No database access? Space tools → Content Tools → Attachments gives a per-space view from the UI.

Part 3: The Pages That Are Quietly Enormous

Query 9 — largest current pages by stored body size

SELECT s.spacekey, c.title, LENGTH(bc.body)
FROM BODYCONTENT bc
JOIN CONTENT c ON bc.contentid = c.contentid
JOIN SPACES s  ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
  AND c.contenttype IN ('BLOGPOST','PAGE')
ORDER BY LENGTH(bc.body) DESC
LIMIT 25;

Source: How to find the largest Confluence pages by storage size — tested on MySQL

A handful of two-megabyte pages explains a surprising share of “Confluence is slow” tickets, and this finds them in seconds.

Query 10 — the top 25 pages by historical version size

SELECT s.spacekey, c.title, subq.sum_size_in_bytes
FROM CONTENT c
JOIN SPACES s ON c.spaceid = s.spaceid
JOIN (
    SELECT c2.prevver, SUM(LENGTH(bc.body)) AS sum_size_in_bytes
    FROM BODYCONTENT bc
    JOIN CONTENT c2 ON bc.contentid = c2.contentid
    WHERE c2.prevver IS NOT NULL
      AND c2.contenttype IN ('BLOGPOST','PAGE')
    GROUP BY c2.prevver
    ORDER BY SUM(LENGTH(bc.body)) DESC
    LIMIT 25
) AS subq ON c.contentid = subq.prevver
ORDER BY subq.sum_size_in_bytes DESC;

Source: same KB page as Query 9

Part 4: Retention Rules Will Delete From Your Archived Spaces

You now have numbers. The obvious next step is to turn on retention rules and reclaim the space.

A rollout order that doesn’t end in an incident

1. Back up, and verify the restore. Export anything irreplaceable separately via Space tools → Content Tools → Export.
2. Configure exemptions before you create a single global rule. Archived spaces, personal spaces, anything under legal hold.
3. Start long, then tighten. Set a generously long interval, watch what expires, decrease in steps.
4. Measure the delta. Re-run Queries 5 and 6. Retention rules without a before-and-after measurement is faith, not administration.

The 30-Minute Run Order

The two numbers: your worst three spaces by stale-page count, and the attachment version delta in GB.

That’s your migration prep, your storage business case, and next quarter’s cleanup backlog — from one afternoon of read-only queries.

What I Could Not Verify

Everything above links to the Atlassian page it came from. These did not survive fact-checking, so they’re not in the article:

• The exact Confluence version that introduced retention rules. Atlassian’s retention-rules page doesn’t state one. They exist in current Data Center — check your own admin menu rather than trusting a version number from a blog.
• Whether Data Center has native page-level archiving. Space-level archiving is documented. I could not find page-level archiving for DC, and much of what surfaces in search results is Cloud-only guidance. Confirm against your version before concluding you need an app.
• Whether DC Analytics exports per-page view counts, or in what format. The Analytics page mentions exporting reports without specifying either.
• Whether Query 7’s instance-wide total includes historical attachment versions. Atlassian’s page is silent. Use Queries 5 and 6 for anything you plan to quote.
• The trash-size-per-space query. Atlassian’s KB for it sits behind authentication, so I couldn’t check it and won’t reproduce it from memory.
• Attachment directory internals. The commonly-cited ver003 path does not appear on Atlassian’s attachment storage configuration page. The attachments directory in Confluence home does.

One more, on the CQL queries: Tier 1’s syntax is built from documented fields and functions, but the specific query strings are my compositions, not Atlassian’s examples. Community reports of lastmodified behaving inconsistently across versions exist. Spot-check a handful of results against actual page history before you build a deletion list on them.

Run them, then tell me what your version delta turned out to be. I’m collecting numbers across instances and the spread so far is wider than I expected.

Related reading: 4 Queries That Find Deletable Custom Fields · Jira Database Audit: SQL Queries That Find What the UI Hides · Jira Inactive Users: Find & Remove to Save on Licenses