How to list Private and Shared filters in Jira from Database?

Summary

This article addresses two common scenarios faced by JIRA administrators related to private filters and dashboards:

Scenario #1

JIRA administrators are unable to view or modify private filters and dashboards. This limitation can become a significant issue if a user leaves the company and the administrator needs to access the user’s private filters and dashboards. These private items are not displayed under Shared Filters or Shared Dashboards, creating potential blockers for administrative tasks.

Scenario #2

When managing a large number of filters in JIRA, distinguishing between private and shared filters is essential, particularly during clean-up activities. This article provides specific database queries to help identify which filters are private and which are shared.

Solution

To address these scenarios, follow the steps and queries below to identify private filters and dashboards in your JIRA database:

• Login to the JIRA database.

• Run the following SQL accordingly:

  • For private Dashboard:

    Postgres DB

    1 select * from portalpage where id not in (select entityid from sharepermissions where entitytype='PortalPage');

    MySQL

    1 SELECT p.* FROM portalpage p LEFT JOIN sharepermissions s ON p.id = s.entityid AND s.entitytype = 'PortalPage' WHERE s.entityid IS NULL;

    MSSQL

    1 SELECT * FROM portalpage WHERE id NOT IN (SELECT entityid FROM sharepermissions WHERE entitytype = 'PortalPage');

    Oracle

    1 SELECT p.* FROM portalpage p LEFT JOIN sharepermissions s ON p.id = s.entityid AND s.entitytype = 'PortalPage' WHERE s.entityid IS NULL;

  • For private Filter, there are 2 ways to fetch the same result. Either Query #1 or Query #2 can be used to fetch the requested details:

    Query #1:

    Postgres DB

    1 2 3 SELECT filtername FROM searchrequest WHERE id NOT IN (SELECT entityid FROM sharepermissions);

    MySQL DB

    1 SELECT sr.filtername FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid WHERE sp.entityid IS NULL;

    Oracle DB

    1 SELECT sr.filtername FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid WHERE sp.entityid IS NULL;

    MSSQL DB

    1 SELECT sr.filtername FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid WHERE sp.entityid IS NULL;

    Query #2:

    Postgres DB

    1 select * from searchrequest where id not in (select entityid from sharepermissions where entitytype='SearchRequest');

    MySQL DB

    1 SELECT sr.* FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid AND sp.entitytype = 'SearchRequest' WHERE sp.entityid IS NULL;

    MSSQL DB

    1 SELECT sr.* FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid AND sp.entitytype = 'SearchRequest' WHERE sp.entityid IS NULL;

    Oracle DB

    1 SELECT sr.* FROM searchrequest sr LEFT JOIN sharepermissions sp ON sr.id = sp.entityid AND sp.entitytype = 'SearchRequest' WHERE sp.entityid IS NULL;