Sql query to get all attachment in jira

anuj__sharma

I am trying to get the Jira issue attachment details from database using a sql query.

The query is :

SELECT  pkey, CI.NEWVALUE, CI.NEWSTRING FROM jiraissue JI, 
 changegroup CG, 
 changeitem CI 
 WHERE JI.ID = CG.issueid 
   AND JI.PROJECT IN (select id from project 
                    where pkey = 'projectkey') 
   AND CG.ID = CI.groupid 
   AND CI.FIELD = 'Attachment' 
   AND JI.issuestatus IN (select ID from issuestatus);

When i verify this data with the attachment folder in jira/attachment
The data fetched from database is coming out to be less(incomplete).

There are some attachment in the jira/attachment folder with the syntax :
"attachmentid_attachmentname" which i am not able to retrieve using database.

Can someone suggest a solution

anuj__sharma

I have got the required data from the 'fileattachment' table.

 

But still i have a doubt :
why jira saves some files in the file system as 'attachmentid' only and 
some files as 'attachmentid_filename' ?

This is also described here
https://confluence.atlassian.com/jirakb/jira-unable-to-find-attachments-269984254.html

But can some one clarify please

 

 

Thanks