データベース クエリによる CCMA での添付ファイル移行を監視する
プラットフォームについて: Data Center のみ。 - This article only applies to Atlassian apps on the Data Center プラットフォーム。
この KB は Data Center バージョンの製品用に作成されています。Data Center 固有ではない機能の Data Center KB は、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。 Server* 製品のサポートは 2024 年 2 月 15 日に終了しました。Server 製品を実行している場合は、 アトラシアン Server サポート終了 のお知らせにアクセスして、移行オプションを確認してください。
*Fisheye および Crucible は除く
要約
このガイドでは、Confluence Server/Data Center での添付ファイル移行中に CCMA の進捗状況を追跡するための SQL クエリを提供しています。
移行するデータセットによっては、UI でプランのステータスが更新されるまでに時間がかかる場合があります。
次のクエリで、管理者はすでに移行されたデータの最新状況を確認できます。
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.
ソリューション
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 クエリ
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"この内容はお役に立ちましたか?