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

Reference links

Related Feature Requests

Please vote for the feature requests if you interested to get it built in CCMA.

Purpose

This guide provides a SQL query to follow CCMA's plans progress or completion for a Confluence Server/Data Center migration to the Atlassian Cloud.

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 or has been migrated previously.

Limitations

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.

SQL Query

SELECT p.planName           AS "Plan Name"
     , s.stepConfig         AS "Entity"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , round(extract(epoch from p.endTime - p.startTime)::numeric(18, 3) / 60, 0) AS "Plan Duration (minutes)"
     , s.taskId             AS "Task ID"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , round(extract(epoch from s.endTime - s.startTime)::numeric(18, 3), 0) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
  FROM MIG_PLAN p
 INNER JOIN MIG_STEP s ON (p.id = s.planId) 
 ORDER BY p.startTime, s.startTime

SELECT p.planName           AS "Plan Name"
     , s.stepConfig         AS "Entity"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((CAST(p.endTime AS DATE) - CAST(p.startTime AS DATE)) * 24 * 60, 0) AS "Plan Duration (minutes)"
     , s.taskId             AS "Task ID"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , ROUND((CAST(s.endTime AS DATE) - CAST(s.startTime AS DATE)) * 24 * 60 * 60, 0) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
  FROM MIG_PLAN p
 INNER JOIN MIG_STEP s ON (p.id = s.planId) 
 ORDER BY p.startTime, s.startTime

SELECT p.planName           AS "Plan Name"
     , s.stepConfig         AS "Entity"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , DATEDIFF(minute, p.startTime, p.endTime) AS "Plan Duration (minutes)"
     , s.taskId             AS "Task ID"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , DATEDIFF(second, s.startTime, s.endTime) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
  FROM MIG_PLAN p
 INNER JOIN MIG_STEP s ON (p.id = s.planId) 
 ORDER BY p.startTime, s.startTime

SELECT p.planName           AS "Plan Name"
     , s.stepConfig         AS "Entity"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((TIMESTAMPDIFF(SECOND, p.startTime, p.endTime) / 3600), 2) AS "Plan Duration (hours)"
     , s.taskId             AS "Task ID"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , ROUND((TIMESTAMPDIFF(SECOND, s.startTime, s.endTime) / 3600), 2) AS "Step Duration (hours)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
  FROM MIG_PLAN p
 INNER JOIN MIG_STEP s ON (p.id = s.planId) 
 ORDER BY p.startTime, s.startTime

Output (query)

You should see an output similar to the example below (depending on the database you're running it against)

Monitoring 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 (with psql):

watch command to monitor the query execution

# 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"

Output (watch command)

Additional SQLs

Getting the total migration time of a plan:

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , sum(round(extract(epoch from s.endTime - s.startTime)::numeric(18, 3), 0)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , SUM(ROUND((CAST(s.endTime AS DATE) - CAST(s.startTime AS DATE)) * 24 * 60 * 60, 0)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , SUM(DATEDIFF(second, s.startTime, s.endTime)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , SUM(ROUND((TIMESTAMPDIFF(SECOND, s.startTime, s.endTime) / 3600), 2)) AS "Step Duration (hours)"
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime;

Getting the report of each space within a plan:

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , round(extract(epoch from p.endTime - p.startTime)::numeric(18, 3) / 60, 0) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key"
     , SUM(round(extract(epoch from s.endTime - s.startTime)::numeric(18, 3), 0)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime,t.spaceKey
ORDER BY t.spaceKey;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((CAST(p.endTime AS DATE) - CAST(p.startTime AS DATE)) * 24 * 60, 0) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key"
     , SUM(ROUND((CAST(s.endTime AS DATE) - CAST(s.startTime AS DATE)) * 24 * 60 * 60, 0)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime,t.spaceKey
ORDER BY t.spaceKey;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , DATEDIFF(minute, p.startTime, p.endTime) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key" 
     , SUM(DATEDIFF(second, s.startTime, s.endTime)) AS "Step Duration (seconds)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime,t.spaceKey
ORDER BY t.spaceKey;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((TIMESTAMPDIFF(SECOND, p.startTime, p.endTime) / 3600), 2) AS "Plan Duration (hours)"
     , t.spacekey           AS "Space Key"
     , SUM(ROUND((TIMESTAMPDIFF(SECOND, s.startTime, s.endTime) / 3600), 2)) AS "Step Duration (hours)"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
GROUP BY p.planName, p.startTime, p.endTime,t.spaceKey
ORDER BY t.spaceKey;

Getting the detailed report of each space within a plan:

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , round(extract(epoch from p.endTime - p.startTime)::numeric(18, 3) / 60, 0) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , round(extract(epoch from s.endTime - s.startTime)::numeric(18, 3), 0) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
ORDER BY p.startTime, s.startTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((CAST(p.endTime AS DATE) - CAST(p.startTime AS DATE)) * 24 * 60, 0) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , ROUND((CAST(s.endTime AS DATE) - CAST(s.startTime AS DATE)) * 24 * 60 * 60, 0) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent" 
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
ORDER BY p.startTime, s.startTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , DATEDIFF(minute, p.startTime, p.endTime) AS "Plan Duration (minutes)"
     , t.spaceKey           AS "Space Key" 
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , DATEDIFF(second, s.startTime, s.endTime) AS "Step Duration (seconds)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent" 
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
ORDER BY p.startTime, s.startTime;

SELECT p.planName           AS "Plan Name"
     , p.startTime          AS "Plan Created"
     , p.endTime            AS "Plan Ended"
     , ROUND((TIMESTAMPDIFF(SECOND, p.startTime, p.endTime) / 3600), 2) AS "Plan Duration (hours)"
     , t.spacekey           AS "Space Key"
     , s.stepType           AS "Step Name"
     , s.startTime          AS "Task Started"
     , s.endTime            AS "Task Ended"
     , ROUND((TIMESTAMPDIFF(SECOND, s.startTime, s.endTime) / 3600), 2) AS "Step Duration (hours)"
     , s.executionStatus    AS "Task Status"
     , s.completionPercent  AS "Task Completion Percent"
FROM MIG_PLAN p
INNER JOIN MIG_STEP s ON (p.id = s.planId) 
INNER JOIN MIG_TASK t ON (s.taskid = t.id)
WHERE p.id = '<Plan-ID>'
ORDER BY p.startTime, s.startTime;

Atlassian Cloud Support

Knowledge base

Products

Jira Software

Jira Service Management

Jira Work Management

Confluence

Bitbucket

Resources

Documentation

Community

System Status

Suggestions and bugs

Marketplace

Billing and licensing

Monitoring CCMA plans timing via database queries

Still need help?

Reference links

Related Feature Requests

Purpose

Limitations

SQL Query

Output (query)

Monitoring with the watch command

Output (watch command)

Additional SQLs

Getting the total migration time of a plan:

Getting the report of each space within a plan:

Getting the detailed report of each space within a plan:

Page

Viewport

Confluence

Monitoring CCMA plans timing via database queries

Related content

Still need help?

Reference links

Related Feature Requests

Purpose

Limitations

SQL Query

Output (query)

Monitoring with the watch command

Output (watch command)

Additional SQLs

Getting the total migration time of a plan:

Getting the report of each space within a plan:

Getting the detailed report of each space within a plan:

Related content