When discussing the migration of your server/data center environment to the Atlassian cloud, it’s crucial to thoroughly assess various aspects before hitting the “Migrate” button. Below, I’ve compiled a list of queries commonly used to analyze the data within your instance and to determine whether any corrective actions are required prior to initiating the migration process.

Please note that this is a generic list of queries and should be adapted to your specific environment and requirements:

  1. Queries for Data Cleanup
    1. Identify Pages Using Nested Macros in Confluence
    2. Identify User Filter Usage
    3. Find Duplicate Email Addresses with Query
    4. Limit Attachment Length to 250 Characters
    5. Calculate Total Attachment Size in MB
    6. Identify Dashboards with High Gadget Count
    7. Find Workflows with References to a Specific Custom Field
    8. Identify Custom Fields with Low Usage
    9. Identify Custom Fields with Global Context
    10. Identify Custom Fields with No Value (Empty)
    11. Identify Custom Fields Not Updated After a Specific Date (YYY-MM-DD)
    12. Identify Boards Linked to Non-Existing Filters
    13. Identify Board Columns Linked to Non-Existing Statuses
    14. Identify Invalid JQL in Quick Filter
    15. Identify Personal Data (Email Addresses) in Filters
    16. Identify Boards Owned by Inactive Users
    17. Identify Filters Owned by Inactive Users
    18. Identify Boards Owned by Deleted Users
    19. Identify Filters Owned by Deleted Users
  2. Queries for Public Access
    1. Identify Projects with Any Permission Set to “Anyone”
    2. Identify Projects with Anonymous Access in Browse Project Permission
    3. Identify Filters with Share Type “Share with Everyone”
    4. Identify Agile Boards with Share Type “Share with Everyone”
    5. Identify Dashboards with Share Type “Share with Everyone”
    6. Check Public Access Settings in Confluence

Queries for Data Cleanup

Identify Pages Using Nested Macros in Confluence

SELECT c.CONTENTID
     , replace(replace(cast(b.BODY AS varchar(max)), char(10),' '), char(13),' ') AS body
  FROM BODYCONTENT b
  JOIN CONTENT c ON (c.CONTENTID = b.CONTENTID)
  JOIN SPACES s  ON (s.SPACEID = c.SPACEID)
 WHERE c.CONTENTTYPE IN ('PAGE', 'BLOGPOST')
   AND c.CONTENT_STATUS = 'current'
   AND c.PREVVER IS NULL;

#!/bin/bash
 
[[ -z "$1" ]] && { echo "Provide input file" ; exit 1 ; } || { input_file=$1 ; }
[ -d output ] && { rm -r output ; }
mkdir output;
 
output_folder="output"
first_output_sed="$input_file.sed"
second_output_sed="$first_output_sed.final"
output_file="results.csv"
 
sed -r 's/<ac:structured-macro ac:name="[a-zA-Z0-9\-]+" \/>/foobar/g' $input_file > $first_output_sed
 
sed -r 's/<ac:structured-macro ac:name="[0-9a-zA-Z\-\_]+" ac:schema-version="[0-9]+" ac:macro-id="[0-9a-zA-Z\-]+" \/>/foobar/g' $first_output_sed > $second_output_sed
 
for lineno in $(grep -Eon '<ac:structured-macro|</ac:structured-macro' $second_output_sed | uniq -c | sed -r 's/^ {2,}//g' | sort -nr | awk '$1 > 1' | cut -d ":" -f 1 | sort -u  | cut -d " " -f 2) ; do sed -n "${lineno}p" $second_output_sed | cut -d ";" -f 1 ; done | sort -u > $output_file
 
mv $output_file $output_folder
 
rm -f $first_output_sed $second_output_sed

Identify User Filter Usage

SELECT authorname, COUNT(authorname)
FROM searchrequest
GROUP BY authorname
ORDER BY COUNT(authorname) DESC;

Find Duplicate Email Addresses with Query

SELECT lower_email_address, COUNT(lower_email_address), STRING_AGG(user_name, ', ') AS "Users with Dupe E-Mail"
FROM cwd_user
GROUP BY lower_email_address
HAVING COUNT(lower_email_address) > 1;

SELECT lower_email_address, COUNT(lower_email_address), STRING_AGG(user_name, ', ') AS "Users with Dupe E-Mail"
FROM cwd_user
GROUP BY lower_email_address
HAVING COUNT(lower_email_address) > 1;

Limit Attachment Length to 250 Characters

SELECT id, issueid, LEN(filename)
FROM fileattachment
WHERE LEN(filename) > 250;

Calculate Total Attachment Size in MB


SELECT ROUND(SUM(filesize) / (1024 * 1024), 2) AS "Total Attachment Size(MB)"
FROM fileattachment
JOIN jiraissue ON jiraissue.id = fileattachment.issueid;

Identify Dashboards with High Gadget Count

SELECT pc.portalpage, pp.pagename, pp.username, COUNT(pc.portalpage) AS num_gadgets
FROM portletconfiguration pc
JOIN portalpage pp ON pc.portalpage = pp.ID
GROUP BY pc.portalpage, pp.pagename, pp.username
ORDER BY COUNT(pc.portalpage) DESC;

Find Workflows with References to a Specific Custom Field

SELECT *
FROM jiraworkflows wf
WHERE wf.descriptor LIKE '%customfield_10281%';

Identify Custom Fields with Low Usage

SELECT customfield.id, COUNT(*)
FROM customfield
LEFT JOIN customfieldvalue ON customfield.id = customfieldvalue.customfield
GROUP BY customfield.id
HAVING COUNT(*) < 5
ORDER BY COUNT(*) DESC;

Identify Custom Fields with Global Context


SELECT DISTINCT p.pkey, cf.cfname, cf.id, p.pname
FROM CUSTOMFIELDVALUE cfv
LEFT JOIN JIRAISSUE ji ON cfv.ISSUE = ji.id
RIGHT JOIN CUSTOMFIELD cf ON cf.id = cfv.customfield
LEFT JOIN project p ON p.id = ji.project
WHERE cf.id IN ('<globalContextIdsCommaSeparated>')
GROUP BY cf.id, cf.cfname, p.pkey, p.pname
ORDER BY cf.cfname, p.pname;

Identify Custom Fields with No Value (Empty)

SELECT customfield.id
FROM customfield
LEFT JOIN customfieldvalue ON customfield.id = customfieldvalue.customfield
WHERE customfieldvalue.stringvalue IS NULL
AND customfieldvalue.numbervalue IS NULL
AND customfieldvalue.textvalue IS NULL
ORDER BY customfield.id;

Identify Custom Fields Not Updated After a Specific Date (YYY-MM-DD)

SELECT field.id, field.cfname
FROM customfield field
WHERE field.cfname NOT IN (
    SELECT item.field
    FROM changeitem item
    JOIN changegroup cgroup ON item.groupid = cgroup.id
    WHERE item.fieldtype = 'custom'
    AND cgroup.created > 'YYYY-MM-DD'
)
AND customfieldtypekey NOT LIKE '%com.pyxis.greenhopper%'
AND customfieldtypekey NOT LIKE '%com.atlassian.servicedesk%'
AND customfieldtypekey NOT LIKE '%com.atlassian.bonfire%';

Identify Boards Linked to Non-Existing Filters

SELECT DISTINCT
       rv.ID              AS "Board ID"
     , rv.NAME            AS "Board Name"
     , rv.OWNER_USER_NAME AS "Board Owner"
     , ba.RAPID_VIEW_ID   AS "Foreign Key ID from the Board Admins table"
  FROM AO_60DB71_RAPIDVIEW rv
  LEFT JOIN searchrequest sr ON (rv.SAVED_FILTER_ID = sr.id)
  LEFT JOIN AO_60DB71_BOARDADMINS ba ON (rv.ID = ba.RAPID_VIEW_ID)
 WHERE NOT EXISTS ( SELECT *
                      FROM searchrequest
                      WHERE id = rv.SAVED_FILTER_ID )
 ORDER BY rv.ID;

Identify Board Columns Linked to Non-Existing Statuses

SELECT rv.ID   AS "Board ID"
     , rv.NAME AS "Board Name"
     , c.NAME  AS "Column Name"
     , i.pname AS "Issue Status Name"
FROM AO_60DB71_RAPIDVIEW rv
JOIN AO_60DB71_COLUMN c ON (rv.ID = c.RAPID_VIEW_ID)
JOIN AO_60DB71_COLUMNSTATUS cs ON (c.ID = cs.COLUMN_ID)
LEFT JOIN issuestatus i ON (i.id = cs.STATUS_ID)
WHERE NOT EXISTS ( SELECT id
                     FROM issuestatus
                    WHERE id = cs.STATUS_ID );

Identify Invalid JQL in Quick Filter

SELECT rv.NAME AS "Board Name"
     , qf.NAME AS "Quick Filter Name"
 FROM AO_60DB71_RAPIDVIEW rv
INNER JOIN AO_60DB71_QUICKFILTER qf ON (rv.ID = qf.RAPID_VIEW_ID)
 WHERE qf.LONG_QUERY LIKE '%@%';

Identify Personal Data (Email Addresses) in Filters

SELECT * FROM searchrequest WHERE reqcontent LIKE '%@%';

Identify Boards Owned by Inactive Users

SELECT rv.NAME                 AS "Board name"
     , cwu.lower_user_name     AS "Board inactive owner username"
     , cwu.lower_email_address AS "Board inactive owner email address"    
     , cwu.display_name        AS "Board inactive owner display name"
     , au.user_key             AS "Board inactive owner user key"
  FROM cwd_user cwu
 INNER JOIN app_user au       ON (cwu.lower_user_name = au.lower_user_name)
  JOIN AO_60DB71_RAPIDVIEW rv ON (rv.OWNER_USER_NAME = cwu.lower_user_name OR rv.OWNER_USER_NAME = au.user_key)
 WHERE cwu.active = 0
 ORDER BY rv.ID;

Identify Filters Owned by Inactive Users

SELECT DISTINCT sr.filtername  AS "Filter name"
     , sr.username             AS "Filter username"
     , sr.authorname           AS "Filter author"
     , cwu.lower_email_address AS "Filter inactive user email address"
     , cwu.display_name        AS "Filter inactive user display name"
     , au.user_key             AS "Filter inactive user user key"
     , CASE WHEN cwu.active = 0 THEN 'Inactive' ELSE 'Active' END AS "User status"
     , CAST(sr.reqcontent AS nvarchar(max)) AS "Filter JQL"
  FROM cwd_user cwu
 INNER JOIN app_user au ON (cwu.lower_user_name = au.lower_user_name)
  JOIN searchrequest sr ON ( (sr.username = cwu.lower_user_name OR sr.username = au.user_key) OR (sr.authorname = cwu.lower_user_name OR sr.authorname = au.user_key) )
 WHERE cwu.active = 0 ;

Identify Boards Owned by Deleted Users

SELECT rv.NAME           AS "Board Name"
     , u.lower_user_name AS "Deleted Username"
 FROM AO_60DB71_RAPIDVIEW rv
 JOIN app_user u        ON (rv.OWNER_USER_NAME = u.user_key)
 LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
WHERE NOT EXISTS ( SELECT *
                     FROM cwd_user
                    WHERE lower_user_name = u.lower_user_name );

Identify Filters Owned by Deleted Users

SELECT sr.filtername     AS "Filter name"
     , u.lower_user_name AS "Username"
  FROM searchrequest sr
  JOIN app_user u        ON (sr.authorname = u.lower_user_name)
  LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
 WHERE NOT EXISTS ( SELECT *
                      FROM cwd_user
                     WHERE lower_user_name = u.lower_user_name );

Queries for Public Access

Identify Projects with Any Permission Set to “Anyone”

SELECT p.id, p.pname, ps.name, sp.permission_key
FROM project p
INNER JOIN nodeassociation na ON p.id = na.source_node_id
INNER JOIN schemepermissions sp ON na.sink_node_id = sp.scheme
INNER JOIN permissionscheme ps ON na.sink_node_id = ps.id
WHERE na.source_node_entity = 'Project'
AND na.sink_node_entity = 'PermissionScheme'
AND sp.perm_type = 'group'
AND sp.perm_parameter IS NULL;

Identify Projects with Anonymous Access in Browse Project Permission

SELECT p.id, p.pname, ps.name
FROM project p
INNER JOIN nodeassociation na ON p.id = na.source_node_id
INNER JOIN schemepermissions sp ON na.sink_node_id = sp.scheme
INNER JOIN permissionscheme ps ON na.sink_node_id = ps.id
WHERE na.source_node_entity = 'Project'
AND na.sink_node_entity = 'PermissionScheme'
AND sp.permission_key = 'BROWSE_PROJECTS'
AND sp.perm_type = 'group'
AND sp.perm_parameter IS NULL;

Identify Filters with Share Type “Share with Everyone”

SELECT sr.filtername, sp.sharetype AS current_share_state, sr.username AS owner_name, sr.reqcontent AS JQL
FROM searchrequest sr
INNER JOIN sharepermissions sp ON sp.entityid = sr.id
WHERE sp.sharetype = 'global' AND sp.entitytype = 'SearchRequest';

Identify Agile Boards with Share Type “Share with Everyone”

SELECT DISTINCT rv.NAME AS "Board Name", sr.filtername, sp.sharetype AS current_share_state, sr.username AS owner_name
FROM AO_60DB71_RAPIDVIEW AS rv
INNER JOIN searchrequest sr ON sr.id = rv.SAVED_FILTER_ID
INNER JOIN sharepermissions sp ON sp.entityid = sr.id
WHERE sp.sharetype = 'global' AND sp.entitytype = 'SearchRequest';

Identify Dashboards with Share Type “Share with Everyone”

SELECT DISTINCT pp.id as Dashboard_Id, pp.pagename AS Dashboard_name, sp.sharetype AS current_share_state, pp.username AS owner_name
FROM portalpage pp
INNER JOIN sharepermissions sp ON sp.entityid = pp.id
WHERE sp.sharetype = 'global' AND sp.entitytype = 'PortalPage'
ORDER BY pp.id;

Check Public Access Settings in Confluence

SELECT SPACENAME
FROM SPACES
WHERE SPACEID IN (
  SELECT SPACEID
  FROM SPACEPERMISSIONS
  WHERE PERMTYPE = 'VIEWSPACE'
    AND PERMGROUPNAME IS NULL
    AND PERMUSERNAME IS NULL
    AND PERMALLUSERSSUBJECT IS NULL
);

By addressing these queries and ensuring all necessary preparations are made, you can significantly increase the chances of a successful migration to the Atlassian cloud while minimizing disruptions to your operations.