Monitoring an attachments migration in CCMA via database queries
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 guide provides a SQL query to follow CCMA's progress while it's doing an attachments migration in Confluence Server/Data Center.
Depending on the dataset to be migrated, the UI may take some time to get updated with the plan's status.
The query below will give the admin a more up-to-date view of what's been migrated already.
The information on this page is provided for informational purposes only, and will not be directly supported by Atlassian, should you wish to pursue possible workarounds in light of this limitation.
This information is not actively maintained and should be thoroughly tested before applying it to any production environment.
Solution
Make sure to replace the <placeholder> value with the expected data that needs to be queried, e.g. replace '<SPACE KEY/KEYS>' with the actual space key/keys - 'KEY1, KEY2, KEY3'
SQL Query
PostgreSQL
select * from customfieldvalue cv join jiraissue j on cv.issue = j.id WHERE customfield ='10106'
select * from customfieldvalue cv join jiraissue j on cv.issue = j.id WHERE customfield ='10200'Oracle
WITH total_attachments AS (
SELECT s.spacekey AS spacekey
, COUNT(c.contentid) AS space_attachments
FROM content c
INNER JOIN spaces s ON (s.spaceid = c.spaceid)
WHERE s.spacekey IN (<SPACE KEY/KEYS>)
AND c.contenttype = 'ATTACHMENT'
GROUP BY s.spacekey
) , ccma_attachments AS (
SELECT s.spacekey AS spacekey
, COUNT(m.attachmentid) AS migrated_attachments
FROM MIG_ATTACHMENT m
INNER JOIN content c ON (m.attachmentid = c.contentid)
INNER JOIN spaces s ON (s.spaceid = c.spaceid)
WHERE s.spacekey IN (<SPACE KEY/KEYS>)
AND m.mediaid IS NOT NULL
GROUP BY s.spacekey
)
SELECT ta.spacekey
, CONCAT(ROUND(CAST(ca.migrated_attachments AS NUMBER(4,2)) / CAST(ta.space_attachments AS NUMBER(4,2)) * 100, 2), '%') AS attachment_migration_progress
, ta.space_attachments
, ca.migrated_attachments
FROM total_attachments ta
INNER JOIN ccma_attachments ca ON (ta.spacekey = ca.spacekey)
ORDER BY attachment_migration_progress DESC, ta.space_attachments DESCSQL Server
WITH total_attachments AS (
SELECT s.SPACEKEY AS spacekey
, COUNT(c.CONTENTID) AS space_attachments
FROM CONTENT c
INNER JOIN SPACES s ON (s.SPACEID = c.SPACEID)
WHERE s.SPACEKEY IN (<SPACE KEY/KEYS>)
AND c.CONTENTTYPE = 'ATTACHMENT'
GROUP BY s.SPACEKEY
) , ccma_attachments AS (
SELECT s.SPACEKEY AS spacekey
, COUNT(m.attachmentId) AS migrated_attachments
FROM MIG_ATTACHMENT m
INNER JOIN CONTENT c ON (m.attachmentId = c.CONTENTID)
INNER JOIN SPACES s ON (s.SPACEID = c.SPACEID)
WHERE s.SPACEKEY IN (<SPACE KEY/KEYS>)
AND m.mediaId IS NOT NULL
GROUP BY s.SPACEKEY
)
SELECT ta.spacekey
, CONCAT(CONVERT(NUMERIC(4,2), ca.migrated_attachments) / CONVERT(NUMERIC(4,2), ta.space_attachments) * 100, '%') AS attachment_migration_progress
, ta.space_attachments
, ca.migrated_attachments
FROM total_attachments ta
INNER JOIN ccma_attachments ca ON (ta.spacekey = ca.spacekey)
ORDER BY attachment_migration_progress DESC, ta.space_attachments DESCMySQL
SELECT ta.spacekey
, CONCAT(ROUND(CAST(ca.migrated_attachments AS DECIMAL(4,2)) / CAST(ta.space_attachments AS DECIMAL(4,2)) * 100, 2), '%') AS attachment_migration_progress
, ta.space_attachments
, ca.migrated_attachments
FROM ( SELECT s.spacekey AS spacekey
, COUNT(c.contentid) AS space_attachments
FROM CONTENT c
INNER JOIN SPACES s ON (s.spaceid = c.spaceid)
WHERE s.spacekey IN (<SPACE KEY/KEYS>)
AND c.contenttype = 'ATTACHMENT'
GROUP BY s.spacekey ) ta
INNER JOIN ( SELECT s.spacekey AS spacekey
, COUNT(m.attachmentid) AS migrated_attachments
FROM MIG_ATTACHMENT m
INNER JOIN CONTENT c ON (m.attachmentid = c.contentid)
INNER JOIN SPACES s ON (s.spaceid = c.spaceid)
WHERE s.spacekey IN (<SPACE KEY/KEYS>)
AND m.mediaid IS NOT NULL
GROUP BY s.spacekey ) ca ON (ta.spacekey = ca.spacekey)
ORDER BY attachment_migration_progress DESC, ta.space_attachments DESCMonitoring with the watch command
If you want to have the query run automatically in a Linux terminal, you can use the watch command. Example below:
# save the query in a text file
# run the watch command with the psql client executing the file
# the command below will run execute the file where you saved the query, every 0.5 seconds
# -n - interval of execution
# -t - suppress the watch header output
# -d - highlights any changes in the output
# the date command is just so you can see what time it is
# the echo command is to print a blank line before the SQL output
watch -n0.5 -t -d "date ; echo ; psql -h <DB host> -p <DB port> -U <DB username> <DB database name> -f <file with the query>.sql"Was this helpful?