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.
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;
For Oracle we came up with this:
SELECT SR.id as FilterID,
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
--and PP.id IS NULL
and gup.userprefvalue is not null
ORDER BY 1;
Hello Atlassian Community! My name is Emilee, and I’m a Product Marketing Manager for the Marketplace team. Starting with this post, I'm kicking off a monthly series of Spotlights to highlight Ma...
Connect with like-minded Atlassian users at free events near you!Find a group
Connect with like-minded Atlassian users at free events near you!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot