How to find unused Jira Software Boards
Platform Notice: Data Center Only - This article only applies to Atlassian products 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
Often customers need to find unused Jira Software Boards in the Jira instance either for migration planning or instance cleanup. There is no way to track the usage of Jira Agile boards out of the box. We had an open feature request for this tracked underJSWSERVER-16050: Retrieve usage information of boards, but it was closed due to low interest and engagement as per our policy. As a workaround, we can run the SQL query in the Jira database directly and check the table userhistoryitem that holds Jira user activity history. If we query for entitytype 'RapidView' we can get the latest timestamp as the last time the board was viewed by some user. This will give you an indication of usage
Environment
Jira Data Center
Jira Server
Solution
Execute appropriate SQL query in on Jira Database/schema. The query will return the following information:
board_id - Board ID
board_name - Board Name
lastviewed - Date time representing when was the last time any user opened the board.
lastviewed_as - Last user that opened the board
lastviewed_email - Email of the last user that opened the board
lastviewed_active - Is the last user that opened the board active
Postgres
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
rv."ID" as board_id,
rv."NAME" as board_name,
to_timestamp(cast(uh1.lastviewed as BIGINT)/ 1000) as "lastviewed",
cu.lower_user_name as lastviewed_as,
cu.email_address as lastviewed_email,
cu.active as lastviewed_active
from
userhistoryitem uh1
join
(
select
entityid ,
max(lastviewed) as lastviewed
from
userhistoryitem
where
entitytype = 'RapidView'
group by
entityid
) uh2 on
uh2.entityid = uh1.entityid
and uh2.lastviewed = uh1.lastviewed
join "AO_60DB71_RAPIDVIEW" rv on
cast(rv."ID" as varchar) = uh1.entityid
join app_user au on
uh1.username = au.user_key
join cwd_user cu on
au.lower_user_name = cu.lower_user_name
order by 3 asc;Oracle
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
rv."ID" as board_id,
rv."NAME" as board_name,
DATE '1970-01-01' + (1/24/60/60/1000) * uh1.lastviewed as "lastviewed",
cu.lower_user_name as lastviewed_as,
cu.email_address as lastviewed_email,
cu.active as lastviewed_active
from
userhistoryitem uh1
join
(
select
entityid ,
max(lastviewed) as lastviewed
from
userhistoryitem
where
entitytype = 'RapidView'
group by
entityid
) uh2 on
uh2.entityid = uh1.entityid
and uh2.lastviewed = uh1.lastviewed
join "AO_60DB71_RAPIDVIEW" rv on
rv."ID" = uh1.entityid
join app_user au on
uh1.username = au.user_key
join cwd_user cu on
au.lower_user_name = cu.lower_user_name
order by 3 asc;MSSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
rv."ID" as board_id,
rv."NAME" as board_name,
cast(dateadd(second, uh1.lastviewed/1000,'1970-01-01')as datetime) as "lastviewed",
cu.lower_user_name as lastviewed_as,
cu.email_address as lastviewed_email,
cu.active as lastviewed_active
from
userhistoryitem uh1
join
(
select
entityid ,
max(lastviewed) as lastviewed
from
userhistoryitem
where
entitytype = 'RapidView'
group by
entityid
) uh2 on
uh2.entityid = uh1.entityid
and uh2.lastviewed = uh1.lastviewed
join "AO_60DB71_RAPIDVIEW" rv on
cast(rv."ID" as varchar) = uh1.entityid
join app_user au on
uh1.username = au.user_key
join cwd_user cu on
au.lower_user_name = cu.lower_user_name
order by 3 asc;MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select
rv.ID as board_id,
rv.NAME as board_name,
from_unixtime(uh1.lastviewed / 1000) as "lastviewed",
cu.lower_user_name as lastviewed_as,
cu.email_address as lastviewed_email,
cu.active as lastviewed_active
from
userhistoryitem uh1
join
(
select
entityid ,
max(lastviewed) as lastviewed
from
userhistoryitem
where
entitytype = 'RapidView'
group by
entityid
) uh2 on
uh2.entityid = uh1.entityid
and uh2.lastviewed = uh1.lastviewed
join AO_60DB71_RAPIDVIEW rv on
rv.ID = uh1.entityid
join app_user au on
uh1.username = au.user_key
join cwd_user cu on
au.lower_user_name = cu.lower_user_name
order by 3 asc;For more information about the Jira database please refer to:
Compiled list of third-party plugin tables that you can use as a reference: List of Jira Server AO table names and vendors.
Was this helpful?