Way to find filters which are not used in any dashboard

Neeta Dubey August 23, 2016

Hi,

I have around 700 filters created by users in JIRA. I'm doing some cleaning and want to delete filters which are not used in any dashboard. Many time user creates test filter which are never used.

Is there a way I could find which filter are not used in any dashboard?

I've already looked for popularity option in shared filter however popularity gives information about how many users have marked it as their favorite but there is a lot of possibility that although filter is not marked as favorite but used in any of the dashboard.

 

 

2 answers

2 votes
Betsy Walker {Appfire}
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 24, 2016

It's a little tricky since the searchrequest table contains the filters but you have to navigate the gadgetuserpreference table to find those used within a dashboard gadget, the portletconfiguration table to find the dashboard in which the gadget is used, then the portalpage table to find information about that dashboard.

The following is  a MySQL query that finds what you're looking for. You can adjust the WHERE clause (per the comments) as needed. PP.id is the id of the dashboard.

-- Remove WHERE clause to locate all filters, even if not used within a dashboard
-- Include WHERE clause to locate only those filters used on a dashboard
SELECT SR.id as FilterID,
	SR.filtername, 
	SR.reqcontent AS FilterJQL, 
	SR.authorname AS FilterCreator,
	SUBSTRING_INDEX(PC.gadget_xml,'/',-1) AS GadgetType,
	PP.id AS DashboardID, 
	PP.pagename AS DashboardName
FROM searchrequest SR
LEFT OUTER JOIN gadgetuserpreference GUP ON SR.id = RIGHT(GUP.userprefvalue,5)
LEFT OUTER JOIN portletconfiguration PC on GUP.portletconfiguration = PC.id
LEFT OUTER JOIN portalpage PP ON PC.portalpage = PP.id
WHERE PP.id IS NULL
ORDER BY 1;
Jonathan Chatwin October 10, 2017

For Oracle we came up with this:

 

SELECT SR.id as FilterID,
SR.filtername,
SR.reqcontent AS FilterJQL,
SR.authorname AS FilterCreator,
SUBSTR( PC.gadget_xml, INSTR( PC.gadget_xml, '/', -1 ) + 1 ) as GadgetType,
PP.id AS DashboardID,
PP.pagename AS DashboardName
FROM jira.searchrequest SR
LEFT OUTER JOIN jira.gadgetuserpreference GUP
ON SR.id = to_number(substr(dbms_lob.substr(gup.userprefvalue, 50, 1), -5))
LEFT OUTER JOIN jira.portletconfiguration PC
on GUP.portletconfiguration = PC.id
LEFT OUTER JOIN jira.portalpage PP
ON PC.portalpage = PP.id
WHERE 1=1
--and PP.id IS NULL
and gup.userprefkey='filterId'
and gup.userprefvalue is not null
ORDER BY 1;  
1 vote
Andre Lehmann
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 24, 2016

I think the best way to solve that is within the database.

There are tables for filters and talbles for dashboards (and agile boards)

You just need to find out, which filter is not mentioned in any board.

You will need a DBA and SQL-Knowledge smile

 

Neeta Dubey August 24, 2016

Thanks Andre,

I may take help from my DBA team. Do you have any idea what could be table name/ Database name so it's easy for DBA team also.

 

Andre Lehmann
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 24, 2016

Filters are in table: searchrequest

Dashboards are in table portalpage

There is also a table portletconfiguration

 

There could be more tables, in special all the AO_ tables where filters could be linked

Neeta Dubey August 24, 2016

Thanks for your quick response. I'll work with my DB team however is there any limit to number of filters that can be created in JIRA. I thought 700 is huge number and I should clean them up.

Andre Lehmann
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
August 24, 2016

I don't think that there is a known limit...

I just checked the number of filters in our instance:

select count(*) from searchrequest

Result was 10491 - growing alsmost every day smile

 

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events