Detection Engineering
Auditing Microsoft Sentinel
Analytics Rules with Python
A practical walkthrough of building a rule audit pipeline — from raw JSON exports to a scored remediation backlog and an interactive HTML dashboard — with no live Azure access required.
Why audit analytics rules?
Microsoft Sentinel analytics rules are the backbone of your detection capability. They are the KQL queries that run continuously against your ingested data, generating alerts when suspicious behaviour is detected. Yet in most Sentinel environments, the rules estate accumulates over time with surprisingly little governance applied to it.
Rules get imported from Content Hub solutions, deployed via ARM templates, or created manually by engineers who have since left the team. The result is a mixed bag — some rules fire reliably and map cleanly to MITRE ATT&CK, others have never produced an alert in months, and a non-trivial number are subtly misconfigured in ways that mean they will never fire at all.
A rule that is enabled but never fires is not a silent guardian. It is a false sense of security dressed up as detection coverage.
The purpose of this audit is to surface these problems systematically — not by manually reviewing each rule in the portal, but by processing the exported rule definitions programmatically and applying a consistent set of checks across every rule at once.
What the audit covers
The audit assesses ten parameter categories across every rule, divided into two classes: parameters that can be fully assessed from the rule definition JSON, and parameters that require a live connection to the Sentinel workspace.
| Category | What is checked | Requires live access? |
|---|---|---|
| Rule identity | Name, description completeness, enabled status, GUID validity | No |
| Rule logic | KQL query presence, lookback period, run frequency, suppression settings | No |
| Severity & triage | Severity assigned and valid, not uniformly defaulted | No |
| MITRE ATT&CK | Tactics populated, techniques assigned, tactic name validity | No |
| Incident configuration | Incident creation enabled, grouping settings, re-open behaviour | No |
| Entity mapping | At least one entity mapped, entity type validity | No |
| Alert details | Dynamic vs static alert names, event grouping strategy | No |
| Metadata | Author, version, source (Content Hub vs custom) | No |
| Operational health | Query syntax, last run, last fired | Yes |
| Data source coverage | Connector status, table ingestion recency | Yes |
Eight of the ten categories are fully assessable from exported JSON. The two that require live access are clearly marked as N/A - No Live Access in the output rather than being silently omitted.
Working without live access
A common constraint in consulting, regulated environments, and pre-deployment reviews is that you are given the rule definitions to assess — but not access to the live workspace. The audit pipeline is specifically designed for this scenario.
Important limitation
JSON-only audit cannot tell you whether a rule is actually working in the environment. It can tell you whether a rule is correctly configured to work. Always communicate this boundary clearly to whoever receives the audit report.
The pipeline handles ARM template format specifically — the format produced when you export rules from the Azure portal or deploy via Infrastructure as Code pipelines. It supports both wrapped ARM envelope format (where the rule sits inside a resources array) and direct rule object format (where the root of the file is the rule itself).
One subtle issue worth flagging: ARM exports use expressions like [concat(parameters('workspace'),'/Microsoft.SecurityInsights/rule-guid')] in the name field. The audit script parses these with a regex to extract the actual GUID.
Script 1 — sentinel_audit.py
The first script is the extractor and checker. It reads every JSON file in a folder, parses the rule definition, applies all assessable checks, and writes one row per rule to a CSV file.
What it does
1
Detects the schema variant
Distinguishes between ARM envelope format (has $schema and resources) and direct rule object format (has kind and properties at root).
2
Extracts the rule kind
Routes audit logic by kind — Scheduled, NRT, Fusion, and MicrosoftSecurityIncidentCreation each have different applicable fields.
3
Runs all audit checks
Applies checks across all eight assessable categories, flagging issues such as frequency exceeding period, suppression silencing alerts, missing entity mappings, and invalid MITRE tactic names.
4
Marks non-assessable columns
Operational health and connector status columns are explicitly set to N/A - No Live Access.
5
Writes one row per rule
Outputs a CSV with 48 columns covering every audit parameter, plus an audit_flags column listing all issues as a pipe-separated string.
How to run it
# Basic usage
python sentinel_audit.py --input ./rules --output audit_results.csv
# Custom paths
python sentinel_audit.py --input "C:\exports\sentinel_rules" --output "C:\reports\audit_results.csv"
The frequency period ratio — explained
The frequency_period_ratio is calculated as run frequency ÷ lookback period. A value of 0.2 is excellent — it means the rule runs 5× within its own lookback window. A value above 1.0 means coverage gaps exist between runs.
| Ratio | Meaning | Example |
|---|---|---|
| ≤ 0.5 | Excellent — large overlap | Runs every 5min, looks back 1hr |
| 0.5 – 1.0 | Good — some overlap, no gaps | Runs every 30min, looks back 1hr |
| = 1.0 | Borderline — no overlap, no gaps | Runs every 1hr, looks back 1hr |
| > 1.0 | Warning — blind spots exist | Runs every 2hr, looks back 1hr |
| > 2.0 | Critical — large coverage gaps | Runs every 6hr, looks back 1hr |
Full source code
Copy this file and save it as sentinel_audit.py in your project folder.
sentinel_audit.py
Python
Python
"""
Sentinel Analytics Rule Audit Script
======================================
Audits Microsoft Sentinel analytics rule JSON files (ARM export format)
and produces a CSV report.
Supports:
- Direct rule object (root has 'kind' / 'properties')
- ARM template envelope (root has '$schema' / 'resources')
- Rule kinds: Scheduled, NRT, Fusion, MicrosoftSecurityIncidentCreation
Usage:
python sentinel_audit.py --input ./rules --output audit_results.csv
--input Path to folder containing JSON rule files (default: ./rules)
--output Output CSV filename (default: sentinel_audit_results.csv)
"""
import os
import re
import json
import csv
import argparse
from datetime import datetime
# ── Constants ──────────────────────────────────────────────────────────────────
NA = "N/A - No Live Access"
RULE_KINDS = {
"Scheduled",
"NRT",
"Fusion",
"MicrosoftSecurityIncidentCreation",
"MLBehaviorAnalytics",
"ThreatIntelligence",
}
VALID_SEVERITIES = {"High", "Medium", "Low", "Informational"}
KNOWN_MITRE_TACTICS = {
"InitialAccess", "Execution", "Persistence", "PrivilegeEscalation",
"DefenseEvasion", "CredentialAccess", "Discovery", "LateralMovement",
"Collection", "Exfiltration", "CommandAndControl", "Impact",
"Reconnaissance", "ResourceDevelopment", "PreAttack",
}
ENTITY_TYPES = {
"Account", "Host", "IP", "URL", "FileHash", "File",
"Process", "CloudApplication", "DNS", "AzureResource",
"IoTDevice", "Mailbox", "MailMessage", "MailCluster",
"SecurityGroup", "SubmissionMail",
}
# CSV columns in output order
CSV_COLUMNS = [
# Identity
"file_name",
"rule_guid",
"display_name",
"description_present",
"description_length",
"rule_kind",
"enabled",
# Rule logic
"query_present",
"query_line_count",
"query_tables_referenced",
"query_period",
"query_frequency",
"frequency_period_ratio",
"frequency_period_gap_flag",
"suppression_enabled",
"suppression_duration",
"suppression_vs_frequency_flag",
# Severity & triage
"severity",
"severity_valid",
# MITRE
"tactics",
"tactics_count",
"tactics_valid_flag",
"unknown_tactics",
"techniques",
"techniques_count",
# Incident configuration
"incident_creation_enabled",
"grouping_enabled",
"grouping_lookback",
"grouping_reopen_closed",
"grouping_match_only",
# Entity mapping
"entity_mapping_count",
"entity_types_mapped",
"entity_mapping_present_flag",
# Alert details
"alert_name_format",
"alert_name_is_dynamic",
"alert_description_format",
"event_grouping_strategy",
# Metadata
"kind_version",
"last_modified_utc",
"created_by",
"source_name",
"template_version",
# Operational health — not assessable without live access
"query_syntax_valid",
"tables_exist_in_workspace",
"connector_status",
"rule_last_run",
"rule_last_fired",
# Audit metadata
"audit_flags",
"audit_timestamp",
]
# ── Helpers ────────────────────────────────────────────────────────────────────
def extract_guid(name_field: str) -> str:
"""Extract GUID from ARM name expressions like
"[concat(parameters('workspace'),'/Microsoft.SecurityInsights/rule-guid')]"
or plain strings."""
if not name_field:
return "UNKNOWN"
guid_pattern = r"[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}"
match = re.search(guid_pattern, name_field)
if match:
return match.group(0)
# Fallback: take the last segment after '/'
parts = name_field.replace("'", "").replace('"', "").split("/")
return parts[-1].strip("]").strip() if parts else name_field
def parse_iso_duration(duration: str) -> int:
"""Convert ISO 8601 duration (PT5M, PT1H, P1D) to minutes."""
if not duration:
return 0
pattern = r"P(?:(\d+)D)?T?(?:(\d+)H)?(?:(\d+)M)?"
match = re.match(pattern, duration.upper())
if not match:
return 0
days = int(match.group(1) or 0)
hours = int(match.group(2) or 0)
minutes = int(match.group(3) or 0)
return days * 1440 + hours * 60 + minutes
def extract_kql_tables(query: str) -> list:
"""Heuristically extract table names from a KQL query."""
if not query:
return []
# Match words at start of line or after pipe, union, join — capitalised
table_pattern = r"(?:^|\|\s*|\bunion\s+|\bjoin\s+\w*\s*\()([A-Z][A-Za-z0-9_]+)"
matches = re.findall(table_pattern, query, re.MULTILINE)
# Filter out KQL keywords that might match
kql_keywords = {
"where", "project", "extend", "summarize", "join", "union",
"let", "datatable", "print", "range", "search", "find",
"count", "top", "limit", "order", "sort", "render",
"evaluate", "invoke", "parse", "mv-expand", "mv-apply",
"distinct", "take", "sample", "getschema", "not", "and",
"or", "in", "contains", "startswith", "endswith", "between",
"has", "matches", "ago", "now", "bin", "format_datetime",
"True", "False", "NRT", "Scheduled",
}
tables = [t for t in matches if t not in kql_keywords and len(t) > 2]
return sorted(set(tables))
def is_dynamic(value: str) -> bool:
"""Check if a string uses dynamic template fields like {{FieldName}}."""
if not value:
return False
return bool(re.search(r"\{\{.+?\}\}", value))
def load_rule_from_file(filepath: str) -> tuple:
"""
Load and normalise a rule JSON file.
Returns (kind, properties, raw_name, flags_list).
Handles both direct rule objects and ARM envelope format.
"""
flags = []
with open(filepath, "r", encoding="utf-8") as f:
try:
data = json.load(f)
except json.JSONDecodeError as e:
return None, None, None, [f"JSON_PARSE_ERROR: {e}"]
# ARM envelope: has '$schema' and 'resources'
if "$schema" in data and "resources" in data:
resources = data.get("resources", [])
rule_resources = [
r for r in resources
if "alertRules" in r.get("type", "") or "alertRules" in r.get("name", "")
]
if not rule_resources:
return None, None, None, ["NO_ALERT_RULE_RESOURCE_FOUND_IN_ARM_TEMPLATE"]
if len(rule_resources) > 1:
flags.append(f"MULTIPLE_RULES_IN_FILE ({len(rule_resources)} found, using first)")
rule = rule_resources[0]
else:
# Direct rule object
rule = data
kind = rule.get("kind", "UNKNOWN")
properties = rule.get("properties", {})
raw_name = rule.get("name", "")
if not properties:
flags.append("EMPTY_PROPERTIES")
if kind not in RULE_KINDS:
flags.append(f"UNKNOWN_KIND: {kind}")
return kind, properties, raw_name, flags
# ── Audit functions per category ───────────────────────────────────────────────
def audit_identity(kind, props, raw_name, filename):
display_name = props.get("displayName", "")
description = props.get("description", "")
enabled = props.get("enabled", None)
return {
"rule_guid": extract_guid(raw_name),
"display_name": display_name or "MISSING",
"description_present": bool(description),
"description_length": len(description) if description else 0,
"rule_kind": kind,
"enabled": enabled if enabled is not None else "MISSING",
}
def audit_rule_logic(kind, props):
result = {}
has_query = kind in ("Scheduled", "NRT")
if not has_query:
result.update({
"query_present": "N/A",
"query_line_count": "N/A",
"query_tables_referenced": "N/A",
"query_period": "N/A",
"query_frequency": "N/A",
"frequency_period_ratio": "N/A",
"frequency_period_gap_flag": "N/A",
"suppression_enabled": "N/A",
"suppression_duration": "N/A",
"suppression_vs_frequency_flag": "N/A",
})
return result
query = props.get("query", "")
query_present = bool(query and query.strip())
tables = extract_kql_tables(query) if query_present else []
line_count = len(query.strip().splitlines()) if query_present else 0
query_period_raw = props.get("queryPeriod", "")
query_freq_raw = props.get("queryFrequency", "") if kind == "Scheduled" else "N/A (NRT)"
period_mins = parse_iso_duration(query_period_raw)
freq_mins = parse_iso_duration(query_freq_raw) if kind == "Scheduled" else 0
# Gap flag: frequency > period means the rule has blind spots
if kind == "Scheduled" and freq_mins > 0 and period_mins > 0:
ratio = round(freq_mins / period_mins, 2)
gap_flag = freq_mins > period_mins
else:
ratio = "N/A"
gap_flag = "N/A"
suppression = props.get("suppressionEnabled", False)
suppression_dur = props.get("suppressionDuration", "")
suppression_mins = parse_iso_duration(suppression_dur) if suppression else 0
# Suppression longer than frequency means alerts can be permanently suppressed
if kind == "Scheduled" and suppression and freq_mins > 0 and suppression_mins > 0:
suppression_flag = suppression_mins >= freq_mins
else:
suppression_flag = False
result.update({
"query_present": query_present,
"query_line_count": line_count,
"query_tables_referenced": "|".join(tables) if tables else "NONE_DETECTED",
"query_period": query_period_raw or "MISSING",
"query_frequency": query_freq_raw or ("MISSING" if kind == "Scheduled" else "N/A"),
"frequency_period_ratio": ratio,
"frequency_period_gap_flag": gap_flag,
"suppression_enabled": suppression,
"suppression_duration": suppression_dur or "N/A",
"suppression_vs_frequency_flag": suppression_flag,
})
return result
def audit_severity(kind, props):
severity = props.get("severity", "")
valid = severity in VALID_SEVERITIES
if kind in ("Fusion", "MicrosoftSecurityIncidentCreation"):
return {
"severity": severity or "N/A",
"severity_valid": "N/A",
}
return {
"severity": severity or "MISSING",
"severity_valid": valid if severity else "MISSING",
}
def audit_mitre(kind, props):
tactics = props.get("tactics", []) or []
techniques = props.get("techniques", []) or []
unknown_tactics = [t for t in tactics if t not in KNOWN_MITRE_TACTICS]
tactics_valid = len(unknown_tactics) == 0 and len(tactics) > 0
return {
"tactics": "|".join(tactics) if tactics else "NONE",
"tactics_count": len(tactics),
"tactics_valid_flag": tactics_valid,
"unknown_tactics": "|".join(unknown_tactics) if unknown_tactics else "None",
"techniques": "|".join(techniques) if techniques else "NONE",
"techniques_count": len(techniques),
}
def audit_incident(kind, props):
if kind in ("Fusion", "MLBehaviorAnalytics", "ThreatIntelligence"):
return {
"incident_creation_enabled": "N/A",
"grouping_enabled": "N/A",
"grouping_lookback": "N/A",
"grouping_reopen_closed": "N/A",
"grouping_match_only": "N/A",
}
incident_config = props.get("incidentConfiguration", {}) or {}
create_incident = incident_config.get("createIncident", None)
grouping_config = incident_config.get("groupingConfiguration", {}) or {}
grouping_enabled = grouping_config.get("enabled", False)
lookback = grouping_config.get("lookbackDuration", "")
reopen = grouping_config.get("reopenClosedIncident", False)
match_only = grouping_config.get("matchingMethod", "")
return {
"incident_creation_enabled": create_incident if create_incident is not None else "MISSING",
"grouping_enabled": grouping_enabled,
"grouping_lookback": lookback or "N/A",
"grouping_reopen_closed": reopen,
"grouping_match_only": match_only or "N/A",
}
def audit_entity_mapping(kind, props):
if kind in ("Fusion", "MicrosoftSecurityIncidentCreation"):
return {
"entity_mapping_count": "N/A",
"entity_types_mapped": "N/A",
"entity_mapping_present_flag": "N/A",
}
mappings = props.get("entityMappings", []) or []
entity_types = [m.get("entityType", "UNKNOWN") for m in mappings]
return {
"entity_mapping_count": len(mappings),
"entity_types_mapped": "|".join(entity_types) if entity_types else "NONE",
"entity_mapping_present_flag": len(mappings) > 0,
}
def audit_alert_details(kind, props):
if kind not in ("Scheduled", "NRT"):
return {
"alert_name_format": "N/A",
"alert_name_is_dynamic": "N/A",
"alert_description_format": "N/A",
"event_grouping_strategy": "N/A",
}
alert_name = props.get("alertDetailsOverride", {}) or {}
name_format = alert_name.get("alertDisplayNameFormat", "")
desc_format = alert_name.get("alertDescriptionFormat", "")
event_grouping = (props.get("eventGroupingSettings", {}) or {}).get("aggregationKind", "")
return {
"alert_name_format": name_format or "STATIC (not customised)",
"alert_name_is_dynamic": is_dynamic(name_format) if name_format else False,
"alert_description_format": desc_format or "STATIC (not customised)",
"event_grouping_strategy": event_grouping or "MISSING",
}
def audit_metadata(props):
return {
"kind_version": props.get("templateVersion", props.get("version", "MISSING")),
"last_modified_utc": props.get("lastModifiedUtc", "MISSING"),
"created_by": props.get("author", {}).get("name", "MISSING") if isinstance(props.get("author"), dict) else props.get("author", "MISSING"),
"source_name": props.get("source", {}).get("sourceName", "MISSING") if isinstance(props.get("source"), dict) else props.get("source", "MISSING"),
"template_version": props.get("templateVersion", "MISSING"),
}
def build_audit_flags(row: dict, kind: str) -> str:
"""Derive a pipe-separated list of notable findings from the row."""
flags = []
if row.get("display_name") == "MISSING":
flags.append("MISSING_DISPLAY_NAME")
if not row.get("description_present"):
flags.append("NO_DESCRIPTION")
if row.get("enabled") is False:
flags.append("RULE_DISABLED")
if row.get("query_present") is False:
flags.append("EMPTY_QUERY")
if row.get("frequency_period_gap_flag") is True:
flags.append("FREQUENCY_EXCEEDS_PERIOD")
if row.get("suppression_vs_frequency_flag") is True:
flags.append("SUPPRESSION_MAY_SILENCE_ALERTS")
if row.get("severity") == "MISSING":
flags.append("MISSING_SEVERITY")
if row.get("tactics_count") == 0 and kind in ("Scheduled", "NRT"):
flags.append("NO_MITRE_TACTICS")
if row.get("techniques_count") == 0 and kind in ("Scheduled", "NRT"):
flags.append("NO_MITRE_TECHNIQUES")
if row.get("incident_creation_enabled") is False:
flags.append("INCIDENT_CREATION_DISABLED")
if row.get("entity_mapping_present_flag") is False:
flags.append("NO_ENTITY_MAPPING")
if row.get("alert_name_is_dynamic") is False and kind in ("Scheduled", "NRT"):
flags.append("STATIC_ALERT_NAME")
if row.get("unknown_tactics") not in ("None", "N/A", None, ""):
flags.append("UNKNOWN_MITRE_TACTICS")
return "|".join(flags) if flags else "NONE"
# ── Main audit runner ──────────────────────────────────────────────────────────
def audit_rule_file(filepath: str) -> dict:
filename = os.path.basename(filepath)
kind, props, raw_name, parse_flags = load_rule_from_file(filepath)
row = {"file_name": filename, "audit_timestamp": datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ")}
if kind is None:
# File could not be parsed — fill everything as error
for col in CSV_COLUMNS:
if col not in row:
row[col] = "PARSE_ERROR"
row["audit_flags"] = "|".join(parse_flags)
return row
# Run all audit sections
row.update(audit_identity(kind, props, raw_name, filename))
row.update(audit_rule_logic(kind, props))
row.update(audit_severity(kind, props))
row.update(audit_mitre(kind, props))
row.update(audit_incident(kind, props))
row.update(audit_entity_mapping(kind, props))
row.update(audit_alert_details(kind, props))
row.update(audit_metadata(props))
# Non-assessable operational fields
row.update({
"query_syntax_valid": NA,
"tables_exist_in_workspace": NA,
"connector_status": NA,
"rule_last_run": NA,
"rule_last_fired": NA,
})
# Derive audit flags
row["audit_flags"] = "|".join(
parse_flags + ([build_audit_flags(row, kind)] if build_audit_flags(row, kind) != "NONE" else [])
) or "NONE"
return row
def run_audit(input_folder: str, output_csv: str):
json_files = [
os.path.join(input_folder, f)
for f in os.listdir(input_folder)
if f.lower().endswith(".json")
]
if not json_files:
print(f"No JSON files found in '{input_folder}'.")
return
print(f"Found {len(json_files)} JSON file(s) to audit...")
results = []
for filepath in sorted(json_files):
try:
row = audit_rule_file(filepath)
results.append(row)
status = row.get("audit_flags", "")
print(f" ✓ {os.path.basename(filepath):50s} flags: {status}")
except Exception as e:
print(f" ✗ {os.path.basename(filepath):50s} ERROR: {e}")
results.append({
"file_name": os.path.basename(filepath),
"audit_flags": f"UNHANDLED_ERROR: {e}",
"audit_timestamp": datetime.utcnow().strftime("%Y-%m-%dT%H:%M:%SZ"),
})
with open(output_csv, "w", newline="", encoding="utf-8") as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=CSV_COLUMNS, extrasaction="ignore")
writer.writeheader()
for row in results:
# Fill any missing columns with empty string
for col in CSV_COLUMNS:
if col not in row:
row[col] = ""
writer.writerow(row)
print(f"\nAudit complete. {len(results)} rule(s) assessed.")
print(f"Results written to: {output_csv}")
# Summary stats
flag_counts = {}
for row in results:
for flag in row.get("audit_flags", "").split("|"):
if flag and flag != "NONE":
flag_counts[flag] = flag_counts.get(flag, 0) + 1
if flag_counts:
print("\nTop findings across all rules:")
for flag, count in sorted(flag_counts.items(), key=lambda x: -x[1]):
print(f" {count:>4}x {flag}")
# ── Entry point ────────────────────────────────────────────────────────────────
if __name__ == "__main__":
parser = argparse.ArgumentParser(
description="Audit Microsoft Sentinel analytics rule JSON files (ARM export format)."
)
parser.add_argument(
"--input",
default="./rules",
help="Path to folder containing JSON rule files (default: ./rules)",
)
parser.add_argument(
"--output",
default="sentinel_audit_results.csv",
help="Output CSV filename (default: sentinel_audit_results.csv)",
)
args = parser.parse_args()
if not os.path.isdir(args.input):
print(f"Error: input folder '{args.input}' does not exist.")
exit(1)
run_audit(args.input, args.output)
Script 2 — sentinel_analyse.py
The second script takes the raw audit CSV as input and produces three outputs: a scored CSV with PASS/WARN/FAIL ratings per rule, a prioritised remediation backlog, and a summary report with an embedded column dictionary.
The scoring model
| Score range | Rating | Meaning |
|---|---|---|
| 0 | PASS | No issues found |
| 1 – 20% of max | PASS WITH NOTES | Minor issues only, rule is functional |
| 21 – 50% of max | WARN | Meaningful issues that should be addressed |
| > 50% of max | FAIL | Critical issues — rule may not work correctly |
How to run it
# Run analysis on the audit output
python sentinel_analyse.py --input audit_results.csv --output ./analysisOutput files produced
analysis/
├── scored_rules.csv # All rules with audit_score, audit_rating, remediation_actions
├── remediation_backlog.csv # Rules with issues only, sorted by priority rank
└── summary_report.txt # Console report + full column dictionary
├── scored_rules.csv # All rules with audit_score, audit_rating, remediation_actions
├── remediation_backlog.csv # Rules with issues only, sorted by priority rank
└── summary_report.txt # Console report + full column dictionary
Full source code
Copy this file and save it as sentinel_analyse.py in your project folder.
sentinel_analyse.py
Python
Python
"""
Sentinel Audit Results Analyser
=================================
Reads the CSV produced by sentinel_audit.py and produces:
1. A human-readable summary report (console + .txt file)
2. A per-rule scored CSV with PASS/WARN/FAIL ratings
3. A remediation backlog CSV sorted by priority
4. A column dictionary reference (embedded in summary report)
Usage:
python sentinel_analyse.py --input sentinel_audit_results.csv
--input Path to the audit CSV (default: sentinel_audit_results.csv)
--output Output folder for reports (default: ./audit_analysis)
"""
import os
import csv
import argparse
from collections import defaultdict
from datetime import datetime
# ── Scoring weights ────────────────────────────────────────────────────────────
# Each check contributes a penalty score. Higher = more critical.
# Total score per rule: 0 = perfect, higher = more issues.
CHECKS = [
# (column, condition_fn, penalty, severity, remediation_hint)
(
"enabled",
lambda v: v == "False",
5,
"WARN",
"Rule is disabled — confirm intentional and document reason",
),
(
"description_present",
lambda v: v == "False",
3,
"WARN",
"Add a meaningful description explaining what the rule detects and why",
),
(
"description_length",
lambda v: v.isdigit() and int(v) < 30,
2,
"WARN",
"Description is too short (< 30 chars) — likely a placeholder, expand it",
),
(
"query_present",
lambda v: v == "False",
10,
"FAIL",
"Rule has no KQL query — it will never produce alerts",
),
(
"frequency_period_gap_flag",
lambda v: v == "True",
8,
"FAIL",
"Query frequency exceeds lookback period — coverage gaps exist between runs. "
"Set queryFrequency <= queryPeriod",
),
(
"suppression_vs_frequency_flag",
lambda v: v == "True",
9,
"FAIL",
"Suppression duration >= frequency — rule may be permanently silenced after first alert. "
"Reduce suppressionDuration or increase queryFrequency",
),
(
"severity",
lambda v: v == "MISSING",
6,
"FAIL",
"Severity is not set — assign High/Medium/Low/Informational",
),
(
"severity_valid",
lambda v: v == "False",
4,
"WARN",
"Severity value is non-standard — use High, Medium, Low, or Informational",
),
(
"tactics_count",
lambda v: v.isdigit() and int(v) == 0,
4,
"WARN",
"No MITRE ATT&CK tactics assigned — add at least one tactic for coverage visibility",
),
(
"techniques_count",
lambda v: v.isdigit() and int(v) == 0,
2,
"WARN",
"No MITRE techniques assigned — add technique IDs for precise coverage mapping",
),
(
"tactics_valid_flag",
lambda v: v == "False",
3,
"WARN",
"One or more tactic names are not valid MITRE ATT&CK names — check spelling",
),
(
"incident_creation_enabled",
lambda v: v == "False",
7,
"FAIL",
"Incident creation is disabled — alerts fire silently with no SOC incident created. "
"Enable incidentConfiguration.createIncident",
),
(
"entity_mapping_present_flag",
lambda v: v == "False",
5,
"WARN",
"No entity mappings defined — add Account, Host, IP, or other entities to "
"enable investigation pivoting in Sentinel",
),
(
"alert_name_is_dynamic",
lambda v: v == "False",
2,
"WARN",
"Alert name is static — use dynamic fields like {{AccountName}} so each "
"alert is uniquely identifiable at triage",
),
(
"query_line_count",
lambda v: v.isdigit() and 0 < int(v) <= 2,
3,
"WARN",
"Query is very short (1-2 lines) — verify it is not a placeholder or overly broad",
),
]
MAX_POSSIBLE_SCORE = sum(c[2] for c in CHECKS)
SEVERITY_ORDER = {"FAIL": 0, "WARN": 1, "PASS": 2}
# ── Column dictionary ──────────────────────────────────────────────────────────
# Each entry: (column_name, description, good_bad_guidance)
COLUMN_DICTIONARY = [
# --- Identity ---
("--- IDENTITY ---", "", ""),
("file_name", "Source JSON filename on disk",
"Used as fallback identifier if rule name is missing"),
("rule_guid", "Unique GUID extracted from the ARM name field",
"UNKNOWN = GUID could not be parsed from the name field"),
("display_name", "Human-readable rule name shown in Sentinel",
"MISSING = rule has no display name set"),
("description_present", "Whether a description field exists and is non-empty",
"True = good | False = no description"),
("description_length", "Character count of the description",
"0 = no description | <30 chars usually means a placeholder"),
("rule_kind", "Rule type: Scheduled, NRT, Fusion, MicrosoftSecurityIncidentCreation, etc.",
"Determines which other columns are applicable"),
("enabled", "Whether the rule is active in Sentinel",
"True = active | False = disabled, will not fire"),
# --- Rule logic ---
("--- RULE LOGIC ---", "", ""),
("query_present", "Whether a non-empty KQL query exists",
"False = rule has no query and will never detect anything"),
("query_line_count", "Number of lines in the KQL query",
"1-2 lines may indicate an overly broad or placeholder query"),
("query_tables_referenced", "Pipe-separated list of data tables the KQL appears to query",
"NONE_DETECTED = no recognisable table names found, check query manually"),
("query_period", "How far back the query looks for data (e.g. PT1H=1hr, P1D=1day)",
"MISSING = rule has no lookback window defined"),
("query_frequency", "How often the rule runs (e.g. PT5M=every 5min, PT1H=every hour)",
"NRT rules do not have this field — shows N/A"),
("frequency_period_ratio", "frequency / period — relationship between run interval and lookback window",
"<=0.5 EXCELLENT | <=1.0 GOOD (no gaps) | >1.0 WARN/CRITICAL (coverage gaps exist)"),
("frequency_period_gap_flag", "True if frequency exceeds period — the rule has blind spots between runs",
"False = no gaps (good) | True = coverage gaps exist (bad)"),
("suppression_enabled", "Whether alert suppression is turned on",
"True = suppression active, verify duration vs frequency"),
("suppression_duration", "How long alerts are suppressed after the rule fires",
"Should always be shorter than the query frequency"),
("suppression_vs_frequency_flag", "True if suppression duration >= frequency — alerts may be permanently silenced",
"True = critical misconfiguration, rule may never alert twice"),
# --- Severity & MITRE ---
("--- SEVERITY & MITRE ---", "", ""),
("severity", "Alert severity: High, Medium, Low, Informational",
"MISSING = not set | All rules being Informational is a tuning red flag"),
("severity_valid", "Whether severity is one of the four accepted values",
"False = non-standard severity value"),
("tactics", "Pipe-separated MITRE ATT&CK tactic names assigned to the rule",
"NONE = no tactics mapped, reduces coverage visibility"),
("tactics_count", "Number of MITRE tactics assigned",
"0 = no MITRE coverage | 1-3 typical | >5 may indicate over-tagging"),
("tactics_valid_flag", "Whether all assigned tactics are recognised MITRE ATT&CK names",
"False = typo or non-standard tactic name present"),
("unknown_tactics", "Any tactic names not in the known MITRE ATT&CK list",
"None = all tactics are valid"),
("techniques", "Pipe-separated MITRE technique/sub-technique IDs (e.g. T1078, T1078.001)",
"NONE = no technique-level mapping"),
("techniques_count", "Number of MITRE techniques assigned",
"0 = no technique-level mapping"),
# --- Incident configuration ---
("--- INCIDENT CONFIGURATION ---", "", ""),
("incident_creation_enabled", "Whether the rule creates a Sentinel incident when it fires",
"False = alerts fire silently with no SOC incident created"),
("grouping_enabled", "Whether related alerts are grouped into a single incident",
"True = reduces alert noise (generally preferred)"),
("grouping_lookback", "Time window used to group related alerts together",
"Should align with the rule's query period"),
("grouping_reopen_closed", "Whether a new alert reopens a previously closed incident",
"Depends on SOC process — neither value is universally correct"),
("grouping_match_only", "Grouping method: AllEntities, AnyAlert, Selected, etc.",
"AllEntities = strictest | AnyAlert = broadest grouping"),
# --- Entity mapping & alert details ---
("--- ENTITY MAPPING & ALERT DETAILS ---", "", ""),
("entity_mapping_count", "Number of entity mappings defined on the rule",
"0 = no entities mapped, limits investigation pivoting in Sentinel"),
("entity_types_mapped", "Pipe-separated entity types (Account, Host, IP, URL, etc.)",
"NONE = no entity context attached to alerts"),
("entity_mapping_present_flag", "Whether at least one entity is mapped",
"False = no entity mapping at all (should be remediated)"),
("alert_name_format", "The alert name template — static string or dynamic formula",
"STATIC = every alert has the same name, harder to triage at volume"),
("alert_name_is_dynamic", "Whether the alert name uses dynamic fields like {{AccountName}}",
"True = alert names include contextual data (preferred)"),
("alert_description_format", "The alert description template",
"STATIC = generic description for every alert instance"),
("event_grouping_strategy", "How events within a single rule run are grouped",
"AlertPerResult = one alert per matching row (can be noisy at scale)"),
# --- Metadata ---
("--- METADATA ---", "", ""),
("kind_version", "Version of the rule template",
"MISSING = no version tracked, harder to manage changes"),
("last_modified_utc", "Timestamp of last modification (from export — may not reflect live state)",
"MISSING = modification history not captured in the export"),
("created_by", "Author name from the rule metadata",
"MISSING = no ownership information"),
("source_name", "Origin of the rule: Content Hub solution name or custom",
"Helps distinguish vendor templates from custom rules"),
("template_version", "Version string of the originating Content Hub template",
"MISSING = custom rule or version not tracked"),
# --- Operational health ---
("--- OPERATIONAL HEALTH (requires live access) ---", "", ""),
("query_syntax_valid", "Whether the KQL query executes without errors",
"N/A - No Live Access"),
("tables_exist_in_workspace", "Whether the tables referenced in the query are actively ingesting",
"N/A - No Live Access"),
("connector_status", "Whether the required data connectors are enabled and connected",
"N/A - No Live Access"),
("rule_last_run", "Timestamp of the most recent rule execution",
"N/A - No Live Access"),
("rule_last_fired", "Timestamp of the last time the rule produced an alert",
"N/A - No Live Access"),
# --- Audit metadata ---
("--- AUDIT METADATA ---", "", ""),
("audit_flags", "Pipe-separated list of all issues found for this rule",
"NONE = rule passed all assessable checks"),
("audit_timestamp", "When this audit row was generated (UTC)",
"Used to version the audit run"),
# --- Analysis columns (added by this script) ---
("--- ANALYSIS COLUMNS (added by sentinel_analyse.py) ---", "", ""),
("audit_score", "Penalty score: 0 = perfect, higher = more/worse issues",
f"Range: 0 to {MAX_POSSIBLE_SCORE} | 0 = PASS | 1-{int(MAX_POSSIBLE_SCORE*0.2)} = PASS WITH NOTES | "
f"{int(MAX_POSSIBLE_SCORE*0.2)+1}-{int(MAX_POSSIBLE_SCORE*0.5)} = WARN | "
f"{int(MAX_POSSIBLE_SCORE*0.5)+1}+ = FAIL"),
("audit_rating", "Overall rule rating derived from audit_score",
"PASS | PASS WITH NOTES | WARN | FAIL"),
("findings_count", "Total number of individual issues found on this rule",
"0 = no issues"),
("fail_count", "Number of FAIL-severity issues on this rule",
"0 = no critical issues"),
("warn_count", "Number of WARN-severity issues on this rule",
"0 = no warnings"),
("freq_ratio_rating", "Human-readable interpretation of frequency_period_ratio",
"EXCELLENT / GOOD / WARNING / CRITICAL"),
("remediation_actions", "Pipe-separated list of specific fix instructions for every issue found",
"Empty = no issues to fix"),
]
# ── Helpers ────────────────────────────────────────────────────────────────────
def rate_frequency_ratio(value: str) -> str:
"""Return a human-readable rating for frequency_period_ratio."""
try:
ratio = float(value)
except (ValueError, TypeError):
return "N/A"
if ratio <= 0:
return "N/A"
if ratio <= 0.5:
return "EXCELLENT (large overlap, no gaps)"
if ratio <= 1.0:
return "GOOD (some overlap, no gaps)"
if ratio <= 2.0:
return "WARNING (frequency > period, small gaps)"
return "CRITICAL (frequency >> period, large coverage gaps)"
def overall_rating(score: int, max_score: int) -> str:
pct = score / max_score if max_score else 0
if pct == 0:
return "PASS"
if pct <= 0.2:
return "PASS WITH NOTES"
if pct <= 0.5:
return "WARN"
return "FAIL"
def severity_badge(s: str) -> str:
badges = {"FAIL": "[FAIL]", "WARN": "[WARN]", "PASS": "[PASS]", "PASS WITH NOTES": "[NOTE]"}
return badges.get(s, s)
# ── Core analysis ──────────────────────────────────────────────────────────────
def analyse_rule(row: dict) -> dict:
"""Score a single rule row and return enriched result."""
findings = []
score = 0
kind = row.get("rule_kind", "")
for col, condition, penalty, sev, hint in CHECKS:
val = row.get(col, "")
# Skip checks that don't apply to this rule kind
logic_only = {"query_present", "frequency_period_gap_flag",
"suppression_vs_frequency_flag", "query_line_count",
"alert_name_is_dynamic"}
incident_only = {"incident_creation_enabled"}
if col in logic_only and kind not in ("Scheduled", "NRT"):
continue
if col in incident_only and kind in ("Fusion", "MLBehaviorAnalytics"):
continue
if condition(str(val)):
findings.append({
"column": col,
"severity": sev,
"penalty": penalty,
"hint": hint,
})
score += penalty
rating = overall_rating(score, MAX_POSSIBLE_SCORE)
# Enrich frequency ratio
freq_rating = rate_frequency_ratio(row.get("frequency_period_ratio", ""))
return {
"score": score,
"rating": rating,
"findings": findings,
"freq_ratio_rating": freq_rating,
}
def analyse_all(rows: list) -> list:
results = []
for row in rows:
analysis = analyse_rule(row)
results.append({**row, **{
"audit_score": analysis["score"],
"audit_rating": analysis["rating"],
"findings_count": len(analysis["findings"]),
"fail_count": sum(1 for f in analysis["findings"] if f["severity"] == "FAIL"),
"warn_count": sum(1 for f in analysis["findings"] if f["severity"] == "WARN"),
"freq_ratio_rating": analysis["freq_ratio_rating"],
"remediation_actions": " | ".join(f["hint"] for f in analysis["findings"]),
}})
return results
# ── Report generation ──────────────────────────────────────────────────────────
def format_column_dictionary() -> list:
"""Format the column dictionary as text lines for the report."""
lines = []
lines.append("=" * 70)
lines.append(" COLUMN DICTIONARY")
lines.append(" Reference guide for every column in the audit CSV output")
lines.append("=" * 70)
col_w = 38
desc_w = 35
lines.append(f" {'COLUMN':<{col_w}} {'DESCRIPTION':<{desc_w}} GOOD / BAD GUIDANCE")
lines.append(" " + "-" * 66)
for entry in COLUMN_DICTIONARY:
col, desc, guidance = entry
# Section headers
if col.startswith("---"):
lines.append("")
else:
# Wrap long descriptions
col_str = col[:col_w]
desc_str = desc[:desc_w]
lines.append(f" {col_str:<{col_w}} {desc_str:<{desc_w}} {guidance}")
lines.append("")
return lines
def print_summary(results: list, output_file=None):
lines = []
total = len(results)
pass_count = sum(1 for r in results if r["audit_rating"] in ("PASS", "PASS WITH NOTES"))
warn_count = sum(1 for r in results if r["audit_rating"] == "WARN")
fail_count = sum(1 for r in results if r["audit_rating"] == "FAIL")
disabled = sum(1 for r in results if str(r.get("enabled", "")) == "False")
no_entity = sum(1 for r in results if str(r.get("entity_mapping_present_flag", "")) == "False")
no_tactics = sum(1 for r in results if str(r.get("tactics_count", "0")).isdigit()
and int(r.get("tactics_count", 0)) == 0)
no_incident = sum(1 for r in results if str(r.get("incident_creation_enabled", "")) == "False")
gap_flag = sum(1 for r in results if str(r.get("frequency_period_gap_flag", "")) == "True")
suppression_flag = sum(1 for r in results if str(r.get("suppression_vs_frequency_flag", "")) == "True")
lines.append("=" * 70)
lines.append(" SENTINEL ANALYTICS RULE AUDIT — ANALYSIS REPORT")
lines.append(f" Generated: {datetime.utcnow().strftime('%Y-%m-%d %H:%M UTC')}")
lines.append("=" * 70)
lines.append("")
lines.append("OVERVIEW")
lines.append("-" * 40)
lines.append(f" Total rules assessed : {total}")
lines.append(f" PASS / PASS WITH NOTES : {pass_count} ({pct(pass_count, total)}%)")
lines.append(f" WARN : {warn_count} ({pct(warn_count, total)}%)")
lines.append(f" FAIL : {fail_count} ({pct(fail_count, total)}%)")
lines.append("")
lines.append("TOP FINDINGS")
lines.append("-" * 40)
lines.append(f" Rules disabled : {disabled} ({pct(disabled, total)}%)")
lines.append(f" No entity mapping : {no_entity} ({pct(no_entity, total)}%)")
lines.append(f" No MITRE tactics : {no_tactics} ({pct(no_tactics, total)}%)")
lines.append(f" Incident creation disabled : {no_incident} ({pct(no_incident, total)}%)")
lines.append(f" Coverage gaps (freq > period) : {gap_flag} ({pct(gap_flag, total)}%)")
lines.append(f" Suppression silencing alerts : {suppression_flag} ({pct(suppression_flag, total)}%)")
lines.append("")
# Severity distribution
sev_dist = defaultdict(int)
for r in results:
sev_dist[r.get("severity", "MISSING")] += 1
lines.append("SEVERITY DISTRIBUTION")
lines.append("-" * 40)
for sev, count in sorted(sev_dist.items(), key=lambda x: -x[1]):
lines.append(f" {sev:<20} : {count} ({pct(count, total)}%)")
lines.append("")
# Rule kind distribution
kind_dist = defaultdict(int)
for r in results:
kind_dist[r.get("rule_kind", "UNKNOWN")] += 1
lines.append("RULE KIND DISTRIBUTION")
lines.append("-" * 40)
for kind, count in sorted(kind_dist.items(), key=lambda x: -x[1]):
lines.append(f" {kind:<40} : {count}")
lines.append("")
# MITRE tactic coverage
tactic_counts = defaultdict(int)
for r in results:
tactics = r.get("tactics", "")
if tactics and tactics not in ("NONE", "N/A", ""):
for t in tactics.split("|"):
tactic_counts[t.strip()] += 1
if tactic_counts:
lines.append("MITRE TACTIC COVERAGE (rules per tactic)")
lines.append("-" * 40)
for tactic, count in sorted(tactic_counts.items(), key=lambda x: -x[1]):
bar = "#" * min(count, 40)
lines.append(f" {tactic:<35} : {count:>3} {bar}")
lines.append("")
# Top 10 worst rules
sorted_results = sorted(results, key=lambda x: -x["audit_score"])
lines.append("TOP 10 RULES NEEDING ATTENTION")
lines.append("-" * 40)
for r in sorted_results[:10]:
lines.append(
f" {severity_badge(r['audit_rating'])} "
f"Score:{r['audit_score']:>3} "
f"FAIL:{r['fail_count']} WARN:{r['warn_count']} "
f"{r.get('display_name', r.get('file_name', 'UNKNOWN'))[:50]}"
)
lines.append("")
lines.append("=" * 70)
lines.append(" OUTPUT FILES")
lines.append(" - scored_rules.csv : All rules with scores and ratings")
lines.append(" - remediation_backlog.csv: Rules with issues, sorted by priority")
lines.append(" - summary_report.txt : This report (includes column dictionary)")
lines.append("=" * 70)
lines.append("")
lines.extend(format_column_dictionary())
report = "\n".join(lines)
print(report)
if output_file:
with open(output_file, "w", encoding="utf-8") as f:
f.write(report)
return report
def pct(n, total):
if total == 0:
return 0
return round(n / total * 100)
def write_scored_csv(results: list, filepath: str):
if not results:
return
extra_cols = ["audit_score", "audit_rating", "findings_count",
"fail_count", "warn_count", "freq_ratio_rating", "remediation_actions"]
# Put extra cols right after audit_flags
base_cols = list(results[0].keys())
ordered = [c for c in base_cols if c not in extra_cols]
# Insert extra cols before audit_timestamp
insert_at = ordered.index("audit_timestamp") if "audit_timestamp" in ordered else len(ordered)
for i, col in enumerate(extra_cols):
ordered.insert(insert_at + i, col)
with open(filepath, "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=ordered, extrasaction="ignore")
writer.writeheader()
writer.writerows(results)
def write_remediation_backlog(results: list, filepath: str):
"""Write only rules with issues, sorted by score descending, with clear action items."""
issues = [r for r in results if r["audit_rating"] not in ("PASS",)]
issues_sorted = sorted(issues, key=lambda x: (
SEVERITY_ORDER.get(x["audit_rating"], 99), -x["audit_score"]
))
cols = [
"priority_rank", "audit_rating", "audit_score", "fail_count", "warn_count",
"display_name", "rule_kind", "enabled", "severity", "file_name", "rule_guid",
"freq_ratio_rating", "remediation_actions", "audit_flags",
]
with open(filepath, "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=cols, extrasaction="ignore")
writer.writeheader()
for i, row in enumerate(issues_sorted, 1):
row["priority_rank"] = i
writer.writerow(row)
return len(issues_sorted)
# ── Entry point ────────────────────────────────────────────────────────────────
def run(input_csv: str, output_folder: str):
if not os.path.isfile(input_csv):
print(f"Error: input file '{input_csv}' not found.")
return
os.makedirs(output_folder, exist_ok=True)
with open(input_csv, "r", encoding="utf-8") as f:
reader = csv.DictReader(f)
rows = list(reader)
if not rows:
print("No rows found in the audit CSV.")
return
print(f"Analysing {len(rows)} rule(s)...\n")
results = analyse_all(rows)
# Write outputs
scored_path = os.path.join(output_folder, "scored_rules.csv")
backlog_path = os.path.join(output_folder, "remediation_backlog.csv")
report_path = os.path.join(output_folder, "summary_report.txt")
write_scored_csv(results, scored_path)
backlog_count = write_remediation_backlog(results, backlog_path)
print_summary(results, report_path)
print(f"\nFiles written to '{output_folder}':")
print(f" scored_rules.csv ({len(results)} rules)")
print(f" remediation_backlog.csv ({backlog_count} rules needing attention)")
print(f" summary_report.txt")
if __name__ == "__main__":
parser = argparse.ArgumentParser(
description="Analyse Sentinel audit CSV and produce scored reports."
)
parser.add_argument(
"--input",
default="sentinel_audit_results.csv",
help="Path to audit CSV from sentinel_audit.py (default: sentinel_audit_results.csv)",
)
parser.add_argument(
"--output",
default="./audit_analysis",
help="Output folder for analysis reports (default: ./audit_analysis)",
)
args = parser.parse_args()
run(args.input, args.output)
The HTML dashboard
The HTML dashboard is a fully self-contained single-file application. No server, no Python, no dependencies — open sentinel_dashboard.html in any modern browser and drop in your audit CSV.
The five tabs
1
Overview
Summary metric cards, donut chart of PASS/WARN/FAIL distribution, top findings bar chart, severity and rule kind distributions, and a top 10 worst rules table.
2
MITRE Coverage
Heatmap of all 15 ATT&CK tactics showing how many rules cover each one, top techniques bar chart, and High-severity rules per tactic.
3
All Rules
Full searchable, sortable, filterable table. Live search across name, GUID, kind, and flags. Click any column header to sort. Paginated at 30 per page.
4
Remediation Backlog
Every rule with issues, ranked by priority, with all findings expanded inline showing the specific fix instruction for each.
5
Column Dictionary
Full reference guide for all 48 output columns — always available even without loading a CSV.
Browser compatibility
Works in Chrome, Edge, Firefox, and Safari. Chrome or Edge recommended for best CSV drag-and-drop handling.
Full source code
Copy this and save it as sentinel_dashboard.html. Open it directly in your browser — no server needed.
sentinel_dashboard.html
HTML
HTML
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Sentinel Rule Audit Dashboard</title>
<link href="https://fonts.googleapis.com/css2?family=IBM+Plex+Mono:wght@400;500&family=IBM+Plex+Sans:wght@300;400;500;600&display=swap" rel="stylesheet">
<style>
:root {
--bg: #0a0c0f;
--bg2: #111418;
--bg3: #181c22;
--border: #1e2530;
--border2: #2a3340;
--text: #c8d0db;
--text2: #6b7a8d;
--text3: #3d4a58;
--amber: #f59e0b;
--amber-bg: #1a1400;
--red: #ef4444;
--red-bg: #160808;
--green: #22c55e;
--green-bg: #061208;
--blue: #3b82f6;
--blue-bg: #060d1a;
--mono: 'IBM Plex Mono', monospace;
--sans: 'IBM Plex Sans', sans-serif;
}
* { box-sizing: border-box; margin: 0; padding: 0; }
body {
background: var(--bg);
color: var(--text);
font-family: var(--sans);
font-size: 14px;
line-height: 1.6;
min-height: 100vh;
}
/* ── HEADER ── */
header {
border-bottom: 1px solid var(--border);
padding: 0 32px;
display: flex;
align-items: center;
justify-content: space-between;
height: 56px;
background: var(--bg2);
position: sticky;
top: 0;
z-index: 100;
}
.logo {
display: flex;
align-items: center;
gap: 12px;
}
.logo-icon {
width: 28px; height: 28px;
border: 1.5px solid var(--amber);
display: flex; align-items: center; justify-content: center;
font-family: var(--mono);
font-size: 12px;
color: var(--amber);
font-weight: 500;
}
.logo-text {
font-family: var(--mono);
font-size: 13px;
font-weight: 500;
color: var(--text);
letter-spacing: 0.08em;
text-transform: uppercase;
}
.logo-sub {
font-family: var(--mono);
font-size: 11px;
color: var(--text2);
letter-spacing: 0.05em;
}
.header-right {
display: flex;
align-items: center;
gap: 16px;
}
#run-info {
font-family: var(--mono);
font-size: 11px;
color: var(--text3);
}
.load-btn {
font-family: var(--mono);
font-size: 11px;
font-weight: 500;
letter-spacing: 0.06em;
text-transform: uppercase;
padding: 7px 16px;
border: 1px solid var(--amber);
background: transparent;
color: var(--amber);
cursor: pointer;
transition: background 0.15s;
}
.load-btn:hover { background: rgba(245,158,11,0.08); }
/* ── NAV TABS ── */
nav {
display: flex;
gap: 0;
border-bottom: 1px solid var(--border);
padding: 0 32px;
background: var(--bg2);
overflow-x: auto;
}
.tab {
font-family: var(--mono);
font-size: 11px;
letter-spacing: 0.07em;
text-transform: uppercase;
padding: 12px 20px;
border: none;
background: none;
color: var(--text2);
cursor: pointer;
border-bottom: 2px solid transparent;
transition: color 0.15s, border-color 0.15s;
white-space: nowrap;
}
.tab:hover { color: var(--text); }
.tab.active { color: var(--amber); border-bottom-color: var(--amber); }
/* ── MAIN LAYOUT ── */
main { padding: 28px 32px; max-width: 1400px; }
.section { display: none; }
.section.active { display: block; }
/* ── DROP ZONE ── */
#drop-zone {
border: 1px dashed var(--border2);
padding: 80px 40px;
text-align: center;
cursor: pointer;
transition: border-color 0.2s, background 0.2s;
margin: 60px auto;
max-width: 600px;
}
#drop-zone:hover, #drop-zone.drag-over {
border-color: var(--amber);
background: rgba(245,158,11,0.03);
}
.drop-icon {
font-family: var(--mono);
font-size: 32px;
color: var(--text3);
margin-bottom: 16px;
}
.drop-title {
font-family: var(--mono);
font-size: 14px;
font-weight: 500;
color: var(--text);
margin-bottom: 8px;
letter-spacing: 0.05em;
}
.drop-sub {
font-size: 12px;
color: var(--text2);
}
/* ── METRIC CARDS ── */
.metrics-grid {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(160px, 1fr));
gap: 1px;
background: var(--border);
border: 1px solid var(--border);
margin-bottom: 28px;
}
.metric {
background: var(--bg2);
padding: 20px 20px 16px;
}
.metric-label {
font-family: var(--mono);
font-size: 10px;
letter-spacing: 0.1em;
text-transform: uppercase;
color: var(--text2);
margin-bottom: 10px;
}
.metric-value {
font-family: var(--mono);
font-size: 28px;
font-weight: 500;
line-height: 1;
margin-bottom: 4px;
}
.metric-sub {
font-size: 11px;
color: var(--text2);
font-family: var(--mono);
}
.metric.pass .metric-value { color: var(--green); }
.metric.warn .metric-value { color: var(--amber); }
.metric.fail .metric-value { color: var(--red); }
.metric.info .metric-value { color: var(--blue); }
/* ── SECTION HEADINGS ── */
.section-title {
font-family: var(--mono);
font-size: 11px;
font-weight: 500;
letter-spacing: 0.12em;
text-transform: uppercase;
color: var(--text2);
margin-bottom: 14px;
padding-bottom: 8px;
border-bottom: 1px solid var(--border);
display: flex;
align-items: center;
gap: 10px;
}
.section-title::before {
content: '';
display: inline-block;
width: 3px;
height: 12px;
background: var(--amber);
}
/* ── TWO-COL LAYOUT ── */
.two-col { display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin-bottom: 28px; }
.three-col { display: grid; grid-template-columns: 1fr 1fr 1fr; gap: 20px; margin-bottom: 28px; }
@media (max-width: 900px) {
.two-col, .three-col { grid-template-columns: 1fr; }
}
/* ── PANEL ── */
.panel {
background: var(--bg2);
border: 1px solid var(--border);
padding: 20px;
}
/* ── BAR CHART ── */
.bar-row {
display: flex;
align-items: center;
gap: 10px;
margin-bottom: 8px;
font-size: 12px;
}
.bar-label {
width: 180px;
font-family: var(--mono);
font-size: 11px;
color: var(--text);
flex-shrink: 0;
white-space: nowrap;
overflow: hidden;
text-overflow: ellipsis;
}
.bar-track {
flex: 1;
height: 6px;
background: var(--bg3);
position: relative;
}
.bar-fill {
height: 100%;
background: var(--amber);
transition: width 0.6s cubic-bezier(0.4,0,0.2,1);
}
.bar-fill.green { background: var(--green); }
.bar-fill.red { background: var(--red); }
.bar-fill.blue { background: var(--blue); }
.bar-count {
font-family: var(--mono);
font-size: 11px;
color: var(--text2);
width: 32px;
text-align: right;
flex-shrink: 0;
}
/* ── FINDINGS LIST ── */
.finding-row {
display: flex;
align-items: center;
justify-content: space-between;
padding: 9px 0;
border-bottom: 1px solid var(--border);
gap: 12px;
}
.finding-row:last-child { border-bottom: none; }
.finding-name {
font-family: var(--mono);
font-size: 11px;
color: var(--text);
flex: 1;
}
.finding-count {
font-family: var(--mono);
font-size: 13px;
font-weight: 500;
min-width: 36px;
text-align: right;
}
.finding-pct {
font-family: var(--mono);
font-size: 10px;
color: var(--text2);
min-width: 36px;
text-align: right;
}
/* ── BADGES ── */
.badge {
font-family: var(--mono);
font-size: 10px;
font-weight: 500;
letter-spacing: 0.06em;
padding: 2px 8px;
display: inline-block;
}
.badge-fail { background: var(--red-bg); color: var(--red); border: 1px solid rgba(239,68,68,0.3); }
.badge-warn { background: var(--amber-bg); color: var(--amber); border: 1px solid rgba(245,158,11,0.3); }
.badge-pass { background: var(--green-bg); color: var(--green); border: 1px solid rgba(34,197,94,0.3); }
.badge-note { background: var(--blue-bg); color: var(--blue); border: 1px solid rgba(59,130,246,0.3); }
.badge-na { background: var(--bg3); color: var(--text2); border: 1px solid var(--border2); }
/* ── MITRE HEATMAP ── */
.mitre-grid {
display: flex;
flex-wrap: wrap;
gap: 6px;
}
.mitre-cell {
font-family: var(--mono);
font-size: 10px;
padding: 8px 12px;
border: 1px solid var(--border2);
text-align: center;
min-width: 130px;
flex: 1;
cursor: default;
transition: border-color 0.15s;
}
.mitre-cell-name { color: var(--text); font-weight: 500; margin-bottom: 4px; }
.mitre-cell-count { font-size: 18px; font-weight: 500; }
.mitre-cell.zero { opacity: 0.35; }
.mitre-cell.low { border-color: rgba(245,158,11,0.2); }
.mitre-cell.medium { border-color: rgba(245,158,11,0.5); background: rgba(245,158,11,0.03); }
.mitre-cell.high { border-color: var(--amber); background: rgba(245,158,11,0.07); }
.mitre-cell.low .mitre-cell-count { color: var(--text2); }
.mitre-cell.medium .mitre-cell-count { color: var(--amber); }
.mitre-cell.high .mitre-cell-count { color: var(--amber); }
.mitre-cell.zero .mitre-cell-count { color: var(--text3); }
/* ── RULES TABLE ── */
.table-wrap {
overflow-x: auto;
border: 1px solid var(--border);
margin-bottom: 28px;
}
table {
width: 100%;
border-collapse: collapse;
font-size: 12px;
}
thead th {
font-family: var(--mono);
font-size: 10px;
font-weight: 500;
letter-spacing: 0.08em;
text-transform: uppercase;
color: var(--text2);
background: var(--bg3);
padding: 10px 14px;
text-align: left;
border-bottom: 1px solid var(--border);
white-space: nowrap;
cursor: pointer;
user-select: none;
}
thead th:hover { color: var(--text); }
thead th.sorted { color: var(--amber); }
tbody tr {
border-bottom: 1px solid var(--border);
transition: background 0.1s;
}
tbody tr:hover { background: var(--bg3); }
tbody td {
padding: 9px 14px;
font-family: var(--mono);
font-size: 11px;
color: var(--text);
vertical-align: middle;
white-space: nowrap;
max-width: 260px;
overflow: hidden;
text-overflow: ellipsis;
}
.td-name { color: var(--text); max-width: 220px; }
.td-muted { color: var(--text2); }
.td-score { font-weight: 500; }
.score-high { color: var(--red); }
.score-med { color: var(--amber); }
.score-low { color: var(--green); }
/* ── SEARCH / FILTER ── */
.table-controls {
display: flex;
gap: 10px;
margin-bottom: 12px;
flex-wrap: wrap;
}
.search-input {
background: var(--bg2);
border: 1px solid var(--border2);
color: var(--text);
font-family: var(--mono);
font-size: 12px;
padding: 8px 14px;
flex: 1;
min-width: 200px;
outline: none;
}
.search-input:focus { border-color: var(--amber); }
.filter-btn {
font-family: var(--mono);
font-size: 11px;
padding: 8px 14px;
border: 1px solid var(--border2);
background: var(--bg2);
color: var(--text2);
cursor: pointer;
transition: all 0.15s;
letter-spacing: 0.05em;
}
.filter-btn:hover { border-color: var(--text2); color: var(--text); }
.filter-btn.active-fail { border-color: var(--red); color: var(--red); background: var(--red-bg); }
.filter-btn.active-warn { border-color: var(--amber); color: var(--amber); background: var(--amber-bg); }
.filter-btn.active-pass { border-color: var(--green); color: var(--green); background: var(--green-bg); }
/* ── REMEDIATION TABLE ── */
.rem-card {
background: var(--bg2);
border: 1px solid var(--border);
margin-bottom: 10px;
padding: 16px 20px;
}
.rem-header {
display: flex;
align-items: flex-start;
gap: 14px;
margin-bottom: 12px;
}
.rem-rank {
font-family: var(--mono);
font-size: 11px;
color: var(--text3);
min-width: 28px;
}
.rem-name {
font-family: var(--mono);
font-size: 13px;
font-weight: 500;
color: var(--text);
flex: 1;
}
.rem-kind {
font-family: var(--mono);
font-size: 10px;
color: var(--text2);
margin-top: 3px;
}
.rem-actions { margin-top: 10px; }
.rem-action {
display: flex;
gap: 10px;
padding: 6px 0;
border-top: 1px solid var(--border);
font-size: 12px;
color: var(--text2);
align-items: flex-start;
}
.rem-action-sev {
font-family: var(--mono);
font-size: 10px;
min-width: 34px;
margin-top: 2px;
}
.rem-action-sev.fail { color: var(--red); }
.rem-action-sev.warn { color: var(--amber); }
.rem-action-text { flex: 1; line-height: 1.5; }
/* ── COLUMN DICTIONARY ── */
.dict-group { margin-bottom: 28px; }
.dict-group-title {
font-family: var(--mono);
font-size: 10px;
letter-spacing: 0.12em;
text-transform: uppercase;
color: var(--amber);
padding: 6px 0;
margin-bottom: 2px;
border-bottom: 1px solid var(--border);
}
.dict-row {
display: grid;
grid-template-columns: 220px 1fr 1fr;
gap: 16px;
padding: 8px 0;
border-bottom: 1px solid var(--border);
align-items: start;
}
.dict-row:last-child { border-bottom: none; }
.dict-col { font-family: var(--mono); font-size: 11px; color: var(--blue); }
.dict-desc { font-size: 12px; color: var(--text); }
.dict-guide { font-size: 11px; color: var(--text2); font-family: var(--mono); }
@media (max-width: 800px) {
.dict-row { grid-template-columns: 1fr; }
}
/* ── DONUT CHART ── */
.donut-wrap { display: flex; align-items: center; gap: 24px; }
.donut-legend { display: flex; flex-direction: column; gap: 8px; }
.legend-item { display: flex; align-items: center; gap: 8px; font-family: var(--mono); font-size: 11px; }
.legend-dot { width: 8px; height: 8px; flex-shrink: 0; }
/* ── ANIMATIONS ── */
@keyframes fadeIn { from { opacity: 0; transform: translateY(8px); } to { opacity: 1; transform: translateY(0); } }
.section.active { animation: fadeIn 0.25s ease; }
/* ── SCROLLBAR ── */
::-webkit-scrollbar { width: 6px; height: 6px; }
::-webkit-scrollbar-track { background: var(--bg); }
::-webkit-scrollbar-thumb { background: var(--border2); }
::-webkit-scrollbar-thumb:hover { background: var(--text3); }
.empty-state {
text-align: center;
padding: 48px;
font-family: var(--mono);
font-size: 12px;
color: var(--text3);
}
.pagination {
display: flex;
align-items: center;
gap: 8px;
padding: 12px 0;
font-family: var(--mono);
font-size: 11px;
color: var(--text2);
}
.page-btn {
background: var(--bg2);
border: 1px solid var(--border2);
color: var(--text2);
font-family: var(--mono);
font-size: 11px;
padding: 4px 10px;
cursor: pointer;
}
.page-btn:hover:not(:disabled) { border-color: var(--amber); color: var(--amber); }
.page-btn:disabled { opacity: 0.3; cursor: default; }
.page-info { flex: 1; }
input[type="file"] { display: none; }
</style>
</head>
<body>
<header>
<div class="logo">
<div class="logo-icon">MS</div>
<div>
<div class="logo-text">Sentinel Audit</div>
<div class="logo-sub">Analytics Rule Dashboard</div>
</div>
</div>
<div class="header-right">
<span id="run-info">No data loaded</span>
<label class="load-btn" for="csv-file-input">Load CSV</label>
<input type="file" id="csv-file-input" accept=".csv">
</div>
</header>
<nav>
<button class="tab active" data-tab="overview">Overview</button>
<button class="tab" data-tab="coverage">MITRE Coverage</button>
<button class="tab" data-tab="rules">All Rules</button>
<button class="tab" data-tab="backlog">Remediation Backlog</button>
<button class="tab" data-tab="dictionary">Column Dictionary</button>
</nav>
<main>
<!-- ── OVERVIEW ── -->
<div id="tab-overview" class="section active">
<div id="drop-zone">
<div class="drop-icon">[ ↓ ]</div>
<div class="drop-title">Drop sentinel_audit_results.csv here</div>
<div class="drop-sub">or click "Load CSV" in the top-right corner</div>
</div>
</div>
<!-- ── MITRE ── -->
<div id="tab-coverage" class="section">
<div class="empty-state" id="mitre-empty">Load a CSV to view MITRE coverage</div>
<div id="mitre-content" style="display:none">
<div class="section-title">MITRE ATT&CK Tactic Coverage</div>
<div class="mitre-grid" id="mitre-grid"></div>
<br>
<div class="two-col">
<div class="panel">
<div class="section-title">Techniques distribution</div>
<div id="techniques-bars"></div>
</div>
<div class="panel">
<div class="section-title">Severity by tactic</div>
<div id="tactic-severity"></div>
</div>
</div>
</div>
</div>
<!-- ── ALL RULES ── -->
<div id="tab-rules" class="section">
<div class="empty-state" id="rules-empty">Load a CSV to view rules</div>
<div id="rules-content" style="display:none">
<div class="table-controls">
<input class="search-input" id="rule-search" placeholder="Search by name, GUID, kind..." type="text">
<button class="filter-btn" id="filter-all">All</button>
<button class="filter-btn" id="filter-fail">FAIL</button>
<button class="filter-btn" id="filter-warn">WARN</button>
<button class="filter-btn" id="filter-pass">PASS</button>
</div>
<div class="table-wrap">
<table id="rules-table">
<thead>
<tr>
<th data-col="display_name">Rule name</th>
<th data-col="rule_kind">Kind</th>
<th data-col="enabled">Enabled</th>
<th data-col="severity">Severity</th>
<th data-col="audit_rating">Rating</th>
<th data-col="audit_score">Score ↕</th>
<th data-col="fail_count">Fails</th>
<th data-col="warn_count">Warns</th>
<th data-col="tactics_count">Tactics</th>
<th data-col="entity_mapping_count">Entities</th>
<th data-col="freq_ratio_rating">Freq ratio</th>
</tr>
</thead>
<tbody id="rules-tbody"></tbody>
</table>
</div>
<div class="pagination">
<button class="page-btn" id="prev-page">← Prev</button>
<span class="page-info" id="page-info"></span>
<button class="page-btn" id="next-page">Next →</button>
</div>
</div>
</div>
<!-- ── BACKLOG ── -->
<div id="tab-backlog" class="section">
<div class="empty-state" id="backlog-empty">Load a CSV to view remediation backlog</div>
<div id="backlog-content" style="display:none">
<div class="section-title">Remediation backlog — sorted by priority</div>
<div id="backlog-list"></div>
</div>
</div>
<!-- ── DICTIONARY ── -->
<div id="tab-dictionary" class="section">
<div id="dict-content"></div>
</div>
</main>
<script>
// ── DATA ──────────────────────────────────────────────────────────────────────
const CHECKS = [
["enabled", v => v==="False", 5, "WARN", "Rule is disabled — confirm intentional and document reason"],
["description_present", v => v==="False", 3, "WARN", "Add a meaningful description explaining what the rule detects and why"],
["description_length", v => /^\d+$/.test(v) && parseInt(v)<30, 2, "WARN", "Description too short (<30 chars) — likely a placeholder, expand it"],
["query_present", v => v==="False", 10, "FAIL", "Rule has no KQL query — it will never produce alerts"],
["frequency_period_gap_flag", v => v==="True", 8, "FAIL", "Query frequency exceeds lookback period — coverage gaps exist. Set queryFrequency <= queryPeriod"],
["suppression_vs_frequency_flag", v => v==="True", 9, "FAIL", "Suppression duration >= frequency — rule may be permanently silenced. Reduce suppressionDuration"],
["severity", v => v==="MISSING", 6, "FAIL", "Severity not set — assign High/Medium/Low/Informational"],
["severity_valid", v => v==="False", 4, "WARN", "Severity value non-standard — use High, Medium, Low, or Informational"],
["tactics_count", v => /^\d+$/.test(v) && parseInt(v)===0, 4, "WARN", "No MITRE ATT&CK tactics assigned — add at least one tactic for coverage visibility"],
["techniques_count", v => /^\d+$/.test(v) && parseInt(v)===0, 2, "WARN", "No MITRE techniques assigned — add technique IDs for precise coverage mapping"],
["tactics_valid_flag", v => v==="False", 3, "WARN", "One or more tactic names are not valid MITRE ATT&CK names — check spelling"],
["incident_creation_enabled", v => v==="False", 7, "FAIL", "Incident creation disabled — alerts fire silently with no SOC incident created"],
["entity_mapping_present_flag",v => v==="False", 5, "WARN", "No entity mappings — add Account, Host, IP, etc. to enable investigation pivoting"],
["alert_name_is_dynamic", v => v==="False", 2, "WARN", "Alert name is static — use dynamic fields like {{AccountName}} for unique alert names"],
["query_line_count", v => /^\d+$/.test(v) && parseInt(v)>0 && parseInt(v)<=2, 3, "WARN", "Query very short (1-2 lines) — verify not a placeholder or overly broad"],
];
const MAX_SCORE = CHECKS.reduce((s,c)=>s+c[2],0);
const LOGIC_ONLY = new Set(["query_present","frequency_period_gap_flag","suppression_vs_frequency_flag","query_line_count","alert_name_is_dynamic"]);
const INCIDENT_ONLY = new Set(["incident_creation_enabled"]);
const MITRE_TACTICS = ["InitialAccess","Execution","Persistence","PrivilegeEscalation","DefenseEvasion","CredentialAccess","Discovery","LateralMovement","Collection","Exfiltration","CommandAndControl","Impact","Reconnaissance","ResourceDevelopment","PreAttack"];
const COLUMN_DICT = [
{ group: "Identity" },
{ col:"file_name", desc:"Source JSON filename on disk", guide:"Fallback identifier if rule name is missing" },
{ col:"rule_guid", desc:"Unique GUID extracted from the ARM name field", guide:"UNKNOWN = GUID could not be parsed" },
{ col:"display_name", desc:"Human-readable rule name shown in Sentinel", guide:"MISSING = rule has no display name set" },
{ col:"description_present", desc:"Whether a description field exists and is non-empty", guide:"True = good | False = no description" },
{ col:"description_length", desc:"Character count of the description", guide:"0 = no description | <30 chars = likely placeholder" },
{ col:"rule_kind", desc:"Rule type: Scheduled, NRT, Fusion, etc.", guide:"Determines which other columns are applicable" },
{ col:"enabled", desc:"Whether the rule is active in Sentinel", guide:"True = active | False = disabled, will not fire" },
{ group: "Rule Logic" },
{ col:"query_present", desc:"Whether a non-empty KQL query exists", guide:"False = will never detect anything" },
{ col:"query_line_count", desc:"Number of lines in the KQL query", guide:"1–2 lines may indicate a placeholder or overly broad query" },
{ col:"query_tables_referenced", desc:"Pipe-separated data tables the KQL queries", guide:"NONE_DETECTED = no recognisable table names found" },
{ col:"query_period", desc:"How far back the query looks (PT1H=1hr, P1D=1day)", guide:"MISSING = no lookback window defined" },
{ col:"query_frequency", desc:"How often the rule runs (PT5M=every 5min, PT1H=hourly)", guide:"NRT rules do not have this — shows N/A" },
{ col:"frequency_period_ratio", desc:"frequency ÷ period — run interval vs lookback relationship", guide:"≤0.5 EXCELLENT | ≤1.0 GOOD | >1.0 WARN | >2.0 CRITICAL" },
{ col:"frequency_period_gap_flag", desc:"True if frequency exceeds period — blind spots exist", guide:"False = no gaps | True = coverage gaps (bad)" },
{ col:"suppression_enabled", desc:"Whether alert suppression is active", guide:"True = verify duration vs frequency" },
{ col:"suppression_duration", desc:"How long alerts are suppressed after the rule fires", guide:"Must be shorter than query frequency" },
{ col:"suppression_vs_frequency_flag",desc:"True if suppression >= frequency — alerts may be permanently silenced", guide:"True = critical misconfiguration" },
{ group: "Severity & MITRE" },
{ col:"severity", desc:"Alert severity: High, Medium, Low, Informational", guide:"MISSING = not set | All Informational = tuning red flag" },
{ col:"severity_valid", desc:"Whether severity is one of the four accepted values", guide:"False = non-standard severity value" },
{ col:"tactics", desc:"Pipe-separated MITRE ATT&CK tactic names", guide:"NONE = no tactics mapped" },
{ col:"tactics_count", desc:"Number of MITRE tactics assigned", guide:"0 = no coverage | 1–3 typical | >5 may be over-tagged" },
{ col:"tactics_valid_flag", desc:"Whether all tactics are recognised MITRE ATT&CK names", guide:"False = typo or non-standard name" },
{ col:"unknown_tactics", desc:"Tactic names not in the known MITRE list", guide:"None = all tactics valid" },
{ col:"techniques", desc:"Pipe-separated MITRE technique IDs (e.g. T1078, T1078.001)", guide:"NONE = no technique-level mapping" },
{ col:"techniques_count", desc:"Number of MITRE techniques assigned", guide:"0 = no technique-level mapping" },
{ group: "Incident Configuration" },
{ col:"incident_creation_enabled", desc:"Whether the rule creates a Sentinel incident when it fires", guide:"False = alerts fire silently, no SOC incident created" },
{ col:"grouping_enabled", desc:"Whether related alerts are grouped into a single incident", guide:"True = reduces alert noise (generally preferred)" },
{ col:"grouping_lookback", desc:"Time window used to group related alerts", guide:"Should align with the rule's query period" },
{ col:"grouping_reopen_closed", desc:"Whether a new alert reopens a previously closed incident", guide:"Depends on SOC process — no universal correct value" },
{ col:"grouping_match_only", desc:"Grouping method: AllEntities, AnyAlert, Selected, etc.", guide:"AllEntities = strictest | AnyAlert = broadest" },
{ group: "Entity Mapping & Alert Details" },
{ col:"entity_mapping_count", desc:"Number of entity mappings defined on the rule", guide:"0 = no entities mapped, limits investigation pivoting" },
{ col:"entity_types_mapped", desc:"Pipe-separated entity types (Account, Host, IP, URL, etc.)", guide:"NONE = no entity context attached to alerts" },
{ col:"entity_mapping_present_flag", desc:"Whether at least one entity is mapped", guide:"False = no entity mapping at all (remediate)" },
{ col:"alert_name_format", desc:"The alert name template — static or dynamic", guide:"STATIC = every alert has same name, harder to triage" },
{ col:"alert_name_is_dynamic", desc:"Whether alert name uses dynamic fields like {{AccountName}}", guide:"True = includes contextual data (preferred)" },
{ col:"alert_description_format", desc:"The alert description template", guide:"STATIC = generic description for every alert instance" },
{ col:"event_grouping_strategy", desc:"How events within a rule run are grouped", guide:"AlertPerResult = one alert per row (can be noisy)" },
{ group: "Metadata" },
{ col:"kind_version", desc:"Version of the rule template", guide:"MISSING = no version tracked" },
{ col:"last_modified_utc", desc:"Timestamp of last modification (from export — may not be live)", guide:"MISSING = modification history not in export" },
{ col:"created_by", desc:"Author name from rule metadata", guide:"MISSING = no ownership information" },
{ col:"source_name", desc:"Origin: Content Hub solution name or custom", guide:"Helps distinguish vendor templates from custom rules" },
{ col:"template_version", desc:"Version string of originating Content Hub template", guide:"MISSING = custom rule or version not tracked" },
{ group: "Operational Health — N/A (requires live access)" },
{ col:"query_syntax_valid", desc:"Whether the KQL query executes without errors", guide:"N/A - No Live Access" },
{ col:"tables_exist_in_workspace", desc:"Whether referenced tables are actively ingesting data", guide:"N/A - No Live Access" },
{ col:"connector_status", desc:"Whether required data connectors are enabled", guide:"N/A - No Live Access" },
{ col:"rule_last_run", desc:"Timestamp of most recent rule execution", guide:"N/A - No Live Access" },
{ col:"rule_last_fired", desc:"Timestamp of last alert produced", guide:"N/A - No Live Access" },
{ group: "Audit & Analysis Columns" },
{ col:"audit_flags", desc:"Pipe-separated list of all issues found for this rule", guide:"NONE = rule passed all assessable checks" },
{ col:"audit_timestamp", desc:"When this audit row was generated (UTC)", guide:"Used to version the audit run" },
{ col:"audit_score", desc:"Penalty score: 0=perfect, higher=more/worse issues", guide:`Range 0–${MAX_SCORE} | 0=PASS | >0.5×${MAX_SCORE}=FAIL` },
{ col:"audit_rating", desc:"Overall rating derived from audit_score", guide:"PASS | PASS WITH NOTES | WARN | FAIL" },
{ col:"findings_count", desc:"Total number of individual issues found", guide:"0 = no issues" },
{ col:"fail_count", desc:"Number of FAIL-severity issues", guide:"0 = no critical issues" },
{ col:"warn_count", desc:"Number of WARN-severity issues", guide:"0 = no warnings" },
{ col:"freq_ratio_rating", desc:"Human-readable interpretation of frequency_period_ratio", guide:"EXCELLENT / GOOD / WARNING / CRITICAL" },
{ col:"remediation_actions", desc:"Pipe-separated specific fix instructions for every issue", guide:"Empty = no issues to fix" },
];
// ── STATE ─────────────────────────────────────────────────────────────────────
let allRules = [];
let filteredRules = [];
let sortCol = "audit_score";
let sortDir = -1;
let filterRating = "all";
let searchQuery = "";
let currentPage = 1;
const PAGE_SIZE = 30;
// ── CSV PARSER ────────────────────────────────────────────────────────────────
function parseCSV(text) {
const lines = text.split(/\r?\n/).filter(l => l.trim());
if (!lines.length) return [];
const headers = splitCSVLine(lines[0]);
return lines.slice(1).map(line => {
const vals = splitCSVLine(line);
const obj = {};
headers.forEach((h, i) => obj[h.trim()] = (vals[i] || "").trim());
return obj;
});
}
function splitCSVLine(line) {
const res = []; let cur = ""; let inQ = false;
for (let i = 0; i < line.length; i++) {
const c = line[i];
if (c === '"') { inQ = !inQ; }
else if (c === ',' && !inQ) { res.push(cur); cur = ""; }
else cur += c;
}
res.push(cur);
return res;
}
// ── SCORING ───────────────────────────────────────────────────────────────────
function scoreRule(row) {
const kind = row.rule_kind || "";
const findings = [];
let score = 0;
for (const [col, cond, penalty, sev, hint] of CHECKS) {
if (LOGIC_ONLY.has(col) && !["Scheduled","NRT"].includes(kind)) continue;
if (INCIDENT_ONLY.has(col) && ["Fusion","MLBehaviorAnalytics"].includes(kind)) continue;
const val = String(row[col] || "");
if (cond(val)) { findings.push({col, sev, penalty, hint}); score += penalty; }
}
const pct = score / MAX_SCORE;
const rating = pct === 0 ? "PASS" : pct <= 0.2 ? "PASS WITH NOTES" : pct <= 0.5 ? "WARN" : "FAIL";
return { score, rating, findings, failCount: findings.filter(f=>f.sev==="FAIL").length, warnCount: findings.filter(f=>f.sev==="WARN").length };
}
function rateFreqRatio(v) {
const r = parseFloat(v);
if (!r || isNaN(r)) return "N/A";
if (r <= 0.5) return "EXCELLENT";
if (r <= 1.0) return "GOOD";
if (r <= 2.0) return "WARNING";
return "CRITICAL";
}
function enrichRules(rows) {
return rows.map(r => {
const s = scoreRule(r);
return { ...r, audit_score: s.score, audit_rating: s.rating, _findings: s.findings, fail_count: s.failCount, warn_count: s.warnCount, freq_ratio_rating: rateFreqRatio(r.frequency_period_ratio) };
});
}
// ── HELPERS ───────────────────────────────────────────────────────────────────
function pct(n, total) { return total ? Math.round(n/total*100) : 0; }
function badge(rating) {
const map = { "FAIL": "badge-fail", "WARN": "badge-warn", "PASS": "badge-pass", "PASS WITH NOTES": "badge-note" };
return `<span class="badge ${map[rating]||'badge-na'}">${rating}</span>`;
}
function sevBadge(sev) {
if (!sev || sev==="MISSING") return `<span class="badge badge-na">MISSING</span>`;
const map = { High:"badge-fail", Medium:"badge-warn", Low:"badge-note", Informational:"badge-na" };
return `<span class="badge ${map[sev]||'badge-na'}">${sev}</span>`;
}
// ── OVERVIEW ──────────────────────────────────────────────────────────────────
function renderOverview() {
const total = allRules.length;
const failC = allRules.filter(r=>r.audit_rating==="FAIL").length;
const warnC = allRules.filter(r=>r.audit_rating==="WARN").length;
const passC = allRules.filter(r=>["PASS","PASS WITH NOTES"].includes(r.audit_rating)).length;
const disabled = allRules.filter(r=>r.enabled==="False").length;
const noEntity = allRules.filter(r=>r.entity_mapping_present_flag==="False").length;
const noTactics = allRules.filter(r=>/^\d+$/.test(r.tactics_count)&&parseInt(r.tactics_count)===0).length;
const noIncident = allRules.filter(r=>r.incident_creation_enabled==="False").length;
const gapFlag = allRules.filter(r=>r.frequency_period_gap_flag==="True").length;
const suppFlag = allRules.filter(r=>r.suppression_vs_frequency_flag==="True").length;
// severity dist
const sevDist = {};
allRules.forEach(r => { const s=r.severity||"MISSING"; sevDist[s]=(sevDist[s]||0)+1; });
const kindDist = {};
allRules.forEach(r => { const k=r.rule_kind||"UNKNOWN"; kindDist[k]=(kindDist[k]||0)+1; });
// donut SVG
const donutData = [
{ label:"FAIL", count:failC, color:"var(--red)" },
{ label:"WARN", count:warnC, color:"var(--amber)" },
{ label:"PASS", count:passC, color:"var(--green)" },
];
const r=54, cx=70, cy=70, circumference=2*Math.PI*r;
let offset=0;
const slices = donutData.map(d => {
const fraction = total ? d.count/total : 0;
const dash = fraction * circumference;
const slice = { ...d, dash, gap: circumference-dash, offset, fraction };
offset += dash;
return slice;
});
const donutSVG = `<svg width="140" height="140" viewBox="0 0 140 140">
<circle cx="${cx}" cy="${cy}" r="${r}" fill="none" stroke="var(--border)" stroke-width="18"/>
${slices.map(s=>`<circle cx="${cx}" cy="${cy}" r="${r}" fill="none" stroke="${s.color}" stroke-width="18"
stroke-dasharray="${s.dash} ${s.gap}" stroke-dashoffset="${-(s.offset - circumference/4)}"
style="transition:stroke-dasharray 0.6s"/>`).join('')}
<text x="${cx}" y="${cy-4}" text-anchor="middle" font-family="IBM Plex Mono" font-size="20" font-weight="500" fill="var(--text)">${total}</text>
<text x="${cx}" y="${cy+14}" text-anchor="middle" font-family="IBM Plex Mono" font-size="10" fill="var(--text2)">RULES</text>
</svg>`;
const html = `
<div class="metrics-grid">
<div class="metric info"><div class="metric-label">Total rules</div><div class="metric-value">${total}</div><div class="metric-sub">assessed</div></div>
<div class="metric fail"><div class="metric-label">FAIL</div><div class="metric-value">${failC}</div><div class="metric-sub">${pct(failC,total)}% of rules</div></div>
<div class="metric warn"><div class="metric-label">WARN</div><div class="metric-value">${warnC}</div><div class="metric-sub">${pct(warnC,total)}% of rules</div></div>
<div class="metric pass"><div class="metric-label">PASS</div><div class="metric-value">${passC}</div><div class="metric-sub">${pct(passC,total)}% of rules</div></div>
<div class="metric warn"><div class="metric-label">Disabled</div><div class="metric-value">${disabled}</div><div class="metric-sub">${pct(disabled,total)}% disabled</div></div>
<div class="metric warn"><div class="metric-label">No entity map</div><div class="metric-value">${noEntity}</div><div class="metric-sub">${pct(noEntity,total)}% of rules</div></div>
</div>
<div class="two-col">
<div class="panel">
<div class="section-title">Rating breakdown</div>
<div class="donut-wrap">
${donutSVG}
<div class="donut-legend">
${donutData.map(d=>`<div class="legend-item"><div class="legend-dot" style="background:${d.color}"></div><span style="color:${d.color}">${d.label}</span><span style="color:var(--text2);margin-left:4px">${d.count} (${pct(d.count,total)}%)</span></div>`).join('')}
</div>
</div>
</div>
<div class="panel">
<div class="section-title">Top findings</div>
${[
["Rules disabled", disabled],
["No entity mapping", noEntity],
["No MITRE tactics", noTactics],
["Incident creation off", noIncident],
["Coverage gaps", gapFlag],
["Suppression silencing", suppFlag],
].map(([label,count])=>`
<div class="finding-row">
<span class="finding-name">${label}</span>
<span class="finding-count" style="color:${count>0?(count/total>0.3?'var(--red)':'var(--amber)'):'var(--green)'}">${count}</span>
<span class="finding-pct">${pct(count,total)}%</span>
</div>`).join('')}
</div>
</div>
<div class="two-col">
<div class="panel">
<div class="section-title">Severity distribution</div>
${Object.entries(sevDist).sort((a,b)=>b[1]-a[1]).map(([sev,count])=>{
const colors = {High:"red",Medium:"",Low:"blue",Informational:"blue",MISSING:"red"};
return `<div class="bar-row">
<div class="bar-label">${sev}</div>
<div class="bar-track"><div class="bar-fill ${colors[sev]||''}" style="width:${pct(count,total)}%"></div></div>
<div class="bar-count">${count}</div>
</div>`;
}).join('')}
</div>
<div class="panel">
<div class="section-title">Rule kind distribution</div>
${Object.entries(kindDist).sort((a,b)=>b[1]-a[1]).map(([kind,count])=>`
<div class="bar-row">
<div class="bar-label">${kind}</div>
<div class="bar-track"><div class="bar-fill green" style="width:${pct(count,total)}%"></div></div>
<div class="bar-count">${count}</div>
</div>`).join('')}
</div>
</div>
<div class="panel" style="margin-bottom:28px">
<div class="section-title">Top 10 rules needing attention</div>
<div class="table-wrap" style="border:none;margin-bottom:0">
<table>
<thead><tr>
<th>Rule name</th><th>Kind</th><th>Rating</th><th>Score</th><th>Fails</th><th>Warns</th><th>Severity</th>
</tr></thead>
<tbody>
${[...allRules].sort((a,b)=>b.audit_score-a.audit_score).slice(0,10).map(r=>`
<tr>
<td class="td-name" title="${r.display_name}">${r.display_name||r.file_name||'—'}</td>
<td class="td-muted">${r.rule_kind||'—'}</td>
<td>${badge(r.audit_rating)}</td>
<td class="td-score ${r.audit_score>MAX_SCORE*0.5?'score-high':r.audit_score>MAX_SCORE*0.2?'score-med':'score-low'}">${r.audit_score}</td>
<td style="color:var(--red)">${r.fail_count}</td>
<td style="color:var(--amber)">${r.warn_count}</td>
<td>${sevBadge(r.severity)}</td>
</tr>`).join('')}
</tbody>
</table>
</div>
</div>`;
document.getElementById('tab-overview').innerHTML = html;
}
// ── MITRE ─────────────────────────────────────────────────────────────────────
function renderMitre() {
const tacticCounts = {};
MITRE_TACTICS.forEach(t => tacticCounts[t] = 0);
allRules.forEach(r => {
const tactics = r.tactics || "";
if (tactics && !["NONE","N/A",""].includes(tactics)) {
tactics.split("|").forEach(t => {
const trimmed = t.trim();
if (tacticCounts.hasOwnProperty(trimmed)) tacticCounts[trimmed]++;
else tacticCounts[trimmed] = (tacticCounts[trimmed]||0)+1;
});
}
});
const maxCount = Math.max(...Object.values(tacticCounts), 1);
const grid = MITRE_TACTICS.map(t => {
const count = tacticCounts[t] || 0;
const level = count===0?"zero":count/maxCount<0.33?"low":count/maxCount<0.66?"medium":"high";
return `<div class="mitre-cell ${level}">
<div class="mitre-cell-name">${t}</div>
<div class="mitre-cell-count">${count}</div>
</div>`;
}).join('');
document.getElementById('mitre-grid').innerHTML = grid;
// Techniques bar
const techCounts = {};
allRules.forEach(r => {
const techs = r.techniques || "";
if (techs && !["NONE","N/A",""].includes(techs)) {
techs.split("|").forEach(t => { const tt=t.trim(); if(tt) techCounts[tt]=(techCounts[tt]||0)+1; });
}
});
const topTechs = Object.entries(techCounts).sort((a,b)=>b[1]-a[1]).slice(0,12);
const maxT = topTechs[0]?.[1] || 1;
document.getElementById('techniques-bars').innerHTML = topTechs.length
? topTechs.map(([tech,count])=>`<div class="bar-row">
<div class="bar-label">${tech}</div>
<div class="bar-track"><div class="bar-fill" style="width:${pct(count,maxT)}%"></div></div>
<div class="bar-count">${count}</div>
</div>`).join('')
: '<div style="color:var(--text3);font-family:var(--mono);font-size:12px;padding:16px 0">No technique data</div>';
// Tactic severity heatmap (simplified — show high severity rules per tactic)
const tacticHighSev = {};
allRules.forEach(r => {
const tactics = r.tactics || "";
if (tactics && !["NONE","N/A",""].includes(tactics) && r.severity==="High") {
tactics.split("|").forEach(t => { const tt=t.trim(); if(tt) tacticHighSev[tt]=(tacticHighSev[tt]||0)+1; });
}
});
const topTacticSev = Object.entries(tacticHighSev).sort((a,b)=>b[1]-a[1]).slice(0,10);
const maxS = topTacticSev[0]?.[1] || 1;
document.getElementById('tactic-severity').innerHTML = topTacticSev.length
? topTacticSev.map(([tactic,count])=>`<div class="bar-row">
<div class="bar-label">${tactic}</div>
<div class="bar-track"><div class="bar-fill red" style="width:${pct(count,maxS)}%"></div></div>
<div class="bar-count">${count}</div>
</div>`).join('')
: '<div style="color:var(--text3);font-family:var(--mono);font-size:12px;padding:16px 0">No High severity data</div>';
document.getElementById('mitre-empty').style.display = 'none';
document.getElementById('mitre-content').style.display = 'block';
}
// ── RULES TABLE ───────────────────────────────────────────────────────────────
function applyFilters() {
filteredRules = allRules.filter(r => {
const matchRating = filterRating === "all"
|| (filterRating==="pass" && ["PASS","PASS WITH NOTES"].includes(r.audit_rating))
|| r.audit_rating === filterRating.toUpperCase();
const q = searchQuery.toLowerCase();
const matchSearch = !q
|| (r.display_name||"").toLowerCase().includes(q)
|| (r.rule_guid||"").toLowerCase().includes(q)
|| (r.rule_kind||"").toLowerCase().includes(q)
|| (r.audit_flags||"").toLowerCase().includes(q);
return matchRating && matchSearch;
});
filteredRules.sort((a,b) => {
const av = isNaN(a[sortCol]) ? String(a[sortCol]||"") : Number(a[sortCol]);
const bv = isNaN(b[sortCol]) ? String(b[sortCol]||"") : Number(b[sortCol]);
return av < bv ? sortDir : av > bv ? -sortDir : 0;
});
currentPage = 1;
renderRulesTable();
}
function renderRulesTable() {
const start = (currentPage-1)*PAGE_SIZE;
const pageRules = filteredRules.slice(start, start+PAGE_SIZE);
const totalPages = Math.ceil(filteredRules.length/PAGE_SIZE);
document.getElementById('rules-tbody').innerHTML = pageRules.map(r => `
<tr>
<td class="td-name" title="${r.display_name||''}">${r.display_name||r.file_name||'—'}</td>
<td class="td-muted">${r.rule_kind||'—'}</td>
<td>${r.enabled==="True"?'<span style="color:var(--green)">●</span>':'<span style="color:var(--red)">●</span>'} ${r.enabled}</td>
<td>${sevBadge(r.severity)}</td>
<td>${badge(r.audit_rating)}</td>
<td class="td-score ${r.audit_score>MAX_SCORE*0.5?'score-high':r.audit_score>MAX_SCORE*0.2?'score-med':'score-low'}">${r.audit_score}</td>
<td style="color:var(--red)">${r.fail_count}</td>
<td style="color:var(--amber)">${r.warn_count}</td>
<td class="td-muted">${r.tactics_count||0}</td>
<td class="td-muted">${r.entity_mapping_count||0}</td>
<td class="td-muted" style="font-size:10px">${r.freq_ratio_rating||'N/A'}</td>
</tr>`).join('');
document.getElementById('page-info').textContent =
`Showing ${start+1}–${Math.min(start+PAGE_SIZE, filteredRules.length)} of ${filteredRules.length} rules`;
document.getElementById('prev-page').disabled = currentPage <= 1;
document.getElementById('next-page').disabled = currentPage >= totalPages;
document.getElementById('rules-empty').style.display = 'none';
document.getElementById('rules-content').style.display = 'block';
}
// ── REMEDIATION BACKLOG ───────────────────────────────────────────────────────
function renderBacklog() {
const issues = [...allRules]
.filter(r => !["PASS"].includes(r.audit_rating))
.sort((a,b) => {
const order = {FAIL:0,WARN:1,"PASS WITH NOTES":2};
const oa = order[a.audit_rating]??9, ob = order[b.audit_rating]??9;
return oa !== ob ? oa-ob : b.audit_score - a.audit_score;
});
if (!issues.length) {
document.getElementById('backlog-list').innerHTML = '<div class="empty-state" style="color:var(--green)">No issues found — all rules passed</div>';
} else {
document.getElementById('backlog-list').innerHTML = issues.map((r,i) => `
<div class="rem-card">
<div class="rem-header">
<div class="rem-rank">#${i+1}</div>
<div style="flex:1">
<div class="rem-name">${r.display_name||r.file_name||'Unknown rule'}</div>
<div class="rem-kind">${r.rule_kind||''} · GUID: ${r.rule_guid||'—'} · Score: ${r.audit_score}/${MAX_SCORE}</div>
</div>
<div style="display:flex;gap:8px;align-items:center">
${badge(r.audit_rating)}
${sevBadge(r.severity)}
${r.enabled==="False"?'<span class="badge badge-na">DISABLED</span>':''}
</div>
</div>
<div class="rem-actions">
${r._findings.map(f=>`
<div class="rem-action">
<span class="rem-action-sev ${f.sev.toLowerCase()}">${f.sev}</span>
<span class="rem-action-text">${f.hint}</span>
</div>`).join('')}
</div>
</div>`).join('');
}
document.getElementById('backlog-empty').style.display = 'none';
document.getElementById('backlog-content').style.display = 'block';
}
// ── COLUMN DICTIONARY ─────────────────────────────────────────────────────────
function renderDictionary() {
let html = '';
let currentGroup = '';
let groupHtml = '';
COLUMN_DICT.forEach(entry => {
if (entry.group) {
if (currentGroup && groupHtml) {
html += `<div class="dict-group"><div class="dict-group-title">${currentGroup}</div>${groupHtml}</div>`;
}
currentGroup = entry.group;
groupHtml = '';
} else {
groupHtml += `<div class="dict-row">
<div class="dict-col">${entry.col}</div>
<div class="dict-desc">${entry.desc}</div>
<div class="dict-guide">${entry.guide}</div>
</div>`;
}
});
if (currentGroup && groupHtml) {
html += `<div class="dict-group"><div class="dict-group-title">${currentGroup}</div>${groupHtml}</div>`;
}
document.getElementById('dict-content').innerHTML = `
<div class="section-title" style="margin-bottom:20px">Column dictionary — all ${COLUMN_DICT.filter(e=>e.col).length} audit columns explained</div>
${html}`;
}
// ── FILE LOADING ──────────────────────────────────────────────────────────────
function loadCSV(text, filename) {
const rows = parseCSV(text);
if (!rows.length) { alert("No data found in CSV."); return; }
// Check if already scored (from sentinel_analyse.py) or raw (from sentinel_audit.py)
const alreadyScored = rows[0].hasOwnProperty('audit_score');
allRules = alreadyScored ? rows.map(r => ({
...r,
audit_score: parseInt(r.audit_score)||0,
fail_count: parseInt(r.fail_count)||0,
warn_count: parseInt(r.warn_count)||0,
_findings: parseFindings(r)
})) : enrichRules(rows);
filteredRules = [...allRules];
document.getElementById('run-info').textContent =
`${filename} · ${allRules.length} rules · ${new Date().toLocaleTimeString()}`;
renderOverview();
renderMitre();
renderRulesTable();
renderBacklog();
renderDictionary();
}
function parseFindings(row) {
// Re-derive findings for display in backlog even if pre-scored
return scoreRule(row).findings;
}
// ── EVENT WIRING ──────────────────────────────────────────────────────────────
document.querySelectorAll('.tab').forEach(tab => {
tab.addEventListener('click', () => {
document.querySelectorAll('.tab').forEach(t=>t.classList.remove('active'));
document.querySelectorAll('.section').forEach(s=>s.classList.remove('active'));
tab.classList.add('active');
document.getElementById('tab-'+tab.dataset.tab).classList.add('active');
});
});
const dropZone = document.getElementById('drop-zone');
dropZone.addEventListener('dragover', e => { e.preventDefault(); dropZone.classList.add('drag-over'); });
dropZone.addEventListener('dragleave', () => dropZone.classList.remove('drag-over'));
dropZone.addEventListener('drop', e => {
e.preventDefault(); dropZone.classList.remove('drag-over');
const file = e.dataTransfer.files[0];
if (file) readFile(file);
});
dropZone.addEventListener('click', () => document.getElementById('csv-file-input').click());
document.getElementById('csv-file-input').addEventListener('change', e => {
const file = e.target.files[0];
if (file) readFile(file);
});
function readFile(file) {
const reader = new FileReader();
reader.onload = e => loadCSV(e.target.result, file.name);
reader.readAsText(file);
}
document.getElementById('rule-search').addEventListener('input', e => {
searchQuery = e.target.value;
applyFilters();
});
['all','fail','warn','pass'].forEach(f => {
document.getElementById('filter-'+f).addEventListener('click', function() {
filterRating = f;
document.querySelectorAll('.filter-btn').forEach(b=>b.className='filter-btn');
if (f!=='all') this.classList.add('active-'+f);
else this.classList.add('');
applyFilters();
});
});
document.querySelectorAll('#rules-table thead th').forEach(th => {
th.addEventListener('click', () => {
const col = th.dataset.col;
if (sortCol === col) sortDir *= -1;
else { sortCol = col; sortDir = -1; }
document.querySelectorAll('#rules-table thead th').forEach(t=>t.classList.remove('sorted'));
th.classList.add('sorted');
applyFilters();
});
});
document.getElementById('prev-page').addEventListener('click', () => { currentPage--; renderRulesTable(); });
document.getElementById('next-page').addEventListener('click', () => { currentPage++; renderRulesTable(); });
// Render dictionary on load (no CSV needed)
renderDictionary();
</script>
</body>
</html>
Step-by-step: how to use it
Prerequisites
Python 3.6 or later. Both scripts use only the standard library — no pip install needed.
python --version # should return Python 3.6.x or laterWindows note
Use python not python3. If Python is not recognised, reinstall it and ensure the “Add Python to PATH” checkbox is ticked on the first installer screen.
Step 1 — Prepare your JSON files
Place all exported rule JSON files in a single folder. Mixed ARM envelope and direct rule object formats are handled automatically.
my_project/
├── rules/
│ ├── rule-brute-force.json
│ └── rule-impossible-travel.json
├── sentinel_audit.py
├── sentinel_analyse.py
└── sentinel_dashboard.html
├── rules/
│ ├── rule-brute-force.json
│ └── rule-impossible-travel.json
├── sentinel_audit.py
├── sentinel_analyse.py
└── sentinel_dashboard.html
Step 2 — Run the audit script
python sentinel_audit.py --input ./rules --output audit_results.csvStep 3 — Run the analysis script
python sentinel_analyse.py --input audit_results.csv --output ./analysisStep 4 — Open the dashboard
Open sentinel_dashboard.html in Chrome or Edge. Drag and drop audit_results.csv onto the drop zone, or click Load CSV in the top-right corner.
Tip — share the dashboard
The dashboard is a single HTML file. Email it, drop it in Teams, or put it on SharePoint. The recipient opens it and loads their own CSV — no installation required.
Understanding the output columns
The audit CSV contains 48 columns. The audit_flags column is your quickest triage tool — it lists all issues found as a pipe-separated string. Filter it in Excel using Contains to find all rules with a specific problem.
| Column | What it tells you |
|---|---|
| frequency_period_ratio | How well run frequency covers the lookback window. <1.0 is good, >1.0 means gaps. |
| description_length | Character count. Below 30 typically means a placeholder. |
| tactics_count | Number of MITRE tactics. Zero means no coverage visibility in the MITRE matrix. |
| entity_mapping_count | Number of entity mappings. Zero means no investigation pivoting possible. |
| query_line_count | Lines in the KQL. One or two lines often indicates a placeholder query. |
| audit_score | Penalty score from sentinel_analyse.py. 0 = perfect, higher = more issues. |
| audit_rating | PASS / PASS WITH NOTES / WARN / FAIL derived from the score. |
| remediation_actions | Pipe-separated specific fix instructions for every issue found on this rule. |
Reading the audit flags
| Flag | Severity | Remediation |
|---|---|---|
| EMPTY_QUERY | FAIL | Rule has no KQL — add a valid query or delete the rule |
| SUPPRESSION_MAY_SILENCE_ALERTS | FAIL | Reduce suppressionDuration to less than queryFrequency |
| FREQUENCY_EXCEEDS_PERIOD | FAIL | Set queryFrequency ≤ queryPeriod to eliminate blind spots |
| MISSING_SEVERITY | FAIL | Assign High, Medium, Low, or Informational |
| INCIDENT_CREATION_DISABLED | FAIL | Enable incidentConfiguration.createIncident |
| RULE_DISABLED | WARN | Confirm intentional — document reason in description |
| NO_ENTITY_MAPPING | WARN | Add entity mappings (Account, Host, IP) for investigation pivoting |
| NO_MITRE_TACTICS | WARN | Assign at least one MITRE ATT&CK tactic |
| NO_DESCRIPTION | WARN | Add a description explaining what the rule detects and why |
| STATIC_ALERT_NAME | WARN | Use dynamic fields like {{AccountName}} in the alert name |
| UNKNOWN_MITRE_TACTICS | WARN | One or more tactic names are not valid ATT&CK names — check spelling |
Prioritisation guidance
Fix FAIL-rated rules first — especially EMPTY_QUERY, SUPPRESSION_MAY_SILENCE_ALERTS, and INCIDENT_CREATION_DISABLED. These are rules that are either completely broken or will never notify your SOC.
What comes next
AI-powered rule review
The most impactful near-term enhancement is passing each rule’s KQL query to a language model for review. Mechanical checks verify a query is non-empty — but they cannot assess whether the logic actually detects what it claims to, whether it has always-true conditions, or whether it will time out. An AI review layer catches these issues and suggests specific improvements.
Live workspace integration
When live access is available, connecting to the Azure SDK (azure-mgmt-securityinsight) as the data source instead of local JSON files unlocks the two currently unavailable categories: operational health and data source coverage.
Continuous monitoring
Scheduling the pipeline to run on a regular cadence — weekly against the live workspace, or as a CI/CD gate on rule deployments — turns it from a point-in-time audit into a continuous quality assurance mechanism.
Coverage gap analysis
A more sophisticated analysis would map the specific KQL logic of each rule to specific MITRE techniques and sub-techniques, identify which techniques in your threat model have no coverage, and produce a prioritised list of detection gaps to feed a detection engineering roadmap.
The three files are designed to work together as a pipeline but are also independently useful. The audit script can feed any downstream analysis tool. The dashboard can load any CSV that uses the same column schema. And the analysis script can be extended with additional checks without touching the other two.
By profession, a CloudSecurity Consultant; by passion, a storyteller. Through SunExplains, I explain security in simple, relatable terms — connecting technology, trust, and everyday life.
Leave a Reply