def getGroupVulns(cur, team, colorVulnerable=False): c=getGroups(team) glist=[] for x in c: glist.append(x.getDict()) if colorVulnerable: for x in glist: for comp in x["components"]: vuln=cvedb.isVulnerable(comp["cpe"]) if vuln: x["vulnerable"]=True comp["vulnerable"]=vuln return sorted(glist, key=lambda s: s["groupName"].lower())
def getTicket(cur, cve, cpe, system, team): cur.execute("""SELECT Tickets.id, Teams.name, System_Groups.name, Tickets.cve, Components.name, Ticket_Statusses.name, Ticket_Histories.ticket_notes, Ticket_Histories.ticket_resolution_id, Ticket_Histories.ticket_resolution_fix, Ticket_Histories.cvss, Ticket_Histories.ticket_priority_id FROM Tickets, System_Groups, Components, Teams, Ticket_Histories, Ticket_Resolutions, Ticket_Statusses WHERE cve=%s AND component_id IN ( SELECT id FROM Components WHERE name = %s ) AND system_group_id IN ( SELECT id FROM System_Groups WHERE name = %s AND team_id IN ( SELECT id FROM Teams WHERE name = %s )) AND Teams.id = System_Groups.team_id AND System_Groups.id = Tickets.system_group_id AND Components.id = Tickets.component_id AND Ticket_Histories.id = Tickets.history_last_id AND Ticket_Statusses.id = Ticket_Histories.ticket_status_id;""", (cve, cpe, system, team)) data=cur.fetchone() c=list(data) if data else data if c: if c[10]: cur.execute("SELECT name FROM Ticket_Priorities where id=%s", (c[10],)) priority=(cur.fetchone())[0] else: priority=None cur.execute("SELECT name FROM Ticket_Resolutions") t_res= [x[0] for x in cur.fetchall()] cur.execute("SELECT name FROM Ticket_Priorities") t_prior= [x[0] for x in cur.fetchall()] cur.execute("SELECT name FROM Ticket_Urgencies") t_urgent= [x[0] for x in cur.fetchall()] cur.execute("""SELECT datetime, Users.user_name, cvss, ticket_notes, Ticket_Statusses.name, ticket_resolution_id, ticket_resolution_fix, ticket_priority_id, ticket_urgency_id FROM Ticket_Histories, Ticket_Statusses, Users WHERE ticket_id=%s AND Ticket_Statusses.id = Ticket_Histories.ticket_status_id AND Users.id = Ticket_Histories.user_id;""", (c[0],)) histories=cur.fetchall() h=[] for x in histories: h.append({"time": x[0], "user": x[1], "cvss": x[2], "notes": x[3], "status": x[4], "update": {"type": xFromy(x[5], t_res), "cpe": x[6]}, "priority": xFromy(x[7], t_prior), "urgency": xFromy(x[8], t_urgent)}) if c[7]: cur.execute("""SELECT name FROM Ticket_Resolutions WHERE id = %s""", (c[7],)) c[7]=cur.fetchone()[0] c=Ticket(c[1], c[2], c[3], c[4], status=c[5], notes=c[6], history=h, resolution=c[7], resolution_fix=c[8], cvss=c[9], priority=priority, objID=c[0]) else: tick=Ticket(team, system, cve, cpe) addTicket(tick) c=getTicket(tick.cve, tick.vulnComp["cpe"], tick.groupName, tick.team) c.addInfo(cvedb.getCVEInfo(cve)) updatePriority(c) return c
def addTicket(cur, _ticket): if type(_ticket) is not Ticket: raise(InvalidVariableTypes) t=_ticket.getDict() try: cur.execute("""SELECT id FROM System_Groups WHERE name = %s AND team_id IN (SELECT id FROM Teams WHERE name = %s);""",(t["groupName"], t["team"])) sgid=cur.fetchone()[0] cur.execute("""SELECT id FROM Components WHERE name = %s;""",(t["vulnComp"]["cpe"],)) cid=cur.fetchone()[0] cur.execute("""SELECT id FROM Teams WHERE name = %s;""",(t["team"],)) tid=cur.fetchone()[0] priority = calculatePriority(t) info = cvedb.getCVEInfo(t["cve"]) cur.execute("""INSERT INTO Tickets VALUES (DEFAULT, %s, %s, %s, %s, %s, %s) RETURNING ID;""",(None, None, t["cve"], sgid, cid, tid)) ticket_id=cur.fetchone()[0] cur.execute("""INSERT INTO Ticket_Histories(ticket_id, datetime, cvss, ticket_status_id, ticket_priority_id) VALUES(%s, %s, %s, %s, %s) RETURNING ID;""", (ticket_id, datetime.fromtimestamp(time.time()), info["cvss"], _DEFAULT_STATUS, priority)) history=cur.fetchone()[0] cur.execute("""UPDATE Tickets SET history_created_id=%s, history_last_id=%s WHERE id=%s"""%(ticket_id, ticket_id, history)) vmdb.commit() return ticket_id except Exception as e: print(traceback.format_exc()) return None
def getStatistics(cur, team): today=date.today() start=date(today.year, today.month,1) if today.month < 12: end=date(today.year, today.month+1,1) else: end=date(today.year+1, 1, 1) systems=[] for x in getGroupVulns(team): new=0 closed=0 oac=0 tickets=[] for cpe in [y["cpe"] for y in x["components"]]: tickets.extend([getTicket(cve, cpe, x["groupName"], team) for cve in [x["id"] for x in cvedb.getVulns([cpe], 0)]]) for ticket in tickets: opened=False for action in ticket.history: aDate=date(action["time"].year, action["time"].month, action["time"].day) if "status" in action and aDate >= start and aDate < end: if action["status"] == _DEFAULT_STATUS_TEXT: new+=1 opened=True elif action["status"].lower().startswith("closed"): if opened: oac+=1 new-=1 else: closed+=1 systems.append({"systems":x["groupName"], "new": new, "closed": closed, "openedAndClosed":oac}) return systems
def getLastTeamVulns(cur, team, limit): return cvedb.getVulns(getCPENames(team), limit)
def getVulnsForSystem(cur, groupName, systemTeam, cpes=None): if type(cpes) is not list and cpes:cpes=[cpes] if not cpes: cur.execute("""SELECT name FROM Components WHERE id IN ( SELECT component_id FROM Components_in_System_Group WHERE system_group_id IN ( SELECT id FROM System_Groups WHERE name = %s AND team_id IN ( SELECT id FROM Teams WHERE name = %s )));""",(groupName, systemTeam)) cpes=[x[0] for x in cur.fetchall()] # Get tickets for every cve for the vulnerable systems tickets=[] for cpe in cpes: tickets.extend([getTicket(cve, cpe, groupName, systemTeam) for cve in [x["id"] for x in cvedb.getVulns([cpe], 0)]]) tickets=[x for x in tickets if not x.status.lower().startswith("closed")] return tickets