Как достать из JIRA все что угодно с помощью SQL

На форумах очень часто спрашивают о существовании готовых отчетов или плагинов (даже платных!), которые позволяют решить определенную управленческую задачу. И часто такие вопросы остаются без ответа. Можем сказать с уверенностью, что все бы эти задачи решились с помощью SQL и прямых обращений к БД JIRA.

В данной статье рассмотрим несколько примеров SQL-запросов, которые можно выполнять напрямую к БД системы JIRA. Запросы ориентированы на СУБД MS SQL Server.

 

Получить все запросы в проекте DEMO

select ji.*
from project p
join jiraissue ji on (ji.project = p.id)
where p.pkey = ‘DEMO’


Получить общие трудозатраты в проекте DEMO, чел-час

SELECT sum(ji.timespent/3600) wl_total
from project p
join jiraissue ji on (ji.project = p.id)
where p.pkey = ‘DEMO’


Получить список всех закрытых багов в проекте

SELECT ji.*
from project p
join jiraissue ji on (ji.project = p.id)
join issuetype it on (it.id = ji.issuetype)
join issuestatus ist on (ist.id = ji.issuestatus)
where ji.project = p.id
and p.pkey = ‘DEMO’
and it.pname in (‘Bug’)
and ist.pname = ‘Closed’


Получить запросы в проекте DEMO которые переводились в статус Reopened с количеством таких переходов

SELECT ji.pkey, ji. summary, count(*)
FROM changeitem ci
join changegroup cg on (cg.id = ci.groupid)
join jiraissue ji on (cg.issueid = ji.id)
join project p on (p.id = ji.project)
where field = ‘status’
and cast(ci.newstring as nvarchar) = ‘Reopened’
and p.pkey = ‘DEMO’
group by ji.pkey, ji. summary


Статистика переоткрытий задач и багов в проекте DEMO

SELECT DATEPART(MONTH,cg.created) month, DATEPART(YEAR,cg.created) year,COUNT(*) cnt
FROM project p
join jiraissue ji on (ji.PROJECT = p.ID)
join issuetype it on (ji.issuetype = it.ID)
join changegroup cg on (cg.issueid = ji.id)
join changeitem ci on (ci.groupid = cg.id)
where p.pkey = ‘DEMO’
and it.pname in (‘Task’,’Bug’)
and ci.field = ‘status’
and cast(ci.newstring as nvarchar) = ‘Reopened’
group by DATEPART(month,cg.created), DATEPART(YEAR,cg.created)


За период времени узнать по сотруднику «Какие коменты он добавлял и в каких запросах он менял статусы»

declare @start_date date, @end_date date, @user nvarchar(100)

set @start_date = cast(‘2013.01.01’ AS date)
set @end_date = cast(‘2013.01.10’ AS date)
set @user = ‘user_name’

select cg.CREATED,
‘status changed’ action,
cast(ji.pkey as nvarchar(100))+’ from ‘+cast(ci.OLDSTRING as nvarchar(100)) +’ to ‘ + cast(ci.NEWSTRING as nvarchar(100)) actionbody
from changegroup cg,
changeitem ci,
jiraissue ji
where cg.CREATED between @start_date and @end_date
and AUTHOR = @user
and ci.groupid = cg.ID
and ci.FIELD = ‘status’
and ji.ID = cg.issueid
union all
SELECT ja.CREATED, ‘comment created’ action , ji.pkey +’ ‘ cast(ja.actionbody as nvarchar(1000)) actionbody
FROM jiraaction ja,
jiraissue ji
where ja.CREATED between @start_date and @end_date
and ja.AUTHOR = @user
and ji.id = ja.issueid
union all
SELECT ja.CREATED, ‘comment created’ action , ji.pkey +’ ‘ cast(ja.actionbody as nvarchar(1000)) actionbody
FROM jiraaction,
jiraissue ji
where UPDATED between @start_date and @end_date
and UPDATEAUTHOR = @user
and ji.id = ja.issueid


Трудозатраты в разрезе видов работ

SELECT  wlt.pname,

sum(wl.timeworked/3600) wl_total

from  project p

 

join  jiraissue ji on (ji.project = p.id)

 

join worklog wl on (wl.issueid = ji.ID)

 

join jiraworklog jwl on (jwl.id = wl.ID)

 

join worklogtype wlt on (wlt.id = jwl.worklogtype)

where p.pkey = ‘DEMO’

group by wlt.pname