#!/usr/bin/python # Email a summary report of the bug statistics which also embeds the PNG chart # generated by teamgraph.py import sys, os, smtplib, time, MySQLdb, operator from datetime import date, timedelta from time import strftime from string import join, split from email.MIMEMultipart import MIMEMultipart from email.MIMEText import MIMEText from email.MIMEImage import MIMEImage from settings import * # 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 "sendstats.py: can't connect to database\n" sys.exit() # return the most recent date from our data set def getThisWeekDate(slashed=False): sql = "select distinct date from secbugs_Details order by date desc limit 1;" c.execute(sql) row = c.fetchone() if slashed: return row["date"].strftime("%m/%d/%y") else: return row["date"].strftime("%Y-%m-%d") # return the second-most recent date from our data set def getLastWeekDate(slashed=False): sql = "select distinct date from secbugs_Details order by date desc limit 1,1;" c.execute(sql) row = c.fetchone() if slashed: return row["date"].strftime("%m/%d/%y") else: return row["date"].strftime("%Y-%m-%d") # format a number so positive values have a leading "+" def getDelta(n): if n > 0: return "+" + str(n) else: return n # given two lists of bug numbers, return the set that went away in the latter 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 # given two lists of bug numbers, return the set that showed up in the latter 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 # simple object to store and sort data from multiple queries class DataRow(): def __init__(self): self.numCritical = 0 self.critList = "" self.numHigh = 0 self.highList = "" self.numModerate = 0 self.modList = "" self.numLow = 0 self.lowList = "" self.total = 0 self.product = "" self.component = "" def dump(self): s = "Critical: "+str(self.numCritical)+", " s += "High: "+str(self.numHigh)+", " s += "Moderate: "+str(self.numModerate)+", " s += "Low: "+str(self.numLow)+", " return s # start building email body body = "\n\n\n\n\n" body += "\n

Hello,

Here are some statistics regarding Mozilla Client Security Bugs for the week of %s:

Open Bugs By Severity

\n\n\n" % (getThisWeekDate(True)) # keep track of totals for counts curTotal = 0 lastTotal = 0 totCleared = [] totAdded = [] i = 0 # bug counts for cat in [("sg_critical", "Critical"), ("sg_high", "High"), ("sg_moderate", "Moderate"), ("sg_low", "Low")]: # get the stats from this week sql = "select d.count, d.bug_list from secbugs_Details d, secbugs_Stats s where d.sid=s.sid and s.category='%s' and d.date like '%s%%';" % (cat[0], getThisWeekDate()) c.execute(sql) thisWkCount = 0 thisWkList = "" row = c.fetchone() while row != None: thisWkCount += row["count"] thisWkList += row["bug_list"] if not len(thisWkList) else ","+row["bug_list"] row = c.fetchone() # get the stats from last week sql = "select d.count, d.bug_list from secbugs_Details d, secbugs_Stats s where d.sid=s.sid and s.category='%s' and d.date like '%s%%';" % (cat[0], getLastWeekDate()) c.execute(sql) lastWkCount = 0 lastWkList = "" row = c.fetchone() while row != None: lastWkCount += row["count"] lastWkList += row["bug_list"] if not len(lastWkList) else ","+row["bug_list"] row = c.fetchone() # find out which bugs were cleared and which were added in the past week cleared = getCleared(thisWkList, lastWkList) added = getAdded(thisWkList, lastWkList) if i%2 == 0: body += "\n" else: body += "\n" # current count if thisWkCount > 0: curLink = "%s" % (thisWkList, thisWkCount) else: curLink = "0" # previous count if lastWkCount > 0: prevLink = "%s" % (lastWkList, lastWkCount) else: prevLink = "0" body += " \n \n \n \n \n\n" % (join(added,","), len(added)) else: body += "Added 0)\n\n" curTotal += thisWkCount lastTotal += lastWkCount totCleared += cleared totAdded += added i += 1 # alternate row color body += "\n \n \n \n \n \n\n" % (join(totAdded,","), len(totAdded)) else: body += "Added 0)\n" body += "

Category	Current	Last	Delta
%s	%s	%s	%s	" % (cat[1], curLink, prevLink, getDelta(thisWkCount-lastWkCount)) # links to lists of cleared and added bugs if len(cleared) > 0: body += "(Cleared %s, " % (join(cleared,","), len(cleared)) else: body += "(Cleared 0, " if len(added) > 0: body += "Added %s)
	%s	%s	%s	" % (curTotal, lastTotal, getDelta(curTotal-lastTotal)) if len(totCleared) > 0: body += "(Cleared %s, " % (join(totCleared,","), len(totCleared)) else: body += "(Cleared 0, " if len(totAdded) > 0: body += "Added %s)

\n" # show top teams with security bugs i = 0 curDate = getThisWeekDate() # 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} # Figure out how many open bugs (and cummulative risk index) each team has for team in TEAMS: teamName = team[0] sql = "SELECT secbugs_Stats.category, secbugs_Details.date, GROUP_CONCAT(secbugs_Details.bug_list) AS bugs 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 category;" % (curDate, team[1]) c.execute(sql) rows = c.fetchall() # print "** %s **" % team[0] # Keep track of the list of each category of bugs for each team and the # weighted risk index teamStats[teamName] = {"sg_critical": "", "num_sg_critical": 0, "sg_high": "", "num_sg_high": 0, "sg_moderate": "", "num_sg_moderate": 0, "sg_low": "", "num_sg_low": 0, "score": 0} for row in rows: # print "%s: %d" % (row["category"], len(row["bugs"].split(","))) # Store the list of bugs teamStats[teamName][row["category"]] = row["bugs"] count = len(row["bugs"].split(",")) if len(row["bugs"]) else 0 teamStats[teamName]["num_" + row["category"]] += count # Add points to score - critical: 5, high: 4, moderate: 2, low: 1 points = weights[row["category"]] * count teamStats[teamName]["score"] += points # calculate some totals totalRisk = sum([t[1]["score"] for t in teamStats.items()]) totalCritical = sum([t[1]["num_sg_critical"] for t in teamStats.items()]) totalHigh = sum([t[1]["num_sg_high"] for t in teamStats.items()]) totalModerate = sum([t[1]["num_sg_moderate"] for t in teamStats.items()]) totalLow = sum([t[1]["num_sg_low"] for t in teamStats.items()]) # Sort the list of teams by their risk index sortedTeams = sorted(teamStats.items(), key = lambda k: k[1]["score"], reverse = True) # "Risk Index By Team" table body += "

Risk Index By Team

\n\n\n" i = 0 # Add a table row for each team for s in sortedTeams: # ('GFX', {'score': 13, 'sg_critical': '', # 'sg_low': '563740,566209,455573,322708,351800,103454,563838,304123', # 'sg_moderate': '', 'sg_high': '575294'}) if i%2 == 0: body += "\n" else: body += "\n" # rank, team, score body += " " % \ (i+1, s[0], s[1]["score"]) # critical numCritical = len(s[1]["sg_critical"].split(",")) if len(s[1]["sg_critical"]) else 0 if numCritical > 0: body += "" % (s[1]["sg_critical"], numCritical) else: body += "" # high numHigh = len(s[1]["sg_high"].split(",")) if len(s[1]["sg_high"]) else 0 if numHigh > 0: body += "" % (s[1]["sg_high"], numHigh) else: body += "" # moderate numModerate = len(s[1]["sg_moderate"].split(",")) if len(s[1]["sg_moderate"]) else 0 if numModerate > 0: body += "" % (s[1]["sg_moderate"], numModerate) else: body += "" # low numLow = len(s[1]["sg_low"].split(",")) if len(s[1]["sg_low"]) else 0 if numLow > 0: body += "" % (s[1]["sg_low"], numLow) else: body += "\n" i += 1 # add totals to bottom if i%2 == 0: body += "\n" else: body += "\n" body += "\n" % (totalRisk, totalCritical, totalHigh, totalModerate, totalLow) # finish up Team rank table and scoring rubric body += "

Rank	Team	Risk Index	Critical	High	Moderate	Low
%d	%s	%d	%s	0	%s	0	%s	0	%s	0
		%d	%d	%d	%d	%d

\n
\n" body += """

Scoring	Severity	Critical	High	Moderate	Low
Scoring	Weight	5	4	2	1

""" # embed chart of historic risk index body += "

Historic Risk Index by Team

\n" # finish up the email message body += "\n" # set up multipart email message msgRoot = MIMEMultipart("related") msgRoot["Subject"] = "Weekly Client Security Bug secbugs_Stats Report" msgRoot["From"] = EMAIL_FROM msgRoot["To"] = ",".join(EMAIL_TO) msgRoot.preamble = "This is a multi-part message in MIME format." # Plaintext body msgAlternative = MIMEMultipart("alternative") msgRoot.attach(msgAlternative) msgText = MIMEText("Plain text alternative not supported.") msgAlternative.attach(msgText) # HTML body msgText = MIMEText(body, "html") msgAlternative.attach(msgText) # image attachment files = os.popen("find %s -name '*.png'" % JSONLOCATION) img = files.readline().strip() if img: fp = open(img, "rb") msgImage = MIMEImage(fp.read()) fp.close() msgImage.add_header("Content-ID", "") msgRoot.attach(msgImage) print msgRoot.as_string()