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
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;
__id3q
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.
