Sentinel Ingestion Report

Sentinel Ingestion Analysis Report — Instructions

Purpose

This skill generates a comprehensive Sentinel Ingestion Analysis Report covering workspace data volume, table-level breakdown, tier classification, ingestion anomalies, detection coverage, and optimization opportunities.

Entity Type: Sentinel workspace (from config.json)

What this report covers: Table-level volume breakdown with tier classification (Analytics/Basic/Data Lake), SecurityEvent/Syslog/CommonSecurityLog deep dives, ingestion anomaly detection (24h and week-over-week), analytic rule inventory with detection coverage cross-reference, rule health monitoring, tier migration candidates with DL-eligibility assessment, and license benefit analysis (DfS P2 and M365 E5).

Architecture

 ┌─────────────────────────────────────────────────────────────────┐
 │  YAML query files        PowerShell script        LLM render    │
 │  queries/phase1-5/  ──→  Invoke-IngestionScan  ──→  Phase 6     │
 │  (23 .yaml files)        .ps1 (~2600 lines)       (SKILL-       │
 │                          • az monitor (KQL)        report.md)   │
 │                          • az rest (REST API)                   │
 │                          • az monitor table list                │
 │                          • Invoke-MgGraphRequest                │
 │                          ↓                                      │
 │                     temp/ingest_scratch_<ts>.md                 │
 │                     (~50 KB, 64 sections)                       │
 └─────────────────────────────────────────────────────────────────┘

Execution model:

• Phases 1-5 (data gathering): Fully automated by Invoke-IngestionScan.ps1. KQL queries run via az monitor log-analytics query. Non-KQL data (analytic rules, tier classifications, custom detections) is gathered via REST API, Azure CLI, and Microsoft Graph.
• Phase 6 (rendering): LLM reads the scratchpad + SKILL-report.md and renders the report. This is the only phase requiring LLM involvement.

Design decision — TopRecommendations: The Top 3 Recommendations are computed by the LLM at render time (Phase 6), not pre-computed by PS1. Three of the seven Rule E categories (Data loss, DCR filter, Split ingestion) require cross-section reasoning that spans multiple scratchpad sections — this is precisely what the LLM excels at. The PS1 provides all the raw data; the LLM applies Rule E scoring across it.

Companion Files — When to Load

This skill spans 4 files. Load only the file(s) needed for the current phase:

📑 TABLE OF CONTENTS

1. Quick Start - 3-step execution pattern
2. Critical Workflow Rules - Prerequisites and prohibitions
3. Execution Workflow - Phases 0-6
4. Query File Reference - All 23 YAML files
5. Output Modes - Inline chat vs. Markdown file
6. Deterministic Rendering Rules - Rules A-G (mandatory for Phase 6)
7. Domain Reference - SecurityEvent, Syslog, CommonSecurityLog interpretation
8. Tier Classification - Analytics vs Basic vs Data Lake background
9. Migration Classification - Zero-rule table categorization for §7a
10. Reference: Data Lake Migration - DL-eligible tables, decision matrix, trade-off analysis
11. Reference: License Benefits - DfS P2 / E5 pool calculations
12. Report Template - JIT pointer → SKILL-report.md
13. Post-Report Drill-Down Reference - Rule cross-referencing, Custom Detection API, ASIM verification, error handling
14. SVG Dashboard Generation - Visual dashboard from completed report

Quick Start (TL;DR)

3-step execution pattern:

Step 1:  Run Invoke-IngestionScan.ps1 (Phases 1-5 — data gathering)
Step 2:  Read scratchpad + SKILL-report.md (Phase 6 prep)
Step 3:  Render full report (§1-§8) → create_file

Step 1: Run Data Gathering

# From workspace root — run all phases (default: 30 days):
& ".github/skills/sentinel-ingestion-report/Invoke-IngestionScan.ps1"

# Specify a custom window (1, 7, 30, 60, or 90 days):
& ".github/skills/sentinel-ingestion-report/Invoke-IngestionScan.ps1" -Days 7

# Or run a specific phase (for re-runs / debugging):
& ".github/skills/sentinel-ingestion-report/Invoke-IngestionScan.ps1" -Phase 3

# Synthetic mode — use pre-built test data (no Azure auth required):
& ".github/skills/sentinel-ingestion-report/Invoke-IngestionScan.ps1" -SyntheticDataDir ".github/skills/sentinel-ingestion-report/test-data/enterprise"

Synthetic mode: When the user asks to generate a report using "synthetic data" or "test data", use -SyntheticDataDir pointing to the enterprise test data directory. This bypasses all Azure/Sentinel queries and loads pre-built JSON files instead. Useful for testing report rendering without live workspace access.

Output: Scratchpad file at temp/ingest_scratch_<timestamp>.md (~50 KB, 64 sections).

Timing: Full run (Phase 0 = all phases) takes ~20-25 seconds. Individual phases: 3-8 seconds each.

Step 2: Load Rendering Context

1. Read the scratchpad file (path printed by PS1 at completion)
2. Read SKILL-report.md for rendering templates

Step 3: Render Report (Single Write)

Render the complete report (§1-§8) in a single create_file call. Apply SKILL-report.md templates to scratchpad data, following Rules A-G. Render all 8 sections (Executive Summary, Ingestion Overview, Deep Dives, Anomaly Detection, Detection Coverage, License Benefit Analysis, Optimization Recommendations, Appendix) and write to the report file.

⛔ Single-write requirement: The entire report MUST be rendered in one create_file call. Do NOT split rendering across multiple tool calls — splitting causes the LLM to lose template context for later sections (§5-§8), resulting in heading drift, column mutations, and invented content. The complete SKILL-report.md template must be active throughout the entire generation.

🔴 Verbatim table/skeleton blocks — use the deterministic slicer, never hand-copy. The PS1 pre-renders every table, the ASCII cost-waterfall, and the §-heading skeleton under ## PRERENDERED in the scratchpad (Headings, CostWaterfall, DailyChart, TopTables, DetectionPosture, AnomalyTable, CrossReference, SE_Computer, SE_EventID, SyslogHost/Facility/FacSev/Process, CSL_Vendor/Activity, Migration, HealthAlerts, BenefitSummary, DfSP2Detail, E5Tables, QueryTable, Footer). Copy them with the read-only helper instead of transcribing by hand:

python .github/skills/sentinel-ingestion-report/slice_scratch.py --scratch temp/ingest_scratch_<ts>.md --list
python .github/skills/sentinel-ingestion-report/slice_scratch.py --scratch temp/ingest_scratch_<ts>.md --section AnomalyTable

The slicer prefers the ## PRERENDERED copy when a section name also exists as a raw data block, folds the nested ### lines of the Headings skeleton into one block (so --section Headings returns the full §-heading lock list), strips pipeline scaffolding (<!-- … --> comments, SectionTitle: markers), preserves #### sub-headings, and collapses blank runs — so the output drops straight into the report as a valid markdown table or fenced block. Do NOT paste the raw Key | Value | … data blocks (the early raw sections with a <!-- header --> comment and no |---| separator row) — they render as plain text, not tables, and dumping the whole scratchpad tail into one section corrupts the report.

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

Before starting ANY ingestion report:

1. Run Invoke-IngestionScan.ps1 — this single script handles ALL data gathering (Phases 1-5). The LLM does NOT run queries, transcribe output, or write scratchpad sections
2. Read config.json for workspace ID, tenant, subscription, and Azure MCP parameters
3. ALWAYS ask the user for output mode if not specified: inline chat summary, markdown file report, or both (default: both)
4. ALWAYS ask the user for timeframe if not specified: supported values are 1, 7, 30 (default), 60, or 90 days. The -Days parameter controls the primary window; deep-dive and comparison windows are derived automatically

Date Window Model

The -Days parameter drives three time windows used across all queries:

Example: -Days 60 → primary=60d, deep-dive=14d, comparison=28d

Dynamic period labels: Report column headers adapt automatically ("This Week"/"Last Week" for 7d deep-dive, "This Month"/"Last Month" for 30d, "This Period"/"Last Period" for 14d).

Exception: Q14 (24h anomaly detection) is unaffected by -Days — it uses fixed algorithmic constants (P30D lookback, 29-day weekday baseline). 5. ALWAYS use create_file for markdown reports (NEVER use PowerShell terminal commands) 6. ALWAYS sanitize PII from saved reports — use generic placeholders for real hostnames, workspace names, and tenant GUIDs in committed files 7. Read scratchpad + SKILL-report.md before rendering — the scratchpad is the sole data source for the report 8. Tier display convention — Azure CLI reports Data Lake tier tables as plan Auxiliary internally, but always refer to this tier as "Data Lake" in output — never use "Auxiliary"

Prerequisites

🔴 PROHIBITED

• ❌ Running KQL queries via MCP tools during data gathering — PS1 handles all queries
• ❌ Writing or modifying scratchpad sections manually — PS1 is the sole writer
• ❌ Reporting cost in dollar amounts — always use GB savings (e.g., "~78.7 GB/month savings")
• ❌ Fabricating ingestion volumes, device names, or anomaly percentages
• ❌ Overriding DL eligibility classification from PS1 output based on LLM knowledge
• ❌ Rendering the report without first reading the scratchpad file

Execution Workflow

Phase 0: Initialization

1. Read config.json for sentinel_workspace_id, subscription_id, Azure MCP parameters
2. Confirm output mode and timeframe with user (pass -Days to PS1; default 30)
3. Verify prerequisites: az login session active, correct subscription set

Phases 1-5: Data Gathering (automated by PS1)

Run Invoke-IngestionScan.ps1 — it handles all 5 phases automatically:

Post-processing (automated by PS1, Phases 4-5):

Scratchpad output: PS1 writes all results to temp/ingest_scratch_<timestamp>.md (~50 KB, ~64 named sections including PHASE_, PRERENDERED, and META blocks). See SKILL-report.md for the Section-to-Scratchpad Mapping.

Phase 6: Render Output (LLM)

🔴 MANDATORY — Load scratchpad + report template before rendering:

1. Read the scratchpad file (path printed by PS1). This single file contains ALL data from Phases 1-5.
2. Read SKILL-report.md for the complete rendering templates and formatting rules.

Pre-render validation:

1. Verify scratchpad has all 5 phase sections (PHASE_1 through PHASE_5)
2. Check that PHASE_5.DL_Script_Output is populated (proof of DL classification execution)
3. Cross-validate: Q11 TotalRulesInHealth against Q9 AR_Enabled — if >10% gap, note it

Render — Section-by-Section Checklist:

Render the report section by section per SKILL-report.md templates. Do NOT skip any section. If a section's data returned 0 results, render the section header with a "✅ No anomalies/items found" note.

Compute Top 3 Recommendations using Rule E: scan all scratchpad sections, score each candidate, select the top 3 by score.

Post-render:

• Render inline chat executive summary (if requested)
• Confirm markdown file path to user

Query File Reference

All queries are defined as YAML files in queries/phase1-5/. PS1 discovers, parses, and executes them automatically.

YAML Format

id: ingestion-q1                              # Unique identifier
name: Usage by DataType with Billing Breakdown # Human-readable name
description: Top 20 tables ranked by volume    # What it does
phase: 1                                       # Which phase (1-5)
type: kql                                      # kql | rest | cli | graph
timespan: P{days}D                             # Placeholder — PS1 substitutes at runtime
query: |                                       # KQL query (multiline block scalar)
  Usage
  | where TimeGenerated > ago({days}d)
  ...

Non-KQL types have additional fields:

Complete Query Inventory

Output Modes

Mode 1: Inline Chat Summary (default for quick requests)

Compact executive summary rendered directly in chat.

Mode 2: Markdown File Report

Full detailed report saved to reports/sentinel/sentinel_ingestion_report_<YYYYMMDD_HHMMSS>.md.

Mode 3: Both (default when user says "report" or "generate report")

Inline chat executive summary + full markdown file.

Ask user if not specified:

"How would you like the report? I can provide:

1. Inline chat summary — executive overview in chat
2. Markdown file — detailed report saved to reports/sentinel/
3. Both (recommended) — summary in chat + full report file"

Deterministic Rendering Rules

These rules eliminate LLM interpretation variance. Apply them EXACTLY during report rendering (Phase 6). No discretion allowed — the thresholds and formulas below are the sole authority.

Rule A: Anomaly Severity Classification

⚙️ Pre-computed by PS1 → PRERENDERED.AnomalyTable. Thresholds below retained for §8 methodology reference and manual verification.

Assign severity to each anomaly row deterministically based on absolute deviation AND volume.

Volume floor: The 0.01 GB minimum is enforced by the KQL queries. Tables below this floor are noise and MUST NOT appear in the anomaly table regardless of deviation percentage.

Override 1 — Rule-count: ANY table with ≥5 enabled rules AND an absolute change ≥40% (in either 24h or WoW) is automatically 🟠 regardless of base thresholds — a significant drop on a table feeding multiple rules signals potential connector or TI feed health issues that affect detection coverage. The 24h override catches same-day connector outages; the WoW override catches gradual multi-day degradation.

Override 2 — Near-zero: ANY table with deviation ≤ −95% AND max(volume) ≥ 0.05 GB is automatically 🟠 regardless of rule count — a near-complete signal loss on a significant table is an operational emergency (e.g., connector failure, API key expiry) even if no rules reference it directly.

⛔ PROHIBITED: Assigning severity based on "judgment", "context", or "this table is important" UNLESS the high-rule-count override above applies. Outside that specific override, the emoji MUST match the threshold table above — no discretionary overrides.

Rule B: Risk Rating Definition

In the Top 3 Recommendations table (§1), the "Risk" column means:

Risk = the security or operational impact of NOT acting on this recommendation.

⛔ PROHIBITED: Interpreting "Risk" as implementation difficulty, effort, or change management complexity. Those concerns belong in prose recommendations (§7b-d), NOT the Risk column.

Rule C: Weekday Average Computation

⚙️ Pre-computed by PS1 → PRERENDERED.DailyChart. Logic below retained for §8 methodology reference.

When computing per-weekday averages for the §4b daily trend chart:

1. Exclude the report-generation day: If the last day in PHASE_1.DailyTrend matches META.Generated date, always exclude it from weekday averages — the report was generated mid-day so this is a partial day regardless of its volume. This prevents the partial day from non-deterministically dragging down whichever weekday it falls on.
2. Exclude ingestion gaps: Any remaining day with total ingestion < 0.1 GB is also excluded. These are ingestion reporting gaps, not representative of normal patterns.
3. Formula: Weekday Avg = sum(GB for that weekday, excluding days per rules 1–2) / count(qualifying days for that weekday)
4. Round to 2 decimal places.

⛔ PROHIBITED: Including the report-generation partial day or days with < 0.1 GB in averages — they drag down specific weekdays non-deterministically.

Rule D: Cross-Validation Denominator

In §5b cross-validation (Q11 vs Q9), always use AR-only enabled count from Q9 as the denominator:

Gap% = (Q9_AR_Enabled - Q11_DistinctRules) / Q9_AR_Enabled × 100

Do NOT use Combined_Enabled (AR+CD) as the denominator. SentinelHealth only tracks AR executions (Scheduled + NRT), not Custom Detection executions. Comparing Q11 against combined AR+CD inflates the gap percentage.

Rule E: Top 3 Recommendation Ranking

Rank ALL candidate recommendations using this scoring formula. The top 3 by score become the Top 3 in §1. Computed by the LLM at render time by cross-referencing all scratchpad sections.

Score = SeverityWeight × ImpactValue

Sorting: severity-first, then score. 🔴 items always rank above 🟠 items, which always rank above 🟡 items, regardless of score. Within the same severity tier, rank by descending score. This ensures detection gaps and data loss signals are never buried below cost optimizations.

Tie-breaking within same severity: higher score wins. If scores are equal, higher SeverityWeight wins. If still tied, higher ImpactValue wins.

⛔ PROHIBITED: Selecting Top 3 recommendations based on narrative variety, "one from each category", or subjective importance. The formula determines ranking — the LLM renders, it does not curate.

License benefit activation: Surfaces when PRERENDERED.BenefitSummary or PRERENDERED.E5Tables show E5-eligible or DfS-P2-eligible volume that is not yet being claimed (benefit shows 0 or is absent while eligible tables are ingesting). ImpactValue = the eligible GB/day that could be offset.

Volume spike / cost anomaly: Surfaces when PHASE_5.Anomaly24h shows a large positive deviation (>50% above baseline) on a table with zero detection rules (per PHASE_4.CrossRef). A spiking table with no rules has cost impact but no detection value — a strong signal to investigate and potentially filter or move to DL.

Duplicate ingestion: Surfaces when the same network appliance sends data via both Syslog and CommonSecurityLog (CEF/ASA). Compare appliance names/IPs in PRERENDERED.SyslogFacility/PRERENDERED.SyslogHost against PRERENDERED.CSL_Vendor — overlapping sources indicate double billing for the same data. ImpactValue = the smaller of the two streams (the duplicate portion).

Domain Reference

This section provides the domain knowledge needed during Phase 6 rendering. When writing deep dive sections (§3), anomaly analysis (§4), and recommendations (§7), consult these reference tables for interpretation guidance.

SecurityEvent — EventID Optimization

Which EventIDs generate the most volume and their detection vs. cost tradeoff:

🟣 Split ingestion tip: For any deep-dive table classified as 🟢 Keep Analytics (active detection rules), individual high-volume values with zero rule references (verified via Phase 4 value-level check) are strong candidates for sub-table split ingestion. Route those values to Data Lake via DCR transformation — they remain available for hunting while the detection-relevant values stay on Analytics tier. KQL jobs can also run against this split-routed DL data to surface aggregated insights back to Analytics if needed.

Syslog — Facility Reference

Optimization potential by Syslog facility:

Syslog — DCR Severity-per-Facility Recommendations

The Data Collection Rule allows setting a minimum severity level per facility — the single most impactful cost control for Syslog:

Syslog — SeverityLevel Values

Syslog — ProcessName Security Relevance

🟣 Split ingestion tip: If daemon facility accounts for >50% of Syslog and Q6c reveals systemd + systemd-logind + dbus-daemon dominate, consider a DCR transformation routing those processes to Data Lake while keeping sshd, auditd, and other security-critical processes in Analytics. KQL jobs can complement this by querying the DL-routed portion on schedule.

Syslog — Log Forwarding Architecture Note

In environments using centralized rsyslog/syslog-ng forwarders:

• Computer = the log forwarder hostname (many servers collapse to 1-2 forwarders)
• HostName = the actual originating device (from syslog header)
• HostIP = the originating device's IP address

The Q6a query uses SourceHost = iff(isnotempty(HostName) and HostName != Computer, HostName, Computer) to prefer the original source. If Q6a shows only 1-2 hosts despite expecting 100+ servers, the environment uses forwarding.

CommonSecurityLog — Vendor Reference

Firewall traffic/session logs often account for 60-80% of CSL volume. These are primarily TRAFFIC or Accept events with low detection value. Consider DCR transformation, Data Lake tier, split ingestion, or DL + KQL job promotion (these last two can be combined).