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