Any way to determine which filters are used on dashboards?

[Christian Czaia _Decadis AG_]

Hey,

I'd like to list all filters and potentially map them to the dashboards they are associated to. I can find all filters in the DB (searchrequests table) but I can't seem to find a link between a dashboard and a filter.

I'd like to determine which filters (my own filters) are being used on dashboards since I want to delete a few of them. The info if a filter is being marked as a favourite is helpful but I'd need the dashboard info aswell.

Any ideas?

Cheers Christian

[Christian Czaia _Decadis AG_ · Accepted solution]

SELECT * FROM `jira_prod`.`portalpage` p inner join portletconfiguration c on p.id = c.portalpage 
LEFT JOIN gadgetuserpreference g on c.ID = g.portletconfiguration
LEFT JOIN searchrequest s ON s.ID = SUBSTRING(g.USERPREFVALUE,8,5)   
WHERE USERPREFVALUE 
like "filter%"order by p.ID
;

[Syed Khwaja Moinuddin]

Hi Christian,

What is 'jira_prod' here?

-Syed KM

[Adrian Castillo]

@Syed Khwaja Moinuddin this is going to be the name of your Jira's database schema. In this case, theirs is named "jira_prod". In PostgreSQL by default it would be named "public".

Please let me know if you have any questions!

Regards,

Adrian Castillo
Atlassian Support

[MatthewC]

I think you need to join the portalpage table (dashboards) to the portletconfiguration (instance of a gadget on a dashboard) to the gadgetuserpreference (USERPREFKEY='projectorFilterId') then onto the searchrequests table..... not sure I have the stomach for doing the actual SQL myself!

[MatthewC]

Looks like you've done it ;-)