Jira filters owned by invalid users
This insight checks if any of your filters are owned by inactive or deleted users.
What's the problem?
Filters owned by inactive or deleted users won't block your migration, but they might be set to private in Jira Cloud, without easy access to update them later on. To avoid these issues, we recommend that you clean up your filter owners before you migrate.
What’s the recommendation?
To avoid issues with filters set to private in Jira Cloud:
Review affected filters
Change filter owners to active users
Update filter owners to active users
Follow these steps to update filter owners.
Review affected filters
When viewing this insight from Portfolio insights, copy the SQL query (also pasted below).
The query returns filters owned by inactive or deleted users, together with their:
Filter name
Filter author
Filter owner username, email address, display name, user key, user status
Filter's JQL
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",
sr.reqcontent 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;Fix 1: Update filter owners to active users in Jira UI
Updating in Jira UI
To update the filter owners:
Open each affected filter in Jira by going to Administration > Filters, and searching for a filter returned by the SQL query.
For each affected filter, select Actions > Change owner.
Assign the ownership to an active user.
Fix 2: Update filter owners to active users directly in the database
You can also use the following SQL queries to update the filter owners directly in the database.
Make sure to choose an active Jira user to replace the owner.
You'll need to take the user_key of the user from the app_user table.
SELECT user_key FROM app_user WHERE lower_user_name = '<user name of the new owner of the filters>'
With the user_key value, populate it in the UPDATE statement below.
If you face the MySQL ERROR 1175, check: MySQL error code: 1175 during UPDATE in MySQL Workbench
Updating filters owned by inactive users
Choose the SQL query depending on your database.
PostgreSQL, Oracle, Microsoft SQL Server
UPDATE searchrequest
SET username = '<user_key that will be the new owner of these filters>'
, authorname = '<user_key that will be the new owner of these filters>'
WHERE id IN ( SELECT sr.id
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 );MySQL
SET SQL_SAFE_UPDATES = 0;
UPDATE searchrequest
SET username = '<user_key that will be the new owner of these filters>'
, authorname = '<user_key that will be the new owner of these filters>'
WHERE id IN ( SELECT id
FROM ( SELECT sr.id
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 ) AS t );
SET SQL_SAFE_UPDATES = 1;
Updating filters owned by deleted users
Choose the SQL query depending on your database.
PostgreSQL, Oracle, MSSQL
UPDATE searchrequest
SET username = '<user_key that will be the new owner of these filters>'
, authorname = '<user_key that will be the new owner of these filters>'
WHERE id IN ( SELECT sr.id
FROM searchrequest sr
JOIN app_user u ON (sr.authorname = 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 ));MySQL
UPDATE searchrequest sr
JOIN app_user u ON (sr.authorname = u.user_key)
LEFT JOIN cwd_user cwu ON (u.lower_user_name = cwu.lower_user_name)
SET username = '<user_key that will be the new owner of these filters>'
, authorname = '<user_key that will be the new owner of these filters>'
WHERE NOT EXISTS ( SELECT *
FROM cwd_user
WHERE lower_user_name = u.lower_user_name );Was this helpful?