Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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

2 answers

2 votes
Andy Heinzer Atlassian Team May 01, 2019

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

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 Atlassian Team Jan 05, 2022

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

Like Bill Sheboy likes this
0 votes

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you