How to discover Jira changes

Description

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 use it for all the queries. Repeat.

New issues

New issue select

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

Changed issues

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

Comment changes

Comment changes

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