def progress_VT_by_profile():
base_date = "2020-09-22"
header, lst = query_to_list(
"select date from stats order by 1 DESC limit 1")
last_date = lst[0][0]
cmd = f"""
SELECT milestone, profile_name, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts",
sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 0) as "Fixed %"
FROM (
SELECT a.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
a.total_vt BT,
b.total_vt LT, (a.total_vt - b.total_vt) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY milestone, profile_name
HAVING "# Fixed" != 0
UNION
SELECT "Total", "",sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 0) as "Fixed %"
FROM (
SELECT b.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
a.total_vt BT,
b.total_vt LT, (a.total_vt - b.total_vt) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)"""
# print (cmd)
return query_to_list(cmd) # return header, rows
def check_new_assets(scan_data): # not used anymore
conn, cursor = open_db()
for row in scan_data:
ip = row[IP_ADDRESS_COL]
cmd = f"SELECT profile_name FROM assets WHERE ipaddress = '{ip}'"
header, lst = query_to_list(cmd)
if len(lst)== 0: # IP not found, add it as is until manual fix, add "(NEW)"
print(f"Newly added server: {ip}, tmp profile:{row[0]}")
new_servers = True
profile_name = row[0]
profile_list.append(profile_name)
rec = [ip, profile_name, row[ASSET_NAME_COL], "?", "?", ""]
try:
insert_row_list(conn, cursor, "assets", rec)
conn.commit()
except:
print (f"check_new_assets: record already exist")
continue # record already exist frrom previous scan row
else: # found, relace temp scan profile name with asset profile_name
profile_list.append(lst[0][0])
close_db(cursor)
if new_servers:
messagebox.showinfo("Warning", "Newly added assets to assets name '(NEW)', please arrange to adjust")
return profile_list
def add_new_assets():
cmd = """
SELECT DISTINCT vul.ipaddress
FROM vulnerability vul LEFT JOIN assets ast USING (ipaddress)
GROUP by vul.name
HAVING ast.ipaddress is NULL"""
new_profile_list = query_to_list(cmd, False)
# print (len(new_profile_count))
if len(new_profile_list) == 0:
return False
cmd = """
INSERT INTO assets
SELECT *
FROM
(
SELECT DISTINCT Null, vul.ipaddress, vul.name, vul.assetName, "?","?","?","?",NULL
FROM vulnerability vul LEFT JOIN assets ast USING (ipaddress)
GROUP by vul.name
HAVING ast.ipaddress is NULL
)a"""
exec_db_cmd(cmd)
return True
return True
def print_query(cmd,
output,
title,
max_len=30,
export=False,
confirmation=True):
# print ("type of cmd: ", type(cmd), export)
if type(cmd) == str:
header, rows = query_to_list(cmd)
else:
header, rows = cmd() # a function that returns header & rows
# print ("return back from cmd,", len(rows), "\n", rows)
if not export:
if max_len: # wrap long fields at 30 chars
new_rows = []
for i, r in enumerate(rows):
w_row = []
for j, c in enumerate(r):
if len(str(c)) > max_len:
w_row.append(wrap(c, max_len))
else:
w_row.append(c)
new_rows.append(w_row)
if i > MAX_DISPLAY_LINES: # display max of 100 lines
break
table = tabulate(new_rows, headers=header, showindex="always")
else:
table = tabulate(rows, headers=header, showindex="always")
output.delete(0.0, END) # clear window
output.insert(END, title + '\n\n')
for line in table:
output.insert(END, line)
else: # export to excel
if not os.path.exists(OUT_FOLDER):
messagebox.showinfo("Error",
f"Folder does not exist: {OUT_FOLDER}")
return -1
wb = Workbook()
ws = wb.active
ws.title = title
ws.append(header)
for row in rows:
ws.append(row)
# ws['G2'].style = 'Percent'
file_name = os.path.join(OUT_FOLDER, title + ".xlsx")
try:
wb.save(os.path.join(file_name))
except:
print("error saving to excel file: ", file_name)
if confirmation:
messagebox.showinfo("Done!", f"file exported {file_name}! ")
def replace_temp_profile_name(scan_data):
# replace temp profile_name with the one from assets
for row in scan_data:
cmd = f"SELECT profile_name FROM assets WHERE ipaddress = '{row[IP_ADDRESS_COL]}'"
# print (cmd)
profile_name = query_to_list(cmd, return_header=False)
if len(profile_name) == 1: # if not found, keep old
row[0] = profile_name[0][0]
return scan_data
def progress_by_milestone(export=None):
# print ("export: ", export)
if export:
if not os.path.exists(OUT_FOLDER):
messagebox.showinfo("Error",
f"Folder does not exist: {OUT_FOLDER}")
return -1
base_date = "2020-09-22"
header, lst = query_to_list(
"select date from stats order by 1 DESC limit 1")
last_date = lst[0][0]
cmd = f"""
SELECT milestone, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT b.milestone, a.numHosts BH, b.numHosts LH,
(a.High + a.Medium) BT,
(b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY milestone
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT b.milestone, a.numHosts BH, b.numHosts LH,
(a.High + a.Medium) BT,
(b.High + b.Medium) LT, (a.High + a.Medium - b.High - b.Medium) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)"""
# if export:
# query_to_excel(cmd, os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))
# messagebox.showinfo("Export DOne", os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))
# else:
return query_to_list(cmd) # return header, rows
def vulner_CVEID_list():
cmd = """
SELECT ast.followup_group, name as 'profile name', vul.vulnerability, vul.cveid, vfc.fix_category
FROM vulnerability vul
LEFT JOIN assets ast using (ipaddress)
LEFT JOIN vul_fix_category vfc USING (vulnerability, followup_group)
WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) = 0
GROUP by vul.vulnerability,vfc.fix_category, followup_group
order by 1"""
return query_to_list(cmd) # return header, rows
def progress_VT_by_group(export=None): #stats by VT Vulnetability Type
if export:
if not os.path.exists(OUT_FOLDER):
messagebox.showinfo("Error",
f"Folder does not exist: {OUT_FOLDER}")
return -1
base_date = "2020-09-22"
header, lst = query_to_list(
"select date from stats order by 1 DESC limit 1")
last_date = lst[0][0]
cmd = f"""
SELECT profile_group, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts",
sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT a.profile_group, a.numHosts BH, b.numHosts LH,
a.total_vt BT,
b.total_vt LT, (a.total_vt - b.total_vt) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY profile_group
UNION
SELECT "Total", sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner",
(sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT a.profile_group, a.numHosts BH, b.numHosts LH,
a.total_vt BT,
b.total_vt LT, (a.total_vt - b.total_vt) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)"""
return query_to_list(cmd) # return header, rows
def progress_by_profile():
base_date = "2020-09-22"
header, lst = query_to_list(
"select date from stats order by 1 DESC limit 1")
last_date = lst[0][0]
cmd = f"""
SELECT milestone, profile_name, sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts",
sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT a.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
a.total_HM BT,
b.total_HM LT, (a.total_HM - b.total_HM) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)
GROUP BY milestone, profile_name
HAVING "# Fixed" != 0
UNION
SELECT "Total", "",sum (BH) "Base # Hosts", sum(LH) "Last Scan # Hosts", sum (BT) "Base # Vulner", sum(LT) "Last Scan # Vulner", (sum (BT) - sum(LT)) as "# Fixed",
ROUND((sum (BT) - sum(LT))*1.0 /sum(BT)* 100.0, 1) as "Fixed %"
FROM (
SELECT b.milestone, a.profile_name, a.numHosts BH, b.numHosts LH,
a.total_HM BT,
b.total_HM LT, (a.total_HM - b.total_HM) as F
FROM stats b INNER join stats a on a.profile_group = b.profile_group AND a.milestone = b.milestone
AND a.profile_name = b.profile_name
AND (a.date = "{base_date}" AND b.date = "{last_date}")
)"""
# print (cmd)
# if export:
# query_to_excel(cmd, os.path.join(OUT_FOLDER, "Summary_progress by Milestone.xlsx"))
# else:
return query_to_list(cmd) # return header, rows
def progress_VT_by_followup_group():
cmd = """
SELECT followup_group, base_numHosts "Base # Hosts", current_numHosts "Last Scan # Hosts",
base_total_vt "Base # Vulner", current_total_vt "Last Scan # Vulner",
(base_total_vt - current_total_vt) as "# Fixed"
, ifnull(ROUND((base_total_vt - current_total_vt)*100.0 /base_total_vt,0), 0)as "Fixed %"
FROM stats_vt
UNION
SELECT "Total", sum (base_numHosts), sum (current_numHosts),
sum (base_total_vt) , sum(current_total_vt) ,
(sum (base_total_vt) - sum(current_total_vt)) ,
ROUND((sum (base_total_vt) - sum(current_total_vt))*100.0 /sum(base_total_vt), 0)
FROM stats_vt"""
return query_to_list(cmd) # return header, rows
def progress_by_followup_group():
cmd = """
SELECT followup_group, sum (numHosts) "# Hosts", sum (current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY followup_group
UNION
SELECT "Total", sum (numHosts), sum (current_numHosts) "Last Scan # Hosts",
sum (total_HM) , sum(current_total_HM) ,
(sum (total_HM) - sum(current_total_HM)) ,
ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1)
FROM stats"""
return query_to_list(cmd) # return header, rows
def progress_by_milestone():
cmd = f"""
SELECT milestone, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY milestone
UNION
SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1) as "Fixed %"
FROM stats"""
return query_to_list(cmd) # return header, rows
def progress_by_group_proj_scope():
cmd = """
SELECT milestone, profile_group, sum (numHosts) "# Hosts",
sum (total_HM) "Base # Vulner", sum(proj_scope_current_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(proj_scope_current_HM)) as "# Fixed",
ifnull(ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY milestone, profile_group
UNION
SELECT "Total", "", sum (numHosts),
sum (total_HM) , sum(proj_scope_current_HM) ,
(sum (total_HM) - sum(proj_scope_current_HM)) ,
ROUND((sum (total_HM) - sum(proj_scope_current_HM))*1.0 /sum(total_HM)* 100.0, 1)
FROM stats"""
return query_to_list(cmd) # return header, rows
def progress_VT_by_group(): #stats by VT Vulnetability Type
cmd = f"""
SELECT profile_group, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner",
(sum (total_vt) - sum(current_total_vt)) as "# Fixed",
ifnull(ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY profile_group
UNION
SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner",
(sum (total_vt) - sum(current_total_vt)) as "# Fixed",
ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1) as "Fixed %"
FROM stats"""
return query_to_list(cmd) # return header, rows
def progress_by_group(export=None):
cmd = f"""
SELECT profile_group "Profile Group", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ifnull(ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY profile_group
UNION
SELECT "Total", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 1) as "Fixed %"
FROM stats"""
return query_to_list(cmd) # return header, rows
def progress_VT_by_profile():
cmd = """
SELECT milestone, profile_name, sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner",
(sum (total_vt) - sum(current_total_vt)) as "# Fixed",
ifnull(ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1),0) as "Fixed %"
FROM stats
GROUP BY milestone, profile_name
HAVING "# Fixed" != 0
UNION
SELECT "Total", "", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_vt) "Base # Vulner", sum(current_total_vt) "Last Scan # Vulner",
(sum (total_vt) - sum(current_total_vt)) as "# Fixed",
ROUND((sum (total_vt) - sum(current_total_vt))*1.0 /sum(total_vt)* 100.0, 1) as "Fixed %"
FROM stats"""
# print (cmd)
return query_to_list(cmd) # return header, rows
def progress_by_profile():
cmd = f"""
SELECT row_number() OVER() "#", * FROM
(
SELECT profile_group "Profile Group", profile_name "Profile Name", numHosts "Base # Hosts",
current_numHosts "Last Scan # Hosts",
total_HM "Base # Vulner", current_total_HM "Last Scan # Vulner",
(total_HM - current_total_HM) as "# Fixed",
ifnull(ROUND((total_HM - current_total_HM)*1.0 /total_HM* 100.0, 0),0) as "Fixed %"
FROM stats
ORDER BY "Fixed %" DESC
)
UNION
SELECT "Total", "", "", sum (numHosts) "Base # Hosts", sum(current_numHosts) "Last Scan # Hosts",
sum (total_HM) "Base # Vulner", sum(current_total_HM) "Last Scan # Vulner",
(sum (total_HM) - sum(current_total_HM)) as "# Fixed",
ROUND((sum (total_HM) - sum(current_total_HM))*1.0 /sum(total_HM)* 100.0, 0) as "Fixed %"
FROM stats"""
return query_to_list(cmd) # return header, rows
def cveid_fix_category_stats():
cmd = """
SELECT followup_group, COUNT(vul.vulnerability) as "CVEID Count",
count(CASE WHEN fix_category = 'Irrelevant' THEN 1 END) as "Irrelevant",
count(CASE WHEN substr(fix_category, 1,22) = 'Applied latest package' THEN 1 END) as "Applied latest package",
count(CASE WHEN substr(fix_category, 1,17) = 'Application Issue' THEN 1 END) as 'Application Issue',
count(CASE WHEN fix_category = 'Oracle DBA' THEN 1 END) as 'Oracle DBA',
count(CASE WHEN substr(fix_category, 1,5) = 'Fixed' THEN 1 END) as 'Fixed, rescan needed',
count(CASE WHEN substr(fix_category, 1,10) = 'No Package' THEN 1 END) as 'No Package to Fix',
count(CASE WHEN substr(fix_category, 1,14) = 'False Positive' THEN 1 END) as 'False Positive',
count(CASE WHEN ifnull(fix_category,0) = 0 THEN 1 END) as 'unclassified'
FROM(
SELECT DISTINCT followup_group, vulnerability
FROM vulnerability vul
LEFT JOIN assets ast using (ipaddress)
WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) = 0
GROUP by followup_group, vulnerability
) vul
LEFT JOIN vul_fix_category vfc USING (vulnerability, followup_group)
GROUP BY followup_group
order by 1
"""
return query_to_list(cmd) # return header, rows
def list_excluded_servers():
cmd = """
SELECT ipaddress, assetName, profile_name, location, followup_group, Role
FROM assets
WHERE ifnull(exclude,0) = 1"""
return query_to_list(cmd) # return header, rows
