MySQL syntax for Monitoring CCMA plans timing via database queries
Platform Notice: Cloud Only - This article only applies to Atlassian apps on the cloud platform.
Summary
Reference links
Other Database SQL Syntax
MySQL (this article)
Related Feature Requests
Please vote for the feature requests if you interested to get it built in CCMA.
This guide provides a SQL query in MySQL syntax to follow CCMA's plans progress or completion for a Confluence Server/Data Center migration to the Atlassian Cloud.
There are links at the top for other database syntax if your instance does not run a MySQL database.
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.
Solution
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((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.startTimeOutput (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((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((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((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;Was this helpful?