Delete orphaned boards not linked to projects or inactive user accounts in Jira Cloud

Summary

Sometimes users who created boards, keep the location to the user account profile. When the user account is deactivated and deleted, these boards continue to live in the Jira cloud instance but they are not located on a user account or project. As a result, there is no option to delete these orphaned boards from the options available in the UI.

Diagnosis

• Go to Board List View and check if there are boards that don't have any value in the Location column. These are your orphaned boards.

• You can also run the below SQL queries to find the orphaned boards in your Jira instance:

  • Boards with the project and user location set to NULL

    select "ID", "NAME", "SAVED_FILTER_ID", s.filtername, u.email_address, u.active FROM "AO_60DB71_RAPIDVIEW" B JOIN searchrequest s on "SAVED_FILTER_ID" = s.id JOIN cwd_user u on s.authorname = u.lower_user_name where "PARENT_PROJECT_ID" is NULL AND "USER_LOCATION_ID" is NULL

  • Boards located in the inactive user's location

    select "ID", "NAME", "SAVED_FILTER_ID", s.filtername, u.email_address, u.active FROM "AO_60DB71_RAPIDVIEW" B JOIN cwd_user u on "USER_LOCATION_ID" = u.id JOIN searchrequest s on "SAVED_FILTER_ID" = s.id where "PARENT_PROJECT_ID" is NULL AND "USER_LOCATION_ID" is NOT NULL AND u.active = 0

Solution

• Once you have identified the orphaned boards, create a dummy project in the respective Jira site.

• Fetch the ID of the dummy project using the query: select id, pkey, pname from project where pkey = 'ABC'

• Use the one of queries below to update the project location of all the orphaned boards to the dummy project if the project and user location are set to NULL.

  update "AO_60DB71_RAPIDVIEW" set "PARENT_PROJECT_ID" = 24253, "USER_LOCATION_ID" = NULL where "ID" IN (select "ID" FROM "AO_60DB71_RAPIDVIEW" B JOIN searchrequest s on "SAVED_FILTER_ID" = s.id JOIN cwd_user u on s.authorname = u.lower_user_name where "PARENT_PROJECT_ID" is NULL AND "USER_LOCATION_ID" is NULL)

  • Option 2: Use the queries below to update the project location of all the orphaned boards to the dummy project if the project location is NULL & user location is set to an inactive user account:

    update "AO_60DB71_RAPIDVIEW" set "PARENT_PROJECT_ID" = 24253, "USER_LOCATION_ID" = NULL where "ID" IN (select "ID" FROM "AO_60DB71_RAPIDVIEW" B JOIN cwd_user u on "USER_LOCATION_ID" = u.id JOIN searchrequest s on "SAVED_FILTER_ID" = s.id where "PARENT_PROJECT_ID" is NULL AND "USER_LOCATION_ID" is NOT NULL AND u.active = 0)

  • Option 3: Use the below queries if you know the board ID of the individual boards:

    update "AO_60DB71_RAPIDVIEW" set "PARENT_PROJECT_ID" = 24253, "USER_LOCATION_ID" = NULL where "ID" IN (1234)

• Once the project location of all the orphaned boards are changed to the dummy project, you can ask the customer or you can yourself, delete the dummy project permanently from the Jira site. Deleting the project, will delete all the associated boards of the project.