diff options
Diffstat (limited to 'contrib/secbugstats/bin/morestats.py')
-rwxr-xr-x | contrib/secbugstats/bin/morestats.py | 106 |
1 files changed, 106 insertions, 0 deletions
diff --git a/contrib/secbugstats/bin/morestats.py b/contrib/secbugstats/bin/morestats.py new file mode 100755 index 000000000..a79f68896 --- /dev/null +++ b/contrib/secbugstats/bin/morestats.py @@ -0,0 +1,106 @@ +#!/usr/bin/python +# Process a JSON file yielded by curlbug.py to find out which specific bugs +# are open in each category, what product/component they are open in, and how +# old the various bugs are. +import simplejson, sys, MySQLdb, re +from time import mktime, localtime, strptime +from settings import * + +def median(l): + l.sort() + if len(l) < 1: return 0 + elif len(l)%2 == 1: + return l[len(l)/2] + # even number of elements -> return avg of middle two + else: + return float((l[len(l)/2]+l[(len(l)/2)-1])/2) + +def average(l): + l.sort() + return float(sum(l)/len(l)) + +try: + INPUT_FILE = sys.argv[1] +except: + print "Usage: "+sys.argv[0]+" <input_file>" + sys.exit() + +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() + +# foreign key to relate these details to the higher-level stat bucket +# enforce filename format, e.g. 200808241200_sg_critical.json +FILE_NAME = INPUT_FILE.split("/")[-1] +p = re.compile("[0-9]{12}[a-z_]+\.json") +if not re.match(p, FILE_NAME): + print sys.argv[0]+": Unexpected filename format: %s. Exiting." % (FILE_NAME) + sys.exit() + +category = FILE_NAME[13:-5] +date = "%s-%s-%s %s:%s:00" % (FILE_NAME[0:4],FILE_NAME[4:6],FILE_NAME[6:8], + FILE_NAME[8:10],FILE_NAME[10:12]) + +# bail out if we weren't able to determine which stat bucket to associate with +sql = "SELECT sid FROM secbugs_Stats WHERE category='%s' AND date='%s';" % (category,date) +if DEBUG: print sql +c.execute(sql) +row = c.fetchone() +if not row: + print "%s: unable to determine Stat group for category: %s, date: %s. Exiting." % \ + (sys.argv[0], category, date) + sys.exit() +SID = row[0] + +# storage for our stat details, we'll key on (product, component) +details = {} + +json = open(INPUT_FILE, "rb").read() +buglist = simplejson.loads(json) +for bug in buglist["bugs"]: + # reset field values + product = component = bug_id = bug_age = "" + # gather field values from the JSON object + product = bug["product"] + component = bug["component"] + bug_id = bug["id"] + # bug age in days + try: + bug_age = (mktime(localtime())-mktime(strptime(bug["creation_time"], "%Y-%m-%dT%H:%M:%SZ")))/(60*60*24) + except Exception, e: + print "Exception trying to get bug age:", e + bug_age = 0 + # bail if we don't have other values set + if not product or not component or not bug_id: continue + # DEBUG + if DEBUG: + print "Processing bug_id: %s, bug_age: %s" % (bug_id, bug_age) + # create a new list of bugs based on (product, component) and also + # keep a list of bug ages to generate mean and median age + if (product, component) not in details.keys(): + details[(product, component)] = [[bug_id], [bug_age]] + # add to the existing list of bugs and total age of those bugs + else: + details[(product, component)][0].append(bug_id) + details[(product, component)][1].append(bug_age) + # if DEBUG: print " bug ages for group: %s" % (details[(product, component)][1]) + +# store the details we gathered +for pc in details.keys(): + # print pc, len(details[pc]), details[pc] + # see if we are inserting new details or if we are updating existing details + sql = "SELECT did FROM secbugs_Details where product='%s' AND component='%s' AND sid=%s AND date='%s';" % (pc[0], pc[1], SID, date) + c.execute(sql) + row = c.fetchone() + # update row + if row: + sql = "UPDATE secbugs_Details SET sid=%s, product='%s', component='%s', count=%s, bug_list='%s', date='%s', avg_age_days=%s, med_age_days=%s WHERE did=%s;" % (SID, pc[0], pc[1], len(details[pc][0]), ",".join([str(i) for i in details[pc][0]]), date, int(round(average(details[pc][1]))), int(round(median(details[pc][1]))), row[0]) + # insert new row + else: + sql = "INSERT INTO secbugs_Details(sid, product, component, count, bug_list, date, avg_age_days, med_age_days) VALUES(%s, '%s', '%s', %s, '%s', '%s', %s, %s);" % (SID, pc[0], pc[1], len(details[pc][0]), ",".join([str(i) for i in details[pc][0]]), date, int(round(average(details[pc][1]))), int(round(median(details[pc][1])))) + if DEBUG: print sql + else: c.execute(sql) |