Get orphaned rapid boards

[Andrew]

Hi @All here !! 

How get part of orphaned boards 

Groovy script for ScriptRunner which help retrieve a list of orphaned boards.

What mean orphaned boards?

After deleting old projects in the boards panel, we can find all the boards of the deleted projects. This means that the boards no longer affect the project and are not needed by us.
A warning! In some cases, the board uses mixed filters, and after deleting one project, it can work in another project.

In this script, I am trying to find the part of the board that was used only for one project and check that this project does not exist.

How find orphaned boards? 
The boards use a filter and in the database exist table 'AO_60DB71_RAPIDVIEW' where board point to the filter. Filter we can find in table 'searchrequest'.

Working with a mixed filter is more difficult, and first we try to get simple filters. For that using regex in SQL request '.*project ='.

Summary we give next:

SELECT r.ID, s.reqcontent FROM AO_60DB71_RAPIDVIEW as r inner join searchrequest as s on r.saved_filter_id = s.id where s.reqcontent regexp '.*project =';

In this request we can get mixed projects. For example JQL: 'status = OPEN OR project = DEMO'. I just remove all filters with 'AND' and 'OR' statements.

For these using regex in groovy:

if (it.reqcontent ==~ /^((?!\sAND\s|\sand\s|\sOR\s|\sor\s).)*$/)

In next step we need get project key or name. For that we split string on word 'ORDER' and remove quotes.

def bufStr = it.reqcontent.split('=')[1].split('ORDER')[0]
...
projName = bufStr.replaceAll(/"/, "").trim()

Then we get lists of the projects name and keys.

select pname from project;

select PROJECT_KEY from project_key;

Finally we check the ours project is exist. If not exist that mean the board is orphaned.

Now we can put all this in the script console and try to get a list of orphaned boards.

Code 

https://bitbucket.org/AndrewDvizhok/scriptrunner-useful/src/master/

I hope this post was helpful.

B.R.

[Vinu]

Thank you @Andrew for posting this.

I have a related requirement to get a list of boards that are not accessed for quite some time, say 90 days.

I dont think Jira records the last board access time and hence is a nightmare to get the list of unused boards.

Do you have any suggestion to get this?

[Andrew]

Hi @Vinu ,

Brief resolution it is try find all accessed boards in last 90 days and then inverse sql request. It is possible find in log files (C:\Atlassian\JIRA7\logs\access_log.2019-05-21). 

For example I use bash tools to get all rapids for log files.

In your case needed select only (access_log. [last 90 days]).

Now we just inverse request in database. 

SELECT * FROM ao_60db71_rapidview where ID not in (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 2, 20, 21, 22, 23, 24, 25, 26, 3, 4, 5, 6, 7, 9);

Result should have only boards which get access more than 90 days.

Warning! Please check all twice, it very fast workaround.

B.R.

[Vinu]

Thanks much @Andrew 

That did work. I got a list of boards that are being accessed now for the past 90 days. I am yet to inverse query the database to find the list of unused boards.

I just have one more requirement to clear all the unused boards from Jira. Did some google and couldn't find any material on this. If you had already given the answer to this in the bitbucket code, I am sorry I didn't take a look at it yet.

It would be great to know if you have some ideas or scripts over here to bulk delete the unused boards.

[Andrew]

Hi @Vinu ,

I'm working on this task. I try delete boards use http://www.gebish.org/ . I will write a post when I finish work.

B.R.

[Vinu]

Thanks alot @Andrew 

Will keep watching for updates. Cheers!!

[Andrew]

Hi @Vinu ,

Look here https://community.atlassian.com/t5/Enterprise/Automatic-delete-rapid-boards-from-Jira/gpm-p/1091056

But it has one problem, it cannot delete restricted boards. It is mean if board not shared You cannot find this board on UI.

B.R.