Improving your bug tracking process

We’re running mantis at work. Each customer project gets a private project, developers are assigned to categories, customers are invited. Everything flows just fine.

Then one day it struck me – what if the developer is away for a week and the customer files an urgent bug report… Who gets notified? The answer is: The developer who isn’t there.

A simple solution is to force all project managers to check on their projects every day to make sure reports don’t fall between chairs. There are two things wrong with that solution imho:

* It’s error prone as it assumes people will always do what they are told and never forget.
* It doesn’t entail a single piece of new technology…

As I already had some scripts doing data extraction from the mantis database I figured something along the following would work:

* For each project; list all managers with an internal email address.
* For each project, select new bugs and list them with some info about the bug.
* Send the bug reports to the managers for the project.

Sounds easy enough, doesn’t it.

Here’s the script to get all the project managers

  1.  
  2. SELECT
  3.         project.id, project.name, user.email, userlist.`access_level`
  4. FROM
  5.         `mantis_project_user_list_table` AS userlist,
  6.         mantis_user_table AS user,
  7.         mantis_project_table AS project
  8. WHERE
  9.         user.id = userlist.user_id AND
  10.         project.id = userlist.project_id AND
  11.         userlist.`access_level` > 55 AND
  12.         (user.email LIKE "%mydomain.tld" OR user.email LIKE "%.myotherdomain.tld")
  13. ORDER BY
  14.         project.name
  15.  

Save it as projectmanagers.sql

Worth noting is the filtering on email addresses. In some cases we assign manager rights to the project champion on the customer side. Yu may or may not want them to get the bug report email. Your call.

Here’s a shell script to get the list and then call an awk script to do the rest.

  1.  
  2. #!/bin/sh
  3.  
  4. #
  5. #Written by Jesper Hogstrom 2008
  6. #
  7.  
  8. #This will change depending on where your installation of mantis resides
  9. mantisconfig=/var/www/mantis-1.1.2/config_inc.php
  10. rootdir=`dirname $0`
  11.  
  12. function getconfig {
  13. configoption=$1
  14. cat ${mantisconfig}| \
  15. grep -v ‘^#’|\
  16. grep ${configoption}| \
  17. awk -F = ‘{print $2}’| \
  18. awk ‘{print substr($1,2,length($1)-3)}’
  19. }
  20.  
  21. #Note that all these options MUST be set in config_inc.php!!
  22. db_user=`getconfig g_db_username`
  23. db_password=`getconfig db_password`
  24. db_database=`getconfig g_database_name`
  25. #db_host=`getconfig g_hostname` – currently not used, default is localhost
  26. mantisbaseurl=`getconfig g_path`
  27.  
  28. function executesql {
  29. sql=$1
  30. cat $sql|mysql -u$db_user -p$db_password -D$db_database  –skip-column-names
  31. }
  32.  
  33. executesql ${rootdir}/projectmanagers.sql > /tmp/projman
  34. #cat /tmp/projman
  35. awk -f ${rootdir}/bugreports.awk \
  36.         –assign=db_user=${db_user} \
  37.         –assign=db_password=${db_password} \
  38.         –assign=db_database=${db_database} \
  39.         –assign=rootdir=${rootdir} \
  40.         –assign=mantisbaseurl=${mantisbaseurl////\\\\/} \
  41.         /tmp/projman
  42.  
  43.  

Save it as bugreports and make it executable with chmod +x bugreports
There are some things worth noting:
* I specify the path to the mantis configuration file. This is to avoid having to specify the db credentials twice. I guess I could have extracted the php-variables using php, but I don’t know much php. This works, assuming you formatted your config_inc.php file in a reasonable way.
* The bulk of the work is obviously done elsewhere. You do know about awk, don’t you?
* Getting the slashes right in mantisbaseurl took a few minutes :)

Here comes the awk script.

  1.  
  2. #Written by Jesper Hogstrom 2008
  3. BEGIN {
  4.        FS="\t";
  5.        mailfile="/tmp/mantismail";
  6.        sqltemp="/tmp/mantissql";
  7.        sqlscript=rootdir "/bugreports.rsql"
  8. }
  9. {
  10. #For debugging purposes, name each file differently.
  11. mailfile="/tmp/mantismail_" $1 "_" NR;
  12. print "Investigating " $2 " (" $1 ") for " $3
  13.  
  14. # Refresh the script file to pick out only the current project
  15. print mantisbaseurl
  16. system("sed -e ’s/PROJNUM/" $1 "/g’  -e ’s/BASEURL/" mantisbaseurl "/g’ " sqlscript " > " sqltemp);
  17.  
  18. # Preparing mail
  19. print "To: " $3 > mailfile
  20. print "Subject: New bug reports for " $2 >> mailfile
  21. system("cat " sqltemp "|mysql -u" db_user " -p" db_password " -D" db_database " –skip-column-names >> " mailfile);
  22.  
  23.  
  24. #Don’t send empty emails
  25. linecount=0
  26. while ((getline line < mailfile) > 0)
  27.        linecount+=1
  28. close(mailfile)
  29.  
  30. # Send mail
  31. if (linecount > 2)
  32. {
  33.         print " – Found " linecount – 2 " bugs to send."
  34.        system("cat " mailfile " | sendmail -t " $3);
  35. }
  36. }
  37.  
  38.  

Save it as bugreports.awk

This script will operate on all lines in the /tmp/projman file (with all the project managers in it) and do the following:
* Replace some stuff in a file named bugreports.rsql using sed.
* Put mail headers in a text file
* Execute the expanded sql-file and put the result in the text file that makes up the mail
* Could the number of lines in the mail
* If there are any lines above and beyond the headers, send the mail to the manager.

  1.  
  2. SELECT
  3.         concat(bug.id, " (BASEURL/view.php?id=", bug.id, ")") AS "bug#",
  4.         project.name,
  5.         username AS owner,
  6.         severity,
  7.         date(date_submitted),
  8.         datediff(now(), date_submitted) AS "age (days)",
  9.         summary
  10.  
  11. FROM
  12.         mantis_bug_table AS bug,
  13.         mantis_user_table AS user,
  14.         mantis_project_table AS project
  15. WHERE
  16.         project_id IN (PROJNUM) AND
  17.         resolution = 10 AND
  18.         datediff(now(), date_submitted) < 5 AND
  19.         user.id = handler_id AND
  20.         project.id = project_id
  21. ORDER BY
  22.          datediff(now(), date_submitted);
  23.  

Save it as bugreports.rsql

This will select all bugs in status new (i.e. resolution = 10) that are younger than 5 days and come from a project with the number PROJNUM. We actually use sed to replace PROJNUM with the actual project id and also BASEURL with the g_path variable in config_inc.php.

The mail you send will look something like

123 (http://mydomain.tld/mantis/view.php?id=123)      Project_1       \
developer1 70      2008-10-09      1       \
System stops when power cord is removed.
148 (http://http://mydomain.tld/mantis/view.php?id=148)      Project_1       \
developer2  50      2008-10-08      2       \
Saving big images consumes a lot of disk.

I put line breaks in there manually

I admit it could be done somewhat more glitzy with fireworks and images and what have you. However, it’s merely a status report and there is no need to add more – possibly the column headers.

The scripts make very few assumptions about how you have configured things. Feel free to change what doesn’t suit you (like age of bugs, status, who gets mail).

I run my script nightly by symlinking it to /etc/cron.daily.

–Jesper Högström

  • Share/Bookmark
October 14th, 2008 in Linux, development infrastructure

Leave a comment

Your comment