...
JIRA records all changes to issues in the change log and other tables. There currently is no remote API to access these changes, but you can query the JIRA tables (read only!) to get the information. This can be used to feed into external systems either using the JIRA CLI (an external JIRA system) or other applications. If going to another JIRA, the runFromSql action can be used directly from these queries. Your script will need to keep track of time intervals to ensure all changes are selected only once. One technique is to let your database generate a specific timestamp, save this in a table so it is available across script shutdowns, and then us it for all the queries. Repeat.
New issues
| Code Block |
|---|
| sql | sql |
|---|
| title | New issue select |
|---|
| sql |
|---|
|
select
pkey,
issuetype, -- you may want conditions on this in the where clause or script logic
issuestatus, -- you may want conditions on this in the where clause or script logic
from jiraissue
where created > '${sinceTimestamp}' and created <= '${endTimestamp}'
order by created asc
|
Changed issues
| Code Block |
|---|
sql |
|---|
| sql |
|---|
| title | Changed issues |
|---|
| sql |
|---|
|
select
ji.pkey as pkey,
ji.issuetype as issuetype,
ji.issuestatus as issuestatus,
cg.created as created,
cg.author as author,
ci.field as field,
ci.fieldtype as fieldtype,
ci.oldstring as oldstring,
from changegroup as cg
join jiraissue ji on cg.issueid = ji.id
join changeitem ci on ci.groupid = cg.id
where cg.created > '${sinceTimestamp}' and cg.created <= '${endTimestamp}'
order by cg.created asc
|
| Code Block |
|---|
sql |
|---|
| sql |
|---|
| title | Comment changes |
|---|
| sql |
|---|
|
select
ji.pkey as pkey,
ji.issuetype as issuetype
from jiraaction as ja, jiraissue as ji
where ja.actiontype = 'comment'
and (ja.created > '${sinceTimestamp}' or ja.updated > '${sinceTimestamp}')
and (ja.created <= '${endTimestamp}' or ja.updated <= '${endTimestamp}')
and ja.issueid = ji.id
order by ja.updated asc, ja.created asc
|