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.
1 Comment
“If I were committed only to being right, I would go out and talk anyway, but because I’m committed to making a difference, I wait until I’m ready.” –Julia Butterfly Hill
Feedback Welcome
http://youtu.be/ao0k9qDsOvs
What sort of disturbs me is the Christians walking out. The religion says you have an obligation to spread the gospel, to preach The Word. That means you ought to interact with non-believers and skeptics, not leave the room as soon as some public figure challenges some or another point regarding your religious faith.
Jesus would have stuck around, and perhaps had some salient response regarding the morality of slavery, stoning, and tolerance of non-traditional sexual practices.
1 Comment
Daily Routine
In a hospital room, the Daily Routine consists mainly of waiting.
Feedback Welcome
“We are the supermen who sit idly by and laugh and look at civilization . . .”
I am reading a book by W.E.B. Du Bois, based on this quote which was captured in a photo of New York graffiti.
Even as America’s race problem has evolved since Du Bois published Dusk of Dawn in 1940, his perspective is valuable. A fuller excerpt from Chapter 6, wherein he conducts a Socratic dialogue with a pair of composite “White Man” colleagues, and delivers an excellent perspective on world history, and modesty: (more…)
Feedback Welcome
Remember pets.com? Back in the Dot Com Bubble, they had this awesome business plan to sell pet food … using the Internet! They had a Superbowl commercial, than they died like so many other startups when the Dot Com Bubble imploded on itself.
So, my Bubbledar pinged when I saw this headline this morning:
http://allthingsd.com/20120409/woof-rover-com-fetches-3-4-million-to-be-airbnb-for-dogs/
Yes! Peer-to-peer vacation homes! For pets!
And then, this thing dropped, and my Bubbledar is pinging louder:
http://finance.fortune.cnn.com/2012/04/09/breaking-facebook-buying-instagram-for-1-billion/
Yes! A billion dollars for an iPhone app that takes pictures that look like Polaroids. But you don’t just get the app, you get the iPhone users who have been uploading their photos to social networking sites, because that is a key demographic that Facebook has yet to crack!
A few years ago, Zuckerberg thought $500 million would be a lot of money to acquire Twitter. Fun story here:
http://www.npr.org/2011/07/16/138160765/twitter-co-founder-biz-stone-plays-not-my-job
Feedback Welcome
Above all science was becoming religion; psychology was reducing metaphysics to experiment and a sociology of human action was planned. Fighting the vast concept of evolution, religion went into its heresy trials, its struggle with “higher criticism,” its discomfort at the “revised version” of the New Testament which was published the year I entered college. Wealth was God. Everywhere men sought wealth and especially in America there was extravagant living; everywhere the poor planned to be rich and the rich planned to be richer; everywhere wider, bigger, higher, better things were set down as inevitable.
— W. E. B. Du Bois
… who entered college in 1885
Actually, Chapter 3 of “Dusk of Dawn” describes a transition from the world Du Bois was born into of the latter 19th century:
“(As) a young man, so far as I conceived, the foundations of present culture were laid, the way was charted, the progress toward certain great goals was undoubted and inevitable. There was room for argument concerning details and methods and possible detours in the onsweep of civilization; but the fundamental facts were clear, unquestioned and unquestionable.”
In contrast with the “today” of 1940:
“TODAY both youth and age look upon a world whose foundations seem to be tottering. They are not sure what the morrow will bring; perhaps the complete overthrow of European civilization, of that great enveloping mass of culture into which they were born. Everything in their environment is a meet subject for criticism. They can dispassionately evaluate the past and speculate upon the future. It is a day of fundamental change.”
I feel my heart and mind whipsawing between a world culture which is on the cusp of some fundamental, unimaginable change, and a world in which we will pretty much keep doing what we have done, just bigger, bolder, better, faster, with nanites and a higher rate of return . . . I get dizzy thinking about this world I try to live in.
And Religionists and Conservatives keep shouting their objections to a changing world ever louder, ever more viciously. They’re still attacking Evolution, so the concept and theological implications of Anthropogenic Climate Disruption are even more of a leap . . .
But the today of 2012, when the big revolutions appear to be how the European Union will manage debt among member states, and whether Arab countries can successfully democratize, whether there will be regional wars on either side of Asia, and the capacity of fundamentalists to kill civilians . . . today’s world isn’t tottering as obviously as 1940’s “today.”
But it is the Big Things you don’t hear in the news every day; When will climate change trigger famine and mass migration? Will China’s rise be sustained to the point it becomes a world power or will it implode? When are we going to be hit by that asteroid that superheats the atmosphere? Just after the devastating global pandemic that trained against antibiotics and traveled everywhere on jet planes before we noticed it? Will nanofabrication make industry and perhaps agriculture obsolete? Will the Singularity bring upon us a supra-individualist world consciousness? Will medical science and DNA repeal the eternal inevitabilities of aging and death? Is that when we will feel comfortable encapsulating our bodies on centuries-long trips to distant star systems? The new Magellans will refer to centuries as we refer to decades. My thinking is so early 21st . . .
These are the things I tend to wonder about between meetings at work.
Feedback Welcome
Most of the time, Maggie isn’t big on laps, but today my legs offer a warm dry spot to enjoy a sunny break from the wet weather.
Feedback Welcome
Our Frenchman flew over to San Jose from London, and wanted a picture with our American colleague in Tokyo.
Rockin’ the Cisco TelePresence!
Feedback Welcome
Last week I dug through several menus to try and unsubscribe from all the spam LinkedIn sends me. Today I got another email and at the bottom was an “unsubscribe” link that I clicked on. Here’s what I got:
Not only is this not an unsubscribe feature, LinkedIn gets bonus points for trying to sign you up for MORE e-mail.
Instead of screwing around with the half-dozen sub-menus again, I dropped them a feedback saying that this burns up good will, damages the brand, discourages me from engaging, and may in time lead me to delete my profile. There’s more than one way to search for resumes online.
2 Comments
