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)