How to find Jira content associated with a particular user in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
If you are preparing for a migration from Server to Cloud (S2C), you might be interested in finding out if you need to include a specific user in the migration. There isn't an easy way to obtain that information directly through the UI. This article aims to extract as much information from the database to evaluate what type of content is associated with a particular user.
Environment
This applies when migrating from Server to Cloud (S2C) when using either the Jira Cloud Migration Assistant (JCMA) or the Site Import method.
Solution
Use the query below making sure to replace the placeholder <user-name> (located in line #111) with the username that the user has in the Jira server.
WITH contribution AS
( SELECT reporter AS "user_key",
'issue_reporter' AS "contribution_type",
count(*)
FROM jiraissue
GROUP BY reporter
UNION
( SELECT assignee AS "user_key",
'issue_assignee' AS "contribution_type",
count(*)
FROM jiraissue
GROUP BY assignee )
UNION
( SELECT creator AS "user_key",
'issue_creator' AS "contribution_type",
count(*)
FROM jiraissue
GROUP BY creator )
UNION
( SELECT source_name AS "user_key",
lower(association_type) AS "contribution_type",
count(*)
FROM userassociation
GROUP BY source_name,
association_type )
UNION
( SELECT author AS "user_key",
'attachment_author' AS "contribution_type",
count(*)
FROM fileattachment
GROUP BY author )
UNION
( SELECT author AS "user_key",
'comment_author' AS "contribution_type",
count(*)
FROM jiraaction
WHERE actiontype = 'comment'
GROUP BY author )
UNION
( SELECT perm_parameter AS "user_key",
'permission_in_scheme' AS "contribution_type",
count(*)
FROM schemepermissions
WHERE perm_type='user'
GROUP BY perm_parameter )
UNION
( SELECT lead AS "user_key",
'project_lead' AS "contribution_type",
count(*)
FROM project
GROUP BY lead )
UNION
( SELECT author AS "user_key",
'worklog_author' AS "contribution_type",
count(*)
FROM worklog
GROUP BY author )
UNION
( SELECT author AS "user_key",
'change_author' AS "contribution_type",
count(*)
FROM changegroup
GROUP BY author )
UNION
( SELECT authorname AS "user_key",
'filter_author' AS "contribution_type",
count(*)
FROM searchrequest
GROUP BY authorname )
UNION
( SELECT username AS "user_key",
'filter_owner' AS "contribution_type",
count(*)
FROM searchrequest
GROUP BY username )
UNION
( SELECT username AS "user_key",
'dashboard_owner' AS "contribution_type",
count(*)
FROM portalpage
GROUP BY username)
UNION
( SELECT cfv.stringvalue AS "user_key",
'user_customfield_and_rp' AS "contribution_type",
count(*)
FROM customfieldvalue cfv
JOIN customfield cf ON cfv.customfield = cf.id
WHERE customfieldtypekey ILIKE '%userpicker%'
OR customfieldtypekey = 'com.atlassian.servicedesk:sd-request-participants'
GROUP BY cfv.stringvalue )
UNION
( SELECT roletypeparameter AS "user_key",
'projectroleactor' AS "contribution_type",
count(*)
FROM projectroleactor
WHERE roletype = 'atlassian-user-role-actor'
GROUP BY roletypeparameter )
UNION
( SELECT author_key AS "user_key",
'auditlog_author' AS "contribution_type",
count(*)
FROM audit_log
GROUP BY author_key ))
SELECT ('"' || cwdu.lower_user_name || '"') AS lower_user_name,
('"' || c.user_key || '"') AS user_key,
c.contribution_type,
c.count
FROM cwd_user cwdu
RIGHT JOIN app_user au ON cwdu.lower_user_name = au.lower_user_name
JOIN contribution c ON c.user_key = au.user_key
WHERE cwdu.user_name = '<user-name>';This query works for Postgres. You may need to adjust the syntax based on the database flavor your application uses.
Example results:
lower_user_name | user_key | contribution_type | count |
|---|---|---|---|
jdoe | jdoe | change_author | 2 |
jdoe | jdoe | project_lead | 1 |
jdoe | jdoe | filter_owner | 2 |
jdoe | jdoe | filter_author | 2 |
jdoe | jdoe | issue_creator | 2 |
jdoe | jdoe | auditlog_author | 80 |
jdoe | jdoe | issue_reporter | 2 |
jdoe | jdoe | issue_assignee | 2 |
If you decide to remove a user from your database, make sure to always back up your data before proceeding with the removal of such user.
This query doesn't provide all the possible data associated with a user, but it provides the main ones. We suggest interpreting the results with caution.
Was this helpful?