How to determine the file paths for the attachments of a specific Page
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
Occasionally, an administrator may need to know the disk volume file path for a given attachment on a page, wherein they know the page ID for the page in question.
Solution
For this purpose, you can make use of the following SQL statements.
The Attachments Hierarchy used in Confluence recently changed. Please refer to the DiskLocV3 column result for Confluence 5.7.0 to Confluence 8.0.x; for Confluence 8.1.0+, refer to the DiskLocV4 column result.
PostgreSQL
select
concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', spaceid % 250, '/', spaceid / 1000 % 250, '/', spaceid,
'/', pageid % 250, '/', pageid / 1000 % 250, '/', pageid,
'/', case when prevver is null then contentid else prevver end,
'/', version
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when prevver is null then contentid else prevver end % 65535 % 256,
'/', case when prevver is null then contentid else prevver end % 65535 / 256,
'/', case when prevver is null then contentid else prevver end,
'/', case when prevver is null then contentid else prevver end, '.', version) as DiskLocV4,
spaceid, pageid, contentid, version, *
from content where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);MySQL
select concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', spaceid MOD 250, '/', (spaceid DIV 1000) MOD 250, '/', spaceid,
'/', pageid MOD 250, '/', pageid DIV 1000 MOD 250, '/', pageid,
'/', case when prevver is null then contentid else prevver end,
'/', version
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when prevver is null then contentid else prevver end MOD 65535 MOD 256,
'/', case when prevver is null then contentid else prevver end MOD 65535 DIV 256,
'/', case when prevver is null then contentid else prevver end,
'/', case when prevver is null then contentid else prevver end, '.', version) as DiskLocV4,
spaceid, pageid, contentid, version, CONTENT.* from CONTENT where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM CONTENT c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);MSSQL
select concat('<CONFLUENCE_SHARED_HOME>/attachments/ver003/', SPACEID % 250, '/', FLOOR(SPACEID / 1000) % 250, '/', SPACEID,
'/', PAGEID % 250, '/', FLOOR(PAGEID / 1000) % 250, '/', PAGEID,
'/', case WHEN PREVVER is NULL THEN CONTENTID else PREVVER end,
'/', VERSION
) as DiskLocV3,
concat('<CONFLUENCE_SHARED_HOME>/attachments/v4/',
case when PREVVER is null then CONTENTID else PREVVER end % 65535 % 256,
'/', case when PREVVER is null then CONTENTID else PREVVER end % 65535 / 256,
'/', case when PREVVER is null then CONTENTID else PREVVER end,
'/', case when PREVVER is null then CONTENTID else PREVVER end, '.', VERSION) as DiskLocV4,
SPACEID, PAGEID, CONTENTID, VERSION, CONTENT.* from CONTENT where CONTENTTYPE = 'ATTACHMENT' and
PAGEID in (
select c.CONTENTID FROM CONTENT c JOIN SPACES s ON s.SPACEID = c.SPACEID
where c.CONTENTTYPE = 'PAGE'
AND c.CONTENTID = ######
and s.SPACEID is not null
AND c.PREVVER IS NULL);Oracle
select '<CONFLUENCE_SHARED_HOME>/attachments/ver003/' || MOD(spaceid, 250) || '/' || MOD(FLOOR(spaceid / 1000), 250) || '/' || spaceid ||
'/' || MOD(pageid, 250) || '/' || MOD(FLOOR(pageid / 1000), 250) || '/' || pageid ||
'/' || case when prevver is null then contentid else prevver end || '/' || version as DiskLocV3,
'<CONFLUENCE_SHARED_HOME>/attachments/v4/'
|| MOD(MOD(case when prevver is null then contentid else prevver end, 65535), 256)
|| '/'
|| TRUNC(MOD(case when prevver is null then contentid else prevver end, 65535) / 256) ||
'/' || case when prevver is null then contentid else prevver end ||
'/' || case when prevver is null then contentid else prevver end || '.' || version as DiskLocV4,
spaceid, pageid, contentid, version, content.* from content where contenttype = 'ATTACHMENT' and
pageid in (
select c.contentid FROM content c JOIN SPACES s ON s.spaceid = c.spaceid
where c.contenttype = 'PAGE'
AND c.contentid = ######
and s.spaceid is not null
AND c.prevver IS NULL);
Replace the <PAGEID> in c.contentid=<PAGEID> at the end of each SQL statement with the page's actual pageID