Snapshot of a Bugzilla database

Bugzilla is not my preferred choice when choosing a bug/issue/incident tracking system : I found Jira (way) more powerful and flexible, even a the price of an increased complexity and learning curve (for the administrator).

Some times ago I worked on a bugzilla installation used mainly to track software development for a large, enterprise-level ERP.

One requirement was to display the status of the database at a particular time in the past (for reporting purposes), but Bugzilla doesn’t have such tool (it has other nice reports btw).

Exploring the (MySQL) database I found that:

  • current bugs status is stored in the “bugs” table
  • the table “bugs_activity” stores the changes (who, when and what) to bugs

It is therefore possible to find the value for a bug at a particular time by looking at the bugs activity table for that field either in the “added” or in the “removed” columns, or in the “bugs” table, if no activity is found for that field (ie. the value of the field is the same as its creation).

For the “bug_status” field, this gives (for jan, 01 2014 and bug # 99) :

Repeat as necessary for every desired field.

If you are scripting, you may want to use a user-defined variable for target date

If you are looking for an array of values (i needed multiple milestones) you may find the MySQL “find_in_set” function useful:

Wrap up everything:

And you’re ready to go.

Thank you for reading!