Query or ways to find filters which linked with any dashboard or board

[Varsha Gupta]

Query or ways to find filters which linked with any dashboard or board

[Andy Heinzer · Accepted solution]

Hi Varsha,

I did some digging around the Jira database to try to find you a complete answer here.  My testing was with Postgresql, the syntax might be different for other database types.  I tested this against Jira 8.1.0.

For finding which filters are used on Agile/Software boards you can use the following SQL query:

select "AO_60DB71_RAPIDVIEW"."NAME" as BoardName, "AO_60DB71_RAPIDVIEW"."ID" as BoardID, sr.* from searchrequest sr
join "AO_60DB71_RAPIDVIEW" on "AO_60DB71_RAPIDVIEW"."SAVED_FILTER_ID" = sr.id

This returns the board name's and id numbers along with the filter names and other filter details.

As for how to find which saved filters are being used in a dashboard; that poses a more difficult problem to solve.  I think this is because in my testing I found that some gadgets store this data differently than others.  I found I needed to use two different SQL queries to find these filters that might be stored differently.

Query #1

SELECT pp.id, pp.username, pp.pagename, s.filtername, s.id, gup.USERPREFVALUE FROM portalpage pp
JOIN portletconfiguration pc on pp.id = pc.portalpage
JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration
JOIN searchrequest s on gup.userprefvalue=s.filtername

In this first one, the userprefvalue sometimes stores the filter name for a gadget using it.

Query #2

SELECT pp.id, pp.username, pp.pagename, s.filtername, s.id, gup.USERPREFVALUE FROM portalpage pp
JOIN portletconfiguration pc on pp.id = pc.portalpage
JOIN gadgetuserpreference gup on pc.ID = gup.portletconfiguration
JOIN searchrequest s on cast(s.ID AS text) = SUBSTRING(gup.USERPREFVALUE,8,5)
WHERE gup.USERPREFVALUE like 'filter%' order by pp.ID

In this example, sometimes gadgets use a syntax of filter-12345 in that same field where that 12345 is the id of the filter.  I did come across another answer that almost worked for me for this one in https://community.atlassian.com/t5/Jira-questions/Any-way-to-determine-which-filters-are-used-on-dashboards/qaq-p/326245 Thanks to @Christian Czaia on that thread for the inspiration.  I found that I had to use the cast function in order to compare the values in that field.  But in my testing it does work in the current version of Jira 8.1.0.

I hope this helps.

Andy

[Bill Sheboy]

Hi @Andy Heinzer 

Is there a way in Jira Cloud to determine usage of all filters for boards and dashboard gadgets?  After looking at the various REST API functions, I couldn't find anything promising. 

For dashboards, it seems one must already know the item identifier for each gadget to get properties (where filter may be stored?), and I could not find a function to get an item identifier list by dashboard.

Thanks, and kind regards,
Bill

[Andy Heinzer]

Hi Bill,

I don't believe there is an easy to way to find all the filters in use for each dashboard, short of inspecting each dashboard manually and looking at each gadget's settings in Cloud.  However there is a feature request that seeks to better understand filter usage in dashboards over in JSWCLOUD-20801.

The solution posted above should still function in Jira Server/Data Center instances, but since Cloud does not grant access to the SQL database there is no way for an end user or cloud admin to make this kind of query. 

That said, you could create a support case for your cloud site (presuming you are a site-admin or billing/technical contact), and then ask Atlassian Cloud support team to run such a SQL query against your site.  Atlassian support does still have the ability to query against the database.  I am not 100% certain that this will always work, as I had found 2 ways to store this data in the past and it's possible this has become more complex and complicated in Jira Cloud since this answer was first posted. But if the data is still being stored in much the same way as server/data center, then it might be possible to identify these that way.

Andy

[Chris Tolley]

Just wanted to comment and say thanks @Andy Heinzer for a great answer!

We're looking at cleaning up all filters which aren't used on boards/projects so your answer helps. However, using the first query above - that just gets us a list of filters being used when we want the opposite.

I'm a bit rubbish at SQL so just wondering if you have a similar query to get all the other filters that aren't in use? We get 1435 filters from the query above and think that there are about 4000 filters not being used!

[Chris Tolley]

Probably just answered my own question - we can just grab all filters using

select * from searchrequest

And then the diff from that and the query above will be all the non used ones, right? 

[Angélica Luz]

Hi Varsha,

I saw that you created a ticket with our support related to the same question.
Feel free to add the resolution here once the ticket is resolved to help other customers that may face the same issue.

Regards,
Angélica