Thursday, August 16, 2007

Custom Bugzilla Reports

I'm a big fan of Bugzilla and couldn't agree with more with Joel, a bug tracking system is an absolutely essential tool for making a successful dev team.

Where Bugzilla seems to be lacking the most is on the reporting side. I can cut them a bit of slack on this, as reporting needs are going to differ from team to team.

For a while now, my team has been tracking some items manually both in Bugzilla and on a Wiki page. Last week, I finally got fed up enough to do something about it.

So, this morning, Kelly and I decided to peek behind the curtain and actually look into querying Bugzilla's database directly to get the tidbits of info we needed from it.

I'm not quite sure what I was expecting - some sort of hideous, hacked up mess or something. But I was completely wrong. The Bugzilla MySQL database is pretty much exactly what you'd expect. It has a bugs table, products table, components table and lots of there tables exactly where you'd expect them. They are linked together by perfectly sane foreign keys.

In just a few minutes we had our first query written in PHP, and after an hour (with at least 10 minutes before the report was due), we had a working replacement for the report we had been working on manually.

Bugzilla even has a feature which seems custom made for querying stuff behind its back - keywords. Keywords are basically flickr/delicious style tags. You can add an arbitrary number of them to any bug report and then query against them later. Want to see all bugs that involved a problem with deployments - just tag them deployment and query for that after the fact. (Note: Tonya does our deployments, so we don't have bugs in this category.)

As an example, below is a query which will show all bug reports closed out in the last 7 days which have the tag foo.

  SELECT b.bug_id, b.priority, b.resolution,
         b.short_desc, as product, as component, u.login_name as owner
    bugs b, components c, products p,
    keywords k, keyworddefs d, profiles u
  WHERE b.bug_id = k.bug_id  AND
        k.keywordid =   AND = 'foo'       AND
        b.product_id  = AND
        b.component_id = AND
       (b.bug_status = 'CLOSED' OR b.resolution <> '') AND
        b.assigned_to = u.userid  AND
        b.lastdiffed > (now() - interval 7 day)
  ORDER BY b.lastdiffed DESC

I just love the feeling of replacing manual effort with a script.

1 comment:

  1. Now don't you have to tag every ticket to use the tags for reporting purposes? If this is the case, doesn't it become potentially more error prone due to typing vs selections (radio/drop-down) as in products or components?