182 lines
15 KiB
Python
182 lines
15 KiB
Python
"""Generate SANEF_Qualys_Tags_V3_RuleTypes_v2.xlsx with description column."""
|
|
from openpyxl import Workbook
|
|
from openpyxl.styles import PatternFill, Font, Alignment
|
|
from openpyxl.utils import get_column_letter
|
|
|
|
wb = Workbook()
|
|
|
|
# ============== Sheet 1: Tags Dynamiques (DYN) ==============
|
|
ws1 = wb.active
|
|
ws1.title = "Tags Dynamiques (DYN)"
|
|
ws1.append(["Tag", "Couleur (hex)", "Type", "Rule Type", "Description", "Query QQL"])
|
|
for cell in ws1[1]:
|
|
cell.font = Font(bold=True, color="FFFFFF")
|
|
cell.fill = PatternFill("solid", fgColor="263238")
|
|
cell.alignment = Alignment(horizontal="center", vertical="center")
|
|
|
|
dyn_tags = [
|
|
("OS-LIN-SRV", "#4CAF50", "DYN", "Asset Inventory",
|
|
"Serveurs Linux avec Cloud Agent Qualys",
|
|
"operatingSystem.category1:Linux and operatingSystem.category2:Server and asset.trackingMethod:QAGENT"),
|
|
("OS-WIN-SRV", "#1976D2", "DYN", "Asset Inventory",
|
|
"Serveurs Windows Server avec Cloud Agent Qualys",
|
|
"operatingSystem.category1:Windows and operatingSystem.category2:Server and asset.trackingMethod:QAGENT"),
|
|
("OS-WIN-WKS", "#2196F3", "DYN", "Asset Inventory",
|
|
"Postes de travail Windows avec Cloud Agent Qualys",
|
|
"operatingSystem.category1:Windows and operatingSystem.category2:Client and asset.trackingMethod:QAGENT"),
|
|
("OS-MAC", "#546E7A", "DYN", "Asset Inventory",
|
|
"Postes macOS avec Cloud Agent Qualys",
|
|
"operatingSystem.category1:Mac and asset.trackingMethod:QAGENT"),
|
|
("OS-ESX", "#9C27B0", "DYN", "Asset Inventory",
|
|
"Hyperviseurs VMware ESXi (hors scope, pas d'agent)",
|
|
'operatingSystem:"ESXi"'),
|
|
("EQT-VIR", "#00BCD4", "DYN", "Asset Inventory",
|
|
"Machines virtuelles (hors postes clients)",
|
|
'asset.name:v* and not operatingSystem.category2:"Client"'),
|
|
("EQT-SRV", "#03A9F4", "DYN", "Asset Inventory",
|
|
"Serveurs physiques (hors VM et equipements reseau)",
|
|
'(asset.name:l* or asset.name:s*) and not operatingSystem.category2:"Client" and not asset.name:svp*'),
|
|
("EQT-SWI", "#4DD0E1", "DYN", "Asset Inventory",
|
|
"Equipements reseau (switches, routeurs, firewalls, LB) - detection via OS reseau Juniper/F5/Cisco/Pulse",
|
|
'operatingSystem.category1:"Network Operating System"'),
|
|
("ENV-PRD", "#F44336", "DYN", "Asset Inventory",
|
|
"Production - V3 (vp/sp/lp/ls-) sauf vppi, legacy lam* sauf (lamar/lamr/lamt), + vmm*.sanef-int.adds, restreint Serveurs",
|
|
'(asset.name:vp* or asset.name:sp* or asset.name:lp* or asset.name:ls-* or asset.name:lam* or (asset.name:vmm* and asset.fqdn:*.sanef-int.adds)) and not (asset.name:vppi* or asset.name:lamar* or asset.name:lamr* or asset.name:lamt* or asset.name:vmamr* or asset.name:vmamd* or asset.name:vmrgmao7 or asset.name:vmcmdb1 or asset.name:vmcmdb2) and operatingSystem.category2:"Server"'),
|
|
("ENV-REC", "#FF9800", "DYN", "Asset Inventory",
|
|
"Recette - V3 (vr/sr/lr), legacy lamar/lamr/lamt + AME (vmamr*, vmrgmao7, vmcmdb1/2), + vmd*.recette.adds, exclut exceptions TST, restreint Serveurs",
|
|
'(asset.name:vr* or asset.name:sr* or asset.name:lr* or asset.name:lamar* or asset.name:lamr* or asset.name:lamt* or (asset.name:vmd* and asset.fqdn:*.recette.adds) or asset.name:vmamr* or asset.name:vmrgmao7 or asset.name:vmcmdb1 or asset.name:vmcmdb2) and not (asset.name:vrsupbmap1 or asset.name:vrsupbmbi1) and operatingSystem.category2:"Server"'),
|
|
("ENV-PPR", "#FFC107", "DYN", "Asset Inventory",
|
|
"Pre-Production - V3 (vi/si/vo/vppi), restreint Serveurs",
|
|
'(asset.name:vi* or asset.name:si* or asset.name:vo* or asset.name:vppi*) and operatingSystem.category2:"Server"'),
|
|
("ENV-TST", "#CDDC39", "DYN", "Asset Inventory",
|
|
"Test - V3 (vv/vt/sv) + exceptions (vrsupbmap1, vrsupbmbi1), exclut postes SVP*, restreint Serveurs",
|
|
'(asset.name:vv* or asset.name:vt* or asset.name:sv* or asset.name:vrsupbmap1 or asset.name:vrsupbmbi1) and not asset.name:svp* and operatingSystem.category2:"Server"'),
|
|
("ENV-DEV", "#8BC34A", "DYN", "Asset Inventory",
|
|
"Developpement - V3 (vd/sd) + legacy AME vmamd* + exception vrdsiadev1, restreint Serveurs",
|
|
'(asset.name:vd* or asset.name:sd* or asset.name:vmamd* or asset.name:vrdsiadev1) and operatingSystem.category2:"Server"'),
|
|
("POS-FL", "#009688", "DYN", "Asset Inventory",
|
|
"Flux Libre - Free Flow (BOT/BOO/BOC), AFL, Supervision, BOOST peage, restreint Serveurs",
|
|
'(asset.name:vpbot* or asset.name:vrbot* or asset.name:vibot* or asset.name:vvbot* or asset.name:vdbot* or asset.name:vpboo* or asset.name:vrboo* or asset.name:viboo* or asset.name:vvboo* or asset.name:vdboo* or asset.name:spboo* or asset.name:siboo* or asset.name:svboo* or asset.name:vpboc* or asset.name:vrboc* or asset.name:viboc* or asset.name:vvboc* or asset.name:vdboc* or asset.name:spboc* or asset.name:siboc* or asset.name:vpafl* or asset.name:vrafl* or asset.name:viafl* or asset.name:vvafl* or asset.name:vdafl* or asset.name:vpsupa* or asset.name:vrsupa* or asset.name:visupa* or asset.name:vvsupa* or asset.name:vpsupb* or asset.name:vrsupb* or asset.name:vppeaab* or asset.name:vrpeaab* or asset.name:vipeaab* or asset.name:vvpeaab* or asset.name:vrpeaak* or asset.name:vppeab* or asset.name:vrpeab* or asset.name:vipeab* or asset.name:vvpeab* or asset.name:vppeah* or asset.name:vrpeah* or asset.name:vipeah* or asset.name:vvpeah* or asset.name:vpnit*) and operatingSystem.category2:"Server"'),
|
|
("POS-INF", "#3F51B5", "DYN", "Asset Inventory",
|
|
"Infrastructure DSI - 91 prefixes (DNS/AD/Sauvegarde/SCCM/Logs/etc.) avec 16 exclusions (Gestion + Trafic vpexpat/bt/ar + obsoletes vpsimaapi) + restreint Serveurs",
|
|
"Voir fichier C:\\Claude\\sanef\\QL\\inputs\\dom_inf_rule_v2.txt (91 prefixes + 16 exclusions). Forme : (BIG_OR) and operatingSystem.category2:\"Server\" and not (vpaiiat*/vrdsiat*/vpgesb*/vpechat*/vrechat*/vdechat*/vpsimas*/vpsimaapi*/vpppear*/vpppeas*/vpbipa*/vraiia*/vraptb*/vpexpat*/vpexpbt*/vpexpar*)"),
|
|
("POS-PEA", "#673AB7", "DYN", "Asset Inventory",
|
|
"Peage - sites geographiques (ls-*), OSAP, SVP, ADV, RPA, RPN, BoE, FFB, GRS, BIP, ALB, BO + restreint Serveurs (vpsimas* uniquement, pas vpsim*)",
|
|
'(asset.name:ls-* or asset.name:lrpea* or asset.name:vdosa* or asset.name:viosa* or asset.name:vpadv* or asset.name:vpalb* or asset.name:vpbipa* or asset.name:vpboe* or asset.name:vposa* or asset.name:vppbo* or asset.name:vppeaaa* or asset.name:vppeaae* or asset.name:vppeaar* or asset.name:vpppear* or asset.name:vpppeas* or asset.name:vprpa* or asset.name:vprpn* or asset.name:vprps* or asset.name:vpsimas* or asset.name:vradv* or asset.name:vraiia* or asset.name:vrboe* or asset.name:vrffb* or asset.name:vrgrs* or asset.name:vrosa* or asset.name:vrpeaar* or asset.name:vrrpa* or asset.name:vrrpn* or asset.name:vrrps* or asset.name:vrsvp*) and operatingSystem.category2:"Server"'),
|
|
("POS-TRA", "#E91E63", "DYN", "Asset Inventory",
|
|
"Trafic - AME/Sextan/Octan, Aquarius, Isis, RAU/ASUR, GDEPA, SIG, GMO + legacy vmam*, restreint Serveurs",
|
|
'(asset.name:vpame* or asset.name:vrame* or asset.name:viame* or asset.name:vvame* or asset.name:vdame* or asset.name:vmame* or asset.name:vmamp* or asset.name:vmamr* or asset.name:vmamd* or asset.name:vpdai* or asset.name:vrdai* or asset.name:vidai* or asset.name:vppat* or asset.name:vrpat* or asset.name:vipat* or asset.name:vprau* or asset.name:vrrau* or asset.name:vpdep* or asset.name:vrdep* or asset.name:vpsig* or asset.name:vrsig* or asset.name:visig* or asset.name:vpair* or asset.name:vrair* or asset.name:vpexpa* or asset.name:vpexpb* or asset.name:vpgmo* or asset.name:vrgmo*) and not (asset.name:vpexpaxfb* or asset.name:vpexpbdech*) and operatingSystem.category2:"Server"'),
|
|
("POS-BI", "#FF5722", "DYN", "Asset Inventory",
|
|
"Business Intelligence - SAS Decisionnel/Viya, Bip&Go, Power BI, Reporting, exclut Apta (= POS-GES), restreint Serveurs",
|
|
'(asset.name:vdrep* or asset.name:vpapt* or asset.name:vpbipb* or asset.name:vpdec* or asset.name:vppbi* or asset.name:vpsas* or asset.name:vraptb* or asset.name:vrbip* or asset.name:vrdec* or asset.name:vrpbi*) and not (asset.name:vpapta* or asset.name:vrapta*) and operatingSystem.category2:"Server"'),
|
|
("POS-GES", "#9E9D24", "DYN", "Asset Inventory",
|
|
"Gestion - Institutionnel, Intranet, AgileTime, Talend ETL, Echat, Apta, Aide pilotage, restreint Serveurs",
|
|
'(asset.name:lpagt* or asset.name:lragt* or asset.name:vdechat* or asset.name:vpagt* or asset.name:vpechat* or asset.name:vpint* or asset.name:vppin* or asset.name:vragt* or asset.name:vrechat* or asset.name:vrint* or asset.name:vpaiiat* or asset.name:vrdsiat* or asset.name:vpgesb* or asset.name:vrapta*) and operatingSystem.category2:"Server"'),
|
|
("NOM-LEGACY", "#795548", "DYN", "Asset Inventory",
|
|
"Asset (Serveur ou Network OS) avec nommage pre-V3 a renommer - KPI dette de conformite",
|
|
'(operatingSystem.category2:"Server" or operatingSystem.category1:"Network Operating System") and not (asset.name:vp* or asset.name:vr* or asset.name:vi* or asset.name:vv* or asset.name:vd* or asset.name:vt* or asset.name:vo* or asset.name:vs* or asset.name:sp* or asset.name:sr* or asset.name:si* or asset.name:sd* or asset.name:sv* or asset.name:ss* or asset.name:st* or asset.name:so* or asset.name:lp* or asset.name:lr* or asset.name:ls-* or asset.name:li* or asset.name:lv* or asset.name:ld* or asset.name:lt*)'),
|
|
("TAG-EMV", "#D500F9", "DYN", "Asset Inventory",
|
|
"Asset en zone EMV/PCI-DSS - patching renforce, audits conformite, restreint Serveurs",
|
|
'(asset.name:vpemv* or asset.name:lpemv* or asset.name:lremv* or asset.name:vemvr* or asset.name:spemv* or asset.name:vemvs*) and operatingSystem.category2:"Server"'),
|
|
("TAG-OBS", "#B71C1C", "DYN", "Asset Inventory",
|
|
"OS obsolete/EOL : Windows (XP/7/2003/2008/2008R2/2012), Linux RHEL 5/6/7, CentOS 5/6/7, Ubuntu 14/16/18, Debian 8/9, Oracle Linux 5/6, SLES 11/12, Solaris 10, AIX 6.1. PAS de filtre Server (workstations XP/7 incluses).",
|
|
'operatingSystem:"Windows XP" or operatingSystem:"Windows Server 2003" or operatingSystem:"Windows Server 2008" or operatingSystem:"Windows Server 2008 R2" or operatingSystem:"Windows Server 2012" or operatingSystem:"Windows 7" or operatingSystem:"Windows7" or operatingSystem:"Red Hat Enterprise Linux Server 5" or operatingSystem:"Red Hat Enterprise Linux Server 6" or operatingSystem:"Red Hat Enterprise Linux Server 7" or operatingSystem:"CentOS 5" or operatingSystem:"CentOS 6" or operatingSystem:"CentOS 7" or operatingSystem:"Ubuntu 14" or operatingSystem:"Ubuntu 16" or operatingSystem:"Ubuntu 18" or operatingSystem:"Debian 8" or operatingSystem:"Debian 9" or operatingSystem:"Oracle Linux 5" or operatingSystem:"Oracle Linux 6" or operatingSystem:"SLES 11" or operatingSystem:"SLES 12" or operatingSystem:"Solaris 10" or operatingSystem:"AIX 6.1"'),
|
|
]
|
|
|
|
for row in dyn_tags:
|
|
ws1.append(row)
|
|
|
|
for r in range(2, len(dyn_tags) + 2):
|
|
color_cell = ws1.cell(row=r, column=2)
|
|
hex_val = color_cell.value
|
|
if hex_val and hex_val.startswith("#"):
|
|
color_cell.fill = PatternFill("solid", fgColor=hex_val[1:])
|
|
color_cell.font = Font(color="FFFFFF", bold=True)
|
|
color_cell.alignment = Alignment(horizontal="center")
|
|
|
|
for i, w in enumerate([18, 14, 7, 18, 60, 90], 1):
|
|
ws1.column_dimensions[get_column_letter(i)].width = w
|
|
for row in ws1.iter_rows(min_row=2, max_row=len(dyn_tags) + 1):
|
|
for cell in row:
|
|
cell.alignment = Alignment(wrap_text=True, vertical="top")
|
|
for r in range(2, len(dyn_tags) + 2):
|
|
ws1.row_dimensions[r].height = 60
|
|
|
|
# ============== Sheet 2: Tags Statiques (STAT) ==============
|
|
ws2 = wb.create_sheet("Tags Statiques (STAT)")
|
|
ws2.append(["Tag", "Couleur (hex)", "Description", "Action / Source"])
|
|
for cell in ws2[1]:
|
|
cell.font = Font(bold=True, color="FFFFFF")
|
|
cell.fill = PatternFill("solid", fgColor="263238")
|
|
cell.alignment = Alignment(horizontal="center")
|
|
|
|
stat_tags = [
|
|
("TAG-SED", "#C62828",
|
|
"Securite Exposition Directe - frontaux IP publique (firewalls, F5, HAProxy DMZ, AOV, WAP, proxy)",
|
|
"Bulk SQATM - liste actuelle dans bulk_tag_sed.txt (10 assets)"),
|
|
("TAG-SEI", "#EF6C00",
|
|
"Securite Exposition Indirecte - backends DMZ derriere frontaux (Exchange, DNS, OwnCloud, BOOST backends)",
|
|
"Bulk SQATM - liste actuelle dans bulk_tag_sei.txt (22 assets)"),
|
|
("TAG-ELS", "#1B5E20",
|
|
"Serveur sous Extended Life License (support securite etendu payant) - exception EOL",
|
|
"Manuel SQATM - a remplir au cas par cas selon contrats ELS"),
|
|
("TAG-DEC", "#6D4C41",
|
|
"Decommissionnement en cours - asset a supprimer dans 1 mois",
|
|
"Manuel SQATM - decision operationnelle ITOP"),
|
|
("TAG-INT", "#FDD835",
|
|
"Integration / Implementation en cours - vulnerabilites normales pendant phase deploiement",
|
|
"Manuel SQATM - retirer a la MEP"),
|
|
("TAG-SIC", "#1A237E",
|
|
"Zone SIC - Systeme Information Classifie",
|
|
"Manuel SQATM - validation architecte securite"),
|
|
("TAG-SIA", "#283593",
|
|
"Zone SIA - Systeme Information Administration",
|
|
"Manuel SQATM - validation architecte securite"),
|
|
]
|
|
for row in stat_tags:
|
|
ws2.append(row)
|
|
for r in range(2, len(stat_tags) + 2):
|
|
color_cell = ws2.cell(row=r, column=2)
|
|
hex_val = color_cell.value
|
|
if hex_val and hex_val.startswith("#"):
|
|
color_cell.fill = PatternFill("solid", fgColor=hex_val[1:])
|
|
color_cell.font = Font(color="FFFFFF", bold=True)
|
|
color_cell.alignment = Alignment(horizontal="center")
|
|
for i, w in enumerate([14, 14, 60, 50], 1):
|
|
ws2.column_dimensions[get_column_letter(i)].width = w
|
|
for row in ws2.iter_rows(min_row=2, max_row=len(stat_tags) + 1):
|
|
for cell in row:
|
|
cell.alignment = Alignment(wrap_text=True, vertical="top")
|
|
for r in range(2, len(stat_tags) + 2):
|
|
ws2.row_dimensions[r].height = 50
|
|
|
|
# ============== Sheet 3: Prefixes Manuels ==============
|
|
ws3 = wb.create_sheet("Prefixes manuels")
|
|
ws3.append(["Prefixe", "Description", "Source"])
|
|
for cell in ws3[1]:
|
|
cell.font = Font(bold=True, color="FFFFFF")
|
|
cell.fill = PatternFill("solid", fgColor="263238")
|
|
cell.alignment = Alignment(horizontal="center")
|
|
manual = [
|
|
("APP-xxx", "Application hebergee (APP-SAT, APP-JIRA, APP-GLPI, APP-GED)", "Manuel - non deductible du hostname, declaration projet"),
|
|
("BDD-xxx", "Type de base de donnees (BDD-ORA, BDD-PG, BDD-SQL, BDD-MONGO)", "Semi-manuel - sous-zone b dans hostname suggere BDD, mais type a confirmer"),
|
|
("VRF-xxx", "VRF reseau (VRF-TRAFIC, VRF-EMV, VRF-INTERPHONE)", "Manuel - depend architecture reseau, demander equipe reseau"),
|
|
("MID-xxx", "Middleware (MID-TOMCAT, MID-HAPROXY, MID-NGINX, MID-APACHE)", "Manuel - necessite scan applicatif ou declaration technique"),
|
|
("VULN-xxx", "Vulnerabilite specifique (VULN-LOG4J, VULN-OPENSSL)", "Manuel - creation ad-hoc lors d'incidents/CVE majeurs"),
|
|
]
|
|
for row in manual:
|
|
ws3.append(row)
|
|
for i, w in enumerate([12, 60, 60], 1):
|
|
ws3.column_dimensions[get_column_letter(i)].width = w
|
|
for row in ws3.iter_rows(min_row=2, max_row=len(manual) + 1):
|
|
for cell in row:
|
|
cell.alignment = Alignment(wrap_text=True, vertical="top")
|
|
for r in range(2, len(manual) + 2):
|
|
ws3.row_dimensions[r].height = 40
|
|
|
|
out = r"C:\Claude\sanef\QL\docs\SANEF_Qualys_Tags_V3_RuleTypes_v2.xlsx"
|
|
wb.save(out)
|
|
print("Saved:", out)
|
|
import os
|
|
print("Size:", os.path.getsize(out), "bytes")
|