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
