How to determine the file paths for a page's attachments

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Summary

On occasion an administrator may need to know the disk volume file path for a given attachment on a page, wherein they know the pageID for the page in question.

Solution

Run the following SQL query, replacing the ###### in c.contentid=###### at the end with the page's actual pageID.

(for Confluence Server, the reference to "confluence_shared_home" is just the "confluence_local_home" directory)

Postgres instructions
Postgres
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 DiskLoc,
       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 s.spaceid IS NOT NULL
                             AND c.prevver IS NULL
                             AND c.contentid = ########);
MySQL instructions
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, '/', contentid, '/', version
       ) AS
       DiskLoc,
       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 s.spaceid IS NOT NULL
                             AND c.prevver IS NULL
                             AND c.contentid = ########);
Oracle instructions
Oracle
select concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(concat
('<confluence_shared_home>/attachments/ver003/', MOD(spaceid, 250)), '/'),
MOD(FLOOR(spaceid / 1000), 250)), '/'), spaceid),
'/'), MOD(pageid, 250)), '/'), MOD(FLOOR(pageid / 1000), 250)), '/'), pageid),
'/'), contentid),
'/'), version) as DiskLoc,
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 s.spaceid is not null
AND c.prevver IS NULL
AND c.contentid = ########);
MS SQL instructions
MS SQL
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 DiskLoc,
       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 s.SPACEID IS NOT NULL
                             AND c.PREVVER IS NULL
                             AND c.CONTENTID = ######);




Last modified on Jun 5, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.