How to get attachment statistics from the database in Jira Data Center
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Jira administrators may want to audit their environment's attachment usage. While we recommend using the REST API where possible, these SQL queries may provide additional insights.
Disclaimer
These SQL queries are provided "as is". We do not recommend or support using the database for reporting purposes.
Environment
Any Jira version. The provided queries use the PostgreSQL dialect but can be converted using tools like the jOOq Parser.
Solution
Replace PROJECT-KEY-HERE and ISSUE-NUMBER-HERE where applicable.
Precise data
Get the number of attachments and disk usage (in MB) for an issue
1
2
3
4
5
6
SELECT COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
WHERE p.pkey = 'PROJECT-KEY-HERE'
AND ji.issuenum = 'ISSUE-NUMBER-HERE';Get the number of attachments and disk usage (in MB) for a project
1
2
3
4
5
SELECT COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
WHERE p.pkey = 'PROJECT-KEY-HERE';Get the number of attachments and disk usage (in MB) for all of Jira
General attachment count can also be found at ⚙️ (gear icon) > System > System info > Database statistics > Attachments.
1
2
3
4
5
6
7
8
SELECT
filename,
COUNT(*) AS file_count,
ROUND(SUM(filesize) / 1000000, 2) AS total_mb
FROM
fileattachment
GROUP BY
filename;Aggregate statistics
Find the largest 20 issues by number of attachments
1
2
3
4
5
6
7
SELECT CONCAT(p.pkey, '-', ji.issuenum) AS issue, COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
GROUP BY p.pkey, ji.issuenum
ORDER BY (COUNT(*)) DESC
LIMIT 20;Find the largest 20 issues by disk usage
1
2
3
4
5
6
7
SELECT CONCAT(p.pkey, '-', ji.issuenum) AS issue, COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
GROUP BY p.pkey, ji.issuenum
ORDER BY (SUM(fa.filesize)) DESC
LIMIT 20;Find the largest 20 projects by number of attachments
1
2
3
4
5
6
7
SELECT p.pkey, p.pname, COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
GROUP BY p.pkey, p.pname
ORDER BY (COUNT(*)) DESC
LIMIT 20;Find the largest 20 projects by disk usage
1
2
3
4
5
6
7
SELECT p.pkey, p.pname, COUNT(*), ROUND((SUM(fa.filesize) / 1000000), 2) AS total_mb
FROM fileattachment fa
JOIN jiraissue ji ON ji.id = fa.issueid
JOIN project p ON p.id = ji.project
GROUP BY p.pkey, p.pname
ORDER BY (SUM(fa.filesize)) DESC
LIMIT 20;Was this helpful?