diff options
Diffstat (limited to 'contrib/secbugstats/bin/teamgraph.py')
| -rwxr-xr-x | contrib/secbugstats/bin/teamgraph.py | 151 |
1 files changed, 151 insertions, 0 deletions
diff --git a/contrib/secbugstats/bin/teamgraph.py b/contrib/secbugstats/bin/teamgraph.py new file mode 100755 index 000000000..0ee3051ce --- /dev/null +++ b/contrib/secbugstats/bin/teamgraph.py @@ -0,0 +1,151 @@ +#!/usr/bin/python +# Create a PNG chart of the historical risk index by team to embed in the +# weekly report +import numpy as NP +import matplotlib, sys, MySQLdb, datetime +matplotlib.use("Agg") +from matplotlib import pyplot as PLT +from matplotlib.patches import Rectangle +from matplotlib.font_manager import FontProperties +from settings import * +import time + +# extra debug output +if "--debug" in sys.argv: DEBUG = True +else: DEBUG = False + +# set up database connection +try: + db = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME) + db.autocommit(True) + c = db.cursor(MySQLdb.cursors.DictCursor) +except: + print "teamgraph.py: can't connect to database\n" + sys.exit() + +# Keep track of how many bugs of each severity each team has open +teamStats = {} + +# Risk scoring rubric: assigns point value to each severity of bug +weights = { "sg_critical": 5, + "sg_high": 4, + "sg_moderate": 2, + "sg_low": 1} + +# Gather the risk index for each team at each point in time starting in +# September 2009 (fairly arbitrary choice) +sql = "SELECT DISTINCT date from secbugs_Details WHERE date > '2009-09-01' ORDER BY date;" +c.execute(sql) +rows = c.fetchall() +for row in rows: + date = row["date"].strftime("%Y-%m-%d") + for team in TEAMS: + teamName = team[0] + # Create the empty list for each team. The list will hold + # (date, riskScore) tuples + if teamName not in teamStats.keys(): + teamStats[teamName] = [] + sql2 = "SELECT secbugs_Stats.category, secbugs_Details.date, SUM(secbugs_Details.count) AS total FROM secbugs_Details INNER JOIN secbugs_Stats ON secbugs_Details.sid=secbugs_Stats.sid WHERE secbugs_Details.date LIKE '%s%%' AND secbugs_Stats.category IN ('sg_critical','sg_high','sg_moderate','sg_low') AND (%s) GROUP BY date, category;" % (date, team[1]) + # if DEBUG: print sql2 + # | category | date | total | + # | sg_critical | 2011-08-28 12:00:00 | 3 | + # | sg_high | 2011-08-28 12:00:00 | 6 | + # ... + c.execute(sql2) + sevCounts = c.fetchall() + # Calculate the risk index for this date/team combo + riskIndex = 0 + for sev in sevCounts: + riskIndex += weights[sev["category"]] * sev["total"] + teamStats[teamName].append( (date, riskIndex) ) + +# Sort list of team stats by most recent risk index +statList = sorted(teamStats.items(), key = lambda k: k[1][-1][1]) +# [('Frontend', [('2011-08-07', Decimal('110')), ..., ('2011-08-28', Decimal('102'))]), +# ('DOM', [('2011-08-07', Decimal('115')), ..., ('2011-08-28', Decimal('127'))])] + +# # just create some random data +# fnx = lambda : NP.random.randint(3, 10, 10) +# x = NP.arange(0, 10) +# # [0 1 2 3 4 5 6 7 8 9] +# y1 = fnx() +# # [7 5 7 7 4 3 5 8 7 3] +# y2 = fnx() +# y3 = fnx() + +x = [datetime.datetime.strptime(s[0], "%Y-%m-%d") for s in statList[0][1]] +# x = NP.arange(len(statList[0][1])) + +series = tuple([[s[1] for s in stat[1]] for stat in + [team for team in statList]]) +# ([0, 4, 4, 4], [6, 6, 6, 6], [22, 22, 17, 12], [13, 17, 17, 17], +# [28, 28, 29, 24], [24, 29, 29, 29], [30, 29, 29, 30], [45, 49, 49, +# 49], [32, 42, 52, 63], [110, 110, 107, 102], [115, 123, 123, 127]) + + +# y_data = NP.row_stack((y1, y2, y3)) +# [[7 5 7 7 4 3 5 8 7 3] +# [6 5 5 5 9 3 8 9 5 8] +# [3 7 5 4 7 7 3 6 6 4]] +y_data = NP.row_stack(series) + +# this call to 'cumsum' (cumulative sum), passing in your y data, +# is necessary to avoid having to manually order the datasets +y_data_stacked = NP.cumsum(y_data, axis=0) +# [[0 4 4 4] +# [6 10 10 10] +# [28 32 27 22] +# [41 49 44 39] +# [69 77 73 63] +# [93 106 102 92] +# [123 135 131 122] +# [168 184 180 171] +# [200 226 232 234] +# [310 336 339 336] +# [425 459 462 463]] + +fig = PLT.figure() +ax1 = fig.add_subplot(111) +# set y-axis to start at 0 +PLT.ylim(ymin = 0) + +colors = ["#ffe84c", "#7633bd", "#3d853d", "#a23c3c", "#8cacc6", + "#bd9b33", "#9440ed", "#4da74d", "#cb4b4b", "#afd8f8", + "#edc240"] + +# first one manually? okay... +ax1.fill_between(x, 0, y_data_stacked[0,:], facecolor = colors[0]) +# hack for the legend (doesn't work with fill_between) +# http://www.mail-archive.com/matplotlib-users@lists.sourceforge.net/msg10893.html +rects = [] +rects.insert(0, Rectangle((0, 0), 1, 1, color = colors[0])) +labels = [] +labels.insert(0, statList[0][0]) + +# fill in the rest +for i in range(1, len(y_data_stacked)): + ax1.fill_between(x, y_data_stacked[i-1,:], y_data_stacked[i,:], + facecolor = colors[i]) + # legend hack: add the Rectangle patch to the plot + rects.insert(0, Rectangle((0, 0), 1, 1, color = colors[i])) + labels.insert(0, statList[i][0]) + +# reduce the number of ticks on the bottom axis to improve readability +fig.autofmt_xdate(bottom = 0.2, rotation = 45, ha = "right") +ax1.set_ylim(ymin = 0) + +# reduce width by 10% +box = ax1.get_position() +ax1.set_position([box.x0, box.y0, box.width * 0.9, box.height]) +# shrink the font size +fontP = FontProperties() +fontP.set_size("x-small") +ax1.legend(rects, labels, loc='center left', title = "Teams", + bbox_to_anchor = (1, 0.5), fancybox = True, shadow = False, + prop = fontP) + +# PLT.title("Risk Index: " + x[-1].strftime("%Y-%m-%d")) + +# save the image on the filesystem +filename = "teamgraph-%s.png" % time.strftime('%Y%m%d', time.localtime()) +fig.savefig("%s/%s" % (JSONLOCATION, filename)) |