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, p.name as product,
         c.name as component, u.login_name as owner
  FROM
    bugs b, components c, products p,
    keywords k, keyworddefs d, profiles u
  WHERE b.bug_id = k.bug_id  AND
        k.keywordid = d.id   AND
        d.name = 'foo'       AND
        b.product_id  = p.id AND
        b.component_id = c.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.