JIRA

Postgres exporting

select (p.pkey || '-' || i.issuenum) as key, i.summary, i.reporter, i.assignee, t.pname as issuetype, to_char(i.created,'dd/MM/yy HH24:MI') as created, to_char(i.updated,'dd/MM/yy HH24:MI') as modified, i.description, to_char(i.duedate,'dd/MM/yy HH24:MI') as duedate, i.environment, s.pname as priority, i.resolution, i.resolutiondate, w.status, i.timeestimate as originalestimate, i.timeestimate as remainingestimate, i.timespent from jiraissue i, project p, issuetype t, os_currentstep w, priority s;

#display keys and issues
select (p.pkey || '-' || i.issuenum) as key from jiraissue i, project p;

#show all keys
select (p.pkey || '-' || i.issuenum) as key, i.summary, i.reporter, i.assignee, t.pname as issuetype, to_char(i.created,'dd/MM/yy HH24:MI') as created, to_char(i.updated,'dd/MM/yy HH24:MI') as modified, i.description, to_char(i.duedate,'dd/MM/yy HH24:MI') as duedate, i.environment, s.pname as priority, i.resolution, i.resolutiondate, w.status, i.timeestimate as originalestimate, i.timeestimate as remainingestimate, i.timespent from jiraissue i, project p, issuetype t, os_currentstep w, priority s where i.project = p.id and i.issuetype = t.id and i.workflow_id = w.entry_id and i.priority = s.id and p.pkey = 'KUN';


#--CSV export issues
copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, i.reporter, i.assignee, t.pname as issuetype, to_char(i.created,'dd/MM/yy HH24:MI') as created, to_char(i.updated,'dd/MM/yy HH24:MI') as modified, i.description, to_char(i.duedate,'dd/MM/yy HH24:MI') as duedate, i.environment, s.pname as priority, i.resolution, i.resolutiondate, w.status, i.timeestimate as originalestimate, i.timeestimate as remainingestimate, i.timespent from jiraissue i, project p, issuetype t, os_currentstep w, priority s where i.project = p.id and i.issuetype = t.id and i.workflow_id = w.entry_id and i.priority = s.id and p.pkey = 'KUN') to '/tmp/01test.csv' with CSV HEADER;

#--CSV exports comments
copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, (to_char(c.created,'dd/MM/yy HH24:MI') || ';' || c.author || ';' || c.actionbody) as comment
from jiraissue i, project p, jiraaction c
where i.project = p.id 
and c.actiontype = 'comment'
and c.issueid = i.id
and p.pkey = '<Enter Project Key Here>')

#--CSV exports attachments
copy (select (p.pkey || '-' || i.issuenum) as key, i.summary, (to_char(a.created,'dd/MM/yy HH24:MI') || ';' || a.author || ';' || a.filename || ';' || 'file://' || p.pkey || '/' || p.pkey || '-' || i.issuenum || '/' || a.id ) as attachment
from jiraissue i, project p, fileattachment a
where i.project = p.id 
and a.issueid = i.id
and p.pkey = '<Enter Project Key Here>')
to '<Enter Location to Save File>' with CSV HEADER FORCE QUOTE attachment;

#--CSV export user
copy cwd_user(id, user_name, lower_first_name, lower_last_name) to '/tmp/jirausers.csv' delimiters',' CSV HEADER;

#--CSV export memberships
copy cwd_membership(child_name, parent_name) to '/tmp/jiramembership.csv' delimiters',' CSV HEADER;