def load_increment_scan_result(file_path, output):
output.insert(END, 'Loadind the files:' + file_path + ' .....\n')
if type(file_path) == str: # is folder?
scan_data = load_scan_dir_csv(file_path, output)
else:
scan_data = load_scan_files(file_path, output)
scan_data = replace_temp_profile_name(scan_data)
ips=[r[IP_ADDRESS_COL] for r in scan_data]
output.insert(END," ------- del_old_vulners ---------------\n")
ip_list = '"' + '","'.join(ips) + '"'
cmd = f"DELETE FROM vulnerability WHERE ipaddress in ({ip_list})"
exec_db_cmd(cmd)
output.insert(END," ---- Inset new scan records \n")
conn, cursor = open_db()
for row in scan_data:
insert_row_vulner(conn, cursor, row)
conn.commit()
close_db(cursor)
# lst = check_new_assets(scan_data) # if new assets in this scan, add to assets table
output.insert(END," Add new stats\n")
if add_new_assets(): # there are new assets. stop here and continue after fixing new assets's data
messagebox.showinfo("Warning", "Newly added assets to assets with profile_group '?', please arrange to adjust and then run menu 'Update Stats'" )
return
# add_new_profile_stats() # append stats with newly added profiles
output.insert(END," Update Stats \n")
update_stats(output) # update 'current_' fields with the new stats
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 insert_db_rec(conn, cursor, rec, table):
if table_exists(table, cursor) == 0: # new table
cmd = f"CREATE TABLE IF NOT EXISTS {table} ( {' TEXT,'.join(rec.keys())} )"
# print (cmd)
exec_db_cmd(cmd)
else:
check_col_names(rec, table, conn, cursor)
insert_row_dict(conn, cursor, table, rec)
def load_ACH_folder():
# clear_tk()
data_folder = filedialog.askdirectory(initialdir=".",
title="select data folder",
mustexist=True)
if not data_folder:
return # no files selected
# data_folder = r"C:\Yahia\Python\src\HDB\training\xml\ACH\tttt"
_, cursor = open_db()
if table_exists("GrpHdr", cursor) > 0:
exec_db_cmd('delete from GrpHdr')
# exec_db_cmd('delete from trx')
if table_exists("pacs_008_001_01", cursor) > 0:
exec_db_cmd('delete from pacs_008_001_01')
if table_exists("pacs_002_001_02", cursor) > 0:
exec_db_cmd('delete from pacs_002_001_02')
if table_exists("pacs_004_001_01", cursor) > 0:
exec_db_cmd('delete from pacs_004_001_01')
close_db(cursor)
for folder, subs, files in os.walk(data_folder):
for f in files:
filename, file_extension = os.path.splitext(f)
if file_extension.upper() != ".XML":
continue
xml_to_sqlite(os.path.join(os.path.join(folder, f)))
def add_new_profile_stats():
# check newly profiles not in assets
cmd = """
INSERT INTO stats
select a.milestone, a.profile_group, a.profile_name, followup_group, 0, 0,0,0,0,0, 0, 0, 0,0,0,0,0,0
FROM (
SELECT ms.milestone, ast.profile_group, ast.profile_name, ast.followup_group
FROM assets ast
LEFT JOIN stats st using (profile_name)
LEFT JOIN milestone ms USING(followup_group)
GROUP BY ast.profile_name
HAVING st.profile_name is NULL
)a """
exec_db_cmd(cmd)
def update_book():
cmd = """
UPDATE pdf_book
SET dict_word = dict.dict_word,
type = 'Dict'
FROM
(SELECT pdf_word, dict_word FROM pdf_dict) dict
WHERE pdf_book.pdf_word = dict.pdf_word AND type is NULL"""
r = exec_db_cmd (cmd)
messagebox.showinfo ("Success", f"{r.rowcount} rows updated" )
def load_db(data_folder, output):
if os.path.isdir(data_folder):
# output.delete(0.0, END) # clear window
output.insert(END, f'Loadind the files: {data_folder}\n')
else:
output.insert(END, f'Data folder does not exist: {data_folder}\n')
return
conn, cursor = open_db()
create_tables(cursor)
for folder, subs, files in os.walk(data_folder):
if folder.split('\\')[-1] == 'old':
output.insert(
END, "*** skip folder ***: " + folder.split('\\')[-2] + "-->" +
folder.split('\\')[-1] + "-->old")
continue
for f in files:
filename, file_extension = os.path.splitext(f)
if file_extension != ".csv":
continue
# print(folder.split('\\')[-2], ",", folder.split('\\')[-1], ",", filename)
output.insert(END, folder.split('\\')[-1] + "-->" + f + '\n')
with open(os.path.join(os.path.join(folder, f))) as csv_file:
csv_reader = csv.reader(csv_file, delimiter=',')
for row in csv_reader:
if len(row) != 28:
output.insert(
END,
f'filename skipped, # col are not 28, it is: {len(row)}" \n'
)
break
if row[0] != 'name': # skip header
# output.insert(END, filename + str(i) + '\n')
insert_row_vulner(conn, cursor, row)
conn.commit()
exec_db_cmd(
"delete from vulnerability WHERE ipaddress = '172.17.90.200' AND name = 'HQ-VLAN-90-Windows-Server-FULL'"
)
close_db(cursor)
insert_base_stats(output)
def load_ACH():
# clear_tk()
# file_path = filedialog.askopenfilename(title="Select file",multiple=False,
# filetypes=(("Excel files", "*,xlsx"), ("Excel files", "*.xlsx")))
# if not file_path:
# return # no files selected
# file_name = r"C:\Yahia\HDB\HDB-CBP\3- Execution\Interfaces\IRDs\ACH\0002\booking\ACH sample\29_4003076817_BOOKING_8034_1.xml"
# file_name = r"C:\Yahia\Home\Yahia-Dev\Python\training\xml\ACH\29_PACS008_2021080309241818109.XML"
# file_name = r"C:\Yahia\Home\Yahia-Dev\Python\training\xml\ACH\29_PACS008_2021080309241818109.XML"
file_name = r"C:\Yahia\Home\Yahia-Dev\Python\training\xml\ACH\tttt\29_PACS008_2021080811485962830.XML"
data_folder = r"C:\Yahia\Home\Yahia-Dev\Python\training\xml\ACH"
fexception = open (r".\out\exceptions.txt", "wt", encoding = "UTF8")
conn, cursor = open_db()
exec_db_cmd('delete from GrpHdr')
exec_db_cmd('delete from trx')
exec_db_cmd('delete from pacs_002_004')
if True:
parse_pacs_file(file_name, conn, cursor, fexception)
else:
for folder, subs, files in os.walk(data_folder):
for f in files:
filename, file_extension = os.path.splitext(f)
if file_extension.upper() != ".XML":
continue
parse_pacs_file(os.path.join(os.path.join(folder, f)), conn, cursor, fexception)
conn.commit()
close_db(cursor)
fexception.close()
def pdf2db(page_no):
page = f".\\data\\books\\ABH\\pages\\ABH-{format(page_no, '03')}.png"
words= str(((pytesseract.image_to_string(Image.open(page), lang='ara'))))
# get last record number in dict
conn, cursor = open_db()
rows = exec_query (cursor, "select count(*) FROM pdf_dict")
if len(rows) == 0:
st_dict_no = 1
else:
st_dict_no = rows[0][0]+1
for w in words.split():
insert_row_list(conn, cursor, 'pdf_dict', [w,w,page_no], ignoreUnique=True)
conn.commit()
close_db(cursor)
words = words.replace("'", "*") # replace single quote
words = words.replace('"', "*") # replace double quote
cmd = f"""INSERT INTO page_text VALUES ({page_no}, '{words}', '', {st_dict_no})"""
exec_db_cmd(cmd)
def update_book_word(pdf_word, dict_word, page_no, word_type):
cmd = f"""
UPDATE pdf_book SET type = '{word_type}', dict_word = '{dict_word}' WHERE pdf_word = '{pdf_word}' AND page_no = {page_no}"""
# print (cmd)
exec_db_cmd (cmd)
def insert_base_stats(output):
if exec_db_cmd("DELETE FROM stats") == -1: # Database is locked
return -1
# print ("insert_stats")
cmd = f"""INSERT INTO stats
select a.milestone, a.profile_group, a.name, followup_group, numHosts,High,Medium, Low, Warning,
total_all, total_HM, 0, 0,0,0,0,0,0
FROM
(
SELECT ms.milestone, profile_group, vul.name, ms.followup_group,
COUNT(distinct assets.ipaddress) as "numHosts",
COUNT(CASE WHEN risk = 'High' Then 1 END) as 'High',
COUNT(CASE WHEN risk = 'Medium' Then 1 END) as 'Medium',
COUNT(CASE WHEN risk = 'Low' Then 1 END) as 'Low',
COUNT(CASE WHEN risk = 'Warning' Then 1 END) as 'Warning',
COUNT(risk) as total_all,
COUNT(CASE WHEN risk in ('High' ,'Medium') Then 1 END) as total_HM
FROM base_vul vul
LEFT JOIN assets USING (ipaddress)
LEFT JOIN milestone ms USING(followup_group)
--WHERE ifnull(assets.exclude,0) != 1
GROUP by name
ORDER BY 2
) a"""
exec_db_cmd(cmd)
cmd = """UPDATE stats
SET total_vt = base.total_vt
FROM
(
SELECT vul.name,
COUNT(DISTINCT vulnerability) as "total_vt"
FROM base_vul vul
LEFT JOIN assets ast USING (ipaddress)
WHERE risk in ('High', 'Medium')
-- AND ifnull(ast.exclude,0) != 1
GROUP by vul.name
) as base
WHERE stats.profile_name = base.name"""
# print (cmd)
exec_db_cmd(cmd)
# update vt_stats as it's grouped by followup group not profile_name
cmd = """UPDATE stats_vt
SET base_numHosts = base.numHosts
FROM
(
SELECT DISTINCT followup_group,
count(DISTINCT ipaddress) as numHosts
FROM base_vul vul
LEFT JOIN assets ast USING (ipaddress)
GROUP by followup_group
) as base
WHERE stats_vt.followup_group = base.followup_group"""
# print (cmd)
exec_db_cmd(cmd)
cmd = """UPDATE stats_vt
SET base_total_vt = base.total_vt
FROM
(
SELECT DISTINCT followup_group,
COUNT(DISTINCT vulnerability) as total_vt
FROM base_vul vul
LEFT JOIN assets ast USING (ipaddress)
WHERE risk in ('High', 'Medium')
GROUP by followup_group
) as base
WHERE stats_vt.followup_group = base.followup_group"""
# print (cmd)
exec_db_cmd(cmd)
def update_stats(output):
# print ("update_status")
output.insert(END," delete stats and inset stats record from base_vul\n")
insert_base_stats(output) # delete stats and inset stats record from base_vul
output.insert(END," add_new_profile_stats\n")
add_new_profile_stats()
output.insert(END," Update stats.... \n")
cmd = f"""UPDATE stats
SET current_numHosts = crnt.numHosts,
current_total_all = crnt.total_all,
current_total_HM = crnt.total_HM
FROM
(
SELECT vul.name,
COUNT(DISTINCT vul.ipaddress) as numHosts,
COUNT(risk) as total_all,
COUNT(CASE WHEN risk in ('High' ,'Medium') Then 1 END) as total_HM
FROM vulnerability vul
LEFT JOIN assets ast using (ipaddress)
WHERE ifnull(ast.exclude,0) != 1
GROUP by vul.name
) crnt
WHERE stats.profile_name = crnt.name"""
# print (cmd)
exec_db_cmd(cmd)
# Update Vulnerabilty type progress
cmd = """UPDATE stats
SET current_total_vt = crnt.total_vt
FROM
(
SELECT vul.name,
COUNT(DISTINCT vulnerability) as "total_vt"
FROM vulnerability vul
LEFT JOIN assets ast using (ipaddress)
WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) != 1
GROUP by vul.name
) as crnt
WHERE stats.profile_name = crnt.name"""
# print (cmd)
exec_db_cmd(cmd)
# Update Project scope progress HM
cmd = f"""UPDATE stats
SET
proj_scope_current_HM = crnt.current_total_HM,
proj_scope_current_vt = 0
FROM
(
SELECT name,
COUNT(CASE WHEN risk in ('High' ,'Medium') THEN 1 END ) as current_total_HM
FROM vulnerability vul
LEFT JOIN assets ast using (ipaddress)
WHERE vul.ipaddress in (SELECT distinct ipaddress FROM base_vul)
AND ifnull(ast.exclude,0) != 1
GROUP by name
) crnt
WHERE stats.profile_name = crnt.name"""
exec_db_cmd(cmd)
cmd = """UPDATE stats_vt
SET
current_numHosts = base.numHosts
FROM
(
SELECT DISTINCT followup_group,
count(DISTINCT ipaddress) as numHosts
FROM vulnerability vul
LEFT JOIN assets ast USING (ipaddress)
WHERE ifnull(ast.exclude,0) != 1
GROUP by followup_group
) as base
WHERE stats_vt.followup_group = base.followup_group"""
# print (cmd)
exec_db_cmd(cmd)
cmd = """UPDATE stats_vt
SET
current_total_vt = base.total_vt
FROM
(
SELECT DISTINCT followup_group,
COUNT(DISTINCT vulnerability) as total_vt
FROM vulnerability vul
LEFT JOIN assets ast USING (ipaddress)
WHERE risk in ('High', 'Medium') AND ifnull(ast.exclude,0) != 1
GROUP by followup_group
) as base
WHERE stats_vt.followup_group = base.followup_group"""
# print (cmd)
exec_db_cmd(cmd)