Purpose

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

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

    MySQL

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

    MSSQL

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

    Oracle

    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

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

    
    
    MySQL DB

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

    Oracle DB

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

    MSSQL DB

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

    
    Query #2:
    
    Postgres DB

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

    MySQL DB

    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

    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

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