98 lines
3.5 KiB
Python
98 lines
3.5 KiB
Python
import re, json, sys, io
|
|
from openpyxl import load_workbook
|
|
from unidecode import unidecode
|
|
|
|
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
|
|
|
|
# Extract all checks from PowerShell script
|
|
ps_content = open(r'D:\VNPT-M\Tool\audit_tool\NEW\Update\audit_lhdsin\audit_check_script\audit_cis_windows.ps1', 'r', encoding='utf-8').read()
|
|
checks = re.findall(r'Write-CheckResult\s+"(.+?)"\s+"(.+?)"', ps_content)
|
|
print(f'PS script checks: {len(checks)}')
|
|
|
|
# Build criteria lookup: CheckId -> description
|
|
ps_criteria = {}
|
|
for cid, desc in checks:
|
|
full = f"{cid}. {desc}"
|
|
key = unidecode(full)
|
|
ps_criteria[key] = (cid, full)
|
|
|
|
# Load Excel checklist
|
|
wb = load_workbook(r'D:\VNPT-M\Tool\audit_tool\NEW\Update\audit_lhdsin\config\Windows_Checklist.xlsx')
|
|
ws = wb.worksheets[0]
|
|
|
|
# Build row mapping: scan Excel for criteria IDs (like "1.1.1" in column A)
|
|
# Compare unidecode'd criteria text to match PS output
|
|
excel_rows = {}
|
|
print(f'\nScanning Excel rows 1-{ws.max_row} for criteria IDs...')
|
|
for row in range(1, ws.max_row + 1):
|
|
val_a = ws.cell(row=row, column=1).value
|
|
val_b = ws.cell(row=row, column=2).value
|
|
|
|
# Try to find criteria ID in column A (like "1.1.1")
|
|
text_a = str(val_a).strip() if val_a else ""
|
|
text_b = str(val_b).strip() if val_b else ""
|
|
|
|
# Check if this row has a criteria ID pattern (like "1.1.1" or "1.1.1.")
|
|
# Also try matching the full text from column B
|
|
combined = f"{text_a} {text_b}".strip()
|
|
norm = unidecode(combined)
|
|
|
|
if norm in ps_criteria:
|
|
cid, full_text = ps_criteria[norm]
|
|
excel_rows[row] = full_text
|
|
del ps_criteria[norm] # Remove matched
|
|
|
|
# Now try matching by CheckId prefix in column A
|
|
still_unmatched = dict(ps_criteria)
|
|
for cid_key, (cid, full_text) in still_unmatched.items():
|
|
for row in range(1, ws.max_row + 1):
|
|
val_a = str(ws.cell(row=row, column=1).value or "").strip()
|
|
if val_a and cid in val_a:
|
|
excel_rows[row] = full_text
|
|
del ps_criteria[cid_key]
|
|
break
|
|
|
|
print(f'\nMatched via full text: {len(excel_rows)}')
|
|
print(f'Matched via ID search: {194 - len(ps_criteria) - len(excel_rows)}')
|
|
print(f'Still unmatched: {len(ps_criteria)}')
|
|
|
|
# For unmatched, find the closest row by ID pattern
|
|
# Parse CheckId like "1.1.1" into section numbers
|
|
for cid_key, (cid, full_text) in list(ps_criteria.items()):
|
|
parts = cid.split('.')
|
|
# Try to find a row where column A contains this ID prefix
|
|
found = False
|
|
for row in range(1, ws.max_row + 1):
|
|
val_a = str(ws.cell(row=row, column=1).value or "").strip()
|
|
if val_a == cid or val_a == cid + '.':
|
|
if row not in excel_rows:
|
|
excel_rows[row] = full_text
|
|
del ps_criteria[cid_key]
|
|
found = True
|
|
break
|
|
if not found:
|
|
print(f' No Excel row for: [{cid}] {full_text[:60]}')
|
|
|
|
print(f'\nFinal matched rows: {len(excel_rows)}')
|
|
print(f'Final unmatched: {len(ps_criteria)}')
|
|
|
|
# Build new config
|
|
new_data = []
|
|
for row in sorted(excel_rows.keys()):
|
|
new_data.append({str(row): excel_rows[row]})
|
|
|
|
new_config = {"data": new_data}
|
|
|
|
# Write new config
|
|
output_path = r'D:\VNPT-M\Tool\audit_tool\NEW\Update\audit_lhdsin\config\windows_config_new.json'
|
|
with open(output_path, 'w', encoding='utf-8') as f:
|
|
json.dump(new_config, f, ensure_ascii=False, indent=4)
|
|
|
|
print(f'\nNew config written to: {output_path}')
|
|
print(f'Entries: {len(new_data)}')
|
|
|
|
# Print summary of row mapping
|
|
print('\nSample row mapping:')
|
|
for row in sorted(excel_rows.keys())[:10]:
|
|
print(f' Row {row}: {excel_rows[row][:70]}')
|