SQL query to list issue id and attachment name of all closed issues in a project in the last 12 hours

[Ryan Aherne]

Hi,

I'm look to retrieve the issue id and attachment name of all closed issues in a project

and then maybe all issues closed within the last 12 hours.

Using:
MySQL 5.0.51a-24+lenny4 (debian)
JIRA v4.0#466

Any help appreciated,

Pug.

[Norman Abramovitz · Accepted solution]

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

You will need to add the current time minus 12 hours yourself, but you just need to compare to the updated field as shown below.

AND JI.UPDATED >=

[Norman Abramovitz]

Remember, never modify Jira's database while it is online.

[Ryan Aherne]

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.issuestatus

[Ryan Aherne]

Bravo ;o) If I want to specify the project, is that an easy tweak?

[Norman Abramovitz]

I am working in sql server and I forgot to remove one of the full table references for remote access.

Also changed to use IN instead of =. So try it now.

[Norman Abramovitz]

yes, it is an easy tweak, The table is called project. The field in the jiraissue table is called PROJECT. Do you want to try it yourself?

[Ryan Aherne]

I'll give it a go ;o) thanks for your help. Your a gent!

[Norman Abramovitz]

If you run into issues just add a comment with the SQL that is failing. It is really worth learning some minimal SQL.

[Ryan Aherne]

Specifing project '12345', the SQL is...

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

Thanks again Norman for your help ;o)

[Norman Abramovitz]

That is correct. If want to do it by project name verses project id then it will be similar to the issuestatus clause with a few changes of course. :>)

[Ryan Aherne]

Yes I see, project name is probably a better way to do it. Thanks.

[Ryan Aherne]

Using project name:

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 pname = 'My Project Name')
AND CG.ID = CI.groupid
AND CI.FIELD = 'Attachment'
AND JI.issuestatus IN (select ID from issuestatus
where pname = 'Closed');

[Ryan Aherne]

using project name:

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 pname = 'My Project Name')
   AND CG.ID = CI.groupid
   AND CI.FIELD = 'Attachment'
   AND JI.issuestatus IN (select ID from issuestatus
                    where pname = 'Closed');