diff options
Diffstat (limited to 'contrib/secbugstats/bin/teamstats.py')
-rwxr-xr-x | contrib/secbugstats/bin/teamstats.py | 69 |
1 files changed, 69 insertions, 0 deletions
diff --git a/contrib/secbugstats/bin/teamstats.py b/contrib/secbugstats/bin/teamstats.py new file mode 100755 index 000000000..da5c20d13 --- /dev/null +++ b/contrib/secbugstats/bin/teamstats.py @@ -0,0 +1,69 @@ +#!/usr/bin/python +# aggregate trend data for Eng. teams performance on fixing security bugs +import sys, MySQLdb +from string import join, capitalize, split +from time import mktime +from settings import * + +if "--debug" in sys.argv: DEBUG = True +else: DEBUG = False + +# set up database connection +db = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME) +db.autocommit(True) +c = db.cursor() + +def getCleared(curBugs, lastBugs): + cur = split(curBugs, ",") + last = split(lastBugs, ",") + cleared = [] + for bug in last: + if len(bug) and bug not in cur: cleared.append(bug) + return cleared + +def getAdded(curBugs, lastBugs): + cur = split(curBugs, ",") + last = split(lastBugs, ",") + added = [] + for bug in cur: + if len(bug) and bug not in last: added.append(bug) + return added + +def cleanCat(cat): + return cat[3:].capitalize() + +# return a UNIX timestamp +def formatDate(d): + return mktime(d.timetuple()) + + +severities = ["sg_critical", "sg_high", "sg_moderate", "sg_low"] + +# print a list of JSON objects +print "{" + +for sev in severities: + print " '%s': [" % (cleanCat(sev)) + for t in TEAMS: + # keep track of how many bugs were opened/closed from the last period + last = { "Critical": "", "High": "", "Moderate": "", "Low": "" } + + # only select from one severity at a time + sql = "SELECT secbugs_Details.date, sum(secbugs_Details.count), GROUP_CONCAT(secbugs_Details.bug_list), secbugs_Stats.category, round(sum(secbugs_Details.avg_age_days*secbugs_Details.count)/(sum(secbugs_Details.count))) as avg_age_days FROM secbugs_Details INNER JOIN secbugs_Stats ON secbugs_Details.sid=secbugs_Stats.sid WHERE (%s) AND secbugs_Stats.category='%s' GROUP BY date, category ORDER BY date, category;" % (t[1], sev) + if DEBUG: print "sql:", sql + c.execute(sql) + row = c.fetchone() + while row is not None: + # how many bugs are new from last period + new = getAdded(row[2], last[cleanCat(row[3])]) + if DEBUG: print "new:", new + # how many bugs are closed since last period + cleared = getCleared(row[2], last[cleanCat(row[3])]) + if DEBUG: print "cleared:", cleared + # emit data for this (team, date, category) + print " { 'date': '%s', 'timestamp': %s, 'team': '%s', 'count': %s, 'new': %s, 'cleared': %s, 'avg_age': %s }," % (str(row[0]), formatDate(row[0]), t[0], row[1], len(new), len(cleared), row[4]) + # remember old values + last[cleanCat(row[3])] = row[2] + row = c.fetchone() + print " ]," +print "}" |