Jira Database Audit: 17 SQL Queries for DC Admins

/ Jira Admin Guides / By
Jira Database Audit: SQL Queries That Find What the UI Hides

Jira Database Audit: SQL Queries That Find What the UI Hides

A 30-minute audit that shows you the other 40% of your Jira instance

Database Audit Guide
In this article: 17 ready-to-run PostgreSQL queries that audit what Jira’s admin UI doesn’t show you — orphaned workflow and permission schemes, attachment storage bloat, abandoned boards and filters, screen scheme clutter, unused issue types, and audit log growth. All read-only SELECT statements, safe to run on production. Complete audit takes about 30 minutes.

Every Jira Data Center instance has a hidden layer. The admin UI shows you projects, workflows, and custom fields — but it doesn’t show you the orphaned schemes nobody uses, the permission configurations that stopped making sense two reorgs ago, or the 14 GB of attachments from a project archived in 2019.

The only way to see this layer is through the database.

This guide covers the queries we haven’t covered before. For custom field cleanup, see 4 Queries That Find Deletable Custom Fields. For inactive user audits, see Jira Inactive Users: Find & Remove to Save on Licenses.

What’s here: orphaned workflows, abandoned permission schemes, attachment bloat, configuration drift, and the queries that expose all of it.

Why This Matters More in 2026

Three things changed:

  1. Data Center feature freeze hits March 30, 2026. After that — security patches only. No new admin tools, no new reporting features. What you have now is what you’ll work with.
  2. End of life is March 28, 2029. Every organization on DC will migrate to Cloud eventually. The cleaner your instance, the cheaper and faster that migration will be.
  3. Costs are rising on both sides. DC pricing increased 15–40% in February 2025 and again in February 2026. Cloud licensing adds its own cost pressure. Every unused scheme, orphaned workflow, and forgotten attachment costs real money — now and after migration.

Running a database audit today isn’t just good hygiene. It’s migration prep.

Before You Run Any Query

READ-ONLY RULE

Every query in this guide is a SELECT statement. No INSERT, UPDATE, or DELETE. If you need to make changes based on what you find, do that through the Jira UI or REST API — not through direct database writes.

KNOW YOUR DATABASE

All queries here are written for PostgreSQL (the most common Jira DC database). If you’re on MySQL, Oracle, or SQL Server, you’ll need to adjust syntax — especially date functions and quoting.

Test environment first. If you have a staging instance, run queries there first. Not because SELECT is dangerous, but because a poorly optimized query on a large table can cause temporary performance impact.

Three ways to run these queries

Method Pros Cons
Direct DB access (psql, pgAdmin) Full power, no limitations Requires SSH/VPN, DBA knowledge
Home Directory Database Browser Runs from Jira admin UI, no SSH needed Read-only by design
ScriptRunner Can combine SQL with Jira API Steeper learning curve

Pick whatever you’re comfortable with. The queries are the same regardless.

1 Orphaned Workflow Schemes

Workflow schemes that aren’t associated with any project are dead weight. They clutter the admin interface, confuse new admins, and create the illusion that changing them is risky (“what if something uses this?”).

Find workflow schemes not linked to any project

POSTGRESQL 
SELECT ws.ID, ws.NAME as scheme_name
FROM workflowscheme ws
LEFT JOIN nodeassociation na
  ON na.SINK_NODE_ID = ws.ID
  AND na.SINK_NODE_ENTITY = 'WorkflowScheme'
  AND na.SOURCE_NODE_ENTITY = 'Project'
  AND na.ASSOCIATION_TYPE = 'ProjectScheme'
WHERE na.SINK_NODE_ID IS NULL
ORDER BY ws.NAME;

In mature enterprise instances, a significant portion of workflow schemes have no project association. They were created for testing, copied from templates, or left behind after project restructuring.

Risk level: Low. Orphaned schemes aren’t used by anything. But before deleting, cross-reference with the next query.

List all workflow scheme → project associations (for context)

POSTGRESQL 
SELECT ws.NAME as scheme_name, p.pkey as project_key, p.pname as project_name
FROM workflowscheme ws
JOIN nodeassociation na
  ON na.SINK_NODE_ID = ws.ID
  AND na.SINK_NODE_ENTITY = 'WorkflowScheme'
  AND na.SOURCE_NODE_ENTITY = 'Project'
  AND na.ASSOCIATION_TYPE = 'ProjectScheme'
JOIN project p ON p.ID = na.SOURCE_NODE_ID
ORDER BY ws.NAME, p.pkey;

This gives you the full map: which scheme serves which project. Export it to a spreadsheet. You’ll need this during migration planning anyway.

2 Abandoned Notification Schemes

Notification schemes are one of the most common sources of admin complaints (“why am I getting 47 emails a day from Jira?”). But the bigger problem is the schemes themselves — most instances have dozens that aren’t attached to anything.

Find notification schemes with no project

POSTGRESQL 
SELECT ns.ID, ns.NAME as scheme_name
FROM notificationscheme ns
LEFT JOIN nodeassociation na
  ON na.SINK_NODE_ID = ns.ID
  AND na.SINK_NODE_ENTITY = 'NotificationScheme'
  AND na.SOURCE_NODE_ENTITY = 'Project'
  AND na.ASSOCIATION_TYPE = 'ProjectScheme'
WHERE na.SINK_NODE_ID IS NULL
ORDER BY ns.NAME;

Count notification entries per scheme (find the bloated ones)

POSTGRESQL 
SELECT ns.NAME as scheme_name, COUNT(n.ID) as notification_count
FROM notificationscheme ns
LEFT JOIN notification n ON n.SCHEME = ns.ID
GROUP BY ns.NAME
ORDER BY notification_count DESC;

Schemes with 30+ notification entries — often copied from a “Default” scheme and never trimmed. Every entry is a potential email for every issue transition.

WHY IT MATTERS FOR MIGRATION

Cloud has different notification mechanics. Cleaning up unused schemes before migration eliminates a whole category of “why did this change?” questions from users.

3 Permission Scheme Audit

Permission schemes are where security meets complexity. In a typical enterprise instance, you’ll find 20–50 permission schemes, but only 5–10 that are actually distinct. The rest are copies with minor variations that nobody remembers making.

Find permission schemes and their project count

POSTGRESQL 
SELECT ps.ID, ps.NAME as scheme_name, COUNT(na.SOURCE_NODE_ID) as project_count
FROM permissionscheme ps
LEFT JOIN nodeassociation na
  ON na.SINK_NODE_ID = ps.ID
  AND na.SINK_NODE_ENTITY = 'PermissionScheme'
  AND na.SOURCE_NODE_ENTITY = 'Project'
  AND na.ASSOCIATION_TYPE = 'ProjectScheme'
GROUP BY ps.ID, ps.NAME
ORDER BY project_count ASC;

Schemes with project_count = 0 are candidates for removal. Schemes with project_count = 1 might be unnecessarily unique — could they share a scheme with similar projects?

Compare two permission schemes side-by-side

POSTGRESQL 
SELECT
  sp.PERMISSION_KEY,
  sp.PERM_PARAMETER as granted_to,
  sp.PERM_TYPE as grant_type
FROM schemepermissions sp
WHERE sp.SCHEME = :scheme_id_1

EXCEPT

SELECT
  sp.PERMISSION_KEY,
  sp.PERM_PARAMETER as granted_to,
  sp.PERM_TYPE as grant_type
FROM schemepermissions sp
WHERE sp.SCHEME = :scheme_id_2;

Replace :scheme_id_1 and :scheme_id_2 with actual IDs from the first query. The result shows permissions that exist in scheme 1 but not in scheme 2. Run it both ways to get the full diff.

Risk level: Medium. Consolidating permission schemes requires careful testing. But knowing which schemes are identical or near-identical is the first step.

4 Attachment Storage Analysis

Attachments are the silent storage killer. Unlike issues and custom fields, they don’t show up in Jira’s performance metrics. But they eat disk space, slow down backups, and increase migration time.

Total attachment count and estimated storage

POSTGRESQL 
SELECT
  COUNT(ID) as total_attachments,
  pg_size_pretty(SUM(FILESIZE)) as total_size
FROM fileattachment;

Top 20 projects by attachment volume

POSTGRESQL 
SELECT
  p.pkey as project_key,
  p.pname as project_name,
  COUNT(fa.ID) as attachment_count,
  pg_size_pretty(SUM(fa.FILESIZE)) as total_size
FROM fileattachment fa
JOIN jiraissue ji ON fa.ISSUEID = ji.ID
JOIN project p ON ji.PROJECT = p.ID
GROUP BY p.pkey, p.pname
ORDER BY SUM(fa.FILESIZE) DESC
LIMIT 20;

Find the largest individual attachments

POSTGRESQL 
SELECT
  p.pkey || '-' || ji.ISSUENUM as issue_key,
  fa.FILENAME,
  pg_size_pretty(fa.FILESIZE) as file_size,
  fa.CREATED
FROM fileattachment fa
JOIN jiraissue ji ON fa.ISSUEID = ji.ID
JOIN project p ON ji.PROJECT = p.ID
ORDER BY fa.FILESIZE DESC
LIMIT 50;

A handful of projects (often 3–5) account for 60–80% of total attachment storage. Common culprits: design review projects with full PSD files, test projects with log dumps, and projects where someone used Jira as a file server.

THE MIGRATION MATH

Cloud storage isn’t free. At scale, every GB matters. Knowing where your attachment weight is concentrated lets you make targeted cleanup decisions before migration — archive old projects, remove obsolete test data, move large files to external storage.

5 Orphaned Boards and Filters

Boards and filters owned by deactivated users are a known problem, but most admins underestimate the scale.

Find boards owned by inactive users

POSTGRESQL 
SELECT
  rv.ID as board_id,
  rv.NAME as board_name,
  cu.user_name as owner,
  cu.active as user_active
FROM AO_60DB71_RAPIDVIEW rv
JOIN app_user au ON rv.OWNER_USER_NAME = au.USER_KEY
JOIN cwd_user cu ON au.LOWER_USER_NAME = cu.LOWER_USER_NAME
WHERE cu.active = 0
ORDER BY rv.NAME;

Find saved filters owned by inactive users

POSTGRESQL 
SELECT
  sr.ID as filter_id,
  sr.FILTERNAME as filter_name,
  sr.USERNAME as owner_key,
  cu.user_name as owner_name,
  cu.active as user_active,
  sr.FAVCOUNT as subscriber_count
FROM searchrequest sr
JOIN app_user au ON sr.USERNAME = au.USER_KEY
JOIN cwd_user cu ON au.LOWER_USER_NAME = cu.LOWER_USER_NAME
WHERE cu.active = 0
ORDER BY sr.FAVCOUNT DESC;

Pay attention to subscriber_count

A filter with 0 subscribers owned by an inactive user? Safe to remove.

A filter with 15 subscribers owned by an inactive user? That filter is critical — transfer ownership first.

6 Screen Scheme Bloat

Screen schemes control what fields users see when creating, editing, or viewing issues. Like permission schemes, they tend to accumulate over time.

Find screen schemes not used by any issue type screen scheme

POSTGRESQL 
SELECT fss.ID, fss.NAME as scheme_name
FROM fieldscreenscheme fss
LEFT JOIN issuetypescreenschemeentity itsse ON itsse.FIELDSCREENSCHEME = fss.ID
WHERE itsse.ID IS NULL
ORDER BY fss.NAME;

Count total vs. used screen schemes

POSTGRESQL 
SELECT
  (SELECT COUNT(*) FROM fieldscreenscheme) as total_schemes,
  (SELECT COUNT(DISTINCT fss.ID)
   FROM fieldscreenscheme fss
   JOIN issuetypescreenschemeentity itsse ON itsse.FIELDSCREENSCHEME = fss.ID) as used_schemes;

Many screen schemes are orphaned. They were created during workflow setup experiments and never cleaned up.

7 Issue Type Scheme Analysis

How many issue types do you actually use vs. how many exist?

Issue types with zero issues

POSTGRESQL 
SELECT it.ID, it.pname as issue_type_name, it.STYLE
FROM issuetype it
LEFT JOIN jiraissue ji ON ji.issuetype = it.ID
WHERE ji.ID IS NULL
ORDER BY it.pname;

Issue type usage across projects

POSTGRESQL 
SELECT
  it.pname as issue_type,
  COUNT(DISTINCT ji.PROJECT) as projects_using,
  COUNT(ji.ID) as total_issues
FROM issuetype it
LEFT JOIN jiraissue ji ON ji.issuetype = it.ID
GROUP BY it.pname
ORDER BY total_issues ASC;

Issue types with 0 issues are safe candidates for removal. Issue types used in only 1 project might be candidates for consolidation — especially if they’re functionally identical to a standard type with a different name.

8 Audit Log Health Check

Jira’s audit log table can grow silently and affect database performance.

Check audit log table size (Jira 8.8+)

POSTGRESQL 
SELECT
  COUNT(*) as total_entries,
  to_timestamp(MIN(cast("ENTITY_TIMESTAMP" as bigint)/1000)) as oldest_entry,
  to_timestamp(MAX(cast("ENTITY_TIMESTAMP" as bigint)/1000)) as newest_entry,
  pg_size_pretty(pg_total_relation_size('public."AO_C77861_AUDIT_ENTITY"')) as table_size
FROM public."AO_C77861_AUDIT_ENTITY";
AUDIT LOG RETENTION

If the table is growing beyond 1–2 GB, configure retention in Jira’s admin settings. For large DC instances (1,000+ users), Atlassian recommends the lowest possible database retention with external log management (e.g. Splunk). For smaller instances (150–300 users), 3-year retention is typically sufficient.

List the most common audit actions

POSTGRESQL 
SELECT "ACTION", COUNT(*) as action_count
FROM public."AO_C77861_AUDIT_ENTITY"
GROUP BY "ACTION"
ORDER BY action_count DESC
LIMIT 20;

Putting It All Together: The 30-Minute Audit

You don’t need to run all of these at once. Here’s a priority order:

Priority Query Category Time What You’ll Find
1 Attachment storage (Section 4) 5 min Where your disk space goes
2 Orphaned boards & filters (Section 5) 5 min Ownership gaps before they become incidents
3 Workflow scheme orphans (Section 1) 3 min Configuration clutter to clean up
4 Permission scheme audit (Section 3) 7 min Security gaps and consolidation opportunities
5 Notification scheme cleanup (Section 2) 3 min Source of email noise
6 Screen scheme bloat (Section 6) 2 min Admin UI clutter
7 Issue type analysis (Section 7) 3 min Simplification candidates
8 Audit log health (Section 8) 2 min Hidden database growth

Total: ~30 minutes for a complete picture of what your UI doesn’t show you. Export the results to a spreadsheet. Share it with your team. Use it as the basis for a cleanup plan — or as migration prep documentation.

What Intuit Found When They Did This

The engineering team at Intuit — running Jira Data Center with 3,000 projects, 6 million records, and about 500 custom fields — went through a similar audit process. They removed 100+ unused custom fields, changed field scopes from global to project-specific, and optimized their webhook configuration.

The result: they achieved their target of completing 99% of user operations within 3 seconds at the 90th percentile — across 3,000 projects and hundreds of workflows.

The lesson: you don’t need a massive infrastructure overhaul. You need to find what’s unused, remove it, and tighten the scope of what remains.

Source: Revving Up Jira: Proven Techniques — Intuit Engineering

The Bottom Line

Your Jira admin UI shows you about 60% of what’s actually in your instance. The other 40% — orphaned schemes, abandoned filters, attachment bloat, permission drift — lives in the database and accumulates silently.

With Data Center’s feature freeze weeks away and end-of-life in 2029, there’s no better time to look under the hood. These queries take 30 minutes to run. The cleanup decisions they enable can save weeks during migration — and real money on both DC and Cloud licensing.

Start with attachments. That’s where the biggest surprise usually is.

This is the fourth article in our Jira admin audit series:

Custom Field Bloat → Find Hidden Waste → Safe Deletion → License Audit → Database Audit (you are here)

Skip the SSH. Run These Queries from Jira.

Home Directory Database Browser lets you run all of these queries directly from your Jira admin panel:

  • No SSH, no database credentials to manage
  • Read-only by design — can’t accidentally break anything
  • Copy-paste the queries from this guide and run them instantly
  • Export results to CSV for spreadsheet analysis
Try free for 30 days →
Scroll to Top