JIRA, Technical

Work Note: Quarterly Reports from MySQL

I am not a DBA. I am but a humble SysAdmin who gets asked to figure out things like “how have we been at meeting our SLAs over time?” After I try to excuse myself I’ll inevitably end up say at the JIRA database running a query like this:

echo "Incidents (P3)"
mysql -u jira jiradb< <__id3q
select year(created) as "Year", quarter(created) as "Quarter",
    count(pkey) as "Total",
    sum(resolutiondate < date_add(created, interval x day)) as "Met SLA",
    sum(resolutiondate < date_add(created, interval x day)) / count(pkey) as "SLA %%"
    from jiraissue where pkey like 'OPS-%' and priority = 3
    and assignee != 'nagios' and issuetype = 26
    group by year(created), quarter(created) order by created;

That above is a fragment from a shell script. Shell scripts are great for complex SQL queries, I find. Set a value x at interval x day and the output looks something like:

Incidents (P3)
Year    Quarter Total   Met SLA SLA %%
2011    2       xxx     xxx     x.xxxx
2011    3       xxx     xxx     x.xxxx
2011    4       xxx     xxx     x.xxxx
2012    1       xxx     xxx     x.xxxx
2012    2       xxx     xxx     x.xxxx

The query does some things that are newer to my limited understanding of SQL. For me the magic bits are sum()ed columns and the availability of quarter() … you can do monthly reports just as easily with month(). I’d love to concatenate Year-Month into a string like “2012-05” but for the purposes of making my boss a little happier queries like this are good to have in the locker.

Some day I’ll be hip enough to convert things like this into JIRA widgets.

Oh yeah, and if your SLAs are measured in “business hours” or “business days” this will give you only a crude understanding of how well you have met your SLAs … an accurate measure would probably get embedded in a handler that gets called on issue close which can evaluate SLA fulfillment per issue priority and the local work schedule.

Read More

Categories: JIRA, Technical

  • Martins Kemme

    There is a tool that does JIRA SLA monitoring – SLAdiator.com. You may want to check it out in order to get rid of manual reports.