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
