1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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 "}"
|
