reesesWeb

We've been using Bugzilla yet again for a quick bug tracking tool on projects, since clients don't have to pay for it. But as you may know, the querying interface is pretty rough. I wrote a quick script to do some useful stats and email them out to the team. Of course, it's MySQL-based, so the range of analytics you can do in atomic statements is pretty limited. I would have killed for subselects for trending and some post-processing. I'm too lazy to use Perl, and ruby isn't installed on that box.

Anyway, in case you don't know SQL, here are a few quickies:

Open/non-resolved bugs since 14 Oct 2002:

select count(*) 'open bugs (bugs not closed)' from bugs where bug_status <> 'CLOSED' and creation_ts > '2002-10-14' and bug_status <> 'RESOLVED';

Bugs filed per day since 14 Oct 2002:

select count(*) 'bugs filed per day', date_format(creation_ts, '%Y-%b-%d') 'Date' from bugs where creation_ts > '2002-10-14' group by year(creation_ts) desc, month(creation_ts) desc, dayofmonth(creation_ts) desc;

Bugs closed per day since 14 Oct 2002:

select count(*)'bugs closed per day', date_format(bug_when, '%Y-%b-%d') 'Date' from bugs_activity, bugs where bugs.bug_id = bugs_activity.bug_id and bugs.resolution <> 'DUPLICATE' and added = 'CLOSED' and creation_ts > '2002-10-14' group by year(bug_when) desc, month(bug_when) desc, dayofmonth(bug_when) desc;

Open bugs by user:

select login_name, count(*) 'open bugs' from bugs, profiles where assigned_to = userid and bug_status not in ('RESOLVED', 'CLOSED') and creation_ts > '2002-10-14' group by login_name order by login_name;

Open bugs by severity:

select count(*) 'bugs by severity', bug_severity from bugs where bug_status <> 'CLOSED' and creation_ts > '2002-10-01' and bug_status <> 'RESOLVED' group by bug_severity;

Bookmark/Search this post with:

delicious | digg | reddit | technorati