Conditional Access Policy Investigation - Instructions

Purpose

This skill investigates Conditional Access (CA) policy changes in correlation with sign-in failures to detect:

• Legitimate troubleshooting (authorized policy changes to resolve access issues)
• Security control bypass (unauthorized policy modifications to circumvent blocks)
• Privilege abuse (users with admin rights weakening security controls)

The key distinction is whether policy changes were authorized and necessary vs self-service bypass of security controls.

📑 TABLE OF CONTENTS

1. Critical Investigation Rules - Mandatory workflow steps
2. Common Error Codes - Sign-in failure reference
3. CA Policy States - Understanding policy modes
4. 5-Step Investigation Workflow - KQL queries and analysis
5. Real-World Example - Complete walkthrough
6. Critical Mistakes - What NOT to do
7. Security Recommendations - Remediation guidance

Critical Investigation Rules

When investigating sign-in failures (error codes 53000, 50074) with CA policy correlation:

⚠️ MANDATORY STEPS - DO NOT SKIP:

1. Query ALL CA policy changes in chronological order (±2 days from failure time)
2. Parse policy state transitions from the JSON (enabled → disabled → report-only)
3. Compare failure timeline with policy change timeline
4. Verify logical consistency: Ask "does this make sense?"

Key Questions to Answer:

• Was the user blocked BEFORE the policy change?
• Did the policy change resolve the block?
• Who initiated the policy change? (same user = suspicious)
• What was the business justification?

Common Error Codes

Error Code Investigation Priority

CA Policy State Meanings

State Transition Risk Assessment

Investigation Workflow Pattern

Step 1: Identify Sign-In Failures

Query sign-in failures with CA context:

// Get failures with CA context
union isfuzzy=true SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated between (datetime(<START>) .. datetime(<END>))
| where UserPrincipalName =~ '<UPN>'
| where ResultType != '0'
| where AppDisplayName has '<APPLICATION>'  // e.g., "Visual Studio Code"
| project TimeGenerated, IPAddress, Location, ResultType, ResultDescription, 
    ConditionalAccessStatus, UserAgent
| order by TimeGenerated asc

What to Look For:

• ResultType values: 53000, 50074, 530032, 53003
• ConditionalAccessStatus: "failure", "notApplied"
• Pattern of repeated failures followed by success

Step 2: Query ALL CA Policy Changes in Timeframe

CRITICAL: Query ±2 days from the first failure time

let failure_time = datetime(<FIRST_FAILURE_TIME>);
let start = failure_time - 2d;
let end = failure_time + 2d;
AuditLogs
| where TimeGenerated between (start .. end)
| where OperationName has_any ("Conditional Access", "policy")
| where Identity =~ '<UPN>' or tostring(InitiatedBy) has '<UPN>'
| extend InitiatorUPN = tostring(parse_json(InitiatedBy).user.userPrincipalName)
| extend InitiatorIPAddress = tostring(parse_json(InitiatedBy).user.ipAddress)
| extend TargetName = tostring(parse_json(TargetResources)[0].displayName)
| project TimeGenerated, OperationName, Result, InitiatorUPN, InitiatorIPAddress, 
    TargetName, CorrelationId
| order by TimeGenerated asc  // CRITICAL: Chronological order

Critical Analysis Points:

• InitiatorUPN: Who made the change? Same user as blocked = suspicious
• TargetName: Which policy was modified?
• TimeGenerated: Did change occur AFTER sign-in failures?
• Order: Always chronological (oldest first) to see cause/effect

Step 3: Parse Policy State Changes

For each CorrelationId from Step 2, get detailed changes:

// Get detailed property changes for a specific policy modification
AuditLogs
| where CorrelationId == "<CORRELATION_ID>"
| extend ModifiedProperties = parse_json(TargetResources)[0].modifiedProperties
| mv-expand ModifiedProperties
| extend PropertyName = tostring(ModifiedProperties.displayName)
| extend OldValue = tostring(ModifiedProperties.oldValue)
| extend NewValue = tostring(ModifiedProperties.newValue)
| project TimeGenerated, PropertyName, OldValue, NewValue

Key Properties to Extract:

• Look for "state" property in the JSON
• Parse OldValue and NewValue for state transitions
• Document: enabled → disabled → enabledForReportingButNotEnforced

Step 4: Extract Policy State from JSON

Manual JSON Parsing:

The OldValue and NewValue fields contain JSON. Look for the "state" field:

{
  "state": "enabled",
  "conditions": { ... },
  "grantControls": { ... }
}

Build the Timeline:

1. Extract "state" from each OldValue and NewValue
2. Create chronological list: enabled → disabled → enabledForReportingButNotEnforced
3. Correlate with sign-in failure timeline

Step 5: Security Assessment

Compare timelines and assess intent:

Risk Escalation Criteria:

Real-World Example Analysis

Scenario: User blocked by device compliance policy, then modifies policy

Timeline

Analysis

1. ✅ Policy was correctly blocking non-compliant device

   • Device compliance policy was enforcing as intended
   • User's device failed compliance checks (not enrolled or failing policy)

2. 🚨 User disabled security control to bypass block

   • Same user who was blocked made the policy change
   • Change occurred within 4 minutes of repeated failures
   • No approval or change request documented

3. ⚠️ User partially reversed by enabling report-only

   • Shows some awareness that disabling was too aggressive
   • But report-only still defeats the purpose (doesn't block)

4. ❌ Report-only mode is NOT a valid security posture

   • Logs violations but allows non-compliant access
   • Creates false sense of security (policy "exists" but doesn't protect)

Assessment

Recommendations

1. Immediate Actions:

   • Restore policy to enabled state
   • Verify user's device compliance status
   • Document incident for security review

2. User-Specific:

   • Enroll user's device in Intune
   • Verify device meets compliance requirements
   • Review if user needs Security Administrator role

3. Process Improvements:

   • Implement approval workflow for CA policy changes
   • Create alert for policy state changes (enabled → disabled/report-only)
   • Review all users with permission to modify CA policies
   • Consider PIM for Security Administrator role

Critical Mistakes to Avoid

❌ DON'T:

✅ DO:

Security Recommendations

When CA Policy Changes Are Detected

1. Determine Legitimacy

• Was the policy change authorized?
• Was there a valid business reason?
• Did the user have approval to make this change?
• Is there a change request ticket?

2. Assess Impact

• How many users affected by policy change?
• What applications/resources are now unprotected?
• How long was the policy disabled/weakened?
• Are there compliance implications (regulatory requirements)?

3. Remediation Actions

4. Long-Term Improvements

Prerequisites

Required MCP Servers

This skill requires:

1. Microsoft Sentinel MCP - For KQL queries against SigninLogs and AuditLogs
   • mcp_sentinel-data_query_lake: Execute KQL queries
   • mcp_sentinel-data_search_tables: Discover table schemas

Required Data Sources

• SigninLogs - Interactive sign-in events with CA status
• AADNonInteractiveUserSignInLogs - Non-interactive sign-in events
• AuditLogs - CA policy modification events

Required Permissions

To view CA policy changes in AuditLogs, ensure:

• Sentinel workspace has AuditLogs ingestion enabled
• User has appropriate RBAC to query the workspace

Integration with Other Skills

CA Policy Investigation often follows a user-investigation:

1. Run user-investigation skill → Identifies sign-in failures
2. Notice CA-related error codes → 53000, 50074, 530032
3. Run ca-policy-investigation skill → Correlate failures with policy changes
4. Document findings → Security assessment with remediation recommendations

Key Integration Points:

• Sign-in failure data comes from user investigation
• CA policy changes are NEW queries specific to this skill
• Assessment combines timeline correlation with policy state analysis

Context Memory Review — Instructions

Purpose

Investigation workflows in this project lean on a tenant-context memory file — a local, gitignored living document that records environment-specific ground truth (known automation/orchestration fingerprints, known-good IPs, account classifications, honeypot/field-device inventory, validated personnel, and documented false-positive rules). Scan automations (e.g. the daily Threat Pulse) read that file to render accurate verdicts.

Over a week of scans, drill-down investigations validate new ground truth — new IPs, new personas, new FP classes, new device classes — that is not yet captured in the context file. This skill reads the last N days of scan reports, compares them against the current context file, and produces a propose-only review document: a list of discrete, human-reviewable candidate changes (ADD / MODIFY / FLAG) with section anchors, proposed text, supporting evidence, recurrence counts, and confidence.

This skill is the first half of a deliberate two-phase, human-in-the-loop workflow:

🔴 CRITICAL RULES — READ FIRST

1. PROPOSE-ONLY. NEVER edit the context file in this skill. Do not write, append to, or modify the context memory file. Do not git commit, push, or open a PR. The only file this skill writes is the review document in the output directory.

2. Read-only against the tenant. If any live queries are needed to corroborate a candidate change, they MUST be read-only (per the Remediation Output Policy). Prefer evidence already present in the reports — only query the tenant to disambiguate a contradiction.

3. ⛔ Feedback-loop guard (the single most important rule). Scan reports are partly downstream of the context file: a scan verdict may simply echo an existing context entry rather than independently confirm it. You MUST distinguish:

   • First-party validation — a drill-down in the report actually ran a query/enrichment and confirmed the fact (e.g. "enriched IP 203.0.113.10 → datacenter ASN, 0 abuse reports, recurred on 3 days"). This CAN drive a High-confidence proposal.
   • Context-derived echo — the report verdict only restated something the context file already said ("🟢 known orchestration IP per tenant context"). This must NOT be promoted into a new or strengthened entry. Promoting echoes entrenches errors. When unsure, classify as echo.

4. Never propose weakening or removing a documented FP/safety guardrail based solely on its absence from the week's reports. Absence of a finding ≠ obsolescence of a guardrail. Staleness candidates are FLAG-only, Low confidence, for human judgment — never auto-REMOVE.

5. Evidence-based only. Every proposed change cites the specific report file(s), date(s), and finding it derives from. Never invent entities, counts, IPs, UPNs, or dates. If the reports don't support a change, don't propose it.

6. PII stays local. The review document will contain live tenant entities (IPs, UPNs, device names). Write it ONLY to the gitignored output directory. Never commit it, never include it in a PR, never paste tenant PII into any git artifact.

Inputs (supplied by the invoking prompt / workflow)

The invoking workflow or user supplies these. If invoked interactively without them, ask once, then proceed with the defaults shown.

Execution Workflow

Phase 0 — Load inputs and current state

1. Read the context file in full (context_file). Build an internal index of its structure: every section heading (the anchor targets for proposals), and within sections the discrete entries — table rows (e.g. IP tables), bullet points, labelled sub-notes (e.g. "A.2", "Section C"), device entries. Note any validated YYYY-MM-DD provenance stamps.
2. Enumerate the reports in window. List files in reports_dir matching reports_glob, select those whose date (from filename YYYYMMDD if present, else file mtime) falls within lookback_days. Sort oldest→newest. If zero reports are in window, STOP and report "no reports in window — nothing to review" (a normal quiet-week outcome, not a failure).
3. Read each in-window report. For large reports, read in ranges. Extract structured signal:
   • Concrete entities that appeared with a verdict: IPs, UPNs/accounts, device/host names, OAuth apps, incident IDs, CVEs.
   • For each: was the verdict reached by a first-party drill-down (a query/enrichment was executed in the report) or an echo of existing context? Capture the distinction — it gates confidence.
   • New FP classes / tuning notes the report's drill-downs articulated.
   • Any contradiction: a drill-down that concluded the opposite of an existing context entry.
   • Note in each report whether the context file was successfully loaded/applied during that scan (the reports state this) — echoes only count as echoes if context was actually applied.

Phase 1 — Correlate across the week

Aggregate signal across all in-window reports:

1. Recurrence — For each candidate entity/pattern, count how many distinct report-days it appeared on with a consistent first-party classification. Recurrence is the backbone of confidence.
2. Match against the context file index — For each candidate, determine whether it is:
   • Absent from the context file → ADD candidate.
   • Present but refined by the reports (role/volume/scope changed, new regional sibling, expanded persona list) → MODIFY candidate.
   • Present and merely echoed (no new first-party info) → NOT a candidate (drop it; feedback-loop guard). It may at most justify refreshing a validated date if a first-party drill-down re-confirmed it — and that is a Low/Medium MODIFY, clearly labelled "provenance refresh only".
   • Contradicted by a first-party drill-down → FLAG candidate (never auto-resolve).
3. Staleness sweep (FLAG-only) — Identify context entries that were NOT referenced by ANY in-window report. These are candidates for human review, not removal. Low confidence. Exclude documented safety/FP guardrails from staleness flags entirely (their value is in preventing future errors, not in weekly hit-rate).

Phase 2 — Score and assemble proposals

Assign each candidate a type and confidence:

For every proposal, produce:

• ID — sequential (P1, P2, …).
• Type + Confidence.
• Target section — the exact heading/anchor in the context file where it belongs (for ADD), or the exact existing entry text being changed (for MODIFY/FLAG).
• Proposed text — for ADD/MODIFY, the literal line/table-row/bullet to insert or the before→after change, written in the context file's existing style and including a (validated <today's date>) stamp where the file uses that convention.
• Rationale — one or two sentences.
• Evidence — the report file name(s) + date(s) + the specific finding, and an explicit note of whether it was first-party or echo (only first-party drives ADD/MODIFY).
• Recurrence — "appeared on N of M report-days".
• Apply instruction — precise enough for a later interactive session to make a surgical edit (which section, insert-after-which-line, exact text). For FLAG items, the question the human must answer.

Phase 3 — Write the review document

Write the document to output_dir (create the folder if needed) as: <output_dir>/context-review_<YYYYMMDD>_<HHMMSS>.md

Use this structure:

# Context Memory Review — <today's date>

**Context file reviewed:** <context_file>
**Reports reviewed:** <N> file(s) over <lookback_days>d (<earliest date> → <latest date>)
**Proposed changes:** <A> ADD · <M> MODIFY · <F> FLAG
**Confidence mix:** <High count> High · <Medium count> Medium · <Low count> Low

> ⚠️ PROPOSE-ONLY. No changes have been made to the context file. To apply, open an interactive
> session and say e.g. "apply items P1, P3, P7" — those edits will be made surgically with a
> validated-date stamp. Review each item's evidence before approving.

## Reports in this review window
| Date | File | Context applied during scan? |
|------|------|------------------------------|
| ... | ... | yes / no |

## Proposed changes

### P1 — [ADD · High] <short title>
- **Target section:** <heading/anchor>
- **Proposed text:**
  > <literal text to add, in file style, with (validated <date>)>
- **Rationale:** ...
- **Evidence:** <report file(s) + date(s) + finding>; first-party drill-down.
- **Recurrence:** appeared on N of M report-days.
- **Apply instruction:** Insert under "<section>" after "<anchor line>".

### P2 — [MODIFY · Medium] ...
...

### P3 — [FLAG · Low] <contradiction or staleness> ...
- **Question for human:** ...

## Items considered but NOT proposed (feedback-loop guard)
Brief list of candidate signals that were only context-echoes (already in the file, no new first-party
evidence) and were therefore intentionally dropped — so the reviewer can confirm nothing was missed.

## Summary
One paragraph: the week's theme, the highest-value proposed addition, any contradiction needing
attention, and the count of staleness flags.

Phase 4 — Report to chat

End your response with a concise summary: context file + report window reviewed, counts of ADD/MODIFY/FLAG by confidence, the single highest-value proposed change, any contradictions surfaced, the output document path, and a reminder that nothing was applied and how to apply (interactive "apply items …").

Quality Checklist

Before finishing, verify:

• The context file was not modified; no commit/PR/push occurred.
• The review document was written only to the gitignored output directory.
• Every ADD/MODIFY proposal is backed by first-party evidence (not a context echo).
• No proposal weakens/removes a safety or FP guardrail on the basis of absence alone.
• Every proposal cites specific report file(s) + date(s) and a recurrence count.
• Contradictions are FLAG (human decides), never auto-resolved.
• Staleness candidates are FLAG · Low, and exclude documented guardrails.
• Proposed text matches the context file's existing style and includes a validated-date stamp where the file uses that convention.
• The "considered but not proposed" section documents the dropped echoes.

Notes

• This skill is environment-agnostic. All tenant-specific values (which context file, which reports, output location) are supplied by the invoking workflow or user — keep this file free of any tenant-specific identifiers, hostnames, UPNs, or environment names.
• Apply is intentionally out of scope here. Keeping propose and apply as separate phases — with apply driven by an explicit human instruction — is the safety boundary that prevents an unattended run from silently rewriting the ground-truth the scans depend on.

Custom Detection Authoring — Instructions

Purpose

This skill deploys custom detection rules to Microsoft Defender XDR via the Microsoft Graph API (/beta/security/rules/detectionRules). It handles:

• Query adaptation — Converting Sentinel KQL queries into custom detection format
• Single-rule deployment — Creating one rule via Graph API
• Batch deployment — Deploying multiple rules from a JSON manifest
• Lifecycle management — Listing, updating, enabling/disabling, and deleting rules
• Validation — Dry-run queries in Advanced Hunting before deployment

Entity Type: Custom detection rules (Defender XDR)

Writing new detection queries from scratch? This skill focuses on deploying and managing detection rules — not query creation. If you need to write detection KQL from scratch (schema validation, community examples, performance optimization), use the kql-query-authoring skill first with CD intent markers (say "create custom detection queries for [scenario]"). It will produce Sentinel-format queries with cd-metadata blocks ready for this skill to adapt and deploy.

📑 TABLE OF CONTENTS

1. Prerequisites — Auth, scopes, PowerShell modules
2. Critical Rules — Mandatory constraints (includes query adaptation checklist)
3. Naming Convention — Standardized displayName format (no prefixes, no MITRE IDs, colon separators)
4. API Reference — Graph API schema and field values
5. Frequency & Lookback — Schedule periods, lookback windows, NRT constraints
6. Deployment Workflow — Step-by-step process
7. Batch Deployment — Manifest-driven multi-rule deployment
8. Lifecycle Management — CRUD operations
9. Existing Rule Discovery — Search Analytic Rules & Custom Detections by table, EventID, or keyword
10. Known Pitfalls — Lessons learned (18 pitfalls documented)
11. CD Metadata Contract — Schema for query file ↔ detection skill coordination

Prerequisites

Required PowerShell Module

# Microsoft.Graph.Authentication — provides Invoke-MgGraphRequest
Install-Module Microsoft.Graph.Authentication -Scope CurrentUser

Required Graph API Scopes

Authentication

# Read-only
Connect-MgGraph -Scopes "CustomDetection.Read.All" -NoWelcome

# Full CRUD
Connect-MgGraph -Scopes "CustomDetection.ReadWrite.All" -NoWelcome

Why Invoke-MgGraphRequest? The Graph MCP server and az rest both return 403 for custom detection endpoints — they lack the CustomDetection.* scopes. Invoke-MgGraphRequest uses interactive delegated auth with consent, which works.

Companion Script

Deploy-CustomDetections.ps1 — PowerShell script for manifest-driven batch deployment. See Batch Deployment.

⚠️ CRITICAL RULES — READ FIRST ⚠️

Mandatory Query Requirements

Custom detection queries have strict requirements that differ from Sentinel analytic rules:

Required event-unique columns by table type (MS Learn source):

Query Adaptation Checklist

When converting a Sentinel query to custom detection format:

1. ✅ Remove bare summarize — project raw rows instead. Exception: summarize with arg_max is allowed for threshold-based detections (see Pitfall 3)
2. ✅ Project the timestamp column as-is: TimeGenerated = TimeGenerated for Sentinel/LA tables, Timestamp for XDR tables. Never alias one to the other.
3. ✅ Project the impacted asset identifier column — the column name must match a valid identifier from Impacted Asset Types. Examples: DeviceName = Computer for device-focused detections, AccountUpn = UserId for user-focused. See Pitfall 9.
4. ✅ Project event-unique columns per table type — DeviceId + ReportId for MDE tables; ReportId for other XDR tables; recommended proxy ReportId for Sentinel tables (e.g., ReportId = CorrelationId). Caveat: proxy columns may contain empty strings for some events — acceptable but means those rows won't be individually identifiable in alert details.
5. ✅ Add a time filter as the first where clause — prefer ingestion_time() > ago(1h) over Timestamp > ago(1h) (see tip below). NRT exception: For NRT rules (schedule: "0"), omit all time filters — ingestion_time() causes 400 Bad Request in NRT mode (see Pitfall 17). Timestamp > ago(...) is accepted but unnecessary.
6. ✅ Remove let variables for NRT rules — NRT rejects let entirely (generic 400 error, undocumented). Inline all dynamic arrays directly in where clauses. Non-NRT rules tolerate let.
7. ✅ Validate via Advanced Hunting dry-run before deployment
8. ✅ For NRT rules: avoid tostring() on dynamic columns — use native string columns instead (e.g., Properties instead of tostring(Properties_d)). See Pitfall 11.
9. ✅ For NRT rules: verify the table's ingestion lag justifies NRT. See Pitfall 12.
10. ✅ Count unique {{Column}} references across title AND description combined — max 3 unique columns total (shared across both fields, not per-field). Exceeding this returns 400 Bad Request: "Dynamic properties in alertTitle and alertDescription must not exceed 3 fields". See Pitfall 14.

Performance tip (from MS Learn): "Avoid filtering custom detections by using the Timestamp column. The data used for custom detections is prefiltered based on the detection frequency." Use ingestion_time() instead — it aligns with the platform's pre-filtering for better performance. For scheduled rules, match the time filter to the run frequency (ingestion_time() > ago(1h) for 1H rules). For NRT rules, no time filter is needed. ⚠️ PowerShell note: When building queryText containing backslashes (file paths, regex), always use single-quoted here-strings (@'...'@) to avoid escape sequence mangling — see Pitfall 15.

Example Adaptation

Before (Sentinel KQL — uses summarize):

let _Lookback = 7d;
SecurityEvent
| where TimeGenerated > ago(_Lookback)
| where EventID == 4799
| where TargetSid == "S-1-5-32-544"
| where SubjectUserSid != "S-1-5-18"
| where AccountType != "Machine"
| where not(SubjectUserSid endswith "-500")
| project TimeGenerated, Computer, Actor = SubjectUserName, ...
| summarize EnumerationCount = count(), Processes = make_set(CallerProcess)
    by Actor, ActorDomain, ActorSID

After (Custom Detection — row-level, mandatory columns):

SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4799
| where TargetSid == "S-1-5-32-544"
| where SubjectUserSid != "S-1-5-18"
| where AccountType != "Machine"
| where not(SubjectUserSid endswith "-500")
| project
    TimeGenerated = TimeGenerated,
    DeviceName = Computer,
    AccountName = SubjectUserName,
    AccountDomain = SubjectDomainName,
    AccountSid = SubjectUserSid,
    CallerProcess = CallerProcessName,
    ReportId = CallerProcessId

Key changes:

• Removed let _Lookback → hardcoded ago(1h)
• Removed summarize → raw project
• Added TimeGenerated = TimeGenerated (identity projection — mandatory)
• Added DeviceName = Computer (impacted asset identifier — device-focused detection)
• Added ReportId = CallerProcessId (proxy ReportId — event-unique identifier)

Naming Convention

The displayName should be a clean, title-case description of what the detection finds. The portal columns already show Scheduling Type, Tactics, and Techniques — don't repeat them in the name.

API Reference

Endpoint

POST   /beta/security/rules/detectionRules          — Create
GET    /beta/security/rules/detectionRules           — List all
GET    /beta/security/rules/detectionRules/{id}      — Get by ID
PATCH  /beta/security/rules/detectionRules/{id}      — Update
DELETE /beta/security/rules/detectionRules/{id}      — Delete

Schedule Periods

Alert Severity Values

Alert Category Values

category is a case-sensitive, single-value, server-validated enum accepting two groups:

• MITRE tactics (title case): InitialAccess, Execution, Persistence, PrivilegeEscalation, DefenseEvasion, CredentialAccess, Discovery, LateralMovement, Collection, Exfiltration, CommandAndControl, Impact, Reconnaissance, ResourceDevelopment
• Non-tactic threat categories: Malware, Ransomware, SuspiciousActivity, UnwantedSoftware — these hide the MITRE techniques field in the portal (MS Learn); prefer a tactic when you want techniques to display.

Portal label note: The portal labels this control "Tactic" (UX rename, 2026), but the API field stays category — single-value, automation unaffected. Validation details in Pitfall 18.

Impacted Asset Types

Device asset:

{
    "@odata.type": "#microsoft.graph.security.impactedDeviceAsset",
    "identifier": "<identifier>"
}

Valid device identifiers: deviceId, deviceName, remoteDeviceName, targetDeviceName, destinationDeviceName

User asset:

{
    "@odata.type": "#microsoft.graph.security.impactedUserAsset",
    "identifier": "<identifier>"
}

Valid user identifiers: accountObjectId, accountSid, accountUpn, accountName, accountDomain, accountId, requestAccountSid, requestAccountName, requestAccountDomain, recipientObjectId, processAccountObjectId, initiatingAccountSid, initiatingProcessAccountUpn, initiatingAccountName, initiatingAccountDomain, servicePrincipalId, servicePrincipalName, targetAccountUpn

Mailbox asset:

{
    "@odata.type": "#microsoft.graph.security.impactedMailboxAsset",
    "identifier": "<identifier>"
}

Valid mailbox identifiers: accountUpn, fileOwnerUpn, initiatingProcessAccountUpn, lastModifyingAccountUpn, targetAccountUpn, senderFromAddress, senderDisplayName, recipientEmailAddress, senderMailFromAddress

Minimal Valid POST Body

{
    "displayName": "Rule Name",
    "isEnabled": true,
    "queryCondition": {
        "queryText": "SecurityEvent\r\n| where TimeGenerated > ago(1h)\r\n| ..."
    },
    "schedule": {
        "period": "1H"
    },
    "detectionAction": {
        "alertTemplate": {
            "title": "Alert Title",
            "description": "Alert description text.",
            "severity": "medium",
            "category": "Discovery",
            "recommendedActions": null,
            "mitreTechniques": ["T1069.001"],
            "impactedAssets": [
                {
                    "@odata.type": "#microsoft.graph.security.impactedDeviceAsset",
                    "identifier": "deviceName"
                }
            ]
        },
        "responseActions": []
    }
}

impactedAssets: Must contain at least 1 element — an empty array causes 400 BadRequest. Every detection must map to at least one impacted entity (device, user, or mailbox). See Pitfall 13.

recommendedActions: Can be null or a string. The portal sets it to null by default.

responseActions: Must always be [] — response actions are prohibited in LLM-authored detections (see Critical Rules). Must be [], not null — sending null causes 400 Bad Request. See Pitfall 10.

organizationalScope: Omit this field entirely for tenant-wide rules (the API default). Including "organizationalScope": null explicitly may cause 400 Bad Request in some API versions.

Custom details (not shown above): The API also supports a customDetails array of key-value pairs surfaced in the alert side panel. Each rule supports up to 20 KVPs with a combined 4KB size limit. Keys are display labels; values are query column names. See MS Learn.

Related evidence (not shown above): Beyond impactedAssets, the entity mapping also supports linking related evidence entities (Process, File, Registry value, IP, OAuth application, DNS, Security group, URL, Mail cluster, Mail message). These provide correlation context but are not impacted assets. See MS Learn.

Dynamic Alert Titles and Descriptions

Alert titles and descriptions can reference query result columns using {{ColumnName}} syntax, making alerts self-descriptive:

{
    "title": "Admin Group Enumeration by {{AccountName}} on {{DeviceName}}",
    "description": "User {{AccountName}} enumerated group {{TargetGroupName}} on the device."
}

Frequency & Lookback

Lookback Windows by Frequency

Each frequency has a built-in lookback window. Results outside this window are ignored even if the query requests them:

Tip: Match the query time filter to the run frequency (ago(1h) for 1H rules), not the full lookback window. The lookback ensures late-arriving data is caught, but your filter should target the detection window.

NRT Constraints

NRT (Continuous, period: "0") rules have stricter requirements than scheduled rules:

NRT-Supported Tables

Not all tables support NRT frequency. Use NRT only with these tables:

Defender XDR tables: AlertEvidence, CloudAppEvents, DeviceEvents, DeviceFileCertificateInfo, DeviceFileEvents, DeviceImageLoadEvents, DeviceLogonEvents, DeviceNetworkEvents, DeviceNetworkInfo, DeviceInfo, DeviceProcessEvents, DeviceRegistryEvents, EmailAttachmentInfo, EmailEvents*, EmailPostDeliveryEvents, EmailUrlInfo, IdentityDirectoryEvents, IdentityLogonEvents, IdentityQueryEvents, UrlClickEvents

* EmailEvents: LatestDeliveryLocation and LatestDeliveryAction columns are excluded from NRT.

Sentinel tables (Preview): ABAPAuditLog_CL, ABAPChangeDocsLog_CL, AuditLogs, AWSCloudTrail, AWSGuardDuty, AzureActivity, CommonSecurityLog, GCPAuditLogs, MicrosoftGraphActivityLogs, OfficeActivity, Okta_CL, OktaV2_CL, ProofpointPOD, ProofPointTAPClicksPermitted_CL, ProofPointTAPMessagesDelivered_CL, SecurityAlert, SecurityEvent, SigninLogs

Important: SecurityEvent and SigninLogs support NRT — our Event ID 4799/4702 queries can run as NRT if they meet the single-table/no-joins constraint.

Ingestion Lag Consideration — NRT Suitability

A table being NRT-supported means the API accepts NRT rules — not that NRT is the right choice. If a table's ingestion lag exceeds the detection frequency benefit, NRT adds overhead with no detection speed improvement. See Pitfall 12 for a per-table ingestion lag assessment and recommendation matrix. Rule of thumb: if ingestion lag > 30 min, use 1H scheduled instead.

Custom Frequency (Sentinel Data Only)

For rules based entirely on Sentinel-ingested data, a custom frequency is available (Preview):

• Range: 5 minutes to 14 days
• Lookback: Automatically calculated — 4× frequency for sub-daily, 30 days for daily or longer
• Requirement: Data must be available in Microsoft Sentinel (not XDR-only tables)

Deployment Workflow

🔴 DEPLOYMENT GATE: Only proceed to Steps 2-3 (API calls) when the user has explicitly requested deployment. Trigger phrases: "deploy", "create the rule", "push", "POST it", "make it live". If the user asked to "author", "write", "create a manifest", "prepare", or "draft" a detection — stop after validation (Step 1) and manifest generation. Present the manifest JSON for review and wait for explicit deployment confirmation.

Single Rule Deployment

Step 1: Validate the query in Advanced Hunting

Run the adapted query with a 1h lookback to validate schema:

Use RunAdvancedHuntingQuery with the adapted KQL query.
Confirm: 0 or more results, correct column schema (TimeGenerated, DeviceName, AccountName, etc.)

Then run with 30d lookback to confirm it returns real data:

Change ago(1h) to ago(30d) for the validation run.
Verify results contain expected columns and realistic data.

Step 2: Check for duplicates, then build and POST the rule

Connect-MgGraph -Scopes "CustomDetection.ReadWrite.All" -NoWelcome

# Pre-flight: check if rule name already exists
$ruleName = "Rule Name"
$existing = (Invoke-MgGraphRequest -Method GET `
    -Uri "/beta/security/rules/detectionRules" -OutputType PSObject).value `
    | Where-Object { $_.displayName -eq $ruleName }
if ($existing) {
    Write-Host "Rule '$ruleName' already exists (ID: $($existing.id)). Skipping POST."
    return
}

$body = @{
    displayName = $ruleName
    isEnabled = $true
    queryCondition = @{
        queryText = "SecurityEvent`r`n| where TimeGenerated > ago(1h)`r`n| ..."
    }
    schedule = @{ period = "1H" }
    detectionAction = @{
        alertTemplate = @{
            title = "Alert Title"
            description = "Description"
            severity = "medium"
            category = "Discovery"
            recommendedActions = $null
            mitreTechniques = @("T1069.001")
            impactedAssets = @(
                @{
                    "@odata.type" = "#microsoft.graph.security.impactedDeviceAsset"
                    identifier = "deviceName"
                }
            )
        }
        responseActions = @()
    }
} | ConvertTo-Json -Depth 10

$result = Invoke-MgGraphRequest -Method POST `
    -Uri "/beta/security/rules/detectionRules" `
    -Body $body -ContentType "application/json" -OutputType PSObject

Step 3: Verify creation

$rules = Invoke-MgGraphRequest -Method GET `
    -Uri "/beta/security/rules/detectionRules" -OutputType PSObject
$rules.value | Select-Object id, displayName, isEnabled,
    @{N='Schedule';E={$_.schedule.period}},
    @{N='Status';E={$_.lastRunDetails.status}} | Format-Table -AutoSize

Batch Deployment

Use the companion script Deploy-CustomDetections.ps1 for manifest-driven batch deployment.

Manifest storage: Save manifest JSON files in the temp/ folder (gitignored). Manifests are deployment artifacts, not versioned query definitions.

Manifest Format

See example-manifest.json for a complete 2-rule reference covering NRT and scheduled (with summarize/arg_max) patterns.

The script reads a JSON file containing an array of rule definitions:

[
    {
        "displayName": "Admin Group Enumeration by Non-Admin User",
        "title": "Admin Group Enumeration by {{AccountName}} on {{DeviceName}}",
        "queryText": "SecurityEvent\r\n| where TimeGenerated > ago(1h)\r\n| ...",
        "schedule": "0",
        "severity": "medium",
        "category": "Discovery",
        "mitreTechniques": ["T1069.001", "T1087.001"],
        "description": "User {{AccountName}} enumerated the local Administrators group.",
        "recommendedActions": "Verify whether the user has a legitimate reason to enumerate admin group membership.",
        "impactedAssets": [
            { "type": "device", "identifier": "deviceName" },
            { "type": "user", "identifier": "accountSid" }
        ],
        "responseActions": []
    }
]

Usage

# Dry-run — validate all queries in Advanced Hunting without creating rules
.\Deploy-CustomDetections.ps1 -ManifestPath .\temp\4799_4702.json -DryRun

# Deploy all rules from manifest (skips existing rules by default)
.\Deploy-CustomDetections.ps1 -ManifestPath .\temp\4799_4702.json

# Deploy and overwrite — attempt POST even if rule name exists (may cause 409)
.\Deploy-CustomDetections.ps1 -ManifestPath .\temp\4799_4702.json -Force

Lifecycle Management

List All Rules

$rules = Invoke-MgGraphRequest -Method GET `
    -Uri "/beta/security/rules/detectionRules" -OutputType PSObject
$rules.value | Select-Object id, displayName, isEnabled,
    @{N='Schedule';E={$_.schedule.period}},
    @{N='LastRun';E={$_.lastRunDetails.status}},
    @{N='Created';E={$_.createdDateTime}} | Format-Table -AutoSize

Get Rule by ID

$rule = Invoke-MgGraphRequest -Method GET `
    -Uri "/beta/security/rules/detectionRules/5632" -OutputType PSObject
$rule | ConvertTo-Json -Depth 10

Update Rule (PATCH)

PATCH /beta/security/rules/detectionRules/{id} — send only the fields you want to change. All fields are optional.

Updatable fields:

Examples:

# Rename a rule
$body = @{ displayName = 'Cloud Password Spray: Multi-Account Failed Auth from Single IP' } | ConvertTo-Json
Invoke-MgGraphRequest -Method PATCH `
    -Uri "/beta/security/rules/detectionRules/6044" `
    -Body $body -ContentType "application/json"

# Change schedule and severity
$body = @{
    schedule = @{ period = "24H" }
    detectionAction = @{
        alertTemplate = @{ severity = "high" }
    }
} | ConvertTo-Json -Depth 10
Invoke-MgGraphRequest -Method PATCH `
    -Uri "/beta/security/rules/detectionRules/5632" `
    -Body $body -ContentType "application/json"

# Batch rename (loop pattern)
$renames = @{
    'Old Rule Name' = 'New Rule Name'
    'Another Old Name' = 'Another New Name'
}
$rules = (Invoke-MgGraphRequest -Method GET `
    -Uri "/beta/security/rules/detectionRules" -OutputType PSObject).value
foreach ($old in $renames.Keys) {
    $rule = $rules | Where-Object { $_.displayName -eq $old }
    if (-not $rule) { continue }
    $body = @{ displayName = $renames[$old] } | ConvertTo-Json
    Invoke-MgGraphRequest -Method PATCH `
        -Uri "/beta/security/rules/detectionRules/$($rule.id)" `
        -Body $body -ContentType "application/json"
}

Delete Rule

Invoke-MgGraphRequest -Method DELETE `
    -Uri "/beta/security/rules/detectionRules/5632"

⚠️ Deletion propagation delay: After deleting a rule, the name remains reserved for ~30-60 seconds. Creating a rule with the same displayName during this window returns 409 Conflict — but the rule may still be created despite the error. Always verify with a GET after creation.

Enable/Disable Without Deleting

# Disable
Invoke-MgGraphRequest -Method PATCH `
    -Uri "/beta/security/rules/detectionRules/5632" `
    -Body '{"isEnabled": false}' -ContentType "application/json"

# Enable
Invoke-MgGraphRequest -Method PATCH `
    -Uri "/beta/security/rules/detectionRules/5632" `
    -Body '{"isEnabled": true}' -ContentType "application/json"

Existing Rule Discovery

Before authoring new custom detections, check what Analytic Rules (Sentinel) and Custom Detection rules (Defender XDR) already exist for the same table, EventID, or keyword. This avoids duplicating coverage and helps identify gaps.

Step 0: Construct the Analytic Rules URL (once per session)

$cfg = Get-Content config.json | ConvertFrom-Json
$sub = $cfg.subscription_id
$rg  = $cfg.azure_mcp.resource_group
$ws  = $cfg.azure_mcp.workspace_name
$arUrl = "https://management.azure.com/subscriptions/$sub/resourceGroups/$rg/providers/Microsoft.OperationalInsights/workspaces/$ws/providers/Microsoft.SecurityInsights/alertRules?api-version=2024-09-01"

# Verify (should return rule count)
az rest --method get --url $arUrl --query "length(value)" -o tsv 2>$null

All patterns below reuse $arUrl. The Sentinel REST API returns the full KQL query text for every rule — there is no server-side content filtering, so we pull all rules in one call and filter client-side with JMESPath contains().

Search Analytic Rules by Table Name or Keyword

# Which rules reference a specific table? (e.g., SecurityEvent)
az rest --method get --url $arUrl `
  --query "value[?properties.query && contains(properties.query, 'SecurityEvent')].{name: properties.displayName, severity: properties.severity, enabled: properties.enabled}" `
  -o table 2>$null

Search Analytic Rules by EventID

# Which rules reference a specific EventID?
az rest --method get --url $arUrl `
  --query "value[?properties.query && contains(properties.query, '<EventID>')].{name: properties.displayName, severity: properties.severity, enabled: properties.enabled}" `
  -o table 2>$null

To see the surrounding KQL context of a match:

az rest --method get --url $arUrl `
  --query "value[?properties.query && contains(properties.query, '<EventID>')].properties.query" `
  -o tsv 2>$null | Select-String -Pattern '<EventID>' -Context 1,1

Search Analytic Rules for ASIM Parser Dependencies

$rules = az rest --method get --url $arUrl `
  --query "value[?properties.enabled==``true`` && properties.query].{displayName: properties.displayName, query: properties.query}" `
  -o json 2>$null | ConvertFrom-Json

$asimRules = $rules | Where-Object { $_.query -match '_Im_|_ASim_' }
$asimRules | ForEach-Object {
    $schemas = [regex]::Matches($_.query, '_Im_(\w+)') | ForEach-Object { $_.Groups[1].Value } | Sort-Object -Unique
    Write-Host "$($_.displayName): $($schemas -join ', ')"
}

Dump All Enabled Rule Queries for Local Search

az rest --method get --url $arUrl `
  --query "value[?properties.enabled==``true`` && properties.query].{name: properties.displayName, query: properties.query}" `
  -o json > temp/analytic_rule_queries.json

# Then search locally for any pattern
Get-Content temp/analytic_rule_queries.json | Select-String -Pattern 'EventID\s*(==|in\s*\(|has|contains)' -AllMatches

Search Custom Detection Rules (Graph API)

⚠️ Important: The Graph MCP server returns 403 for the Custom Detection endpoint. Always use Invoke-MgGraphRequest via the terminal.

Import-Module Microsoft.Graph.Authentication -ErrorAction Stop

$ctx = Get-MgContext
if (-not $ctx -or $ctx.Scopes -notcontains 'CustomDetection.Read.All') {
    Connect-MgGraph -Scopes 'CustomDetection.Read.All' -NoWelcome
}

$response = Invoke-MgGraphRequest -Method GET `
    -Uri '/beta/security/rules/detectionRules?$select=id,displayName,isEnabled,queryCondition,schedule,lastRunDetails,createdDateTime,lastModifiedDateTime' `
    -OutputType PSObject

Then filter by table name or keyword:

# Which CD rules reference SecurityEvent?
$response.value | Where-Object { $_.queryCondition.queryText -match 'SecurityEvent' } |
    Select-Object displayName, isEnabled, @{N='Query';E={$_.queryCondition.queryText}}

# Which CD rules reference a specific EventID?
$response.value | Where-Object { $_.queryCondition.queryText -match '4688|ProcessCreate' } |
    Select-Object displayName, isEnabled, @{N='Query';E={$_.queryCondition.queryText}}

Identify stale rules (no run in 90 days):

$cutoff = (Get-Date).AddDays(-90).ToString('yyyy-MM-ddTHH:mm:ssZ')
$response.value | Where-Object {
    $_.lastRunDetails.lastRunDateTime -and $_.lastRunDetails.lastRunDateTime -lt $cutoff
} | Select-Object displayName, isEnabled,
    @{N='LastRun';E={$_.lastRunDetails.lastRunDateTime}},
    @{N='Status';E={$_.lastRunDetails.status}}

Key API Fields for Rule Discovery

Tip: JMESPath contains() (used in az rest --query) is case-sensitive. For case-insensitive search, dump to JSON and use PowerShell -match instead.

Known Pitfalls

Pitfall 1: Timestamp vs TimeGenerated — Project As-Is

The query must project the timestamp column exactly as it appears in the source table. Do NOT alias one to the other.

The MS Learn docs confirm: "Timestamp or TimeGenerated — This column sets the timestamp for generated alerts. The query shouldn't manipulate this column and should return it exactly as it appears in the raw event." Aliasing across types causes 400 Bad Request.

Pitfall 2: Silent Rule Creation on Error Responses (400 AND 409)

The API can silently create a rule even when it returns an error. This applies to both 400 Bad Request and 409 Conflict responses.

Cause A — 400 with partial validation: A POST may pass structural validation (creating the rule) but fail a secondary check (e.g., let variable in NRT query, >3 dynamic fields). The API returns 400 Bad Request — but the rule was already created. A subsequent retry with a fixed query then hits 409 Conflict because the rule exists.

Cause B — Deletion propagation delay: Deleting a rule leaves a name reservation for ~30-60 seconds. POSTing a rule with the same displayName in this window returns 409 Conflict — but the API may still create the rule.

Cause C — Silent success + accidental retry: When running Invoke-MgGraphRequest in a terminal, the POST may succeed but the output buffer splits across calls, making it look like nothing happened. Re-running the same POST produces a 409 because the rule was already created seconds earlier.

Prevention:

1. Always run a GET before POST to check if the rule name already exists (see Step 2)
2. Always verify with GET after ANY error response (400 or 409) — the rule may have been created despite the error
3. Never re-run a POST without first checking via GET whether the previous attempt succeeded
4. If a rule was silently created with a bad query, use PATCH to update the queryCondition.queryText rather than deleting and re-creating

Pitfall 3: summarize — Allowed Only With Row-Level Output

Custom detection queries must return row-level results with required columns (TimeGenerated, DeviceName, ReportId). A bare summarize count() or make_set() as the final operator fails validation because the output lacks these columns.

However, summarize with arg_max IS allowed when used to return the required columns alongside aggregation:

// ✅ ALLOWED — uses arg_max to preserve row-level columns
DeviceEvents
| where ingestion_time() > ago(1d)
| where ActionType == "AntivirusDetection"
| summarize (Timestamp, ReportId)=arg_max(Timestamp, ReportId), count() by DeviceId
| where count_ > 5

This pattern counts by entity but still returns Timestamp, ReportId, and DeviceId per row — satisfying the requirement. Use this for threshold-based detections ("alert when count > N").

Pitfall 4: Graph MCP and az rest Cannot Access This API

Both the Graph MCP server and az rest lack the CustomDetection.ReadWrite.All scope. Only Invoke-MgGraphRequest with interactive delegated auth works.

Pitfall 5: recommendedActions Type

The recommendedActions field is a String (not an array). Set to null if not needed. The portal always sets it to null.

Pitfall 6: Query Newlines in JSON

The queryText JSON field requires \r\n (CRLF) line breaks on the wire. When using ConvertTo-Json on a PowerShell hashtable (the recommended approach), this is handled automatically — multiline here-string content in the hashtable value is serialized with correct CRLF encoding. No manual newline insertion is needed.

If manually constructing a raw JSON string body (not recommended), use PowerShell backtick escapes `r`n to produce CRLF in the output.

Pitfall 7: Duplicate Name AND Title Check

The API enforces unique displayName AND unique title (alert title) across all custom detections. Duplicate displayName returns 409 Conflict. Duplicate title returns 400 Bad Request. The batch deployment script checks for displayName duplicates by default — use -Force to override. The MS Learn docs state both should be unique: "Detection name... make it unique" and "Alert title... make it unique".

Pitfall 8: Alert Deduplication

Custom detections automatically deduplicate alerts. If a detection fires twice on events with the same entities, custom details, and dynamic details, only one alert is created. This can happen when the lookback period is longer than the run frequency (e.g., 1H frequency with 4H lookback means 3 hours of overlap). Different events on the same entity produce separate alert entries under the same alert.

Pitfall 9: impactedAssets Identifier Must Be a Predefined API Value

The identifier field in impactedAssets must use one of the predefined values from the Impacted Asset Types section — NOT arbitrary query column names. Using a custom column name (e.g., "identifier": "TargetComputer" or "identifier": "Actor") causes a silent 400 InvalidInput with an empty error message.

This aligns with the MS Learn docs which list specific "strong identifier" columns for impacted assets. The portal wizard enforces this via a dropdown; the Graph API rejects non-matching values silently.

Identifier values must use camelCase as listed in the Impacted Asset Types section (e.g., recipientEmailAddress, not RecipientEmailAddress). The API treats identifier values as case-sensitive when matching to the predefined list.

Additionally, the query MUST project a column whose name matches the chosen identifier. If you use "identifier": "accountUpn", the query must project an AccountUpn column (alias if needed: AccountUpn = UserId). The column name match is case-insensitive — AccountUpn in the query matches accountUpn in the identifier.

⚠️ InitiatingProcess* column trap (Apr 2026): Device* tables project many InitiatingProcess* columns (e.g., InitiatingProcessAccountName, InitiatingProcessAccountSid, InitiatingProcessAccountUpn, InitiatingProcessAccountObjectId). Only three of these are valid user identifiers: initiatingProcessAccountUpn, and the initiatingAccount* variants (initiatingAccountSid, initiatingAccountName, initiatingAccountDomain). Notably, initiatingProcessAccountName is NOT valid — it looks correct because the column exists, but the API enum uses accountName instead. The API rejects invalid identifiers with a silent 400 InvalidInput (empty error message), making this very hard to debug. Always alias the column: AccountName = InitiatingProcessAccountName. DeviceId requirement: For XDR-native tables (Device*, Email*, CloudAppEvents) with a device-type impactedAsset, the query must project DeviceId (not just DeviceName). Sentinel/LA tables (SecurityEvent, AuditLogs) do not require DeviceId.

Pitfall 10: PowerShell Empty Array Swallowing & organizationalScope

Root cause (Feb 2026): When using PowerShell if/else expressions to assign empty arrays, PowerShell swallows @() and produces $null instead:

# ❌ BUG — $x becomes $null, NOT an empty array
$x = if ($false) { @($items) } else { @() }
# Result: $null

# ✅ CORRECT — assign first, then overwrite conditionally
$x = @()
if ($condition) { $x = @($items) }
# Result: empty Object[] (serializes to [])

This caused array fields like responseActions and mitreTechniques to serialize as null instead of [], which the API rejects with 400 Bad Request.

Combined with organizationalScope: null — including this field explicitly (even as null) was also rejected. The fix: omit organizationalScope entirely and use direct assignment for array fields.

Symptoms: All rules in a batch return 400 Bad Request, but some may be silently created (see Pitfall 2). Manual deployment of the same rule body (without the null fields) succeeds.

Fixed in: Deploy-CustomDetections.ps1 — array fields now use direct assignment, organizationalScope removed from body.

Pitfall 11: tostring() on Dynamic Columns Rejected in NRT Mode

Root cause (Feb 2026): NRT rules (schedule: "0") reject tostring() wrapping dynamic-typed columns. The API returns a generic 400 Bad Request with no useful error message — similar to the let rejection described in NRT Constraints. The same query deploys successfully as a scheduled rule (1H+).

Example — AzureActivity table:

// ❌ FAILS in NRT mode — tostring() on dynamic column
AzureActivity
| where OperationNameValue =~ "MICROSOFT.SECURITY/PRICINGS/WRITE"
| where tostring(Properties_d.pricings_pricingTier) == "Free"

// ✅ WORKS — use the native string column instead
AzureActivity
| where OperationNameValue =~ "MICROSOFT.SECURITY/PRICINGS/WRITE"
| where Properties has '"pricingTier":"Free"'

Workarounds:

1. Prefer native string columns — many Sentinel tables have both a dynamic column (e.g., Properties_d) and a string column (e.g., Properties). Use the string column with has or contains for NRT.
2. Switch to 1H schedule — if tostring() is required for precise extraction, use a scheduled rule where it works reliably.

Ingestion lag consideration: Even when a table is NRT-supported, check whether ingestion lag makes NRT impractical — see Ingestion Lag Consideration.

Pitfall 12: NRT-Supported ≠ NRT-Practical — Check Ingestion Lag

A table appearing in the NRT-Supported Tables list means the API accepts NRT rules for that table — it does NOT mean NRT adds value. Tables with significant ingestion lag negate the benefit of continuous detection.

Rule of thumb: If the table's ingestion lag exceeds 30 minutes, use a 1H scheduled rule instead of NRT. The detection latency is dominated by ingestion lag, not rule frequency.

Pitfall 13: impactedAssets Must Be Non-Empty

Root cause (Feb 2026): The Graph API requires impactedAssets to contain at least 1 element. Sending an empty array ("impactedAssets": []) returns 400 BadRequest with InvalidInput code and the message: "The field ImpactedAssets must be a string or array type with a minimum length of '1'."

This error is particularly difficult to diagnose because:

• The error message only appears in some response formats — when using Invoke-MgGraphRequest with raw JSON strings, the "message" field is often empty ("")
• The actual error text only surfaced when using ConvertTo-Json on a PowerShell hashtable body
• All other fields in the payload may be valid, making it seem like a server-side issue

Every custom detection must declare at least one impacted entity. Choose the most relevant asset type for the detection:

Prevention:

• Always include at least one impactedAssets entry in manifests and API payloads
• The companion script Deploy-CustomDetections.ps1 validates this at manifest load time and rejects rules with empty impactedAssets before calling the API
• Review the Impacted Asset Types section for the full list of valid identifiers per asset type

Pitfall 14: Max 3 Unique Dynamic Columns Across Title + Description

The Graph API enforces 3 unique {{Column}} references across title and description combined (not per field). Exceeding this returns 400 Bad Request — often with an empty error message via Invoke-MgGraphRequest.

⚠️ MS Learn discrepancy: Docs say 3 per field; the API empirically enforces 3 unique total across both fields (confirmed Mar 2026).

Counting: Reuse across fields is free ({{A}} in both = 1). Count distinct names, not occurrences.

Workaround: Replace excess {{Column}} refs with static text, or use customDetails (up to 20 KVPs) to surface extra columns in the alert side panel. Deploy-CustomDetections.ps1 validates this at manifest load time.

Pitfall 15: PowerShell Double-Quoted Here-Strings — Variable Interpolation & Escaping Traps

When building queryText in PowerShell, always use single-quoted here-strings (@'...'@), NEVER double-quoted (@"..."@). Two distinct failure modes make double-quoted here-strings unreliable for KQL:

Risk 1 — $variable interpolation: PowerShell double-quoted strings interpolate $var references. KQL uses $left and $right in join syntax and $ as a dynamic property prefix. Inside @"..."@, PowerShell replaces these with empty strings (undefined variables → $null → empty), silently producing broken KQL with no compile-time warning.

Risk 2 — LLM/human escaping confusion (confirmed Mar 2026): When writing KQL inside a double-quoted context, an LLM (or human) instinctively adapts backslash escaping — writing \skills (single backslash) instead of \\skills (double backslash), because most languages interpret \\ → \ in double-quoted strings. PowerShell does NOT do this (backtick ` is the escape character, not backslash), so the single \ passes through literally to the KQL parser, which rejects \s as an invalid escape sequence → 400 Bad Request: "syntax errors".

Byte-level proof (Mar 2026): When identical \\skills content is deliberately placed in both here-string types, PowerShell produces identical bytes — confirming PowerShell itself does not mangle backslashes. The difference arises from what gets written into the string (by the LLM or human), not from PowerShell processing it. This makes the bug extremely hard to diagnose: the query looks correct in terminal output, and the root cause is an invisible content difference between attempts.

Rule: For ANY queryText, always use @'...'@. This eliminates both $ interpolation bugs and escaping confusion. Applies to inline PowerShell, the batch deployment script, and any LLM-generated deployment commands.

Additional validated finding: ingestion_time() IS accepted by the CD API for scheduled (non-NRT) rules (tested and confirmed Mar 2026). However, NRT rules reject ingestion_time() with 400 Bad Request — empirically confirmed Apr 2026, reproducible on retry. See Pitfall 17.

Pitfall 16: StrictMode .Count on Pipeline Scalars

PowerShell pipelines returning exactly 1 result unwrap to a scalar. Under Set-StrictMode -Version Latest, .Count on a scalar throws a terminating error. Always wrap in @() when .Count will be accessed — applies to pipelines, ConvertFrom-Json (single-element JSON arrays), and Get-* cmdlets.

# ❌  $x is scalar string when 1 result → .Count fails
$x = ... | Sort-Object -Unique
# ✅  $x is always Object[]
$x = @(... | Sort-Object -Unique)

Fixed in Deploy-CustomDetections.ps1 (Mar 2026): dynamic column validation, manifest load, and existing rule fetch all wrapped in @().

Pitfall 17: ingestion_time() Rejected in NRT Rules

NRT rules (schedule: "0") reject ingestion_time() with 400 Bad Request. The NRT Constraints table notes that Timestamp > ago(...) is "unnecessary but harmless" — however, ingestion_time() is NOT harmless in NRT mode. It is a function call (not a column filter), and the NRT streaming pipeline rejects it outright.

Empirically confirmed (Apr 2026): Four-attempt A/B test on the same query (DeviceProcessEvents with vssadmin/bcdedit destructive command detection):

Root cause hypothesis: NRT rules process events via streaming ingestion — ingestion_time() likely depends on a materialized ingestion timestamp that isn't available (or isn't filterable) in the NRT streaming pipeline.

Fix: For NRT rules, omit ingestion_time() entirely. If you need a time filter, use Timestamp > ago(...) instead (accepted but unnecessary since NRT pre-filters automatically).

Pitfall 18: category and mitreTechniques Are Server-Side Validated

Both fields are validated against fixed allowlists — invalid values return 400 Bad Request (with descriptive messages, unlike Pitfall 13), not silently accepted.

• category — title-case and case-sensitive (defenseevasion → "Invalid alert category."), single-value (arrays rejected). Full accepted set in Alert Category Values.
• mitreTechniques — invalid IDs → "Mitre techniques (...) are invalid." The accepted set tracks the tenant's ATT&CK version: legacy IDs (e.g. T1003.001) always work; newer sub-techniques (e.g. T1556.009, T1659) work only on refreshed tenants (expanded set rolling out via preview as of June 2026).

Fallback rule: If a newer sub-technique returns "Mitre techniques ... are invalid", fall back to the parent technique (T1556.009 → T1556) or a legacy ID. Don't assume the newest ATT&CK values are available everywhere.

CD Metadata Contract

Query files in queries/ can include per-query cd-metadata blocks that provide structured data for the detection authoring skill. This is the producer/consumer contract between the KQL Query Authoring skill (producer) and the Detection Authoring skill (consumer).

When cd-metadata is present

When a query in queries/ includes a cd-metadata block, the detection authoring skill uses it to:

• Pre-populate manifest fields (schedule, severity, category, title, impactedAssets, etc.)
• Skip manual CD-readiness assessment — the block declares readiness explicitly
• Generate the adapted CD query by applying the Query Adaptation Checklist to the Sentinel query in the same section

Schema

The cd-metadata block is an HTML comment with YAML content, placed immediately after the per-query metadata fields (Severity, MITRE, Tuning Notes) and before the KQL code block:

### Query N: [Title]

**Purpose:** ...
**Severity:** High
**MITRE:** T1053.005, T1059.001

<!-- cd-metadata
cd_ready: true
schedule: "1H"
category: "Persistence"
title: "Encoded PowerShell in Scheduled Task on {{DeviceName}}"
impactedAssets:
  - type: device
    identifier: DeviceName
recommendedActions: "Investigate the scheduled task XML. Decode the base64 payload and check for malicious content."
adaptation_notes: "Straightforward — already row-level, add mandatory columns"
-->

```kql
// Query code...

### Field Reference

| Field | Required | Type | Description |
|-------|----------|------|-------------|
| `cd_ready` | Yes | `true` / `false` | Whether this query can be adapted for custom detection deployment |
| `schedule` | If cd_ready | `"0"` / `"1H"` / `"3H"` / `"12H"` / `"24H"` | Detection frequency. `"0"` = NRT (single-table, no joins/unions) |
| `category` | If cd_ready | string | Alert category (see [API Reference](#api-reference) for valid values) |
| `title` | No | string | Dynamic alert title with `{{ColumnName}}` placeholders. Falls back to query heading if omitted. **Limit: max 3 unique `{{Column}}` references across `title` AND `description` combined** (see [Pitfall 14](#pitfall-14-max-3-unique-dynamic-columns-across-title--description)) |
| `impactedAssets` | If cd_ready | array | Asset entities to extract. Each entry: `type` (`device`/`user`/`mailbox`) + `identifier` (predefined API value, e.g., `accountUpn`, `deviceName` — see [Impacted Asset Types](#impacted-asset-types)) |
| `recommendedActions` | No | string | Triage guidance shown in the alert. Omit if not needed |
| `responseActions` | No | array | **PROHIBITED** — must always be omitted or empty `[]`. Response actions must only be configured manually in the Defender portal |
| `adaptation_notes` | No | string | Human-readable notes on what adaptation is needed (for the summary table) |

### Queries NOT suitable for CD

For queries that cannot be adapted (baseline queries, statistical aggregations), use:

```markdown
<!-- cd-metadata
cd_ready: false
adaptation_notes: "Statistical baseline query — requires summarize with dcount, not suitable for CD"
-->

This explicitly documents the assessment so the detection skill doesn't re-evaluate it each time.

How the detection skill consumes cd-metadata

1. User says "deploy query 8 as a custom detection" → Skill reads the query file, finds the cd-metadata block for Query 8
2. Pre-populates manifest entry from cd-metadata fields (schedule, category, severity, title, impactedAssets)
3. Applies Query Adaptation Checklist to the Sentinel KQL query in that section
4. Writes manifest JSON to temp/ for review. Only deploys via Graph API if the user explicitly requested deployment (see Deployment Gate)

If a query file has no cd-metadata blocks, the skill assesses CD-readiness manually based on the query structure and the Query Adaptation Checklist.

Geomap Visualization Skill

Purpose

Generate interactive world map visualizations from Microsoft Sentinel data using the Sentinel Geomap MCP App. Geomaps display markers on a world map with coordinates, ideal for visualizing attack origins, geographic distribution of threats, or location-based security data.

📑 TABLE OF CONTENTS

1. Quick Start - Minimal example to get started
2. MCP Tool Reference - Parameters and schemas
3. Data Sources - Tables with native vs enriched geolocation
4. KQL Query Patterns - Ready-to-use queries by scenario
5. Enrichment Integration - Adding threat intel drill-down
6. Examples - End-to-end workflows
7. Follow-Up Investigation Queries - Queries for selected IPs
8. Interactive Selection Feature - Multi-select and chat integration

Quick Start

Minimal Geomap (3 Steps)

# 1. Query Sentinel for data with coordinates
mcp_sentinel-data_query_lake({
  "query": "W3CIISLog | where TimeGenerated > ago(7d) | where scStatus == '401' | summarize value = count(), lat = take_any(RemoteIPLatitude), lon = take_any(RemoteIPLongitude) by ip = cIP | where lat != 0 | project ip, lat, lon, value"
})

# 2. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<query results>],
  "title": "Attack Origins (Last 7 Days)",
  "valueLabel": "Failed Logins",
  "colorScale": "blue-red"
})

MCP Tool Reference

Tool: mcp_sentinel-geom_show-attack-map

Data Schema

{
  "data": [
    {"ip": "101.36.107.228", "lat": 22.25, "lon": 114.15, "value": 44},
    {"ip": "193.142.147.209", "lat": 52.35, "lon": 4.92, "value": 13},
    {"ip": "170.64.158.196", "lat": -33.90, "lon": 151.19, "value": 9}
  ]
}

Enrichment Schema

{
  "enrichment": [
    {
      "ip": "101.36.107.228",
      "city": "Hong Kong",
      "country": "HK",
      "org": "AS135377 UCLOUD INFORMATION TECHNOLOGY",
      "is_vpn": true,
      "is_proxy": false,
      "is_tor": false,
      "abuse_confidence_score": 100,
      "total_reports": 4612,
      "last_reported": "2026-01-29",
      "threat_categories": ["SSH", "Brute-Force", "Web App Attack"]
    }
  ]
}

⚠️ CRITICAL: Complete Enrichment Requirement

When providing enrichment data, ALWAYS include ALL IPs - never a subset.

Rule: 100% Enrichment Coverage

Why This Matters

• Users click markers expecting threat intel panels
• Missing enrichment = empty panels = broken UX
• Partial enrichment misleads security analysts

Workflow to Ensure Complete Enrichment

1. Query Sentinel → Get N IPs with coordinates
2. Batch enrich IPs → python enrich_ips.py <all_ips> or python enrich_ips.py --file <ips.json>
3. Parse enrichment JSON → Extract ALL enriched entries
4. Build enrichment array → One entry per IP, matching data array exactly
5. Call geomap → Both data and enrichment arrays must have same IPs

Example: Building Complete Enrichment

import json

# Load enrichment from batch operation
with open('temp/ip_enrichment_<timestamp>.json', 'r') as f:
    raw_enrichment = json.load(f)

# Build geomap enrichment array - INCLUDE ALL
enrichment = []
for e in raw_enrichment:
    threat_cats = []
    for c in e.get('recent_comments', [])[:5]:
        threat_cats.extend(c.get('categories', []))
    
    enrichment.append({
        'ip': e['ip'],
        'city': e.get('city', 'Unknown'),
        'country': e.get('country', '??'),
        'org': e.get('org', 'Unknown'),
        'is_vpn': e.get('is_vpn') or e.get('vpnapi_security_vpn', False),
        'is_proxy': e.get('is_proxy') or e.get('vpnapi_security_proxy', False),
        'is_tor': e.get('is_tor') or e.get('vpnapi_security_tor', False),
        'abuse_confidence_score': e.get('abuse_confidence_score', 0),
        'total_reports': e.get('total_reports', 0),
        'last_reported': e.get('recent_comments', [{}])[0].get('date', '')[:10] if e.get('recent_comments') else '',
        'threat_categories': list(set(threat_cats))[:5]
    })

# Verify coverage
print(f"Enrichment entries: {len(enrichment)}")  # Must match data array length

❌ NEVER Do This

# BAD: Only including first 25 IPs
enrichment = enrichment[:25]  # WRONG

# BAD: Skipping IPs without abuse scores
enrichment = [e for e in enrichment if e['abuse_confidence_score'] > 0]  # WRONG

✅ ALWAYS Do This

# GOOD: Include all IPs, even if some fields are empty
enrichment = [transform(e) for e in raw_enrichment]  # All entries

# GOOD: If filtering, filter BOTH data and enrichment consistently
valid_ips = set(e['ip'] for e in enrichment if e.get('city'))
data = [d for d in data if d['ip'] in valid_ips]  # Filter both

Data Sources

Tables with Native Geolocation

Some Sentinel tables include lat/lon directly from Microsoft's GeoIP enrichment:

Use these when available - no enrichment needed for coordinates.

Tables Requiring IP Enrichment

These tables have IP addresses but no coordinates:

Enrichment script now captures latitude and longitude from ipinfo.io.

KQL Query Patterns

Pattern 1: Native Geolocation (W3CIISLog)

W3CIISLog
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude),
    country = take_any(RemoteIPCountry)
    by ip = cIP
| where lat != 0 and lon != 0  // Filter unknown locations
| project ip, lat, lon, value
| order by value desc

Pattern 2: Native Geolocation (CommonSecurityLog)

CommonSecurityLog
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize 
    value = count(),
    lat = take_any(DeviceGeoLatitude),
    lon = take_any(DeviceGeoLongitude)
    by ip = SourceIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc

Pattern 3: Enrichment Required (Extract IPs Only)

<Table>
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| where <filter_condition>
| summarize value = count() by ip = <IP_column>
| order by value desc
| take 100

Then run enrich_ips.py to get lat/lon.

Scenario-Specific KQL Queries

Scenario: W3CIISLog - Failed Logins (Native Geo)

W3CIISLog
| where TimeGenerated > ago(90d)
| where Computer startswith "<honeypot_name>"
| where scStatus == "401"  // Failed auth
| where cIP != "127.0.0.1"
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude),
    country = take_any(RemoteIPCountry)
    by ip = cIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc

Scenario: W3CIISLog - Web Attacks (Native Geo)

W3CIISLog
| where TimeGenerated > ago(30d)
| where tolong(scStatus) >= 400
| where csUriStem has_any ("'", "union", "select", "script", "../", "cmd.exe")
| where cIP != "127.0.0.1"
| summarize 
    value = count(),
    lat = take_any(RemoteIPLatitude),
    lon = take_any(RemoteIPLongitude)
    by ip = cIP
| where lat != 0
| project ip, lat, lon, value
| order by value desc
| take 100

Scenario: CommonSecurityLog - Firewall Blocks (Native Geo)

CommonSecurityLog
| where TimeGenerated > ago(7d)
| where DeviceAction == "Deny" or Activity has "blocked"
| summarize 
    value = count(),
    lat = take_any(DeviceGeoLatitude),
    lon = take_any(DeviceGeoLongitude)
    by ip = SourceIP
| where lat != 0 and lon != 0
| project ip, lat, lon, value
| order by value desc
| take 100

Scenario: SigninLogs - Failed Sign-ins (Requires Enrichment)

Step 1: Query IPs and values

SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0  // Failed
| summarize value = count() by ip = IPAddress
| order by value desc
| take 50

Step 2: Enrich IPs

python enrich_ips.py <ip1> <ip2> <ip3> ...

Step 3: Build map data from enrichment JSON (includes lat/lon)

Scenario: SecurityEvent - RDP Brute Force (Requires Enrichment)

SecurityEvent
| where TimeGenerated > ago(7d)
| where EventID == 4625
| where LogonType == 10  // RDP
| where IpAddress != "-" and IpAddress != "127.0.0.1"
| summarize value = count() by ip = IpAddress
| order by value desc
| take 50

Then enrich to get coordinates.

Scenario: DeviceNetworkEvents - Inbound Attacks (Requires Enrichment)

DeviceNetworkEvents
| where TimeGenerated > ago(7d)
| where DeviceName =~ "<device_name>"
| where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted")
| where LocalPort in (3389, 22, 445, 80, 443)
| where RemoteIP !startswith "192.168." and RemoteIP !startswith "10."
| summarize value = count() by ip = RemoteIP
| order by value desc
| take 50

Enrichment Integration

When Coordinates Are Not in Sentinel

For tables without native geo fields, use the enrichment script:

Step 1: Run your KQL query to get IPs and values

Step 2: Enrich IPs:

python enrich_ips.py 203.0.113.42 198.51.100.10 192.0.2.1
# Or from file:
python enrich_ips.py --file temp/attack_ips.json

Step 3: Load enrichment JSON and build map data:

import json

# Load enrichment (now includes latitude/longitude from ipinfo.io)
with open('temp/ip_enrichment_<timestamp>.json', 'r') as f:
    enrichment = json.load(f)

# Build map data
map_data = []
enrichment_out = []

for e in enrichment:
    ip = e['ip']
    lat = e.get('latitude')
    lon = e.get('longitude')
    
    if lat is None or lon is None:
        continue  # Skip IPs without coordinates
    
    # Get value from your KQL results (create a lookup dict)
    value = attack_counts.get(ip, 1)
    
    map_data.append({
        'ip': ip,
        'lat': lat,
        'lon': lon,
        'value': value
    })
    
    # Build enrichment for drill-down
    threat_cats = []
    for c in e.get('recent_comments', [])[:5]:
        threat_cats.extend(c.get('categories', []))
    
    enrichment_out.append({
        'ip': ip,
        'city': e.get('city', 'Unknown'),
        'country': e.get('country', '??'),
        'org': e.get('org', 'Unknown'),
        'is_vpn': e.get('is_vpn') or e.get('vpnapi_security_vpn', False),
        'abuse_confidence_score': e.get('abuse_confidence_score', 0),
        'total_reports': e.get('total_reports', 0),
        'last_reported': e.get('recent_comments', [{}])[0].get('date', '')[:10] if e.get('recent_comments') else '',
        'threat_categories': list(set(threat_cats))[:5]
    })

Interactive Features with Enrichment

When enrichment is provided:

• Click any marker → Opens threat intel panel showing:
  • 📍 Location (city, country)
  • 🏢 Organization/ISP
  • 🏷️ VPN/Proxy/Tor badges
  • 📊 AbuseIPDB confidence meter
  • 📈 Total reports count
  • 🔴 Threat category tags

Color Scale Guide

For threat/attack maps, always use blue-red.

Complete Examples

Example 1: 90-Day Honeypot Attack Map (Native Geo)

# 1. Query with native lat/lon from W3CIISLog
mcp_sentinel-data_query_lake({
  "query": "W3CIISLog | where TimeGenerated > ago(90d) | where Computer startswith '<HONEYPOT_SERVER>' | where scStatus == '401' | summarize value = count(), lat = take_any(RemoteIPLatitude), lon = take_any(RemoteIPLongitude), country = take_any(RemoteIPCountry) by ip = cIP | where lat != 0 and lon != 0 | project ip, lat, lon, value | order by value desc"
})

# 2. Enrich top IPs for threat intel drill-down
python enrich_ips.py 101.36.107.228 193.142.147.209 80.190.82.185

# 3. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [
    {"ip": "101.36.107.228", "lat": 22.25, "lon": 114.15, "value": 44},
    {"ip": "80.190.82.185", "lat": 50.97, "lon": 6.83, "value": 44},
    {"ip": "193.142.147.209", "lat": 52.35, "lon": 4.92, "value": 13},
    {"ip": "170.64.158.196", "lat": -33.9, "lon": 151.19, "value": 9}
  ],
  "title": "Honeypot Attack Origins - 90 Day Analysis",
  "valueLabel": "Failed Logins",
  "colorScale": "blue-red",
  "enrichment": [
    {"ip": "101.36.107.228", "city": "Hong Kong", "country": "HK", "org": "AS135377 UCLOUD", "is_vpn": true, "abuse_confidence_score": 100, "total_reports": 4612, "threat_categories": ["SSH", "Brute-Force"]},
    {"ip": "193.142.147.209", "city": "Amsterdam", "country": "NL", "org": "AS213438 ColocaTel", "is_vpn": true, "abuse_confidence_score": 100, "total_reports": 30973, "threat_categories": ["Web App Attack", "Hacking"]}
  ]
})

Example 2: SigninLogs Attack Map (Enrichment Required)

# 1. Query IPs with failed sign-ins
mcp_sentinel-data_query_lake({
  "query": "SigninLogs | where TimeGenerated > ago(7d) | where ResultType != 0 | summarize value = count() by ip = IPAddress | order by value desc | take 50"
})

# 2. Enrich all IPs (script now captures lat/lon)
python enrich_ips.py <ip1> <ip2> ...

# 3. Load enrichment JSON and build map data
# (See Python code in Enrichment Integration section)

# 4. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<map_data from enrichment>],
  "title": "Failed Sign-In Origins (Last 7 Days)",
  "valueLabel": "Failed Attempts",
  "colorScale": "blue-red",
  "enrichment": [<enrichment_out>]
})

Example 3: Firewall Blocks (Native Geo)

# 1. Query blocked traffic with geo
mcp_sentinel-data_query_lake({
  "query": "CommonSecurityLog | where TimeGenerated > ago(24h) | where DeviceAction == 'Deny' | summarize value = count(), lat = take_any(DeviceGeoLatitude), lon = take_any(DeviceGeoLongitude) by ip = SourceIP | where lat != 0 | project ip, lat, lon, value | order by value desc | take 100"
})

# 2. Display geomap
mcp_sentinel-geom_show-attack-map({
  "data": [<query results>],
  "title": "Blocked Traffic Origins (Last 24h)",
  "valueLabel": "Blocked Connections",
  "colorScale": "blue-red"
})

Follow-Up Investigation Queries

When users select IPs from the geomap and click "🔍 Investigate in Chat", run these queries to provide comprehensive threat analysis. Execute queries in parallel where possible.

Multi-IP Filter Pattern

All queries use this dynamic IP filter:

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>", ...]);

Replace with the actual IPs selected from the geomap.

Query 1: DeviceNetworkEvents (Network Activity)

Purpose: Show all network connections from selected IPs to any device in the environment.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where RemoteIP in (target_ips)
| summarize 
    ConnectionCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetDevices = make_set(DeviceName, 10),
    TargetPorts = make_set(LocalPort, 20),
    Actions = make_set(ActionType, 5)
    by RemoteIP
| extend Duration = LastSeen - FirstSeen
| order by ConnectionCount desc

Columns returned:

• RemoteIP: Attacker IP
• ConnectionCount: Total connections
• FirstSeen/LastSeen: Activity time range
• TargetDevices: Devices contacted
• TargetPorts: Ports targeted (LocalPort = service ports on your devices)
• Actions: Connection types (Success, Blocked, etc.)

Query 2: SecurityEvent (Windows Authentication)

Purpose: Show Windows authentication attempts from selected IPs.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
SecurityEvent
| where TimeGenerated between (start .. end)
| where IpAddress in (target_ips)
| where EventID in (4624, 4625, 4648, 4771, 4776)
| summarize 
    EventCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetComputers = make_set(Computer, 10),
    TargetAccounts = make_set(Account, 20),
    LogonTypes = make_set(LogonType, 5)
    by IpAddress, EventID
| extend EventType = case(
    EventID == 4624, "Successful Logon",
    EventID == 4625, "Failed Logon",
    EventID == 4648, "Explicit Credentials",
    EventID == 4771, "Kerberos Pre-Auth Failed",
    EventID == 4776, "NTLM Auth Attempt",
    "Other")
| project IpAddress, EventType, EventCount, TargetComputers, TargetAccounts, LogonTypes, FirstSeen, LastSeen
| order by EventCount desc

Key Event IDs:

• 4624: Successful logon (ALERT: attacker got in!)
• 4625: Failed logon (brute force indicator)
• 4648: Explicit credentials used (lateral movement)
• 4771: Kerberos pre-auth failed
• 4776: NTLM credential validation

Query 3: W3CIISLog (Web Attacks)

Purpose: Show HTTP requests from selected IPs including attack patterns.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
W3CIISLog
| where TimeGenerated between (start .. end)
| where cIP in (target_ips)
| summarize 
    RequestCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetServers = make_set(Computer, 10),
    URIs = make_set(csUriStem, 20),
    StatusCodes = make_set(tolong(scStatus), 10),
    Methods = make_set(csMethod, 5),
    UserAgents = make_set(csUserAgent, 5)
    by cIP
| extend AttackPatterns = case(
    URIs has_any ("'", "union", "select"), "SQL Injection",
    URIs has "script", "XSS",
    URIs has_any ("../", "..\\"), "Path Traversal",
    URIs has_any ("cmd.exe", "powershell"), "Command Injection",
    "Reconnaissance")
| project IP = cIP, RequestCount, AttackPatterns, TargetServers, StatusCodes, Methods, URIs, FirstSeen, LastSeen
| order by RequestCount desc

Query 4: SigninLogs (Azure AD Activity)

Purpose: Show Azure AD sign-in attempts from selected IPs.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
SigninLogs
| where TimeGenerated between (start .. end)
| where IPAddress in (target_ips)
| summarize 
    SignInCount = count(),
    SuccessCount = countif(ResultType == 0),
    FailureCount = countif(ResultType != 0),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    TargetUsers = make_set(UserPrincipalName, 20),
    TargetApps = make_set(AppDisplayName, 10),
    ErrorCodes = make_set(ResultType, 10),
    ClientApps = make_set(ClientAppUsed, 5)
    by IPAddress
| extend SuccessRate = round(100.0 * SuccessCount / SignInCount, 1)
| project IPAddress, SignInCount, SuccessCount, FailureCount, SuccessRate, TargetUsers, TargetApps, ErrorCodes, FirstSeen, LastSeen
| order by SignInCount desc

CRITICAL: Check SuccessCount > 0 - This indicates the attacker successfully authenticated!

Query 5: ThreatIntelIndicators (Known Threats)

Purpose: Check if selected IPs match threat intelligence databases.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
ThreatIntelIndicators
| extend IndicatorType = replace_string(replace_string(replace_string(tostring(split(ObservableKey, ":", 0)), "[", ""), "]", ""), "\"", "")
| where IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
| extend NetworkSourceIP = toupper(ObservableValue)
| where NetworkSourceIP in (target_ips)
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| extend Description = tostring(parse_json(Data).description)
| where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| summarize arg_max(TimeGenerated, *) by NetworkSourceIP
| project 
    IPAddress = NetworkSourceIP,
    ThreatDescription = Description,
    ActivityGroupNames,
    Confidence,
    ValidUntil,
    TrafficLightProtocolLevel,
    IsActive,
    TimeGenerated
| order by Confidence desc

Key Fields:

• Confidence: 0-100 threat confidence score
• ActivityGroupNames: APT/threat actor attribution (e.g., "PHOSPHORUS", "NOBELIUM")
• ThreatDescription: Details about the threat

Query 6: SecurityAlert with Incident Status

Purpose: Find security alerts that reference selected IPs, with the actual status from SecurityIncident (not the immutable alert status).

⚠️ IMPORTANT: SecurityAlert.Status is immutable ("New" at creation time). The actual status is on the SecurityIncident table. This query joins to get the real incident status.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
// Step 1: Find alerts containing target IPs as entities
let matched_alerts = SecurityAlert
| where TimeGenerated between (start .. end)
| extend EntitiesParsed = parse_json(Entities)
| mv-expand Entity = EntitiesParsed
| where Entity.["Type"] == "ip"
| extend EntityIP = tostring(Entity.Address)
| where EntityIP in (target_ips)
| summarize MatchedIPs = make_set(EntityIP) by SystemAlertId;
// Step 2: Get latest incident status for these alerts (keep AlertIds)
let incident_status = SecurityIncident
| where TimeGenerated between (start .. end)
| summarize arg_max(TimeGenerated, Status, Classification, IncidentNumber, AlertIds) by IncidentName
| mv-expand AlertId = AlertIds
| extend AlertId = tostring(AlertId)
| project AlertId, IncidentStatus = Status, Classification, IncidentNumber;
// Step 3: Join alerts with matched IPs and incident status
SecurityAlert
| where TimeGenerated between (start .. end)
| where SystemAlertId in (matched_alerts)
| join kind=leftouter matched_alerts on $left.SystemAlertId == $right.SystemAlertId
| join kind=leftouter incident_status on $left.SystemAlertId == $right.AlertId
| summarize arg_max(TimeGenerated, AlertName, AlertSeverity, Status, ProviderName, Tactics, Description, MatchedIPs, IncidentStatus, Classification, IncidentNumber) by SystemAlertId
| extend FinalStatus = coalesce(IncidentStatus, Status)  // Use incident status if available
| project 
    TimeGenerated,
    AlertName,
    AlertSeverity,
    Status = FinalStatus,
    Classification,
    IncidentNumber,
    ProviderName,
    Tactics,
    MatchedIPs,
    Description
| order by TimeGenerated desc
| take 25

Why This Matters:

• SecurityAlert.Status = "New" is the creation status (immutable)
• SecurityIncident.Status shows the current status (New/Active/Closed)
• SecurityIncident.Classification shows the closure reason (TruePositive/FalsePositive/BenignPositive)
• Alerts without incidents keep their original "New" status

Entities JSON Structure Example:

[
  {"$id":"3","HostName":"contoso-server","Type":"host"},
  {"$id":"4","Address":"203.0.113.10","Type":"ip"},
  {"$id":"5","Address":"198.51.100.20","Type":"ip"}
]

Query 7: DeviceProcessEvents (Process Execution Post-Compromise)

Purpose: If attacker IPs had successful connections, check for suspicious process execution.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
// First, find devices that had connections from target IPs
let compromised_devices = DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where RemoteIP in (target_ips)
| where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted")
| distinct DeviceName;
// Then check for suspicious processes on those devices
DeviceProcessEvents
| where TimeGenerated between (start .. end)
| where DeviceName in (compromised_devices)
| where FileName in~ ("powershell.exe", "cmd.exe", "wscript.exe", "cscript.exe", "mshta.exe", "certutil.exe", "bitsadmin.exe", "regsvr32.exe", "rundll32.exe")
    or ProcessCommandLine has_any ("Invoke-", "IEX", "DownloadString", "WebClient", "-enc", "-encoded", "bypass", "hidden")
| project TimeGenerated, DeviceName, FileName, ProcessCommandLine, AccountName, InitiatingProcessFileName
| order by TimeGenerated desc
| take 50

Query 8: DeviceFileEvents (Malware Drops)

Purpose: Check for file creation/modification on devices contacted by attacker IPs.

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>"]);
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
// Find devices that had connections from target IPs
let compromised_devices = DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where RemoteIP in (target_ips)
| where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted")
| distinct DeviceName;
// Check for suspicious file activity
DeviceFileEvents
| where TimeGenerated between (start .. end)
| where DeviceName in (compromised_devices)
| where ActionType in ("FileCreated", "FileModified")
| where FileName endswith_cs ".exe" or FileName endswith_cs ".dll" or FileName endswith_cs ".ps1" 
    or FileName endswith_cs ".bat" or FileName endswith_cs ".vbs" or FileName endswith_cs ".js"
| where FolderPath has_any ("\\Temp\\", "\\AppData\\", "\\Downloads\\", "\\ProgramData\\", "\\Users\\Public\\")
| project TimeGenerated, DeviceName, FileName, FolderPath, ActionType, InitiatingProcessFileName, SHA256
| order by TimeGenerated desc
| take 50

Recommended Execution Order

When user selects IPs and clicks "Investigate in Chat":

Phase 1 (Parallel):

• Query 1: DeviceNetworkEvents
• Query 2: SecurityEvent
• Query 3: W3CIISLog
• Query 4: SigninLogs
• Query 5: ThreatIntelIndicators
• Query 6: SecurityAlert

Phase 2 (If connections found):

• Query 7: DeviceProcessEvents (post-compromise activity)
• Query 8: DeviceFileEvents (malware indicators)

Response Format:

Summarize findings with:

1. Threat Level Assessment (Critical/High/Medium/Low)
2. Attack Summary - What the IPs did, which devices/users were targeted
3. Successful Access - ALERT if any successful logins (4624) or Azure AD success (ResultType=0)
4. Threat Intel Matches - Known APT groups, malware campaigns
5. Recommendations - Block IPs, investigate users, isolate devices

Interactive Selection Feature

The geomap supports multi-select mode for follow-up investigations:

How to Use

1. Click "☑ Select" button (top of map) to enter selection mode
2. Click markers to add/remove IPs from selection (green checkmark ✓)
3. Review selection panel showing selected IPs with enrichment summary
4. Click "🔍 Investigate in Chat" to send selected IPs for investigation

What Happens

When you click "Investigate in Chat":

1. All selected IPs are formatted with enrichment context
2. Message is sent to chat as a user message
3. LLM runs the follow-up queries above automatically
4. Results are summarized with threat assessment

Selection Panel Shows

For each selected IP:

• IP address
• City, Country
• Abuse confidence score (color-coded badge)
• Attack value from the map

Technical Notes

• Projection: Robinson projection for accurate world map display
• Map Source: SimpleMaps.com world SVG (MIT license)
• Bundle Size: ~650 KB (includes embedded world map)
• CSP Compliance: No external resources - all assets embedded inline
• Coordinate System: Standard WGS84 (latitude: -90 to 90, longitude: -180 to 180)

When to Use Geomaps

✅ Good Use Cases:

• Attack origin visualization (honeypots, firewalls)
• Geographic threat distribution
• Anomalous sign-in locations
• VPN/anonymization analysis across regions
• Executive briefings on global threats

❌ Skip Geomaps When:

• Fewer than 3 unique locations (too sparse)
• All IPs from same region (use heatmap instead)
• Time-based patterns needed (use heatmap)
• No geographic data available and enrichment not feasible

Last Updated: January 29, 2026

Heatmap Visualization Skill

Purpose

Generate interactive heatmap visualizations from Microsoft Sentinel data using the Sentinel Heatmap MCP App. Heatmaps display aggregated data in a row/column grid with color-coded intensity, ideal for identifying patterns across time periods, comparing entities, or spotting anomalies.

📑 TABLE OF CONTENTS

1. Quick Start - Minimal example to get started
2. MCP Tool Reference - Parameters and schemas
3. KQL Query Patterns - Ready-to-use queries by scenario
4. Enrichment Integration - Adding threat intel drill-down
5. Color Scale Guide - Choosing the right colors
6. Examples - End-to-end workflows

Quick Start

Minimal Heatmap (3 Steps)

# 1. Query Sentinel for aggregated data
mcp_sentinel-data_query_lake({
  "query": "SigninLogs | where TimeGenerated > ago(24h) | summarize value = count() by row = AppDisplayName, column = format_datetime(bin(TimeGenerated, 1h), 'HH:mm') | project row, column, value"
})

# 2. Display heatmap
mcp_sentinel-heat_show-signin-heatmap({
  "data": [<query results>],
  "title": "Sign-Ins by Application (Last 24h)",
  "rowLabel": "Application",
  "colLabel": "Hour (UTC)",
  "valueLabel": "Sign-ins",
  "colorScale": "green-red"
})

MCP Tool Reference

Tool: mcp_sentinel-heat_show-signin-heatmap

Data Schema

{
  "data": [
    {"row": "192.168.1.1", "column": "10:00", "value": 45},
    {"row": "192.168.1.1", "column": "11:00", "value": 62},
    {"row": "10.0.0.5", "column": "10:00", "value": 128}
  ]
}

Enrichment Schema (Optional)

{
  "enrichment": [
    {
      "ip": "80.94.95.83",
      "city": "Timișoara",
      "country": "RO",
      "org": "AS204428 SS-Net",
      "is_vpn": false,
      "abuse_confidence_score": 100,
      "total_reports": 975,
      "last_reported": "2026-01-29",
      "threat_categories": ["RDP Brute-Force", "Hacking", "Port Scan"]
    }
  ]
}

KQL Query Patterns

All queries must return row, column, value columns.

Pattern 1: Activity by Entity and Hour

<Table>
| where TimeGenerated between (datetime(<start>) .. datetime(<end>))
| summarize value = count() 
    by row = <entity_field>, 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc

Pattern 2: Activity by Entity and Day

<Table>
| where TimeGenerated > ago(30d)
| summarize value = count() 
    by row = <entity_field>, 
       column = format_datetime(bin(TimeGenerated, 1d), "yyyy-MM-dd")
| project row, column, value
| order by column asc

Pattern 3: Cross-Tabulation (Two Dimensions)

<Table>
| where TimeGenerated > ago(7d)
| summarize value = count() 
    by row = <dimension1>, 
       column = <dimension2>
| project row, column, value
| order by value desc

Scenario-Specific KQL Queries

Scenario: Sign-In Activity by Application and Hour

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0  // Successful sign-ins
| summarize value = count() 
    by row = AppDisplayName, 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc

Recommended: colorScale: "green-red" (activity = good)

Scenario: Failed Sign-Ins by IP and Hour

SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0  // Failed sign-ins
| summarize value = count() 
    by row = IPAddress, 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc, value desc
| take 500  // Limit to top patterns

Recommended: colorScale: "blue-red" (failures = threat)

Scenario: Honeypot Attack Patterns (SecurityEvent)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
SecurityEvent
| where TimeGenerated between (start .. end)
| where Computer contains honeypot
| where EventID in (4625, 4771, 4776)  // Failed auth events
| where isnotempty(IpAddress) and IpAddress != "-" and IpAddress != "127.0.0.1"
| summarize value = count() 
    by row = IpAddress, 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc, value desc

Recommended: colorScale: "blue-red" (attacks = threat)

Scenario: Web Attack Patterns (W3CIISLog)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
W3CIISLog
| where TimeGenerated between (start .. end)
| where tolong(scStatus) >= 400  // HTTP errors
| where cIP != "127.0.0.1"
| summarize value = count() 
    by row = cIP, 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc, value desc
| take 300

Recommended: colorScale: "blue-red"

Scenario: Defender Alerts by Severity and Day

SecurityAlert
| where TimeGenerated > ago(30d)
| summarize value = count() 
    by row = AlertSeverity, 
       column = format_datetime(bin(TimeGenerated, 1d), "yyyy-MM-dd")
| project row, column, value
| order by column asc

Recommended: colorScale: "blue-yellow" (neutral overview)

Scenario: User Activity by Application

SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ '<UPN>'
| summarize value = count() 
    by row = AppDisplayName, 
       column = format_datetime(bin(TimeGenerated, 1d), "MM-dd")
| project row, column, value
| order by column asc

Recommended: colorScale: "green-red"

Scenario: Multi-Source Combined Heatmap

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
union
  (SecurityEvent
   | where TimeGenerated between (start .. end)
   | where Computer contains honeypot
   | where EventID in (4625, 4771, 4776)
   | where isnotempty(IpAddress) and IpAddress != "-"
   | extend Source = "RDP/SMB", IP = IpAddress),
  (W3CIISLog
   | where TimeGenerated between (start .. end)
   | where Computer contains honeypot
   | where tolong(scStatus) >= 400
   | extend Source = "IIS", IP = cIP),
  (DeviceNetworkEvents
   | where TimeGenerated between (start .. end)
   | where DeviceName contains honeypot
   | where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted")
   | extend Source = "Network", IP = RemoteIP)
| where IP != "127.0.0.1" and IP != "::1"
| summarize value = count()
    by row = strcat(IP, " (", Source, ")"), 
       column = format_datetime(bin(TimeGenerated, 1h), "HH:mm")
| project row, column, value
| order by column asc, value desc

Enrichment Integration

Adding Threat Intel Drill-Down

When displaying IP-based heatmaps, add enrichment data for click-to-expand threat panels:

Step 1: Extract unique IPs from your query results

Step 2: Enrich IPs using the enrichment script:

python enrich_ips.py 80.94.95.83 193.142.147.209 101.36.107.228

Step 3: Transform enrichment output to heatmap format:

enrichment_out = []
for e in enrichment_data:
    threat_cats = []
    for c in e.get('recent_comments', [])[:5]:
        threat_cats.extend(c.get('categories', []))
    
    enrichment_out.append({
        'ip': e['ip'],
        'city': e.get('city', 'Unknown'),
        'country': e.get('country', '??'),
        'org': e.get('org', 'Unknown'),
        'is_vpn': e.get('is_vpn') or e.get('vpnapi_security_vpn', False),
        'abuse_confidence_score': e.get('abuse_confidence_score', 0),
        'total_reports': e.get('total_reports', 0),
        'last_reported': e.get('recent_comments', [{}])[0].get('date', '')[:10],
        'threat_categories': list(set(threat_cats))[:5]
    })

Step 4: Include in heatmap call:

mcp_sentinel-heat_show-signin-heatmap({
  "data": [...],
  "enrichment": [<enrichment_out>],
  ...
})

Interactive Features with Enrichment

When enrichment is provided:

• Click any IP row → Opens threat intel panel showing:
  • 📍 Location (city, country)
  • 🏢 Organization/ISP
  • 🏷️ VPN/Proxy/Tor badges
  • 📊 AbuseIPDB confidence meter (0-100)
  • 📈 Total reports count
  • 🔴 Threat category tags
• Hover any cell → Tooltip with row, column, exact value

Color Scale Guide

Decision Tree

Is the data about threats/failures/attacks?
  → YES: Use "blue-red" (red = danger)
  → NO: Is high volume a positive indicator?
    → YES: Use "green-red" (green = success)
    → NO: Use "blue-yellow" (neutral)

Complete Examples

Example 1: Honeypot Attack Heatmap with Enrichment

# Query attack data
mcp_sentinel-data_query_lake({
  "query": "SecurityEvent | where TimeGenerated between (datetime(<START_DATE>) .. datetime(<END_DATE>)) | where Computer contains '<HONEYPOT_SERVER>' | where EventID == 4625 | where IpAddress != '127.0.0.1' | summarize value = count() by row = IpAddress, column = format_datetime(bin(TimeGenerated, 1h), 'HH:mm') | project row, column, value | order by column asc, value desc | take 200"
})

# Enrich top IPs
python enrich_ips.py 80.94.95.83 193.142.147.209 101.36.107.228

# Display heatmap
mcp_sentinel-heat_show-signin-heatmap({
  "data": [
    {"row": "80.94.95.83", "column": "19:00", "value": 636},
    {"row": "193.142.147.209", "column": "20:00", "value": 245},
    ...
  ],
  "title": "Honeypot Attack Analysis - Click IP for Threat Intel",
  "rowLabel": "Attacker IP",
  "colLabel": "Hour (UTC)",
  "valueLabel": "Failed Auth Attempts",
  "colorScale": "blue-red",
  "enrichment": [
    {"ip": "80.94.95.83", "city": "Timișoara", "country": "RO", "org": "AS204428 SS-Net", "is_vpn": false, "abuse_confidence_score": 100, "total_reports": 975, "threat_categories": ["RDP Brute-Force", "Hacking"]},
    {"ip": "193.142.147.209", "city": "Amsterdam", "country": "NL", "org": "AS213438 ColocaTel Inc.", "is_vpn": true, "abuse_confidence_score": 100, "total_reports": 30972, "threat_categories": ["SSH Brute-Force", "Port Scan"]}
  ]
})

Example 2: Sign-In Activity Overview

# Query sign-in data
mcp_sentinel-data_query_lake({
  "query": "SigninLogs | where TimeGenerated > ago(24h) | where ResultType == 0 | summarize value = count() by row = AppDisplayName, column = format_datetime(bin(TimeGenerated, 1h), 'HH:mm') | project row, column, value | order by column asc"
})

# Display heatmap (no enrichment needed - not IP-based)
mcp_sentinel-heat_show-signin-heatmap({
  "data": [
    {"row": "Microsoft Teams", "column": "09:00", "value": 145},
    {"row": "Outlook", "column": "09:00", "value": 312},
    ...
  ],
  "title": "Sign-In Activity by Application (Last 24h)",
  "rowLabel": "Application",
  "colLabel": "Hour (UTC)",
  "valueLabel": "Sign-ins",
  "colorScale": "green-red"
})

Known Pitfalls

Column Sorting Is Lexicographic

Problem: The heatmap MCP app sorts columns alphabetically. Labels like Nov 10, Dec 01, Jan 05, Feb 02 will render as Dec → Feb → Jan → Nov — completely out of chronological order.
Solution: Always use ISO date format (YYYY-MM-DD) for time-based column labels. 2025-11-10, 2025-12-01, 2026-01-05 sorts correctly both alphabetically and chronologically.

// ✅ CORRECT — sortable column labels
| summarize value = count() by row = ..., column = format_datetime(bin(TimeGenerated, 7d), "yyyy-MM-dd")

// ❌ WRONG — alphabetic sort breaks chronological order
| summarize value = count() by row = ..., column = format_datetime(bin(TimeGenerated, 7d), "MMM dd")

For hourly heatmaps within a single day, HH:mm is fine (00:00–23:00 sorts correctly). The issue only affects multi-day/week/month labels.

When to Use Heatmaps

✅ Good Use Cases:

• Attack patterns over time (by hour/day)
• Comparing activity across entities (IPs, apps, users)
• Identifying peak activity periods
• Spotting anomalies in regular patterns
• Executive-friendly threat visualization

❌ Skip Heatmaps When:

• Fewer than 5 unique rows or columns (too sparse)
• Single-dimension data (use bar chart instead)
• Geographic data (use geomap skill instead)
• Real-time streaming data (heatmaps are for aggregated snapshots)

Last Updated: January 29, 2026

Honeypot Investigation Agent - Instructions

Purpose

This agent performs comprehensive security analysis on honeypot servers to assess attack patterns, threat intelligence, vulnerabilities, and defensive effectiveness. Honeypots are decoy systems designed to attract attackers and provide early warning of emerging threats.

📑 TABLE OF CONTENTS

1. Critical Workflow Rules - Start here!
2. Investigation Parameters - Input requirements
3. Execution Workflow - Complete process with time tracking
4. KQL Query Library - Validated query patterns
5. Report Template - Executive markdown structure
6. Error Handling - Troubleshooting guide
7. Visualization Options - Heatmap and Geomap skills

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

Before starting ANY honeypot investigation:

1. ALWAYS calculate date ranges correctly (use current date from context)
2. ALWAYS track and report time after each major step (mandatory per main instructions)
3. ALWAYS run independent queries in parallel (drastically faster execution)
4. ALWAYS save intermediate results to temp/ (enables debugging and auditing)
5. ALWAYS use create_file for reports (NEVER use PowerShell terminal commands)

Date Range Rules (from main copilot-instructions):

• Real-time/recent searches: Add +2 days to current date for end range
• Example: Current date = Dec 12, 2025; Last 48 hours = datetime(2025-12-10) to datetime(2025-12-14)

Investigation Parameters

Required Inputs

Automatic Derivations

• Start Date: Current date - time range
• End Date: Current date + 2 days (per date range rules)
• Output File: reports/honeypot/Honeypot_Report_<hostname>_<timestamp>.md
• Temp Files: temp/honeypot_ips_<timestamp>.json, temp/honeypot_data_<timestamp>.json

Execution Workflow

🚨 MANDATORY: Time Tracking Pattern

YOU MUST TRACK AND REPORT TIME AFTER EVERY MAJOR STEP:

[MM:SS] ✓ Step description (XX seconds)

Required Reporting Points:

1. After Phase 1 (failed connection queries)
2. After Phase 2 (IP enrichment + threat intel)
3. After Phase 3 (incident filtering)
4. After Phase 4 (vulnerability scan)
5. After Phase 5 (report generation)
6. Final: Total elapsed time

Phase 1: Query Failed Connections (PARALLEL)

Execute ALL THREE queries in parallel using mcp_sentinel-data_query_lake:

Query 1A: SecurityEvent (Windows Security Logs)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
SecurityEvent
| where TimeGenerated between (start .. end)
| where Computer contains honeypot  // Use 'contains' for flexible hostname matching
| where EventID in (4625, 4771, 4776)  // Failed logon attempts
| where isnotempty(IpAddress) and IpAddress != "-"  // IpAddress is built-in field
| where IpAddress != "127.0.0.1"  // Exclude localhost (internal honeypot traffic)
| summarize 
    FailedAttempts=count(), 
    FirstSeen=min(TimeGenerated), 
    LastSeen=max(TimeGenerated),
    TargetAccounts=make_set(Account, 10)
    by IpAddress, EventID
| extend EventType = case(
    EventID == 4625, "Failed Logon",
    EventID == 4771, "Kerberos Pre-Auth Failed",
    EventID == 4776, "NTLM Auth Failed",
    "Unknown")
| order by FailedAttempts desc
| take 50

Query 1B: W3CIISLog (IIS Web Server Logs)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
W3CIISLog
| where TimeGenerated between (start .. end)
| where Computer =~ honeypot
| where tolong(scStatus) >= 400  // HTTP errors (4xx/5xx) - scStatus is string type
| where cIP != "127.0.0.1" and cIP != "::1"  // Exclude localhost (internal honeypot traffic)
| summarize 
    RequestCount=count(), 
    FirstSeen=min(TimeGenerated), 
    LastSeen=max(TimeGenerated),
    TargetedURIs=make_set(csUriStem, 10),
    StatusCodes=make_set(tolong(scStatus), 5)  // Convert to long for proper aggregation
    by IpAddress = cIP
| order by RequestCount desc
| take 50

Query 1C: DeviceNetworkEvents (Defender Network Traffic - INBOUND ONLY)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where DeviceName =~ honeypot
| where ActionType in ("ConnectionSuccess", "InboundConnectionAccepted", "ConnectionFound")  // Successful inbound TCP connections
| where LocalPort in (3389, 80, 443, 445, 22, 21, 23, 8080, 8443)  // Filter by attacked services (LocalPort = honeypot's listening port)
| where RemoteIP != "127.0.0.1" and RemoteIP != "::1" and RemoteIP != "::ffff:127.0.0.1"  // Exclude localhost
| where RemoteIP !startswith "192.168." and RemoteIP !startswith "10." and RemoteIP !startswith "172.16."  // Exclude RFC1918 private IPs
| where RemoteIP !startswith "fe80:" and RemoteIP !startswith "fc00:" and RemoteIP !startswith "fd00:"  // Exclude IPv6 link-local and ULA
| where RemoteIP !startswith "::ffff:"  // Filter out IPv6-mapped IPv4 addresses (reduces duplicate noise)
| summarize 
    ConnectionCount=count(), 
    FirstSeen=min(TimeGenerated), 
    LastSeen=max(TimeGenerated),
    TargetedPorts=make_set(LocalPort, 10),  // LocalPort = attacked services on honeypot
    Actions=make_set(ActionType, 5)
    by RemoteIP  // RemoteIP = attacker source
| order by ConnectionCount desc
| take 50

IMPORTANT: This query shows TCP connection establishment (network layer), NOT successful authentication. Attackers who appear here may still fail at the authentication layer (SecurityEvent 4625). For honeypots, all inbound connections should be treated as reconnaissance/attack attempts.

After Phase 1 completes:

• Merge all three result sets
• Rank IPs by attack volume (prioritize SecurityEvent FailedAttempts, then W3CIISLog RequestCount, then DeviceNetworkEvents ConnectionCount)
• Select top 10-15 IPs for enrichment (focus on high-volume attackers, not one-off scanners)
• Extract unique IP addresses into array
• Save prioritized IPs only to temp/honeypot_ips_<timestamp>.json in format: {"ips": ["1.2.3.4", "5.6.7.8", ...]}
• Document total unique attacker count separately for report statistics
• Report elapsed time: [MM:SS] ✓ Failed connection queries completed (XX seconds) - [total_count] unique IPs identified, top [enrichment_count] prioritized for enrichment

Phase 2: IP Enrichment & Threat Intelligence (PARALLEL)

Execute IP enrichment script AND Sentinel threat intel query in parallel:

2A: Run IP Enrichment Script

# Read prioritized IPs from JSON file (top 10-15 by attack volume)
# This reduces token consumption by ~80% while maintaining critical intelligence
$env:PYTHONPATH = "<WORKSPACE_ROOT>"
cd "<WORKSPACE_ROOT>"
.\.venv\Scripts\python.exe enrich_ips.py --file temp/honeypot_ips_<timestamp>.json

Enrichment provides (for prioritized IPs only):

• Geolocation (city, region, country)
• ISP/Organization (ASN, org name)
• VPN/Proxy/Tor detection (is_vpn, is_proxy, is_tor)
• Abuse reputation (abuse_confidence_score, total_reports)
• Shodan intelligence: open ports, CVEs, tags (e.g., eol-os, self-signed, c2), CPEs, hostnames
• Risk level assessment (HIGH/MEDIUM/LOW)

Note: Enrichment script provides aggregated statistics for all IPs - use these summary stats in report narrative instead of listing every IP

2B: Query Sentinel Threat Intelligence

let target_ips = dynamic(["<IP1>", "<IP2>", "<IP3>", ...]);  // From Phase 1 prioritized list (top 10-15 IPs)
ThreatIntelIndicators
| extend IndicatorType = replace_string(replace_string(replace_string(tostring(split(ObservableKey, ":", 0)), "[", ""), "]", ""), "\"", "")
| where IndicatorType in ("ipv4-addr", "ipv6-addr", "network-traffic")
| extend NetworkSourceIP = toupper(ObservableValue)
| where NetworkSourceIP in (target_ips)
| where IsActive and (ValidUntil > now() or isempty(ValidUntil))
| extend Description = tostring(parse_json(Data).description)
| where Description !contains_cs "State: inactive;" and Description !contains_cs "State: falsepos;"
| extend TrafficLightProtocolLevel = tostring(parse_json(AdditionalFields).TLPLevel)
| extend ActivityGroupNames = extract(@"ActivityGroup:(\S+)", 1, tostring(parse_json(Data).labels))
| summarize arg_max(TimeGenerated, *) by NetworkSourceIP
| project 
    TimeGenerated,
    IPAddress = NetworkSourceIP,
    ThreatDescription = Description,
    ActivityGroupNames,
    Confidence,
    ValidUntil,
    TrafficLightProtocolLevel,
    IsActive
| order by Confidence desc, TimeGenerated desc

After Phase 2 completes:

• Merge IP enrichment JSON with Sentinel threat intel results
• Save combined data to temp/honeypot_data_<timestamp>.json
• Report elapsed time: [MM:SS] ✓ IP enrichment completed (XX seconds)

Phase 3: Query Security Incidents (Sentinel KQL)

Step 3A: Get Device ID from Sentinel

let honeypot = '<HONEYPOT_NAME>';
DeviceInfo
| where TimeGenerated > ago(30d)
| where DeviceName =~ honeypot or DeviceName contains honeypot
| summarize arg_max(TimeGenerated, *)
| project DeviceId, DeviceName, OSPlatform, OSVersion, PublicIP

Extract DeviceId (GUID) from result - returns single most recent device record.

Step 3B: Query Security Incidents

let targetDevice = "<HONEYPOT_NAME>";
let targetDeviceId = "<DEVICE_ID>";  // REQUIRED: Get from DeviceInfo query (Step 3A)
let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let relevantAlerts = SecurityAlert
| where TimeGenerated between (start .. end)
| where Entities has targetDevice or Entities has targetDeviceId
| summarize arg_max(TimeGenerated, *) by SystemAlertId
| project SystemAlertId, AlertName, AlertSeverity, ProviderName, Tactics;
SecurityIncident
| where CreatedTime between (start .. end)  // Filter on CreatedTime for incidents created in range
| summarize arg_max(TimeGenerated, *) by ProviderIncidentId  // Get most recent state per ProviderIncidentId
| project ProviderIncidentId, Title, Severity, Status, Classification, CreatedTime, LastModifiedTime, Owner, AdditionalData, AlertIds, Labels
| where not(tostring(Labels) has "Redirected")  // Exclude merged incidents
| mv-expand AlertId = AlertIds
| extend AlertId = tostring(AlertId)
| join kind=inner relevantAlerts on $left.AlertId == $right.SystemAlertId
| extend ProviderIncidentUrl = tostring(AdditionalData.providerIncidentUrl)
| extend OwnerUPN = tostring(Owner.userPrincipalName)
| extend LastModifiedTime = todatetime(LastModifiedTime)
| summarize 
    Title = any(Title),
    Severity = any(Severity),
    Status = any(Status),
    Classification = any(Classification),
    CreatedTime = any(CreatedTime),
    LastModifiedTime = any(LastModifiedTime),
    OwnerUPN = any(OwnerUPN),
    ProviderIncidentUrl = any(ProviderIncidentUrl),
    AlertCount = count(),
    MitreTactics = make_set(Tactics)
    by ProviderIncidentId
| order by LastModifiedTime desc
| take 10

IMPORTANT:

• This query joins SecurityIncident with SecurityAlert to provide full incident context

• Deduplication: The final summarize statement collapses multiple alerts per incident into a single row (groups by ProviderIncidentId)

• Filter on CreatedTime to find incidents created in the investigation period

• Use arg_max(TimeGenerated, *) by IncidentNumber to get the most recent update for each incident (includes status changes, comments, etc.)

• Returns up to 10 unique incidents (grouped by ProviderIncidentId to ensure one row per external incident ID)

• ⚠️ CHECK STATUS FIELD: Only report incidents with Status="New" or "Active" as threats. Status="Closed" + Classification="BenignPositive" = expected honeypot activity (do not flag as threat)

After Phase 3 completes:

• Report elapsed time: [MM:SS] ✓ Security incidents query completed (XX seconds)

Phase 4: Vulnerability Assessment

⚠️ CRITICAL: TVM tables are snapshot tables — NO time filtering!

• DeviceTvmSoftwareVulnerabilities has NO Timestamp or TimeGenerated column
• Do NOT add where Timestamp between (...) — it will fail with a schema error
• Do NOT use Sentinel Data Lake (query_lake) — TVM tables are only available via Advanced Hunting
• Use RunAdvancedHuntingQuery MCP tool only

Step 4A: Query Vulnerabilities via Advanced Hunting KQL

let deviceName = '<HONEYPOT_NAME>';
DeviceTvmSoftwareVulnerabilities
| where DeviceName startswith deviceName
| project
    CveId,
    VulnerabilitySeverityLevel,
    SoftwareVendor,
    SoftwareName,
    SoftwareVersion,
    RecommendedSecurityUpdate,
    RecommendedSecurityUpdateId
| summarize by CveId, VulnerabilitySeverityLevel, SoftwareVendor, SoftwareName, SoftwareVersion, RecommendedSecurityUpdate, RecommendedSecurityUpdateId
| order by case(VulnerabilitySeverityLevel == "Critical", 1, VulnerabilitySeverityLevel == "High", 2, VulnerabilitySeverityLevel == "Medium", 3, 4) asc
| take 30

Key columns returned:

• CveId — CVE identifier (e.g., CVE-2025-15467)
• VulnerabilitySeverityLevel — String: Critical / High / Medium / Low
• SoftwareVendor, SoftwareName, SoftwareVersion — Affected software details
• RecommendedSecurityUpdate — Patch info (may be empty)

🔴 PROHIBITED:

• ❌ Adding Timestamp or TimeGenerated filters (column does not exist)
• ❌ Projecting CvssScore (column does not exist — use VulnerabilitySeverityLevel instead)
• ❌ Using Sentinel Data Lake MCP (query_lake) for TVM tables
• ❌ Using GetDefenderMachineVulnerabilities API (requires separate machine ID lookup, less reliable)

After Phase 4 completes:

• Report elapsed time: [MM:SS] ✓ Vulnerability scan completed (XX seconds)

Phase 5: Generate Executive Report

Use the Report Template (see section below) to create markdown report.

Critical Report Sections:

1. Executive Summary - High-level findings (2-3 paragraphs)
2. Attack Surface Analysis - Failed connections by IP, service, pattern
3. Threat Intelligence Correlation - Known malicious IPs, APT groups, VPNs
4. Security Incidents - Incidents triggered by honeypot activity
5. Attack Pattern Analysis - Targeted services, credential attacks, web exploits
6. Vulnerability Status - Current CVEs and exploitation risk
7. Key Detection Insights - TTPs, MITRE ATT&CK mapping, novel indicators
8. Honeypot Effectiveness - Metrics and recommendations
9. Conclusion - Summary and next steps

Report Generation:

1. Populate template with data from Phases 1-4
2. Use create_file to save: reports/honeypot/Honeypot_Report_<hostname>_<timestamp>.md
3. Return absolute path to user

After Phase 5 completes:

• Report elapsed time: [MM:SS] ✓ Report generated (XX seconds)
• Provide comprehensive timeline breakdown with total elapsed time

KQL Query Library

Additional Useful Queries

Query: Top Targeted User Accounts (Credential Attacks)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
SecurityEvent
| where TimeGenerated between (start .. end)
| where Computer =~ honeypot
| where EventID == 4625  // Failed logon
| summarize FailedAttempts = count() by Account
| order by FailedAttempts desc
| take 20

Query: Web Exploitation Patterns (SQL Injection, XSS, Path Traversal)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
W3CIISLog
| where TimeGenerated between (start .. end)
| where Computer =~ honeypot
| where csUriStem has_any ("'", "union", "select", "script", "../", "..\\", "cmd.exe", "powershell")
| summarize 
    AttemptCount = count(),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    UniqueIPs = dcount(cIP)
    by ExploitPattern = case(
        csUriStem has_any ("'", "union", "select"), "SQL Injection",
        csUriStem has "script", "XSS",
        csUriStem has_any ("../", "..\\"), "Path Traversal",
        csUriStem has_any ("cmd.exe", "powershell"), "Command Injection",
        "Other")
| order by AttemptCount desc

Query: Port Scanning Detection

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
DeviceNetworkEvents
| where TimeGenerated between (start .. end)
| where DeviceName =~ honeypot
| summarize 
    DistinctPorts = dcount(RemotePort),
    PortsScanned = make_set(RemotePort),
    EventCount = count()
    by RemoteIP
| where DistinctPorts >= 5  // Threshold: 5+ ports = scan
| order by DistinctPorts desc
| take 20

Query: Brute Force Detection (High Volume from Single IP)

let start = datetime(<StartDate>);
let end = datetime(<EndDate>);
let honeypot = '<HONEYPOT_NAME>';
let threshold = 50;  // 50+ failed attempts = brute force
SecurityEvent
| where TimeGenerated between (start .. end)
| where Computer =~ honeypot
| where EventID == 4625
| extend IpAddress = extract(@"Source Network Address:\s+([^\s]+)", 1, tostring(EventData))
| summarize FailedAttempts = count() by IpAddress
| where FailedAttempts >= threshold
| order by FailedAttempts desc

Report Template

Use this structure for executive reports:

# Honeypot Security Analysis - <HONEYPOT_NAME>
**Analysis Period:** <START_DATE> to <END_DATE> (<HOURS> hours)  
**Report Generated:** <TIMESTAMP>  
**Classification:** CONFIDENTIAL

---

## Executive Summary

[3 comprehensive paragraphs covering attack overview, threat landscape, and value delivered]

**Key Metrics:**
- **Total Attack Attempts:** [count]
- **Unique Attacking IPs:** [count]
- **Security Incidents Triggered:** [count]
- **Known Malicious IPs (Threat Intel):** [count] ([percentage]%)
- **Current Vulnerabilities:** [count] HIGH, [count] MEDIUM

---

## 1. Attack Surface Analysis
[Failed connections by source IP, geographic distribution, VPN/anonymization summary]

## 2. Threat Intelligence Correlation
[IPs matched in threat intel, highest confidence threats, MSTIC indicators]

## 3. Security Incidents
[Incidents involving honeypot with severity, status, classification, MITRE tactics]

## 4. Attack Pattern Analysis
[Targeted services, credential attacks, web exploitation, port scanning]

## 5. Honeypot Vulnerability Status
[CVE inventory, exploitation risk assessment, cross-reference with attacks]

## 6. Key Detection Insights
[MITRE ATT&CK mapping, novel indicators, threat actor attribution]

## 7. Honeypot Effectiveness
[Detection metrics, recommendations for optimization]

## 8. Conclusion
[Summary, key takeaways, immediate/short-term/long-term actions]

---

**Investigation Timeline:**
[Phase timing breakdown]

**Total Investigation Time:** [duration]

Error Handling

Common Issues and Solutions

Validation Checklist

Before delivering report, verify:

• ✅ All Phase timestamps reported to user
• ✅ Total elapsed time calculated and displayed
• ✅ IP enrichment data merged with attack logs
• ✅ Incident filtering correctly applied (only honeypot-related incidents)
• ✅ Vulnerability data retrieved (or documented as unavailable)
• ✅ Report saved to correct path: reports/honeypot/Honeypot_Report_<hostname>_<timestamp>.md
• ✅ Absolute path returned to user

Integration with Main Copilot Instructions

This skill follows all patterns from the main copilot-instructions.md:

• Date range handling: Uses +2 day rule for real-time searches
• Parallel execution: Runs independent queries simultaneously
• Time tracking: Mandatory reporting after each phase
• Token management: Uses create_file for all output
• KQL best practices: Follows Sample KQL Query patterns
• IP enrichment: Uses documented enrich_ips.py utility

Example invocations:

• "Investigate the honeypot HONEYPOT-01 over the last 48 hours"
• "Run honeypot security analysis for honeypot-server-01 from Dec 10-12"
• "Generate honeypot report for [hostname] last 7 days"

Visualization Options

After completing the investigation, offer to visualize the attack data using the dedicated visualization skills:

Heatmap Visualization

Use the heatmap-visualization skill (.github/skills/heatmap-visualization/SKILL.md) to show attack patterns over time with threat intel drill-down.

When to offer:

• ✅ After completing honeypot investigation phases
• ✅ When user asks "show me the attack patterns" or "visualize the attacks"
• ✅ For comparing attack volumes across time periods
• ❌ Skip if investigation found minimal activity (<5 unique IPs)

Geomap Visualization

Use the geomap-visualization skill (.github/skills/geomap-visualization/SKILL.md) to show attack origins on a world map.

When to offer:

• ✅ After completing honeypot investigation phases
• ✅ When user asks "where are the attacks coming from?" or "show on a map"
• ✅ For geographic threat distribution analysis
• ❌ Skip if all IPs are from the same region

Note: W3CIISLog includes native RemoteIPLatitude and RemoteIPLongitude fields - use these directly for geomap visualization without additional enrichment.

Last Updated: January 29, 2026

KQL Query Authoring - Instructions

Purpose

Generate validated, production-ready KQL queries by combining schema validation (331+ indexed tables), Microsoft Learn documentation, community examples, and performance best practices.

Prerequisites

Required MCP Servers:

1. KQL Search MCP Server — Schema validation, query examples, table discovery

   • Install: npm install -g kql-search-mcp (npm)

2. Microsoft Docs MCP Server — Official Microsoft Learn documentation and code samples

   • GitHub: MicrosoftDocs/mcp

Verification: Tools should be available as mcp_kql-search_* and mcp_microsoft-lea_*.

⚠️ Known Issues

search_favorite_repos Bug (v1.0.5)

❌ Broken — ERROR_TYPE_QUERY_PARSING_FATAL. Use mcp_kql-search_search_github_examples_fallback instead.

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

1. Validate table schema FIRST — mcp_kql-search_get_table_schema to verify table exists, column names, and data types.

2. Check platform schema — Sentinel uses TimeGenerated; Defender XDR uses Timestamp. Microsoft Learn examples default to XDR syntax — always convert before testing on Sentinel.

3. Check local query library FIRST — Use the discovery manifest (.github/manifests/discovery-manifest.yaml) for domain/MITRE lookups and grep_search for table-name/keyword lookups. See the KQL Pre-Flight Checklist in copilot-instructions.md for the full priority order.

4. Query file structure: NO placeholder TOC — When creating a new query file, do NOT add a ## Quick Reference — Query Index heading or placeholder. scripts/generate_tocs.py creates the heading and table itself. Pre-creating it confuses the strip-and-reinsert logic and produces duplicated content. See ## Creating Query Files below for full file structure rules.

5. Use multiple sources — Schema (authoritative column names) + Microsoft Learn (official patterns) + community queries (real-world examples).

6. Test using the correct execution tool — Follow the Tool Selection Rule in copilot-instructions.md:

   • Sentinel-native tables → Data Lake or AH
   • XDR tables ≤ 30d → Advanced Hunting (free); > 30d → Data Lake
   • XDR-only tables (DeviceTvm*, Exposure*) → Advanced Hunting only
   • Adapt timestamp column when switching tools

7. Test queries before presenting to user — Run with | take 5 via live execution. Use mcp_kql-search_validate_kql_query as fallback if live testing unavailable.

8. Provide context — Explain what the query does, expected results, and any limitations.

9. Read the complete workflow below before starting.

📋 Inherited rules: This skill inherits the KQL Pre-Flight Checklist, Tool Selection Rule (Data Lake vs Advanced Hunting), and Known Table Pitfalls from copilot-instructions.md. Those rules are authoritative — do not contradict them here.

Query Authoring Workflow

Step 1: Understand User Requirements

Extract key information:

• Table(s) needed: Which data source? (e.g., EntraIdSignInEvents, EmailEvents, SecurityAlert)
• Time range: How far back? (e.g., last 7 days, specific date range)
• Filters: What specific conditions? (e.g., user, IP, threat type)
• Output: Statistics, detailed records, time series, aggregations?
• Platform: Sentinel or Defender XDR? (affects column names)
• Deployment target: Custom detection rule? (see below)

Custom Detection Intent Detection:

If the user mentions "custom detection", "detection rule", "deploy as detection", "CD rule", "author detections for", or "deploy to Defender":

1. Read the detection-authoring skill (.github/skills/detection-authoring/SKILL.md) — Critical Rules and CD Metadata Contract sections
2. Design queries with CD constraints — row-level output, mandatory columns (TimeGenerated, DeviceName, ReportId), no bare summarize
3. Include cd-metadata blocks in the output file (see Step 8)
4. Still write queries in Sentinel format (with let variables, 7d lookback) — adaptation to CD format happens at deployment time via the detection-authoring skill

Step 2: Check Local Query Library

Search for existing verified queries before writing from scratch. Use two complementary methods:

1. Manifest lookup (domain/MITRE): Read .github/manifests/discovery-manifest.yaml and match by domain tag (e.g., identity, endpoint, email) or MITRE technique ID (e.g., T1078, T1566). Best when you know the security domain or ATT&CK technique.
2. Targeted grep_search (table/keyword): grep_search for the specific table name (e.g., CloudAppEvents, OfficeActivity) or operation keyword (e.g., New-InboxRule, SecretGet) scoped to queries/** and .github/skills/**. The manifest lacks table-name and keyword fields — grep fills this gap.
3. Check the Ad-Hoc Query Examples appendix in copilot-instructions.md

When to use which: Domain/technique known → manifest first. Table name/operation known → grep first. Both can be used together — manifest for breadth, grep for precision.

If a suitable query is found, adapt it and skip to Step 6. These queries encode known pitfalls and schema quirks.

Step 3: Get Table Schema (MANDATORY)

mcp_kql-search_get_table_schema("<table_name>")

Returns: category, description, all columns with data types, and example queries. Use this to verify column names and understand data types.

Step 4: Get Official Code Samples

mcp_microsoft-lea_microsoft_code_sample_search(
  query: "<table_name> <scenario description>",
  language: "kusto"
)

Include table name + scenario in the query (e.g., "EmailEvents phishing detection").

Step 5: Get Community Examples

mcp_kql-search_search_github_examples_fallback(
  table_name: "<table_name>",
  description: "<goal description>"
)

Also available: mcp_kql-search_search_kql_repositories to find KQL-focused repos.

Step 6: Generate Query

Combine insights: schema for column names, Learn for patterns, community for techniques.

Standalone queries rule: When generating MULTIPLE separate queries, each must start directly with the table name — never use shared let variables across separate queries (they run independently). Use let variables only within a single complex query.

Step 7: Validate and Test (MANDATORY)

Test queries against live data before presenting to the user.

1. Convert Timestamp → TimeGenerated if adapting MS Learn examples for Sentinel
2. Test via mcp_sentinel-data_query_lake or RunAdvancedHuntingQuery with | take 5
3. Verify results are sensible — check for empty results (wrong table/time/filters)
4. Fix schema mismatches or syntax errors, re-test
5. Remove test limits, present to user

Common errors:

Fallback validation: mcp_kql-search_validate_kql_query("<query>") — syntax/schema check only, no live data.

Step 8: Format and Deliver Output

Single query: Provide directly in chat with brief explanation and expected results.

Multiple queries (3+): Create a markdown file in queries/<subfolder>/ with the standardized metadata header. This header is mandatory — build_manifest.py parses it to index the file for discovery by threat-pulse and other skills.

File naming: queries/<subfolder>/<topic>.md — e.g., queries/email/email_threat_detection.md

Required metadata header template (first 10 lines of every query file):

# <Descriptive Title>

**Created:** YYYY-MM-DD  
**Platform:** Microsoft Sentinel | Microsoft Defender XDR | Both  
**Tables:** <comma-separated exact KQL table names>  
**Keywords:** <comma-separated searchable terms — attack techniques, scenarios, field names>  
**MITRE:** <comma-separated technique IDs, e.g., T1098.001, T1136.003, TA0008>  
**Domains:** <comma-separated domain tags from the valid set below>  
**Timeframe:** Last N days (configurable)  

Valid domain tags: incidents, identity, spn, endpoint, email, admin, cloud, exposure

After creating a new query file: Run python .github/manifests/build_manifest.py to regenerate the discovery manifest, then run python scripts/generate_tocs.py to auto-generate the Quick Reference TOC. The validator will flag any missing required fields.

Subfolder selection: Place files in the subfolder matching the primary data source: identity/, endpoint/, email/, network/, cloud/.

Include per-query documentation with Purpose, Thresholds, Expected Results, and Tuning guidance.

Heading format for TOC compatibility: The generate_tocs.py script auto-generates a Quick Reference TOC by scanning ### and ## Query headings that have a KQL code block within 40 lines. To ensure clean TOC output:

• ✅ DO use ### Query N: <Title> or ## Query N: <Title> for query headings — the number prefix ensures proper TOC ordering
• ✅ DO add a ## heading (e.g., ## Queries, ## Part A:, ## Hunts) immediately before the first ### Query N: if the file has preamble content (Overview, Table Selection, etc.). The TOC generator uses a --- → ## heading pair as its insertion anchor — without it, the script inserts the TOC at the bottom of the file.
• ✅ DO start non-query section headings with a non-query keyword (e.g., ### Deployment, ### Tuning, ### References) — these are automatically filtered out by the TOC generator
• ❌ DO NOT add a ## Quick Reference — Query Index heading or placeholder yourself — the script creates the heading and table. Pre-existing placeholders cause duplicated content and a broken file structure. (This is also enforced as Critical Rule #4 above.)
• ❌ DO NOT use ### headings for non-query content that contains a KQL code block within 40 lines — the TOC generator uses KQL proximity to detect query headings and will incorrectly include them

Investigation shortcuts (optional): Query files can include an **Investigation shortcuts:** bulleted list between the ## Quick Reference heading and the TOC table. These document recommended query combos for common investigation scenarios (e.g., "Delivered phishing drill-down: Q2.4 + Q7.6 + Q3.3"). Shortcuts are preserved by generate_tocs.py across re-runs. Don't add them to new files — they're a refinement added after real investigations reveal which query combos work best together.

CD-Aware Output

When CD intent is detected (Step 1), each query MUST include a <!-- cd-metadata --> HTML comment block. The full schema is in .github/skills/detection-authoring/SKILL.md under CD Metadata Contract.

Valid cd-metadata fields (exhaustive list):

⛔ responseActions is NOT a valid cd-metadata field. It shares a name with the Graph API field that is explicitly prohibited in LLM-authored detections ("responseActions": [] is mandatory). Do not include it. Put incident response guidance in recommendedActions instead.

<!-- cd-metadata
cd_ready: true
schedule: "1H"
category: "Persistence"
title: "Suspicious Scheduled Task on {{DeviceName}}"
impactedAssets:
  - type: device
    identifier: DeviceName
recommendedActions: "Investigate the task XML and decode any encoded payloads."
adaptation_notes: "Remove let blocks, add mandatory columns"
-->

For queries not suitable for CD (baseline/statistical):

<!-- cd-metadata
cd_ready: false
adaptation_notes: "Statistical baseline — requires bare summarize, not CD-compatible"
-->

Summary table: Include a CD column in the Implementation Priority table: ✅ 1H / ❌.

Tool Quick Reference

Schema Differences

Other common differences: Identity/UserPrincipalName (Sentinel) vs AccountUpn/AccountName (XDR); IPAddress (Sentinel) vs RemoteIP/LocalIP (XDR). Always verify with get_table_schema.

Sign-In Table Selection (High-Frequency Queries)

Sign-in queries are the most common query type. Use this decision rule:

Common mistakes:

• Using union SigninLogs, AADNonInteractiveUserSignInLogs in AH queries — unnecessary, EntraIdSignInEvents covers both
• Using LogonType == "nonInteractiveUser" — values are JSON arrays (["nonInteractiveUser"]), use has
• Using ResultType on EntraIdSignInEvents — column is ErrorCode (int), not string

Full details: See copilot-instructions.md → Known Table Pitfalls → EntraIdSignInEvents (AH table preference rule) for complete column mapping and additional pitfalls.

Full table pitfalls (dynamic field parsing, immutable fields, table casing, deprecated tables) are documented in copilot-instructions.md under Known Table Pitfalls. Refer there for SecurityAlert.Status, AuditLogs.InitiatedBy, SigninLogs.DeviceDetail, and 20+ other table-specific gotchas.

Best Practices

Performance Optimization

Reference: KQL Best Practices — Microsoft Learn

1. Filter on datetime columns first

The most important optimization. Datetime predicates use efficient index-based shard elimination, skipping entire data partitions without scanning.

// ✅ Correct — datetime first, then selective string filters
SigninLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ "user@domain.com"

// ❌ Wrong — string filter before datetime
SigninLogs
| where UserPrincipalName =~ "user@domain.com"
| where TimeGenerated > ago(7d)

2. Use has over contains for token matching

has uses the term index for full-token lookup. contains scans every character — dramatically slower on large tables.

// ✅ Faster — term-level index lookup
| where UserPrincipalName has "admin"

// ❌ Slower — full substring scan
| where UserPrincipalName contains "admin"

Use contains only when you genuinely need substring matching (e.g., fragments inside URL paths).

3. Prefer case-sensitive operators

Case-sensitive comparisons (==, in, has_cs) are faster than case-insensitive (=~, in~, has). Use case-insensitive only when casing is unpredictable.

// ✅ Faster — ActionType, Operation, OfficeWorkload have consistent casing
| where ActionType == "LogonFailed"
| where Operation in ("New-InboxRule", "Set-InboxRule")
| where OfficeWorkload == "Exchange"

// 🔵 Use =~ only when casing varies (e.g., user-entered UPNs)
| where UserPrincipalName =~ "user@domain.com"

Common fields with consistent casing (always use == / in): ActionType, Operation, OfficeWorkload, EventID, ResultType, DeliveryAction, EmailDirection, LogonType, Severity, Status, Classification.

4. Filter tables BEFORE joins

Pre-filter both sides of a join to reduce data volume. Move where clauses into subqueries.

// ✅ Correct — filter KB table before joining
DeviceTvmSoftwareVulnerabilities
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where IsExploitAvailable == true
    | where CvssScore >= 8.0
) on CveId

// ❌ Wrong — joins full tables, filters after
DeviceTvmSoftwareVulnerabilities
| join kind=inner DeviceTvmSoftwareVulnerabilitiesKB on CveId
| where IsExploitAvailable == true

Join sizing rules:

• Smaller table on the left (or hint.strategy=broadcast when left is small)
• in instead of left semi join for single-column filtering
• lookup instead of join when right side is small (<50 MB)
• hint.shufflekey=<key> when both sides are large with high-cardinality join key

5. Use materialize() for multi-referenced let statements

Without materialize(), the engine may recompute the let expression each time it's referenced.

// ✅ Computed once, reused twice
let SprayFailures = materialize(
    EntraIdSignInEvents
    | where Timestamp > ago(7d)
    | where ErrorCode in (50126, 50053, 50057)
    | summarize FailedAttempts = count(), TargetUsers = dcount(AccountUpn)
        by SourceIP = IPAddress
    | where TargetUsers >= 5);

6. Narrow arg_max to only needed columns

arg_max(TimeGenerated, *) materializes every column. Specify only what you use.

// ✅ Only 5 columns materialized
SecurityAlert
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, Entities, Tactics, Techniques, AlertName, AlertSeverity) by SystemAlertId

// ❌ Materializes all 30+ columns
SecurityAlert
| summarize arg_max(TimeGenerated, *) by SystemAlertId

7. Pre-filter before JSON parsing

For rare key/value lookups in dynamic columns, use has to eliminate rows before expensive parse_json().

// ✅ Term filter first, JSON parse on survivors
AuditLogs
| where tostring(TargetResources) has "MyApp"
| extend Target = tostring(parse_json(tostring(TargetResources[0])).displayName)
| where Target == "MyApp"

8. Filter on table columns, not calculated columns

Filtering on native columns enables index usage; calculated columns force full scans.

// ✅ Filter on native column
SecurityEvent | where EventID == 4625

// ❌ Filter on calculated column
SecurityEvent | extend Cat = case(EventID == 4625, "Fail", ...) | where Cat == "Fail"

9. Project only needed columns early

Drop unnecessary columns before expensive operators (join, summarize, mv-expand) to reduce memory and shuffling.

10. Use take or summarize to limit results

Unbounded queries on large tables consume excessive resources.

11. Platform-specific dynamic column access

In AH, AuditLogs.InitiatedBy and TargetResources are native dynamic — use direct dot-notation. In Data Lake, they may be string-typed requiring parse_json().

// ✅ Advanced Hunting — direct access
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)

// ✅ Data Lake — parse_json wrapper
| extend Actor = tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)

// 🔵 Safe in both — stringify full field
| where tostring(InitiatedBy) has "user@domain.com"

Security and Privacy

• Limit sensitive data exposure — redact PII with strcat(substring(UPN, 0, 3), "***") when appropriate
• Filter early — reduce dataset before projecting sensitive columns

Code Quality

• Comments — explain what the query does and why key filters are applied
• Meaningful variable names — let SuspiciousIPs = ... not let x = ...
• Standalone queries — when providing multiple separate queries, each MUST start with the table name directly. Never share let variables across queries the user will run independently

Dynamic Type Casting

Common "expected string expression" error: After mv-expand, parse_json, or split, values are dynamic — string functions fail. Always convert first:

// After mv-expand
| mv-expand AuthDetails
| extend AuthMethod = tostring(AuthDetails.authenticationMethod)

// After split
| extend Parts = split(UPN, "@")
| extend Domain = tostring(Parts[1])

Rule of thumb: If you get "expected string expression", add tostring().

Threat Pulse — Instructions

Purpose

The Threat Pulse skill is a rapid, broad-spectrum security scan designed for the "if you only had 15 minutes" scenario. It executes 12 queries across 7 security domains in parallel, producing a prioritized dashboard of findings with drill-down recommendations to specialized investigation skills.

What this skill covers:

Data sources: SecurityIncident, SecurityAlert, IdentityInfo, AADUserRiskEvents, EntraIdSignInEvents, DeviceProcessEvents, DeviceLogonEvents, ExposureGraphNodes, AADServicePrincipalSignInLogs, EmailEvents, CloudAppEvents, AuditLogs, DeviceTvmSoftwareVulnerabilities, DeviceTvmSoftwareVulnerabilitiesKB

Portal URL patterns are defined in the Defender XDR Portal Links table in the Take Action section. Append tid=<tenant_id> (from config.json) to ALL security.microsoft.com URLs — use ?tid= or &tid= depending on existing query params. Omit if tenant_id is not configured.

📑 TABLE OF CONTENTS

1. Critical Workflow Rules
2. Execution Workflow — Phase 0–3
3. Phase 4: Interactive Follow-Up Loop
4. Take Action — Portal links, AH queries, defanging
5. Sample KQL Queries — 12 queries
6. Post-Processing — Drift scores, cross-query correlation
7. Query File Recommendations
8. Report Template — Inline + full markdown file structure
9. Known Pitfalls
10. Quality Checklist
11. SVG Dashboard Generation

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

1. Workspace selection — Follow the SENTINEL WORKSPACE SELECTION rule from copilot-instructions.md. Call list_sentinel_workspaces() before first query.

2. Read config.json — Load workspace ID, tenant, subscription, and Azure MCP parameters before execution.

3. Output defaults — Default to inline chat with 7d lookback. Only ask the user for output preferences if they explicitly mention a different mode (e.g., "save to file", "markdown report", "30 day lookback"). If the user just says "threat pulse", "run a scan", or similar — proceed immediately with defaults, do not prompt.

4. ⛔ MANDATORY: Evidence-based analysis only — Every finding must cite query results. Every "clear" verdict must cite 0 results. Follow the Evidence-Based Analysis rule from copilot-instructions.md.

5. Parallel execution — Run the Data Lake query (Q5) and all Advanced Hunting queries (Q1, Q2, Q3, Q4, Q6, Q7, Q8, Q9, Q10, Q11, Q12) simultaneously.

6. Cross-query correlation — After all queries complete, check for correlated findings per the Cross-Query Correlation table in Post-Processing. Escalate priority when patterns match.

7. SecurityIncident output rule — Every incident MUST include a clickable Defender XDR portal URL: https://security.microsoft.com/incidents/{ProviderIncidentId}?tid=<tenant_id>. See Tenant ID in Portal URLs.

8. ⛔ MANDATORY: Query File Recommendations (tiered) — After rendering the main report body (Dashboard Summary through Recommended Actions), append the Query File Recommendations section. This runs AFTER the report is visible to the user — not as a blocking gate. Skip only when ALL verdicts are ✅.

9. ⛔ MANDATORY: 30d drill-down lookback — ALL Phase 4 drill-down queries use 30d (AH) or 90d (Data Lake) lookback, regardless of the Threat Pulse scan window. Entity-scoped queries (filtered by UPN/IP/device) have negligible performance difference between 7d and 30d, and attacks routinely predate the pulse window. AH caps at 30d anyway. Substitute ago(7d) → ago(30d) in all query file and skill queries during drill-downs.

10. ⛔ MANDATORY: The follow-up loop is stateful, memory-backed, and self-sustaining. Three non-negotiable invariants that hold for the ENTIRE session (re-read this rule before any follow-up interaction):

• (a) Memory is the source of truth, not the conversation. The prompt pool lives ONLY in /memories/session/threat-pulse-drilldowns.md. It MUST be created the first time the pool is built (Phase 4 step 1) and is a hard precondition for rendering any selection list. If you are about to present follow-up options and this file does not exist, STOP and create it first. NEVER reconstruct the pool from conversation history — always memory view immediately before each vscode_askQuestions call.
• (b) The loop re-presents itself automatically. After EVERY completed drill-down, you MUST return to Phase 4 step 2 and call vscode_askQuestions again with the updated pool — without waiting for the user to ask for the menu. The only exits are the user selecting Skip, or an empty pool. "Bring the menu back up" should never be something the user has to request.
• (c) The Quick Pick Call Contract is mechanical, not advisory. Run the Pre-Flight Checklist and print the Pool Receipt line before every call. In particular: ZERO recommended keys, multiSelect: true, correct icon taxonomy (🔍 📄 🎯 💾 🆕 🔄 📋), and the 💾 / 🔄 / Skip tail every iteration. Do not substitute an ad-hoc "Done" option for the contracted tail.

Execution Workflow

Phase 0: Prerequisites

1. Read config.json for workspace ID and Azure MCP parameters

2. Call list_sentinel_workspaces() to enumerate available workspaces

3. Use defaults (inline chat, 7d) unless user specified otherwise

4. ⛔ MANDATORY: Display scan summary — Before executing any queries, output the following brief to the user as plain markdown text (NOT inside a fenced code block, NOT as inline code). Use the exact heading, line breaks, and emoji-prefixed bullet items shown below. Substitute <WorkspaceName>, <WorkspaceId>, lookback, and output format. Never skip this step — it sets analyst expectations for what's about to run.

   🔍 Threat Pulse — Scan Plan

   Workspace: <WorkspaceName> (<WorkspaceId>) Lookback: <N>d Output: <Inline / Markdown file / Both>

   Executing 12 queries across 7 domains:

   🔴 Incidents — Open incidents (severity-ranked) + 7d closed summary (Q1, Q2) 🔐 Identity — Identity risk posture, risk event enrichment, auth spray (Q3, Q4) 🤖 NonHuman ID — Service principal behavioral drift (Q5) 💻 Endpoint — Device process drift, rare process chains (Q6, Q7) 📧 Email — Inbound threat snapshot (Q8) 🔑 Admin & Cloud — Cloud app ops, privileged operations (Q9, Q10) 🛡️ Exposure — Critical assets, exploitable CVEs (Q11, Q12)

   Data Lake: 1 query | Advanced Hunting: 11 queries in parallel Estimated time: ~2–4 minutes

Phase 1: Data Lake Query (Q5)

Why only 1 query on Data Lake? Q5 requires a 97-day lookback for SPN baseline computation — AH Graph API caps at 30 days. All other queries use ≤30d lookback on Analytics-tier tables accessible via AH.

Phase 2: Advanced Hunting Queries (Q1, Q2, Q3, Q4, Q6, Q7, Q8, Q9, Q10, Q11, Q12)

Run all 11 in parallel — no dependencies between queries.

Design rationale: The connected LA workspace makes all Sentinel tables (SecurityIncident, IdentityInfo, AADUserRiskEvents, AuditLogs, etc.) queryable via AH. AH is preferred: it's free for Analytics-tier tables and avoids per-query Data Lake billing.

Phase 3: Post-Processing & Report

1. Interpret device drift scores from Q6 results (see Post-Processing)
2. Run cross-query correlation checks (see rule 6 above)
3. Assign verdicts to each domain (🔴 Escalate / 🟠 Investigate / 🟡 Monitor / ✅ Clear)
4. Generate prioritized recommendations with drill-down skill references
5. Render the report immediately — output the Dashboard Summary, Detailed Findings, Cross-Query Correlations, and 🎯 Recommended Actions. Do NOT block on the manifest or prompt pool building.
6. After the report is rendered, run the Query File Recommendations procedure and append the 📂 Recommended Query Files section. This happens while the user is already reading the report — no perceived delay. Skip entirely when all verdicts are ✅.

Performance note: The Recommendation Gate was previously a blocking step (Phase 3.5) that loaded the ~500-line manifest YAML and ranked entries before the report could render. By moving it after the report output, the user sees findings immediately while recommendations load in the background. The Phase 4 prompt pool building also benefits — it reuses the recommendations already computed in step 6 rather than re-scanning all 12 query results independently.

Phase 4: Interactive Follow-Up Loop

After rendering the report, present the user with a selectable list of follow-up actions — skill investigations, query file hunts, and IOC lookups. Runs when at least one 🔴, 🟠, or 🟡 verdict exists (skip only when ALL verdicts are ✅).

This is a loop, not a one-shot. After each action completes, re-present the selection list with the prompt pool updated. Tier depth (🔴/🟠 vs 🟡-only vs all ✅) follows Rule 8.

⛔ Loop invariant — verify before EVERY iteration (per Rule 10): (a) /memories/session/threat-pulse-drilldowns.md exists and was just re-read via memory view — if not, create/read it first; (b) you are re-presenting the menu automatically after the prior drill-down, not because the user asked; (c) the Pre-Flight Checklist passed and the Pool Receipt was printed. If any of the three is false, fix it before calling vscode_askQuestions. The loop only ends on Skip or an empty pool.

Prompt types (three categories, one unified list):

Skill matching rules — derive from findings:

Drill-down lookback — Per Rule 9, substitute ago(7d) → ago(30d) (AH) or ago(90d) (Data Lake) in all drill-down queries.

Procedure:

1. Build the initial prompt pool by combining:

   • Skill prompts: one per unique entity + matching skill from the table above. If the same entity appears in multiple queries (e.g., Q3 and Q9), create ONE skill prompt for that entity — the correlation context goes in the Description, not in the Label.
   • Query file prompts: from Phase 3 step 5 keyword extraction. Each query file is its OWN separate prompt — never merge a query file prompt with a skill prompt.
   • IOC prompts: any suspicious IPs/domains from non-✅ findings not already covered by a skill prompt
   • Deduplicate: if a skill prompt and IOC prompt target the same entity, keep only the skill prompt
   • 🔴 NEVER merge a skill prompt (🔍) with a query file prompt (📄) into a single option. They are different action types with different execution paths.
   • ⛔ Persist the pool. Write the final pool to /memories/session/threat-pulse-drilldowns.md using the exact template below. The format banner is mandatory — it makes the — delimiter contract visible to every iteration and every LLM that edits the file. This memory block is the single source of truth; conversation history is not.

   Memory File Template (write on first pool creation)

   # Threat Pulse Session — <YYYY-MM-DD>
   
   **Workspace:** <name> (<id>)
   **Lookback:** <7d|30d|90d>
   **Scan Start:** <YYYY-MM-DD HH:MM UTC>
   
   ## Active Prompt Pool
   
   <!-- FORMAT: `- <ICON> <action> <entity> — Q<N>: <finding> → <skill-or-query-file>` -->
   <!-- ` — ` (space-emdash-space) is the REQUIRED label/description split delimiter. -->
   <!-- One icon per line. Order = file position (no numbering). Do not edit this comment block. -->
   
   - 🔍 Investigate incident #<IncidentId> — Q1: <brief finding>, <N> alerts, <MITRE-ID> → incident-investigation
   - 🎯 Enrich and investigate IP <IP> — Q4: <N> spray attempts / <N> users → ioc-investigation
   ...
   
   ## Pulse Key Findings (quick reference)
   
   ...
   
   ## Completed Drill-Downs
   
   _(none yet)_
   

2. Call vscode_askQuestions using the Quick Pick Call Contract below. Apply identically on every iteration.

   Quick Pick Call Contract

   • header: Follow-Up Investigation
   • question: Select one or more actions to launch (or skip):
   • options: entity prompts (from memory), then 📋 (if truncated), then 💾 / 🔄 / Skip as the final three — in that order, every iteration. 🆕 prompts prepend to the entity portion only.
     1. 💾 Save full investigation report — Save the complete Threat Pulse session (scan + all drill-downs) as a markdown file
     2. 🔄 Refresh prompt pool — Rebuild the follow-up prompt list from existing pulse + drill-down findings (does NOT re-run the 12 pulse queries)
     3. Skip — No follow-up — investigation complete
   • Allowed Label icons: 🔍 📄 🎯 💾 🆕 🔄 📋. Verdict emoji (🔴🟠🟡🟢✅) are banned from Labels (render as �� in VS Code quick picks) but fine in Descriptions. Drop 💾 after report is saved; 🔄 and Skip always remain.

   🔴 Pre-Flight Checklist — run mechanically before EVERY vscode_askQuestions call

   □ 1. memory view → read `## Active Prompt Pool` just now (not earlier)
   □ 2. Count entity prompts (exclude 💾/🔄/📋/Skip) = N
   □ 3. Format integrity: every entity line starts with `- ` followed by exactly ONE icon. Any legacy `<N>.` prefix → migrate to `- ` first, re-read, then continue.
   □ 4. If N > 12: render top 12 (🆕 first, then memory order) + append `📋 Show full prompt pool (N items)`
   □ 5. For each rendered option: split memory line at FIRST ` — ` → label = text after `- ` up to delimiter, description = right, BYTE-FOR-BYTE (no paraphrasing; if something is missing, edit memory first then re-read)
   □ 6. Atomic check: each option Label has exactly ONE icon; Description has at most ONE `→ target`
   □ 7. `multiSelect: true` in call args
   □ 8. ZERO `recommended` keys anywhere in options[]
   □ 9. Tail = 💾 / 🔄 / Skip (or 📋 / 💾 / 🔄 / Skip if truncated)
   □ 10. Print the Pool Receipt line to chat BEFORE invoking the tool
   

   Pool Receipt (box 10) — one-liner printed to chat so contract violations are user-visible:

   📊 Pool: <N> total / rendering <R> (🆕×<a>, 🔍×<b>, 📄×<c>, 🎯×<d>) / truncated <✔|—> | multiSelect=true ✔ | recommended=0 ✔
   

   If user selects 📋: re-invoke with all entity prompts (drop 📋, keep 💾/🔄/Skip tail).

3. If user selects Skip (alone) or pool is empty: end skill execution. Ignore any freeform text if Skip is selected.

4. Freeform input routing — If user types freeform text instead of (or alongside) selecting options, route by matching intent to validated sources. Do NOT write ad-hoc KQL — find the right skill or query file first. Classified actions feed into step 7 alongside any selected options.

   1. Skill match — Check the request against copilot-instructions.md Available Skills trigger keywords. "Check vulnerabilities on that device" → exposure-investigation or computer-investigation. Route as 🔍 — the read_file gate in step 7 applies.
   2. Query file match — grep_search the request's key terms (table names, operations, attack types) against queries/**. "Check forwarding rules" → queries/email/email_threat_detection.md. Route as 📄.
   3. Contextual question — If answerable from data already in context (e.g., "is that IP in other alerts?"), answer directly. If a query is needed, loop back to sub-steps 1–2 to find the right source.
   4. No match — If no skill or query file covers the request, follow the KQL Pre-Flight Checklist from copilot-instructions.md (schema validation, table pitfalls, existing query search) before writing any KQL. Never skip the pre-flight for freeform requests.

5. 💾 Save full investigation report selected:

   • Read /memories/session/threat-pulse-drilldowns.md (critical after context compaction) and compile pulse dashboard + all drill-down findings into a single markdown file using the Report Template (file mode). Weave drill-down insights into the main report — do NOT just append raw output.
   • If no drill-downs were executed yet, omit the Drill-Down Investigation Results and Cross-Investigation Correlation sections with note: "No drill-down investigations were performed in this session."
   • Save to reports/threat-pulse/Threat_Pulse_YYYYMMDD_HHMMSS.md. Drop 💾 from subsequent pool iterations.

6. 🔄 Refresh prompt pool selected — prompt list ONLY, no KQL execution. Refresh rebuilds the follow-up list; it does NOT re-run Q1–Q12 and does NOT re-run drill-downs. Discard the current pool, rebuild by re-applying Query File Recommendations and the skill matching table against pulse findings + all drill-down findings in memory. Deduplicate against completed prompts. If selected alongside other actions, refresh FIRST, then present the new pool before executing the others.

7. One or more actions selected — execute sequentially. Build a todo list (one item per action). For each:

   • 🔍 Skill prompt: ⛔ read_file the child SKILL.md BEFORE writing ANY query → find Investigation shortcut → match TP Q# trigger → execute with entity substitution. Writing KQL without the prior read_file = schema hallucination. See 🔍 Skill Drill-Down Execution Rule.
   • 📄 Query file prompt: read the file and execute its queries verbatim with entity substitution. See 📄 Query File Execution Rule.
   • 🎯 IOC prompt: load ioc-investigation skill with the target indicator.

   After each drill-down, append a session-state entry to /memories/session/threat-pulse-drilldowns.md under ## Completed Drill-Downs:

   ### <N>. <Prompt Label> (<skill-name>, <YYYY-MM-DD HH:MM>)
   - **Entity:** <target entity>
   - **Trigger:** Q<N> — <original finding>
   - **Key Findings:** <1–8 bullets, evidence-cited>
   - **Risk Assessment:** <emoji> <level> — <1-line justification>
   - **Cross-References:** <overlaps with other drill-downs or pulse queries>
   - **Recommendations:** <top 1–3 actions>
   

   This survives context compaction and feeds the 💾 Save report.

   Before returning to step 2 — MANDATORY, in order:

   1. New Evidence Scan — review drill-down results for entities/TTPs not present in prior findings. Add 🆕 prompts only for meaningful leads (new attacker IP with high abuse, new critical CVE on exposed device, etc.). If nothing warrants follow-up, note: "No actionable new evidence."
   2. Manifest check — for each 🆕 item, consult .github/manifests/discovery-manifest.yaml (match by domains, mitre, or title). Only fall back to ad-hoc KQL if nothing matches.
   3. Reload → mutate → write back — memory view ## Active Prompt Pool → delete the completed bullet line(s) → prepend 🆕 prompts as new bullet lines (- <ICON> ...) → memory str_replace. Every entity line is a bullet — no ordinals, so adding/removing items never requires renumbering. Never reconstruct the pool from conversation history.
   4. Return to step 2. Never render the pool as a markdown table/list instead of calling vscode_askQuestions.

Atomic options — ONE action per option. Each option maps to ONE skill + ONE entity, OR ONE query file. When correlations link findings (e.g., Q3+Q9 same user), generate separate options, put the correlation in the Description. Bundling multiple actions/arrows in a single option is the #1 follow-up mistake.

✅ Correct: 🔍 Investigate user cameron@contoso.com / desc Q3+Q9: identity risk + inbox rule manipulation → user-investigation ❌ Wrong: 🔍 Investigate cameron ... → user-investigation, 📄 Hunt phishing → queries/email/...

📄 Query File Execution Rule

⛔ MANDATORY — applies to ALL 📄 query file prompt executions in Phase 4.

When executing a 📄 prompt, use the queries from the file verbatim with entity substitution. Do NOT rewrite queries against different tables than the file specifies.

1. Read the query file and check its Investigation shortcuts section at the top — match the (TP Q#) annotation to the triggering Threat Pulse query to identify the recommended query chain. Follow that chain for the hunt
2. Substitute entity values (hostnames, IPs, UPNs) and adjust ago(Nd) lookback if context-aware expansion applies
3. ⚠️ Hostname-safe substitution: Device names vary across tables (short hostname vs FQDN vs uppercase). NEVER use == for device/computer filters — use startswith (default, case-insensitive, matches both short name and FQDN), or in~ (multi-device). Override == in query file entity substitution notes with startswith.
4. Execute using the file's exact tables, columns, and filters
5. If supplementing with additional tables, execute the file's queries first, then add your own — clearly label which are from the file vs. supplementary

🔍 Skill Drill-Down Execution Rule

⛔ MANDATORY — applies to ALL 🔍 skill drill-down executions in Phase 4.

When executing a skill drill-down, load the child skill's SKILL.md and use its validated queries. Do NOT write ad-hoc queries from memory — schema hallucination (wrong column names, wrong table) is the #1 drill-down failure mode.

1. Load the child skill's SKILL.md
2. Match the trigger context (TP Q number) against the skill's Investigation shortcuts section to identify the relevant query chain
3. Execute the shortcut query chain — substitute only entity placeholders and date ranges. Do NOT add columns, change project/summarize by, or restructure. Column names vary across Device* tables; the SKILL.md queries already use the correct ones.
4. For quick triage: run only the shortcut chain. For deep investigation: run the full skill workflow

🎬 Take Action — Portal-Ready Remediation Blocks

⚠️ AI-generated content may be incorrect. Always review Take Action queries and portal links for accuracy before executing remediation actions.

After every non-✅ drill-down that surfaces actionable entities, append a 🎬 Take Action section with direct portal links (single entities) or Advanced Hunting queries (bulk entities). Ref: Take action on AH results

Every 🎬 Take Action heading in the output — this one and every subsequent one — MUST be immediately followed by the AI-content warning blockquote above.

Skip when: verdict is ✅/🔵, or the action was already taken (e.g., ZAP purged emails).

Single Entity vs Bulk Entity Decision Rule

The remediation format depends on how many entities need action.

⛔ PROHIBITED: Generating an AH query for a single entity when a direct portal link would suffice. AH Take Action is for bulk remediation — for a single entity, link directly to the portal page where the analyst can act.

ID sources (agent retrieves silently — never ask the user):

• User OID: Graph /v1.0/users/<UPN>?$select=id or IdentityInfo.AccountObjectId
• MDE DeviceId: DeviceInfo.DeviceId or GetDefenderMachine API
• SHA / NetworkMessageId / etc.: from the originating AH table

⛔ Never emit prompts like "Retrieve the DeviceId" — run the lookup and emit the finished link in the same turn.

Required Columns per Entity Type

Missing a required column silently disables the action menu. Always include these:

Template Queries

📧 Email — by NetworkMessageId: (no project — see Email row above)

EmailEvents
| where Timestamp > ago(7d)
| where NetworkMessageId in ("<id1>", "<id2>")

→ Take actions → Move to mailbox folder, Delete email (soft/hard), Submit to Microsoft, Initiate automated investigation

📧 Email — by compromised sender domain:

EmailEvents
| where Timestamp > ago(30d)
| where SenderFromDomain =~ "<domain>" and ThreatTypes has "Phish" and DeliveryAction == "Delivered"
| take 500

→ Take actions → Move to mailbox folder, Delete email (soft/hard), Submit to Microsoft, Initiate automated investigation

💻 Single Device — direct portal link: Link to the Defender XDR machine page. If DeviceId isn't in context, look it up yourself:

DeviceInfo | where DeviceName startswith '<name>' | summarize arg_max(Timestamp, *) by DeviceId | project DeviceId

Then emit: [<DeviceName>](https://security.microsoft.com/machines/v2/<DeviceId>?tid=<tenant_id>). Never fabricate URLs with ?DeviceName=, /machines?, or bare hostnames.

→ Machine page → Response actions → Isolate device, Collect investigation package, Run antivirus scan, Initiate investigation, Restrict app execution

💻 Bulk Devices (2+) — AH query:

DeviceInfo
| where Timestamp > ago(1d)
| where DeviceName in~ ("<device1>", "<device2>")
| summarize arg_max(Timestamp, *) by DeviceId
| project DeviceId, DeviceName, OSPlatform, MachineGroup

→ Take actions → Isolate device, Collect investigation package, Run antivirus scan, Initiate investigation, Restrict app execution

📁 File — by hash:

Source-aware table selection. SHA hashes appear across many tables (DeviceProcessEvents, DeviceImageLoadEvents, DeviceFileEvents, AlertEvidence). Use DeviceFileEvents as the default — it captures file writes and has the columns needed for Quarantine. If the hash was only observed via process execution (no separate file write event), substitute or union with DeviceProcessEvents. The Quarantine action requires DeviceId + SHA1/SHA256 regardless of source table.

File write events (default — DeviceFileEvents):

DeviceFileEvents
| where Timestamp > ago(7d)
| where SHA1 == "<hash>" or SHA256 == "<hash>"
| project DeviceId, DeviceName, SHA1, SHA256, FileName, FolderPath

Process execution events (when file write not captured — DeviceProcessEvents):

DeviceProcessEvents
| where Timestamp > ago(7d)
| where SHA1 == "<hash>" or SHA256 == "<hash>"
| project DeviceId, DeviceName, SHA1, SHA256, FileName, FolderPath, ProcessCommandLine

→ Take actions → Quarantine file

🔗 Bulk Indicators (2+ IPs/domains/hashes) — AH query for Add Indicator:

When blocking multiple IPs, domains, or hashes, provide an AH query that surfaces the values as clickable columns. There is no Take actions dropdown — the analyst clicks each value directly in results → Add indicator.

Source-aware table selection. The table MUST match where the IPs were originally discovered. DeviceNetworkEvents is the default for network-layer IPs (endpoint connections, firewall events). However, IPs from authentication-layer sources (AADUserRiskEvents, EntraIdSignInEvents, SigninLogs, AADServicePrincipalSignInLogs) may never appear in endpoint network events — querying DeviceNetworkEvents for those returns 0 results. Use the originating table so the analyst sees the IPs in context and can click to add indicators.

Network-layer IPs (from DeviceNetworkEvents, DeviceLogonEvents, firewall logs):

// Surface attacker IPs as clickable values for Add Indicator
DeviceNetworkEvents
| where Timestamp > ago(7d)
| where RemoteIP in ("<ip1>", "<ip2>", "<ip3>")
| summarize Connections = count(), Ports = make_set(LocalPort) by RemoteIP
| order by Connections desc

Auth-layer IPs (from AADUserRiskEvents, EntraIdSignInEvents, SigninLogs):

// Surface attacker IPs from sign-in/risk events for Add Indicator
EntraIdSignInEvents
| where Timestamp > ago(30d)
| where IPAddress in ("<ip1>", "<ip2>", "<ip3>")
| summarize SignIns = count(), Users = dcount(AccountUpn), Countries = make_set(Country, 5) by IPAddress
| order by SignIns desc

→ Click any IP value in results → Add indicator → Block and remediate

Variant — domains/URLs:

DeviceNetworkEvents
| where Timestamp > ago(7d)
| where RemoteUrl has_any ("<domain1>", "<domain2>")
| summarize Connections = count() by RemoteUrl
| order by Connections desc

→ Click any RemoteUrl value → Add indicator → Block and remediate

Defender XDR Portal Links — All Entity Types

🔴 Every entity (user, domain, URL, IP, file hash) in action/recommendation tables MUST be a clickable Defender XDR portal link — the entity name IS the link. Do NOT add a separate "Portal" column or leave entities as plain text. VS Code renders bare UPNs as mailto: and bare URLs/IPs as broken links.

User fallbacks: ?upn=<UPN> when ObjectId is unavailable; ?sid=<SID>&accountName=<Name>&accountDomain=<Domain> for on-prem AD.

Device ID source: DeviceId from the DeviceInfo AH table or the id field from GetDefenderMachine API. This is the MDE machine identifier — NOT the Entra Device Object ID (which is different). The computer-investigation skill retrieves this in Step 1b.

🔴 Portal URL Allowlist — No Invented Paths. The 7 patterns above plus /v2/advanced-hunting?tid=<tenant_id> are the ONLY security.microsoft.com URLs you may emit. For any other action (Custom Indicators, Safe Links policy, Email Explorer, CA policy editor, Secure Score, etc.), write a textual breadcrumb — e.g., "Defender XDR → Settings → Endpoints → Indicators → URLs/Domains → Add item". Never guess a path from memory.

Entity Display — Portal Link vs Defang (Mutually Exclusive)

Defang rules: http:// → hxxp://, https:// → hxxps://, . in domain → [.]. VS Code auto-linkifies anything URL-shaped, which is why defanging is required in data tables. Conversely, a portal-linked entity has the portal URL as the link target, so linkification is safe \u2014 defanging would just break the link.

Rules Summary

Sample KQL Queries

All queries below are verified against live Sentinel/Defender XDR schemas. Use them exactly as written. Lookback periods use ago(Nd) — substitute the user's preferred lookback where noted.

Query 1: Open Incidents with Severity-Ranked Backfill & MITRE Techniques

🔴 Incident hygiene — Surfaces unresolved incidents prioritized by severity (Critical → High → Medium → Low), with age, owner, alert count, MITRE tactics, MITRE technique IDs, and extracted entity names (accounts + devices) for cross-query correlation. In large environments, all 10 slots fill with High/Critical. In smaller environments, Medium/Low backfill remaining slots automatically.

Tool: RunAdvancedHuntingQuery

let OpenIncidents = SecurityIncident
| where TimeGenerated > ago(30d)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| where Status in ("New", "Active");
let TotalHighCritical = toscalar(OpenIncidents | where Severity in ("High", "Critical") | count);
let TotalAll = toscalar(OpenIncidents | count);
OpenIncidents
| extend SevRank = case(Severity == "Critical", 0, Severity == "High", 1, Severity == "Medium", 2, Severity == "Low", 3, 4)
| extend ParsedLabels = parse_json(Labels)
| mv-apply Label = ParsedLabels on (
    summarize Tags = make_set(tostring(Label.labelName), 5)
)
| extend Tags = set_difference(Tags, dynamic([""]))
| mv-expand AlertId = AlertIds | extend AlertId = tostring(AlertId)
| join kind=leftouter (
    SecurityAlert
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, Entities, Tactics, Techniques, AlertName, AlertSeverity) by SystemAlertId
    | extend ParsedEntities = parse_json(Entities)
    | mv-expand Entity = ParsedEntities
    | extend EntityType = tostring(Entity.Type),
        AccountUPN = case(
            tostring(Entity.Type) == "account" and isnotempty(tostring(Entity.UPNSuffix)),
            tolower(strcat(tostring(Entity.Name), "@", tostring(Entity.UPNSuffix))),
            tostring(Entity.Type) == "account" and isnotempty(tostring(Entity.AadUserId)),
            tostring(Entity.AadUserId),
            ""),
        HostName = iff(tostring(Entity.Type) == "host", tolower(tostring(Entity.HostName)), "")
    | project SystemAlertId, Tactics, Techniques, AlertName, AlertSeverity, AccountUPN, HostName
) on $left.AlertId == $right.SystemAlertId
| mv-expand Technique = parse_json(Techniques)
| extend Technique = tostring(Technique)
| extend TacticsSplit = split(Tactics, ", ")
| mv-expand Tactic = TacticsSplit
| extend Tactic = tostring(Tactic)
| summarize 
    Tactics = make_set(Tactic),
    Techniques = make_set(Technique),
    AlertNames = make_set(AlertName, 5),
    AlertCount = dcount(AlertId),
    Accounts = make_set(AccountUPN, 5),
    Devices = make_set(HostName, 5),
    Tags = take_any(Tags)
    by ProviderIncidentId, Title, Severity, SevRank, Status, CreatedTime,
       OwnerUPN = tostring(Owner.userPrincipalName)
| extend Techniques = set_difference(Techniques, dynamic([""]))
| extend Tactics = set_difference(Tactics, dynamic([""]))
| extend Accounts = set_difference(Accounts, dynamic([""]))
| extend Devices = set_difference(Devices, dynamic([""]))
| extend AgeDisplay = case(
    datetime_diff('minute', now(), CreatedTime) < 60, strcat(datetime_diff('minute', now(), CreatedTime), "m ago"),
    datetime_diff('hour', now(), CreatedTime) < 24, strcat(datetime_diff('hour', now(), CreatedTime), "h ago"),
    strcat(datetime_diff('day', now(), CreatedTime), "d ago"))
| extend PortalUrl = strcat("https://security.microsoft.com/incidents/", ProviderIncidentId, "?tid=<TENANT_ID>")
| extend TotalHighCritical = TotalHighCritical, TotalAll = TotalAll
| project TotalHighCritical, TotalAll, ProviderIncidentId, Title, Severity, SevRank, AgeDisplay, AlertCount, 
    OwnerUPN, Tactics, Techniques, Accounts, Devices, Tags, PortalUrl, AlertNames, CreatedTime
// --- Deduplicate by Title: keep one representative incident per title for variety ---
| as AllOpenIncidents
| join kind=leftouter (
    AllOpenIncidents | summarize TitleDupCount = count() by Title
) on Title
| project-away Title1
| order by Title asc, SevRank asc, bin(CreatedTime, 1d) desc, AlertCount desc
| extend _rn = row_number(1, prev(Title) != Title)
| where _rn == 1
| project-away _rn
| order by SevRank asc, bin(CreatedTime, 1d) desc, AlertCount desc
| take 10

Purpose: Top 10 open incidents with severity-ranked backfill (Critical→High→Medium→Low). In large envs, all slots fill with High/Critical; small envs backfill with Medium/Low. TotalHighCritical and TotalAll drive the adaptive report header ("Showing 10 of {TotalAll} open incidents ({TotalHighCritical} High/Critical)") and are computed across all open incidents pre-dedup, so header counts stay accurate. The list is deduplicated by Title so the top 10 shows distinct incident types rather than near-identical rows — in noisy envs a single recurring title (password-spray, DLP rule) can otherwise monopolize all 10 slots; the single highest-priority representative per title is kept (severity → newest day → alert count) and TitleDupCount preserves the volume signal. Joins SecurityAlert for MITRE tactics/techniques and extracts Accounts (UPN or AAD ObjectId, lowercased), Devices (hostname, lowercased), and Tags (from Labels — both AutoAssigned ML classifications and User-applied SOC tags) — each capped at 5 per incident — for cross-query correlation with Q3/Q4/Q6/Q7/Q12. Flags unassigned incidents (empty OwnerUPN).

Sort: SevRank asc, bin(CreatedTime, 1d) desc, AlertCount desc — severity tier first, then calendar day (newest first), then complexity within each day.

Verdict logic:

• 🔴 Escalate: 5+ new High/Critical in 24h, OR any incident with AlertCount > 50, OR any unassigned High/Critical with CredentialAccess/LateralMovement tactics
• 🟠 Investigate: Any unassigned High/Critical, OR AlertCount > 10, OR multiple High/Critical in <6h
• 🟡 Monitor: Only Medium/Low incidents exist (no High/Critical), OR High/Critical assigned with low alert count
• ✅ Clear: 0 open incidents of any severity (Q2 closed summary still renders as context)

Query 2: Closed Incident Summary (7-Day Lookback)

🔴 Threat landscape context — Even when all incidents are resolved, the classification breakdown, MITRE tactic distribution, and severity mix from recent closures provide actionable signals for cross-correlation and query file recommendations.

Tool: RunAdvancedHuntingQuery

Always runs in parallel with Q1 — not conditional on Q1 results.

SecurityIncident
| where CreatedTime > ago(7d)
| summarize arg_max(TimeGenerated, *) by IncidentNumber
| where Status == "Closed"
| where array_length(AlertIds) > 0
| mv-expand AlertId = AlertIds | extend AlertId = tostring(AlertId)
| join kind=leftouter (
    SecurityAlert
    | where TimeGenerated > ago(30d)
    | summarize arg_max(TimeGenerated, Tactics, Techniques) by SystemAlertId
    | project SystemAlertId, Tactics, Techniques
) on $left.AlertId == $right.SystemAlertId
| mv-expand Technique = parse_json(Techniques)
| extend Technique = tostring(Technique)
| extend TacticsSplit = split(Tactics, ", ")
| mv-expand Tactic = TacticsSplit
| extend Tactic = tostring(Tactic)
| summarize
    Total = dcount(IncidentNumber),
    TruePositive = dcountif(IncidentNumber, Classification == "TruePositive"),
    BenignPositive = dcountif(IncidentNumber, Classification == "BenignPositive"),
    FalsePositive = dcountif(IncidentNumber, Classification == "FalsePositive"),
    Undetermined = dcountif(IncidentNumber, Classification == "Undetermined"),
    HighCritical = dcountif(IncidentNumber, Severity in ("High", "Critical")),
    MediumLow = dcountif(IncidentNumber, Severity in ("Medium", "Low")),
    Tactics = make_set(Tactic),
    Techniques = make_set(Technique)
| extend Techniques = set_difference(Techniques, dynamic([""]))
| extend Tactics = set_difference(Tactics, dynamic([""]))

Purpose: Provides a 7-day closed incident summary with classification breakdown (TP/BP/FP/Undetermined), severity distribution, aggregated MITRE tactics, and aggregated MITRE technique IDs. Uses CreatedTime (not TimeGenerated) to match portal "created in last 7 days" semantics — TimeGenerated captures any incident updated in the window, inflating counts with old incidents. Filters array_length(AlertIds) > 0 to exclude phantom incidents — the SecurityIncident table contains hundreds of records synced from XDR with empty AlertIds that never surface in the Defender XDR portal queue (see copilot-instructions.md Known Table Pitfalls). This data feeds three downstream uses:

1. TP rate signal — High TruePositive ratio indicates an active threat environment
2. MITRE tactic context — Tactics from closed TPs identify the current threat landscape for cross-correlation with Q3/Q7/Q8 findings
3. Manifest MITRE matching — The Techniques array contains ATT&CK technique IDs (e.g., T1566, T1078, T1059) directly matchable against manifest entry mitre fields. No tactic→technique mapping needed — the technique IDs are the primary matching key for query file recommendations

Verdict logic:

• 🟠 Investigate: TruePositive / Total > 0.5 (majority of closures are real threats — active threat environment)
• 🟡 Monitor: Any TruePositive closures exist, or Undetermined > 0 (some incidents lack classification)
• ✅ Clear: 0 TruePositive closures; all closures are BenignPositive or FalsePositive
• 🔵 Informational: 0 closed incidents in 7d

Rendering rules:

• Always render Q2 results in the report, regardless of Q1 verdict
• In the Dashboard Summary, Q2 gets its own row. In Detailed Findings, render Q2 immediately after Q1 as a compact summary block
• Flatten the Tactics and Techniques arrays and report distinct values from TruePositive incidents
• The Techniques array feeds directly into the Query File Recommendations manifest MITRE matching (no tactic→technique translation needed)
• If 0 closed incidents in 7d, display: "No incidents closed in the last 7 days"

Query 3: Identity Risk Posture & Risk Event Enrichment

🔐 Identity risk posture — Hybrid two-signal query: IdentityInfo.RiskScore (Defender XDR composite, 0-100) captures alert-chain and MITRE-stage risk, while RiskLevel/RiskStatus (Identity Protection) captures sign-in anomalies and AI-driven signals. Uses both because they are independent engines — a user can have RiskScore=93 with Remediated IdP status, or RiskScore=0 with High/AtRisk IdP status. AADUserRiskEvents enriches with the specific detections explaining why they're flagged.

Tool: RunAdvancedHuntingQuery

let lookback = 7d;
// Layer 1: IdentityInfo — hybrid filter (Defender RiskScore + IdP RiskLevel/Status + Criticality)
let IdentityPosture = IdentityInfo
| where Timestamp > ago(lookback)
| summarize arg_max(Timestamp, *) by AccountUpn
| where RiskScore >= 71
    or RiskLevel in ("High", "Medium")
    or RiskStatus in ("AtRisk", "ConfirmedCompromised")
    or CriticalityLevel >= 3;
// Layer 2: AADUserRiskEvents — enrichment (the why)
let UserRiskEvents = AADUserRiskEvents
| where TimeGenerated > ago(lookback)
| extend Country = tostring(parse_json(Location).countryOrRegion)
| summarize
    RiskDetections = count(),
    HighCount = countif(RiskLevel == "high"),
    TopRiskEventTypes = make_set(RiskEventType, 8),
    TopCountries = make_set(Country, 5),
    LatestDetection = max(TimeGenerated)
    by UserPrincipalName;
// IdentityInfo drives, AADUserRiskEvents enriches
IdentityPosture
| join hint.strategy=broadcast kind=leftouter (UserRiskEvents) 
    on $left.AccountUpn == $right.UserPrincipalName
| extend 
    DisplayName = coalesce(AccountDisplayName, AccountName, AccountUpn),
    PortalUrl = strcat("https://security.microsoft.com/user?",
        case(
            isnotempty(AccountObjectId), strcat("aad=", AccountObjectId, "&upn=", AccountUpn),
            isnotempty(OnPremSid), strcat("sid=", OnPremSid, "&accountName=", AccountName,
                                         "&accountDomain=", AccountDomain),
            isnotempty(AccountUpn), strcat("upn=", AccountUpn),
            ""),
        "&tab=overview&tid=<TENANT_ID>")
| project DisplayName, PortalUrl, RiskScore, RiskLevel, RiskStatus, CriticalityLevel,
    RiskDetections = coalesce(RiskDetections, long(0)),
    HighCount = coalesce(HighCount, long(0)),
    TopRiskEventTypes, TopCountries, LatestDetection
| order by RiskScore desc, HighCount desc, RiskDetections desc, CriticalityLevel desc
| take 15

Purpose: RiskScore (int, 0-100) is the Defender XDR composite score on IdentityInfo — factors include alert chains, MITRE stage progression, and asset criticality. Portal thresholds: 71-90 = High, 91-100 = Critical. RiskLevel/RiskStatus are Identity Protection signals (sign-in anomalies, leaked creds, AI signals) — a separate engine that doesn't always agree with RiskScore. The hybrid OR filter ensures users flagged by either engine surface. Users with both signals firing are highest priority (corroborated).

Output columns: DisplayName (linked to Defender XDR Identity page via PortalUrl), RiskScore (0-100, primary sort), RiskLevel, RiskStatus, CriticalityLevel, RiskDetections (count), HighCount, TopRiskEventTypes, TopCountries, LatestDetection.

Portal URL resolution: Three-tier fallback for identity environment coverage:

• Cloud/Hybrid (has Entra ObjectId): aad=<ObjectId>&upn=<UPN>
• On-prem AD (SID only, no Entra sync): sid=<SID>&accountName=<Name>&accountDomain=<Domain>
• External IdP (UPN only, e.g., CyberArk/Okta): upn=<UPN>

Report rendering: Show top 10 users in the dashboard table. Use DisplayName as clickable link text with PortalUrl as the target. If >10 results, note "+N more — drill down with user-investigation skill". For each user, render RiskScore and TopRiskEventTypes as the key risk indicators.

Verdict logic:

• 🔴 Escalate: Any user with RiskScore >= 91, or ConfirmedCompromised status, or HighCount > 3, or multiple users with HighCount > 0
• 🟠 Investigate: RiskScore >= 71, or HighCount > 0 for any user, or any user AtRisk with risk events indicating aiCompoundAccountRisk, impossibleTravel, or maliciousIPAddress
• 🟡 Monitor: Only Medium risk users with low-severity risk event types (e.g., unfamiliarFeatures)
• ✅ Clear: 0 users matching the hybrid filter

⚠️ Risk Event Type Routing Guard (Phase 4 drill-down):

• suspiciousAuthAppApproval → T1621 MFA Fatigue (suspicious Authenticator push approval patterns), NOT OAuth app consent. Route to user-investigation or authentication-tracing. NEVER recommend app-registration-posture based on this risk event alone
• mcasSuspiciousInboxManipulationRules → T1114.003 email exfiltration via inbox rules. Route to user-investigation with OfficeActivity drill-down

Query 4: Password Spray / Brute-Force Detection

🔐 Auth spray detection (T1110.003 / T1110.001) — Identifies IPs targeting multiple users with failed auth across Entra ID cloud sign-ins AND RDP/SSH/network logons on endpoints.

Tool: RunAdvancedHuntingQuery

// Step 1: Count spray-specific failures per IP (materialized — referenced twice)
let SprayFailures = materialize(EntraIdSignInEvents
| where Timestamp > ago(7d)
| where ErrorCode in (50126, 50053, 50057)
| summarize
    FailedAttempts = count(),
    TargetUsers = dcount(AccountUpn),
    SampleTargets = make_set(AccountUpn, 5),
    FailedApps = make_set(Application, 3),
    Countries = make_set(Country, 3)
    by SourceIP = IPAddress
| where TargetUsers >= 5);
// Step 2: Get full traffic profile for flagged IPs (success context)
let IPTrafficProfile = EntraIdSignInEvents
| where Timestamp > ago(7d)
| where IPAddress in ((SprayFailures | project SourceIP))
| summarize
    TotalSignIns = count(),
    Successes = countif(ErrorCode == 0),
    TotalDistinctUsers = dcount(AccountUpn),
    TotalDistinctApps = dcount(Application)
    by SourceIP = IPAddress;
// Step 3: Join and filter — eliminate shared infrastructure false positives
let EntraResults = SprayFailures
| join kind=inner IPTrafficProfile on SourceIP
| extend 
    SprayRatio = round(FailedAttempts * 100.0 / max_of(TotalSignIns, 1), 1),
    SuccessRate = round(Successes * 100.0 / max_of(TotalSignIns, 1), 1)
| where SprayRatio >= 1.0 and TotalDistinctApps < 50
| extend Surface = "Entra ID"
| project SourceIP, FailedAttempts, TargetUsers, SampleTargets, 
    Protocols = FailedApps, Countries, Surface,
    TotalSignIns, Successes, SprayRatio, SuccessRate, TotalDistinctApps;
// Endpoint brute-force — Surface label by LogonType
let EndpointBrute = DeviceLogonEvents
| where Timestamp > ago(7d)
| where ActionType == "LogonFailed"
| where LogonType in ("RemoteInteractive", "Network")
| where isnotempty(RemoteIP)
| summarize
    FailedAttempts = count(),
    TargetUsers = dcount(AccountName),
    SampleTargets = make_set(AccountName, 5),
    Protocols = make_set(strcat(LogonType, " → ", DeviceName), 3),
    Countries = dynamic(["—"]),
    LogonTypes = make_set(LogonType)
    by SourceIP = RemoteIP
| where FailedAttempts >= 10
| extend Surface = iff(array_length(LogonTypes) == 1 and LogonTypes[0] == "RemoteInteractive", "Endpoint (RDP)", "Endpoint (Network Logon)"),
    TotalSignIns = FailedAttempts, Successes = long(0), 
    SprayRatio = 100.0, SuccessRate = 0.0, TotalDistinctApps = long(0)
| project-away LogonTypes;
union EntraResults, EndpointBrute
| order by SprayRatio desc, TargetUsers desc, FailedAttempts desc
| take 15

Purpose: Detects password spray (1 IP → many users, MITRE T1110.003) and brute-force (1 IP → high failure count, T1110.001) across two surfaces, with shared infrastructure false-positive filtering:

• Entra ID: Uses EntraIdSignInEvents (Advanced Hunting) which merges interactive + non-interactive sign-ins into a single table. Error codes: 50126=bad password, 50053=locked account, 50057=disabled account. The query enriches failure data with the IP's full traffic profile to compute SprayRatio (spray failures ÷ total sign-ins) and TotalDistinctApps. Two filters eliminate corporate proxies, VPN concentrators, and Azure gateways:
  • SprayRatio >= 1.0 — spray failures must be ≥1% of the IP's total sign-in volume. A proxy with 500K sign-ins and 77 spray errors → 0.01% → filtered. A pure attacker with 77 failures and 0 successes → 100% → kept.
  • TotalDistinctApps < 50 — IPs serving 50+ distinct applications are shared infrastructure. Real spray targets 1–3 apps.
• Endpoint: RDP (RemoteInteractive) and Network Logon (Network) failed logons on MDE-enrolled devices. Surface labels: Endpoint (RDP) for pure RemoteInteractive, Endpoint (Network Logon) for anything involving Network logon type. NLA caveat: RDP with Network Level Authentication generates LogonType == "Network" (not RemoteInteractive), so Endpoint (Network Logon) may be RDP-via-NLA or SMB — the manifest surfaces both rdp_threat_detection.md and smb_threat_detection.md for drill-down. Threshold of ≥10 failures. No success context available in DeviceLogonEvents for filtering.

Output columns: SourceIP, FailedAttempts, TargetUsers, SampleTargets, Protocols, Countries, Surface, TotalSignIns, Successes, SprayRatio, SuccessRate, TotalDistinctApps. The SprayRatio and TotalDistinctApps columns provide immediate false-positive triage context.

Verdict logic:

• 🔴 Escalate: Any IP targeting >25 Entra users OR >100 endpoint failures from a single IP
• 🟠 Investigate: Any spray/brute-force pattern detected (meets thresholds)
• 🟡 Monitor: Spray activity detected but below thresholds (e.g., single IP with 3–4 target users, or <10 endpoint failures)
• ✅ Clear: 0 results — no spray/brute-force patterns detected

Drill-down: Use user-investigation skill for targeted users, ioc-investigation for source IPs.

Query 5: SPN Behavioral Drift (90d Baseline vs 7d Recent)

🤖 Automation monitoring — Composite drift score across 5 dimensions for service principals, with IPv6 subnet normalization and IPDrift cap.

Tool: mcp_sentinel-data_query_lake (needs >30d lookback)

let BL_Start = ago(97d); let BL_End = ago(7d);
let RC_Start = ago(7d); let RC_End = now();
let BL = AADServicePrincipalSignInLogs
| where TimeGenerated between (BL_Start .. BL_End)
| extend NormalizedIP = case(
    IPAddress has ":", strcat_array(array_slice(split(IPAddress, ":"), 0, 3), ":"),
    IPAddress)
| summarize 
    BL_Vol = count(),
    BL_Res = dcount(ResourceDisplayName),
    BL_IPs = dcount(NormalizedIP),
    BL_Loc = dcount(Location),
    BL_Fail = dcountif(ResultType, ResultType != "0" and ResultType != 0)
    by ServicePrincipalId, ServicePrincipalName;
let RC = AADServicePrincipalSignInLogs
| where TimeGenerated between (RC_Start .. RC_End)
| extend NormalizedIP = case(
    IPAddress has ":", strcat_array(array_slice(split(IPAddress, ":"), 0, 3), ":"),
    IPAddress)
| summarize 
    RC_Vol = count(),
    RC_Res = dcount(ResourceDisplayName),
    RC_IPs = dcount(NormalizedIP),
    RC_Loc = dcount(Location),
    RC_Fail = dcountif(ResultType, ResultType != "0" and ResultType != 0)
    by ServicePrincipalId, ServicePrincipalName;
RC | join kind=inner BL on ServicePrincipalId
| extend 
    VolDrift = round(RC_Vol * 100.0 / max_of(BL_Vol, 10), 0),
    ResDrift = round(RC_Res * 100.0 / max_of(BL_Res, 3), 0),
    IPDriftRaw = round(RC_IPs * 100.0 / max_of(BL_IPs, 3), 0),
    IPDrift = min_of(round(RC_IPs * 100.0 / max_of(BL_IPs, 3), 0), 300),
    LocDrift = round(RC_Loc * 100.0 / max_of(BL_Loc, 2), 0),
    FailDrift = round(RC_Fail * 100.0 / max_of(BL_Fail, 5), 0)
| extend DriftScore = round((VolDrift*0.20 + ResDrift*0.25 + IPDrift*0.25 + LocDrift*0.15 + FailDrift*0.15), 0)
| where DriftScore > 120
| order by DriftScore desc
| take 10

Purpose: Identifies service principals with significant behavioral changes from their 90-day baseline.

Tuning notes:

• IPv6 /64 normalization: IPv6 addresses are collapsed to their /64 prefix before counting. Azure PaaS services (Copilot Studio, Playbook Automation) rotate through dozens of fd00: ULA pod addresses within the same cluster — without normalization, each pod IP inflates IPDrift by hundreds of percent.
• IPDrift cap (300%): IPDriftRaw shows the true ratio; IPDrift is capped to prevent IP-only spikes from dominating. Transparent when IPv4-only SPNs have genuine expansion.
• Weights: Volume 20%, Resources 25%, IPs 25%, Locations 15%, Failure Rate 15%.

Verdict logic:

• 🔴 Escalate: Any SPN with DriftScore > 250 or IPDriftRaw > 400%
• 🟠 Investigate: DriftScore > 150
• 🟡 Monitor: DriftScore 120–150
• ✅ Clear: No SPNs above threshold

Drill-down: Use scope-drift-detection/spn skill for full investigation of flagged SPNs.

Query 6: Fleet-Wide Device Process Drift

💻 Endpoint behavioral baseline — Per-device drift scores computed in-query (7d baseline vs 1d recent), with infrastructure noise filtering and VolDrift cap to prevent automation-driven false positives.

Tool: RunAdvancedHuntingQuery

let uptime = DeviceInfo
| where Timestamp > ago(7d)
| extend IsRecent = Timestamp >= ago(1d)
| summarize
    BaselineHours = dcountif(bin(Timestamp, 1h), not(IsRecent)),
    RecentHours   = dcountif(bin(Timestamp, 1h), IsRecent)
    by DeviceName;
DeviceProcessEvents
| where Timestamp > ago(7d)
| where not(
    InitiatingProcessFileName in ("gc_worker", "gc_linux_service", "dsc_host")
    or (InitiatingProcessFileName == "dash" and InitiatingProcessParentFileName in ("gc_worker", "gc_linux_service"))
  )
| extend IsRecent = Timestamp >= ago(1d), DayBucket = bin(Timestamp, 1d)
| summarize
    BL_Events = countif(not(IsRecent)),
    RC_Events = countif(IsRecent),
    BL_Procs = dcountif(FileName, not(IsRecent)),
    RC_Procs = dcountif(FileName, IsRecent),
    BL_Accts = dcountif(AccountName, not(IsRecent)),
    RC_Accts = dcountif(AccountName, IsRecent),
    BL_Chains = dcountif(strcat(InitiatingProcessFileName, "→", FileName), not(IsRecent)),
    RC_Chains = dcountif(strcat(InitiatingProcessFileName, "→", FileName), IsRecent),
    BL_Comps = dcountif(ProcessVersionInfoCompanyName, not(IsRecent)),
    RC_Comps = dcountif(ProcessVersionInfoCompanyName, IsRecent),
    BaselineDays = dcountif(DayBucket, not(IsRecent))
    by DeviceName
| where RC_Events > 0 and BL_Events > 0 and BaselineDays >= 4
| join kind=inner uptime on DeviceName
| where BaselineHours >= 48 and RecentHours >= 4
| extend
    VolDriftRaw = round(RC_Events * 600.0 / max_of(BL_Events, 1), 0),
    VolDrift = min_of(round(RC_Events * 600.0 / max_of(BL_Events, 1), 0), 300),
    ProcDrift = round(RC_Procs * 100.0 / max_of(BL_Procs, 1), 0),
    AcctDrift = round(RC_Accts * 100.0 / max_of(BL_Accts, 1), 0),
    ChainDrift = round(RC_Chains * 100.0 / max_of(BL_Chains, 1), 0),
    CompDrift = round(RC_Comps * 100.0 / max_of(BL_Comps, 1), 0)
| extend DriftScore = round(VolDrift * 0.30 + ProcDrift * 0.25 + ChainDrift * 0.20 + AcctDrift * 0.15 + CompDrift * 0.10, 0)
| order by DriftScore desc
| take 10
| project DeviceName, DriftScore, BaselineDays, BaselineHours, RecentHours, VolDriftRaw, VolDrift, ProcDrift, AcctDrift, ChainDrift, CompDrift

Purpose: Returns the top 10 devices ranked by composite drift score, pre-computed in KQL. No LLM-side math required — just interpret the returned scores.

Tuning notes:

• GC filter: Excludes Azure Guest Configuration noise (Linux only; <1% impact on Windows).
• Uptime + baseline-days gates: Filter intermittent endpoints whose offline baseline inflates VolDrift. Drop the DeviceInfo join if heartbeats aren't ingested.
• VolDrift cap (300%): VolDriftRaw preserves the true ratio. High VolDriftRaw with ~100 diversity metrics = infrastructure noise; both elevated = high-confidence anomaly.
• Weights: Volume 30%, Processes 25%, Chains 20%, Accounts 15%, Companies 10%.

Verdict logic: See Device Drift Score Interpretation in Post-Processing for the full scale, VolDrift cap context, and fleet-uniformity rule.

Query 7: Rare Process Chain Singletons

💻 Threat hunting — Parent→child process combinations appearing fewer than 3 times in 30 days.

Tool: RunAdvancedHuntingQuery

DeviceProcessEvents
| where Timestamp > ago(30d)
| summarize 
    Count = count(),
    UniqueDevices = dcount(DeviceName),
    SampleDevice = take_any(DeviceName),
    SampleUser = strcat(take_any(AccountDomain), "\\", take_any(AccountName)),
    SampleChildCmd = take_any(ProcessCommandLine),
    GrandparentProcess = take_any(InitiatingProcessParentFileName),
    LastSeen = max(Timestamp)
    by ParentProcess = InitiatingProcessFileName, ChildProcess = FileName
| where Count < 3
| order by Count asc, UniqueDevices asc
| take 20

Purpose: Surfaces the 20 rarest process chains — singletons and near-singletons within the 30-day AH window. Effective for spotting LOLBin abuse, malware execution, or novel attack tooling. Review SampleChildCmd for suspicious command-line patterns.

Verdict logic:

• 🟠 Investigate: Any singleton with suspicious parent (cmd.exe, powershell.exe, wscript.exe, mshta.exe, rundll32.exe) or child running from temp/user profile directories
• 🟡 Monitor: Rare chains from system/update processes (version-stamped binaries, Azure VM agents)
• ✅ Clear: All rare chains are explainable infrastructure artifacts

Query 8: Inbound Email Threat Snapshot

📧 Email posture — Single-row summary of inbound email volume, threat breakdown, and delivered threats.

Tool: RunAdvancedHuntingQuery

EmailEvents
| where Timestamp > ago(7d)
| where EmailDirection == "Inbound"
| summarize
    TotalInbound = count(),
    Clean = countif(isempty(ThreatTypes)),
    Phish = countif(ThreatTypes has "Phish"),
    Malware = countif(ThreatTypes has "Malware"),
    Spam = countif(ThreatTypes has "Spam"),
    HighConfPhish = countif(ConfidenceLevel has "High" and ThreatTypes has "Phish"),
    Blocked = countif(DeliveryAction == "Blocked"),
    Delivered = countif(DeliveryAction == "Delivered"),
    PhishDelivered = countif(ThreatTypes has "Phish" and DeliveryAction == "Delivered"),
    DistinctSenders = dcount(SenderFromAddress),
    DistinctRecipients = dcount(RecipientEmailAddress)

Purpose: Instant C-level email posture briefing. The key escalation metric is PhishDelivered — phishing emails that bypassed all protections and reached mailboxes.

Verdict logic:

• 🔴 Escalate: PhishDelivered > 5 or Malware > 0 delivered
• 🟠 Investigate: PhishDelivered > 0 (any phishing reached mailboxes)
• 🟡 Monitor: Phishing detected but 100% blocked/junked
• ✅ Clear: 0 phishing, 0 malware

Drill-down: Use email-threat-posture skill for full email security analysis including ZAP, Safe Links, and authentication breakdown.

Query 9: Cloud App Suspicious Activity

🔑 Cloud ops monitoring — Detects mailbox rule manipulation, transport rule changes, mailbox delegation, MCAS-flagged compromised sign-ins, and human-initiated Conditional Access policy changes via CloudAppEvents. Focuses on rule/permission/CA mutations — the lower-confidence signals not duplicated by Q1's incident roll-up.

Tool: RunAdvancedHuntingQuery

// Allow-list of Microsoft platform service principals that perform automated mailbox/CA lifecycle ops.
// These appear with empty AccountDisplayName; the real actor name lives in RawEventData.UserId.
// Pattern: any RawEventData.UserId starting with "NT SERVICE\" is Microsoft datacenter automation
// (e.g., MSExchangeAdminApiNetCore for tenant-onboarding/permission hygiene). Exclude from analyst
// view to avoid false-positive "empty actor" alarms.
let PlatformServicePrefix = @"NT SERVICE\";
CloudAppEvents
| where Timestamp > ago(7d)
| where ActionType in (
    // Exchange — Mail flow manipulation
    "New-InboxRule", "Set-InboxRule", "Set-Mailbox",
    "Add-MailboxPermission", "New-TransportRule", "Set-TransportRule", "New-Mailbox",
    // Exchange — Anti-forensic
    "Remove-MailboxPermission", "Remove-InboxRule",
    // Conditional Access manipulation (human-initiated only)
    "Set-ConditionalAccessPolicy", "New-ConditionalAccessPolicy",
    // Compromise signals
    "CompromisedSignIn"
)
// Resolve effective actor: AccountDisplayName when present, else RawEventData.UserId
| extend RawUserId = tostring(parse_json(tostring(RawEventData)).UserId)
| extend EffectiveActor = iff(isnotempty(AccountDisplayName), AccountDisplayName, RawUserId)
// Exclude Microsoft platform service principals (datacenter automation noise)
| where not(EffectiveActor startswith PlatformServicePrefix)
// Filter out system/automation-driven CA changes (CA agent, backup policies)
| where not(ActionType in ("Set-ConditionalAccessPolicy", "New-ConditionalAccessPolicy")
            and isempty(EffectiveActor))
| extend Category = case(
    ActionType in ("New-InboxRule", "Set-InboxRule", "Remove-InboxRule",
                   "Set-Mailbox", "Add-MailboxPermission", "Remove-MailboxPermission",
                   "New-TransportRule", "Set-TransportRule", "New-Mailbox"),
    "Exchange Admin/Rule Change",
    ActionType in ("Set-ConditionalAccessPolicy", "New-ConditionalAccessPolicy"),
    "Conditional Access Change",
    ActionType == "CompromisedSignIn",
    "Compromised Sign-In",
    "Other")
| summarize
    Count = count(),
    UniqueActors = dcount(EffectiveActor),
    TopActors = make_set(EffectiveActor, 5),
    Actions = make_set(ActionType, 5),
    LatestTime = max(Timestamp)
    by Category
| order by Count desc

Purpose: Three-category view of cloud app activity invisible to Q10 (AuditLogs). CompromisedSignIn is an MCAS signal independent from Q3's Identity Protection risk events — dual-source corroboration when both fire. CA changes with empty AccountDisplayName are system/agent-driven and filtered out. Inbox rule, transport rule, and mailbox permission changes are the primary BEC persistence/exfil mechanisms — even when no rule has a forwarding payload, rule creation by a previously-flagged user is a strong follow-up signal.

Actor resolution: AccountDisplayName is often empty for non-interactive ops; the query falls back to RawEventData.UserId. Actors prefixed NT SERVICE\ are Microsoft datacenter automation (e.g., MSExchangeAdminApiNetCore) and are excluded.

Verdict logic:

• 🔴 Escalate: Compromised Sign-In with 5+ users, OR Conditional Access Change by any human actor, OR Exchange Admin/Rule Change with forwarding-related rules (New-InboxRule, Set-InboxRule, New-TransportRule)
• 🟠 Investigate: Compromised Sign-In (any count), OR Remove-InboxRule / Remove-MailboxPermission (anti-forensic cleanup signals)
• 🟡 Monitor: Low-count Set-Mailbox from system actors
• ✅ Clear: 0 results across all categories

Drill-down: Use user-investigation for actors in Compromised Sign-In category. Use ca-policy-investigation for Conditional Access Change. For any Exchange-related Q9 finding, also query OfficeActivity | where OfficeWorkload == "Exchange" — CloudAppEvents only surfaces ActionType summaries; OfficeActivity carries the full Parameters JSON (ForwardTo / RedirectTo / ForwardingSmtpAddress), per-operation ClientIP, and ops like MoveToDeletedItems / SoftDelete / HardDelete / MailboxLogin that reveal post-compromise forensics. See queries/email/email_threat_detection.md and the CloudAppEvents / OfficeActivity entries in copilot-instructions.md Known Table Pitfalls.

Query 10: High-Impact Privileged Operations

🔑 Admin activity monitoring — Category-aggregated view of privileged operations: role assignments, PIM activations, credential lifecycle, consent grants, CA policy changes, password management, MFA registration, app registration, and ownership grants.

Tool: RunAdvancedHuntingQuery

let PrivOps = AuditLogs
| where TimeGenerated > ago(7d)
| where OperationName has_any (
    "role", "credential", "consent", "Conditional Access", "password", "certificate",
    "security info", "owner", "application"
)
| where Result == "success"
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
// Exclude system-driven CA policy additions (empty actor = CA agent)
| where not(OperationName has "conditional access" and isempty(Actor))
| extend Target = tostring(TargetResources[0].displayName)
| extend Category = case(
    OperationName has "security info", "MFA-Registration",
    OperationName has "owner", "Ownership",
    OperationName has "application", "AppRegistration",
    OperationName has "role", "RoleManagement",
    OperationName has "credential" or OperationName has "certificate", "Credentials",
    OperationName has "consent", "Consent",
    OperationName has "Conditional Access", "ConditionalAccess",
    OperationName has "password", "Password",
    "Other");
PrivOps
| summarize 
    Count = count(),
    UniqueActors = dcount(Actor),
    TopActors = make_set(Actor, 5),
    Operations = make_set(OperationName, 5),
    Targets = make_set(Target, 5),
    LatestTime = max(TimeGenerated)
    by Category
| order by Count desc

Purpose: Category-level aggregation ensures all 8 privilege domains surface regardless of volume distribution (previous per-actor aggregation was truncated at 15 rows, hiding MFA-Registration, Ownership, and AppRegistration). Key non-obvious details: MFA-Registration deletion + re-registration by same user = credential takeover (T1556.006). Ownership grants to external accounts = persistence (T1098). System-driven CA additions (empty Actor) are filtered out. Password category is high-volume by nature — flag single-actor bulk resets, not self-service.

Verdict logic:

• 🔴 Escalate: MFA-Registration deletions + registrations for same user (method swap attack), OR Consent grants from unexpected actors, OR Ownership grants to external accounts, OR ConditionalAccess changes by non-admin actors, OR AppRegistration with secrets management from external domains
• 🟠 Investigate: MFA-Registration from CTF/external accounts, OR RoleManagement targeting Global Admin / Security Admin roles, OR AppRegistration consent operations, OR Password with bulk admin resets (single actor, 10+ targets)
• 🟡 Monitor: Normal PIM activations and expirations, self-service password resets, credential lifecycle (WHfB/passkey registration)
• ✅ Clear: 0 results or only system-driven operations with expected volume

Query 11: Critical Assets with Verified Internet Exposure

🛡️ Attack surface — Combines ExposureGraph critical asset inventory with MDE's authoritative DeviceInfo.IsInternetFacing classification to identify verified internet-exposed critical assets.

Tool: RunAdvancedHuntingQuery

let InternetFacing = DeviceInfo
    | where Timestamp > ago(7d)
    | where IsInternetFacing == true
    | summarize arg_max(Timestamp, *) by DeviceId
    | project DeviceName,
        Reason = extractjson("$.InternetFacingReason", AdditionalFields, typeof(string)),
        PublicIP = extractjson("$.InternetFacingPublicScannedIp", AdditionalFields, typeof(string)),
        ExposedPort = extractjson("$.InternetFacingLocalPort", AdditionalFields, typeof(int));
let CriticalAssets = ExposureGraphNodes
    | where set_has_element(Categories, "device")
    | where isnotnull(NodeProperties.rawData.criticalityLevel)
    | extend critLevel = toint(NodeProperties.rawData.criticalityLevel.criticalityLevel)
    | where critLevel < 4
    | project DeviceName = NodeName, CriticalityLevel = critLevel,
        ExposureScore = tostring(NodeProperties.rawData.exposureScore);
CriticalAssets
| join kind=leftouter InternetFacing on DeviceName
| extend IsVerifiedExposed = isnotempty(PublicIP) or isnotempty(Reason)
| project DeviceName, CriticalityLevel, IsVerifiedExposed,
    Reason, PublicIP, ExposedPort, ExposureScore
| order by IsVerifiedExposed desc, CriticalityLevel asc
| take 25

Purpose: Returns the critical asset inventory (criticality 0–3) enriched with MDE's authoritative internet-facing classification. DeviceInfo.IsInternetFacing is confirmed via Microsoft external scans or observed inbound connections and auto-expires after 48h — far more reliable than ExposureGraph properties like isCustomerFacing (business flag) or rawData.IsInternetFacing (not populated in many environments). See MS Docs and queries/network/internet_exposure_analysis.md Query 1 for the canonical reference.

IsVerifiedExposed logic: Checks BOTH PublicIP (populated for PublicScan — Microsoft external scanner) AND Reason (populated for ExternalNetworkConnection — observed inbound traffic). The original isnotempty(PublicIP) missed ExternalNetworkConnection exposures where MDE confirms inbound connections but doesn't populate the scanned public IP field.

Verdict logic:

• 🔴 Escalate: Any IsVerifiedExposed == true with CriticalityLevel == 0 (internet-facing domain controller/CA)
• 🟠 Investigate: Any IsVerifiedExposed == true (internet-facing critical asset)
• 🟡 Monitor: Critical assets exist but none verified internet-facing
• ✅ Clear: All critical assets properly segmented, no internet exposure

Query 12: Exploitable CVEs (CVSS ≥ 8.0) Across Fleet

🛡️ Vulnerability patch priority — Top exploitable critical CVEs with affected device count.

Tool: RunAdvancedHuntingQuery

DeviceTvmSoftwareVulnerabilities
| join kind=inner (
    DeviceTvmSoftwareVulnerabilitiesKB
    | where IsExploitAvailable == true
    | where CvssScore >= 8.0
) on CveId
| summarize 
    AffectedDevices = dcount(DeviceName),
    SampleDevices = make_set(DeviceName, 3),
    Software = make_set(SoftwareName, 3)
    by CveId, VulnerabilitySeverityLevel, CvssScore
| order by AffectedDevices desc, CvssScore desc
| take 15

Purpose: Instant "what should we patch today" list. Ranks exploitable CVEs by fleet impact (devices affected × CVSS severity). Focus on CVEs with public exploits affecting the most devices.

Verdict logic:

• 🔴 Escalate: Any CVE with CvssScore >= 9.0 AND AffectedDevices > 10
• 🟠 Investigate: CVE with CvssScore >= 8.0 AND AffectedDevices > 5
• 🟡 Monitor: Exploitable CVEs exist but affect < 5 devices
• ✅ Clear: No exploitable CVEs with CVSS ≥ 8.0 (unlikely but possible in small environments)

Drill-down: Use exposure-investigation skill for full vulnerability posture assessment.

Post-Processing

Device Drift Score Interpretation (Q6)

Q6 returns pre-computed drift scores directly from KQL — no LLM-side math is needed. Simply present the returned table and apply verdicts using this scale:

VolDrift cap context: VolDriftRaw is projected alongside the capped VolDrift. When interpreting results:

• If VolDriftRaw ≫ 300 but ProcDrift/ChainDrift/AcctDrift are near 100: infrastructure volume spike (GC, patching, agent restart) — low concern despite high raw volume.
• If VolDriftRaw > 300 AND ProcDrift/ChainDrift/AcctDrift are also elevated: genuine multi-dimensional anomaly — high confidence finding.
• If VolDriftRaw ≤ 300: cap was not triggered — score reflects true proportions.

Fleet-uniformity rule: If ALL top-10 devices cluster within 20 points of each other, the fleet is behaving uniformly and the verdict should be downgraded one level. Drift is most meaningful when individual devices diverge from the fleet cluster.

⛔ DO NOT manually recompute drift scores. The KQL query handles Volume normalization (÷6 baseline days), VolDrift capping (at 300%), GC infrastructure filtering, and dcount comparison (direct ratio). Trust the returned DriftScore column.

Cross-Query Correlation

After all queries complete, check these correlation patterns and escalate priority when found:

Query File Recommendations

Use .github/manifests/discovery-manifest.yaml (auto-generated by python .github/manifests/build_manifest.py) to match findings to downstream query files and skills. Contains title, path, domains, mitre, prompt.

Skip entirely when all verdicts are ✅. Tier depth follows the Rule 8 table.

Query-to-Domain Map

Valid tags: incidents, identity, spn, endpoint, email, admin, cloud, exposure.

Procedure

For each non-✅ verdict, collect its domain tag(s), then:

1. Query files — filter manifest.queries where domains contains ANY active tag. Rank by (a) number of matching tags, (b) MITRE technique overlap with Q1/Q2 Techniques (exact string match on mitre field), (c) keyword overlap (entities, process names, CVE IDs, ActionTypes) against title/path. Select top 3–5 files for 🔴/🟠, 1–2 for 🟡-only.
2. Skills — filter manifest.skills where domains matches. Substitute actual entity values into the prompt template's {entity} placeholder. 🔴/🟠: include all matches as drill-down options; 🟡-only: limit to 3. Skills without domains (tooling/visualization) are never auto-suggested.

Report Output

Insert 📂 Recommended Query Files after 🎯 Recommended Actions. Include a 🔧 Suggested Skill Drill-Downs sub-section with manifest skill prompts (entity-substituted).

⛔ Numbered list, NOT table — links inside table cells don't render clickable in VS Code chat.

Format: 1. **[<Title>](queries/<subfolder>/<file>.md)** — Q<N>: <finding> — 💡 *"<entity-specific prompt>"*

• Link text = manifest title, target = manifest path (forward slashes).
• Prompts MUST reference specific entities/IOCs/TTPs from findings — no generic placeholders.
• When no matching files: suggest authoring new queries.

Adding New Query Files or Skills

1. Query files: add **Domains:** <tag1>, <tag2> to metadata header (after **MITRE:**).
2. Skills: add threat_pulse_domains: [<tag>] and drill_down_prompt: '<prompt>' to YAML frontmatter.
3. Run python .github/manifests/build_manifest.py — validator flags missing fields.

Report Template

Output modes:

• Inline chat (default) — render in chat. Truncate data tables to 10 rows; omit Drill-Down, Cross-Investigation, and Investigation Timeline sections when no drill-downs have executed.
• Markdown file — triggered by 💾 Save full investigation report in Phase 4. Full data tables, no row limits. Path: reports/threat-pulse/Threat_Pulse_YYYYMMDD_HHMMSS.md. Source data: pulse results from context + /memories/session/threat-pulse-drilldowns.md (authoritative after context compaction).

Verdicts: 🔴 Escalate | 🟠 Investigate | 🟡 Monitor | ✅ Clear | 🔵 Informational | ❓ No Data

• ❓ No Data — query returned table resolution error or timeout. Report the error and table. Treat as monitoring gap.
• 🔵 Informational — neutral context (e.g., Q2 with 0 closures, Q6 with DriftScore < 80). No action needed.
• Zero results format: ✅ No <type> detected in the last <N>d. Checked: <table> (0 matches)

Structure

# 🔍 Threat Pulse — <Workspace> | <Date>
**Workspace:** <name> (`<id>`)  
**Scan Date:** <YYYY-MM-DD HH:MM UTC>  
**Scan Duration:** <N>min | **Queries:** 12 | **Drill-Downs:** <N>  (file mode only)

## Executive Summary
<2–4 sentences synthesizing pulse + drill-down findings. State final risk posture incorporating all evidence.>

## Dashboard Summary
<12-row table (Q1, Q2, Q3, Q4–Q12) — columns: #, Domain, Status (verdict emoji), Key Finding (1-line).>

## Detailed Findings
<One section per query — EVERY query gets a section (no skipping). Q2 closed summary always renders after Q1 even when Q1 is ✅.>

## Cross-Query Correlations
<Table per Post-Processing rules, or `✅ No correlations detected`.>

## 🎯 Recommended Actions
<Prioritized table: action, trigger query, drill-down skill.>

## 📂 Recommended Query Files
<Per Report Output Block procedure. For 🟡-only verdicts use "📂 Proactive Hunting Suggestions" header. Omit when all ✅.>

## Drill-Down Investigation Results       (file mode, when drill-downs executed)
### 1. <Title> — <Skill Name>
**Triggered by:** Q<N> — <finding>  
**Entity:** <target> | **Lookback:** <timerange> | **Risk:** <emoji> <level>

**Key Findings:** <max 8 evidence-cited bullets>

**Evidence Summary:** <1–2 paragraph narrative with specific numbers/identifiers. Back-reference pulse queries.>

**Recommendations:** <numbered actions>

### 2. <Next Title> — <Skill Name>
...

## Cross-Investigation Correlation        (file mode, when drill-downs executed)
| Connection | Evidence | Drill-Downs | Implication |
|-----------|----------|-------------|-------------|
<Patterns only visible across multiple investigations. If none: `✅ No cross-investigation correlations identified — each finding is independent.`>

## Consolidated Recommendations           (file mode)
| Priority | Recommendation | Source | Risk |
<Deduplicated across pulse + drill-downs. If same action appears in both, cite both sources on one row.>

## Appendix: Investigation Timeline       (file mode)
| Time | Action | Key Result |

Column / Format Rules

• Q1: | Incident | Sev | Title | Age | Alerts | Owner | Tactics | Accounts | Devices | Tags | — Sev = incident severity, Unassigned → ⚠️ Unassigned, Age uses relative AgeDisplay, entity/tag columns render max 5 comma-separated.
  • When TotalAll > 10: prepend **Showing 10 of {TotalAll} open incidents ({TotalHighCritical} High/Critical)** (sorted by severity, then newest, most complex first)
  • The list is deduplicated by Title (one representative per title). When an incident's TitleDupCount > 1, append (+{TitleDupCount-1} more) to its Title cell so recurring/noisy incident types remain visible without monopolizing the table.
  • When TotalHighCritical == 0: prepend **No High/Critical incidents — showing top Medium/Low from {TotalAll} open**
• Q1 incidents must include [#<id>](https://security.microsoft.com/incidents/<ProviderIncidentId>?tid=<tenant_id>) links.
• Q2: Classification breakdown + severity + MITRE tactics/techniques from TP closures. Always render even when Q1 is ✅.

Rules

Known Pitfalls

Schema pitfalls (column names, dynamic fields, parse_json patterns) are covered in copilot-instructions.md Known Table Pitfalls. Refer there for SecurityAlert.Status, ExposureGraphNodes.NodeProperties, timestamp columns, and AuditLogs.InitiatedBy.

Quality Checklist

• All 12 queries executed
• Every query has a verdict row — no omissions, no skipped "clear" sections
• ✅ verdicts cite table + "0 results"; 🔴/🟠 cite specific evidence
• All incidents have clickable XDR portal URLs
• Cross-query correlations checked
• Every non-✅ drill-down has a 🎬 Take Action block with portal-ready KQL (correct required columns per entity type)
• Every 🎬 Take Action block includes the ⚠️ AI-generated content warning immediately below the heading
• 📂 Recommended Query Files section present when any non-✅ verdict exists (clickable links, not tables)
• No fabricated data

SVG Dashboard Generation

After completing the Threat Pulse report, the user may request an SVG visualization. Use the svg-dashboard skill in manifest mode — the widget manifest is at .github/skills/threat-pulse/svg-widgets.yaml.

Execution

1. Read svg-widgets.yaml (widget manifest)
2. Read the svg-dashboard SKILL.md for component rendering rules
3. Map manifest field values to the Threat Pulse report data already in context (or read the saved report file)
4. Render SVG → save to temp/threat_pulse_{date}_dashboard.svg

AI Agent Security Posture — Instructions

Purpose

This skill audits the security posture of AI agents (Copilot Studio, Microsoft 365 Copilot / Agent Builder, Microsoft Foundry, and third-party platforms) across your organization using the AgentsInfo table in Microsoft Defender XDR Advanced Hunting.

🔄 Table migration (AIAgentsInfo → AgentsInfo): This skill was migrated from the deprecated AIAgentsInfo table to the unified multi-platform AgentsInfo table. AIAgentsInfo remains queryable until July 1, 2026, but it is Copilot Studio-only and uses a different schema. All queries in this skill target AgentsInfo. The new table is a different data model, not a rename — see Table Schema Reference and Known Pitfalls for the differences that shaped these queries.

AI agents are autonomous or semi-autonomous applications that can access organizational data, send emails, call external APIs, and use MCP tools. Misconfigured agents — missing authentication, overly broad access, AI-controlled email sending, hard-coded credentials — represent a growing attack surface. This skill systematically evaluates that surface.

What this skill covers:

Data source: AgentsInfo table (Advanced Hunting) — currently in Preview.

References:

• Microsoft Docs — AgentsInfo table
• From runtime risk to real-time defense: Securing AI agents — Microsoft Defender Security Research blog detailing three attack scenarios this skill detects
• Microsoft Agent 365: The control plane for AI agents — Enterprise governance platform for agent lifecycle management (Registry, Access Control, Visualization, Interoperability, Security)
• Securing Copilot Studio agents with Microsoft Defender
• Real-time agent protection during runtime (Preview)

🔴 URL Registry — Canonical Links for Report Generation

MANDATORY: When generating reports, copy URLs verbatim from this registry. NEVER construct, guess, or paraphrase a URL. If a URL is not in this registry, omit the hyperlink entirely and use plain text.

Usage in reports: When referencing attack scenarios, link to BLOG_RUNTIME_RISK. When referencing Agent 365 governance, link to BLOG_AGENT_365. When referencing runtime protection, link to DOCS_RUNTIME_PROTECTION.

Threat Landscape: Why AI Agent Posture Matters

Microsoft Defender Security Research has identified that AI agents represent a fundamentally new attack surface where the agent's capabilities are effectively equivalent to code execution. When a tool is invoked, it can read/write data, send emails, update records, or trigger workflows — and an attacker who can influence the agent's plan can indirectly cause the execution of unintended operations within the agent's capability sandbox.

The core risk: the agent's orchestrator depends on natural language input to determine which tools to use and how to use them. This creates exposure to prompt injection and reprogramming failures, where malicious prompts, embedded instructions, or crafted documents can manipulate the decision-making process.

This skill's queries map directly to three attack scenarios documented by Microsoft:

Attack Scenario 1: Malicious Instruction Injection via Event-Triggered Workflow

Attack Scenario 2: Prompt Injection via Shared Document → Email Exfiltration (XPIA)

Attack Scenario 3: Capability Reconnaissance on Unauthenticated Agent

⚠️ Authentication-type telemetry gap: The deprecated AIAgentsInfo table exposed UserAuthenticationType (None/Integrated/Custom), which let this skill directly flag unauthenticated agents. The new AgentsInfo table has no populated authentication-type column in current telemetry (ToolsAuthenticationType is empty). The closest available exposure signal is RawAgentInfo.allowForAllUsers == "true" (broadly accessible to all tenant users). This is a proxy, not an equivalent — it measures broad reach, not absence of authentication. Treat broadly-accessible agents as the highest-exposure cohort and recommend Entra-based access policies (Agent 365) to close the gap.

Mitigation: Defender Runtime Protection

Microsoft Defender provides webhook-based runtime inspection for Copilot Studio agents. Before every tool, topic, or knowledge action is executed, the generative orchestrator sends a webhook to Defender containing the planned invocation context. Defender analyzes intent and destination in real time and can allow or block the action before execution.

This is the primary runtime defense against all three scenarios above. When reviewing posture findings from this skill, always recommend enabling Defender Runtime Protection for agents flagged as high-risk. See Real-time agent protection during runtime.

Governance Framework: Microsoft Agent 365

Microsoft Agent 365 is the enterprise control plane for AI agents — the platform-level answer to the governance gaps this skill detects. It provides five capabilities that directly map to this skill's risk dimensions:

How to reference Agent 365 in reports: When this skill identifies governance gaps (sprawl, missing authentication, uncontrolled tool access), recommend Agent 365 as the strategic platform to address them. Specific mappings:

• Agent sprawl / no naming conventions → Agent 365 Registry + quarantine for unsanctioned agents
• Missing access controls / broadly-accessible agents → Agent 365 Access Control + Entra agent IDs + Policy Templates
• No visibility into agent-resource connections → Agent 365 Visualization dashboard
• Uncontrolled MCP/tool proliferation → Agent 365 Security + Defender posture management
• XPIA / data exfiltration risk → Agent 365 Security + Purview for real-time data leak prevention

📑 TABLE OF CONTENTS

1. Critical Workflow Rules — Mandatory rules
2. Table Schema Reference — AgentsInfo columns and data types
3. Agent Security Score Formula — Composite risk scoring
4. Execution Workflow — Phase-by-phase query plan
5. Sample KQL Queries — All queries (Q1–Q12)
6. Output Modes — Inline vs Markdown report
7. Inline Report Template — Chat-rendered format
8. Markdown File Report Template — Disk-saved format
9. Known Pitfalls — Schema quirks and edge cases
10. Quality Checklist — Pre-delivery validation
11. SVG Dashboard Generation — Visual dashboard from report

⚠️ CRITICAL WORKFLOW RULES - READ FIRST ⚠️

1. ALWAYS use RunAdvancedHuntingQuery — The AgentsInfo table is an Advanced Hunting table. It is NOT available in Sentinel Data Lake (query_lake). All queries in this skill MUST use RunAdvancedHuntingQuery.

2. ALWAYS deduplicate agents with arg_max — The table contains multiple records per agent (state snapshots over time). Every query that analyzes current agent state MUST use | summarize arg_max(Timestamp, *) by AgentId to get the latest record per agent. Note AgentId is a guid.

3. ALWAYS exclude deleted agents (unless specifically auditing deletions) — Add | where LifecycleStatus != "Deleted" after deduplication. LifecycleStatus is blank for active agents and only set to Deleted for removed ones, so this filter keeps active agents.

4. ASK the user for output format before generating the report:

   • Inline chat summary (quick review in chat)
   • Markdown file report (detailed, archived to reports/ai-agent-posture/)
   • Both (markdown + inline summary)

5. ⛔ MANDATORY: Evidence-based analysis only — Report ONLY what query results show. Use the explicit absence pattern (✅ No [finding] detected) when queries return 0 results. Never guess or assume.

6. 🔴 The rich agent detail lives in RawAgentInfo (dynamic), not in flat columns — Governance signals (creatorId, allowForAllUsers, appType, scope) and deep tool/connector detail (declarativeCopilotMetadata) are nested inside the RawAgentInfo dynamic column. The normalized columns (DeclaredTools, McpServers, DeclaredDataSources) are sparse and flat. Parse RawAgentInfo with mv-expand/dot-notation — never assume a flat column holds the value. See Known Pitfalls.

7. Run queries in parallel batches where possible — Phase 1 queries (Q1–Q3) are independent and can run in parallel. Phase 2 queries (Q4–Q9) are independent and can run in parallel. Phase 3 (Q10–Q12) can run in parallel.

8. Time tracking — Report elapsed time after each phase completion.

Table Schema Reference

The AgentsInfo table (Preview) contains configuration snapshots of AI agents across Copilot Studio, Microsoft 365 Copilot (Agent Builder), Microsoft Foundry, and third-party platforms. The schema below reflects the live table (which differs from the published docs in several places — column casing, types, and which columns are actually populated).

Top-level columns

⚠️ Columns that are EMPTY / unreliable in current telemetry

These columns exist but are not populated in observed data — do NOT build detections on them without first confirming population:

ToolsAuthenticationType (auth-type gap — see below), Availability, Endpoints, Triggers, Permissions, Model (mostly), and most of Owners/SharedWith.

🔴 Authentication-type gap: The deprecated AIAgentsInfo.UserAuthenticationType (None/Integrated/Custom) has no populated equivalent in AgentsInfo. There is no reliable way to flag "unauthenticated" agents from this table. Use RawAgentInfo.allowForAllUsers == "true" as a broad-exposure proxy (Q4) and document the gap.

RawAgentInfo nested keys (the rich data)

For Copilot Studio agents, RawAgentInfo is a marketplace/governance manifest. Key fields the queries below rely on:

DCM nesting (recovers deep tool, operation, and endpoint detail):

RawAgentInfo.declarativeCopilotMetadata[]
  .actions[]
    .apis[]            // .type = OpenApi | RemoteMCPServer | api_action
      .serverUrls[]    // populated for OpenApi + RemoteMCPServer (external hosts)
      .operations[]
        .operationId   // e.g., "Office 365 Outlook Send an email (V2)"

DCM siblings also carry instructions, llmModels (model), and sourceIds (incl. EnvironmentId, SourceAgentId).

Agent Security Score Formula

The Agent Security Score is a composite risk indicator that summarizes the security posture of an organization's AI agent fleet. Higher scores indicate greater risk.

Scoring Dimensions

$$ \text{AgentSecurityScore} = \sum_{i} \text{DimensionScore}_i $$

Each dimension contributes 0–20 points to a maximum of 100:

Interpretation Scale

The Tool & Endpoint Exposure dimension folds external-endpoint risk (Q9) into the MCP exposure signal: an insecure scheme, a non-standard port, or an external endpoint on a broadly-accessible agent each escalates this dimension to its High tier regardless of MCP count.

Supplementary Indicators (not summed into the /100 score)

Two indicators are reported alongside the composite score for added context. They are intentionally not added to the /100 total — they enrich interpretation and feed the dimensions above as evidence.

Execution Workflow

Phase 0: Prerequisites

1. Confirm RunAdvancedHuntingQuery is available (AgentsInfo is AH-only)
2. Ask user for output format (inline / markdown / both)

Phase 1: Inventory & Overview (Q1–Q3)

Run in parallel — no dependencies between queries.

Phase 2: Security Risk Analysis (Q4–Q9)

Run in parallel — no dependencies between queries.

Phase 3: Governance & Trends (Q10–Q12)

Run in parallel — no dependencies between queries.

Phase 4: Score Computation & Report Generation

1. Compute per-dimension scores from Phase 1–3 data
2. Sum dimension scores for composite Agent Security Score
3. Generate report in requested output mode
4. Report total elapsed time

Phase 5: Runtime Correlation (Optional)

AgentsInfo describes how agents are configured; it does not show whether they are actually used or what they do at runtime. To close that gap, correlate the flagged configuration set against the CopilotActivity table (all-surface AI activity log, available in Advanced Hunting).

When to run: After Phase 4, when the user wants to know which flagged agents are actually active, which are dormant, or whether a high-risk agent shows runtime behavior.

🔴 Use a SCOPED lookup, never a fleet-wide join. A leftouter/inner join of the full AgentsInfo fleet (~15k agents, heavy RawAgentInfo dynamic) against CopilotActivity (100k+ rows) times out the Advanced Hunting endpoint. Instead:

1. Phase 4 produces a small flagged-agent NAME list (broadly-accessible from Q4 + sensitive-op agents from Q13 — typically <50 names).
2. Filter CopilotActivity to that name set with where AgentName in (FlaggedNames) — light, no join. See Query 15.

Join-key pitfall: CopilotActivity.AgentId is a composite/prefixed string (e.g., T_<tenant>.<guid>, CopilotStudio.Declarative.T_….gpt.<guid>, or literals like AgentBuilder) — it does not equal the clean AgentsInfo.AgentId GUID, so ID-based joins return 0 matches. AgentName is the reliable correlation key. Also note most CopilotActivity rows have an empty AgentId/AgentName (general M365 Copilot usage, not declarative-agent-attributed), so runtime attribution is inherently low-coverage — absence from CopilotActivity does NOT prove an agent is dormant.

Two high-value correlations:

• Active-and-dangerous — a flagged agent (broadly accessible / XPIA-exposed / sensitive ops) that ALSO appears in CopilotActivity with real interactions → highest remediation priority (Query 15).
• Configured-but-dormant — a flagged agent absent from CopilotActivity over the window → lower urgency, candidate for decommissioning (caveat: attribution gaps above).

For deeper runtime reconstruction (data accessed, tools invoked, jailbreak detections), hand off to the dedicated query library queries/cloud/copilot_activity_investigation.md rather than duplicating queries here.

Keep this phase thin and scoped: the posture skill owns configuration assessment; copilot_activity_investigation.md owns runtime reconstruction. Reference, don't duplicate.

Sample KQL Queries

All queries below are validated against the live AgentsInfo table. Use them exactly as written, substituting only where noted. Because the rich agent detail lives in the RawAgentInfo dynamic column, several queries parse RawAgentInfo.declarativeCopilotMetadata (DCM). DCM is present only for the connector-sourced subset of agents — queries that depend on it carry a coverage caveat.

Query 1: Global Inventory Summary

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| extend CreatorId = tostring(RawAgentInfo.creatorId)
| summarize
    UniqueAgents = dcount(AgentId),
    EarliestRecord = min(Timestamp),
    LatestRecord = max(Timestamp),
    Published = countif(PublishedStatus == "Published"),
    Draft = countif(PublishedStatus == "Draft"),
    Deleted = countif(LifecycleStatus == "Deleted"),
    UniquePlatforms = dcount(Platform),
    UniqueCreators = dcount(CreatorId)

Note: UniqueCreators counts only agents with a populated RawAgentInfo.creatorId (the connector-sourced subset). It under-counts true creators; treat it as a lower bound.

Query 2: Status & Platform Breakdown

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| summarize AgentCount = count() by Platform, PublishedStatus
| order by AgentCount desc

⚠️ Authentication-type gap: The deprecated AIAgentsInfo table broke this down by UserAuthenticationType. AgentsInfo has no populated authentication-type column, so this query reports status by platform instead. For exposure, use Q3 (access posture) and Q4 (broadly-accessible agents).

Query 3: Access Posture Distribution

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| extend AppType = tostring(RawAgentInfo.appType),
         AllowAllUsers = tostring(RawAgentInfo.allowForAllUsers)
| summarize AgentCount = count() by Platform, AppType, AllowAllUsers
| order by AgentCount desc

Interpretation: appType == "lob" (line-of-business) agents are owner-scoped; appType == "shared" are shared more widely. allowForAllUsers == "true" (any platform) is the broad-exposure signal — these reach every tenant user. This replaces the old AccessControlPolicy distribution.

Query 4: Broadly-Accessible Agents

🔴 Security-critical query — agents with allowForAllUsers == "true" are accessible to all tenant users. This is the closest available proxy for the old "unauthenticated / Any access" exposure signal (see the authentication-type gap).

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| extend AllowAllUsers = tostring(RawAgentInfo.allowForAllUsers),
         AppType = tostring(RawAgentInfo.appType),
         CreatorId = tostring(RawAgentInfo.creatorId)
| where AllowAllUsers == "true"
| project Name, Platform, PublishedStatus, AppType, CreatorId, AgentId, CreatedDateTime, Description
| order by PublishedStatus asc, CreatedDateTime desc

Post-processing: For each broadly-accessible agent, note:

• Is it Published (active) or Draft?
• Cross-reference with Q5 (email-capable) and Q7 (knowledge sources) for compounding XPIA / reconnaissance risk.

🔴 Capability Reconnaissance Risk (Attack Scenario 3): Broadly-accessible agents are prime targets for adversarial probing. Published agents with knowledge sources containing customer/internal data are the highest-priority findings.

Query 5: XPIA Email Exfiltration Risk (Email-Capable Agents)

🔴 Security-critical query — agents that can send email via a connector operation. A successful prompt-injection (XPIA) attack could direct the agent to exfiltrate data to arbitrary recipients.

Coverage caveat: Detects email-send operations declared in RawAgentInfo.declarativeCopilotMetadata (DCM). DCM is present only for the connector-sourced agent subset. The old IsGenerativeOrchestrationEnabled flag and action-level inputs (AI-controlled vs hardcoded recipient) are not available in AgentsInfo — this query identifies capability, not orchestration mode.

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| where isnotempty(tostring(RawAgentInfo.declarativeCopilotMetadata))
| mv-expand DCM = RawAgentInfo.declarativeCopilotMetadata
| mv-expand Action = DCM.actions
| mv-expand Api = Action.apis
| mv-expand Op = Api.operations
| extend OperationId = tostring(Op.operationId)
| where OperationId has "Send an email" or OperationId has "SendEmail"
| extend AllowAllUsers = tostring(RawAgentInfo.allowForAllUsers),
         CreatorId = tostring(RawAgentInfo.creatorId)
| summarize EmailOperations = make_set(OperationId)
    by AgentId, Name, Platform, PublishedStatus, AllowAllUsers, CreatorId
| order by AllowAllUsers desc, PublishedStatus asc

Post-processing:

• AllowAllUsers == "true" → email-capable and broadly accessible = highest XPIA risk (any tenant user can trigger the chain).
• Cross-reference with Q7: an email-capable agent that also has knowledge/data sources is the documented XPIA exfiltration pattern (Attack Scenario 2). Prioritize these for Defender Runtime Protection.

🔴 Attack Scenario Mapping: This query detects the agent-configuration precondition (email-send capability) for two documented scenarios — Malicious Instruction Injection via Event Trigger and Prompt Injection via Shared Document. Broadly-accessible email-capable agents (no access restriction + email) are the most dangerous.

Query 6: MCP Tool Inventory Across Agents

🟠 Governance query — MCP servers give agents access to external systems, Graph API, Sentinel data, and more. Uncontrolled MCP proliferation increases the attack surface. AgentsInfo exposes a dedicated McpServers column (cleaner than the old tool-detail parse).

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| where array_length(McpServers) > 0
| mv-expand Mcp = McpServers
| extend McpName = tostring(Mcp.name)
| extend CreatorId = tostring(RawAgentInfo.creatorId),
         AllowAllUsers = tostring(RawAgentInfo.allowForAllUsers)
| summarize McpServerList = make_set(McpName), McpToolCount = dcount(McpName)
    by AgentId, Name, Platform, CreatorId, AllowAllUsers
| order by McpToolCount desc

MCP server distribution (which servers appear on the most agents):

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| where array_length(McpServers) > 0
| mv-expand Mcp = McpServers
| summarize AgentCount = dcount(AgentId) by McpServer = tostring(Mcp.name)
| order by AgentCount desc

Note: McpServers is flat ({name, description} only) — no server URLs or credential config. For external MCP endpoint detail (host/scheme/port), use Q9, which parses RemoteMCPServer serverUrls from DCM.

Query 7: Knowledge / Data Source Audit

🟡 Data exposure query — identifies what data sources agents declare. In AgentsInfo, declared sources appear in the DeclaredDataSources column as an array of source/filename strings.

AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| where array_length(DeclaredDataSources) > 0
| mv-expand DS = DeclaredDataSources
| extend DataSource = tostring(DS)
| extend AllowAllUsers = tostring(RawAgentInfo.allowForAllUsers),
         CreatorId = tostring(RawAgentInfo.creatorId)
| summarize DataSources = make_set(DataSource), SourceCount = dcount(DataSource)
    by AgentId, Name, Platform, AllowAllUsers, CreatorId
| order by SourceCount desc

Post-processing — flag high-risk combinations:

• Data sources + allowForAllUsers == "true" → internal data potentially exposed broadly.
• Any data source on an agent that is also email-capable (Q5) → XPIA exfiltration chain.

Coverage caveat: DeclaredDataSources is sparse and stores source names/filenames (e.g., Priority-Banking-Policy.docx), not the richer $kind/site structure the old KnowledgeDetails column held. Source type classification (SharePoint vs public site vs federated) is not reliably available — report the declared source names and flag broadly-accessible agents that carry any.

🔴 Document Injection Risk (Attack Scenario 2): Data sources are the primary vector for indirect prompt injection (XPIA). Cross-reference with Q5: agents that combine declared data sources with an email-send operation are the textbook XPIA exfiltration pattern — flag these as highest priority in the Knowledge Source Risk dimension.

Query 8: Hard-Coded Credential Scan

🔴 Security-critical query — scans agent Instructions and the connector metadata in RawAgentInfo for patterns matching API keys, JWTs, Basic auth headers, and embedded credentials.

let suspicious_patterns = @"(AKIA[0-9A-Z]{16})|(AIza[0-9A-Za-z_\-]{35})|(xox[baprs]-[0-9a-zA-Z]{10,48})|(ghp_[A-Za-z0-9]{36,59})|(sk_(live|test)_[A-Za-z0-9]{24})|(SG\.[A-Za-z0-9]{22}\.[A-Za-z0-9]{43})|(eyJ[A-Za-z0-9_\-]+\.[A-Za-z0-9_\-]+\.[A-Za-z0-9_\-]+)|(Authorization\s*:\s*Basic\s+[A-Za-z0-9=:+]+)|([A-Za-z]+:\/\/[^\/\s]+:[^\/\s]+@[^\/\s]+)";
AgentsInfo
| summarize arg_max(Timestamp, *) by AgentId
| where LifecycleStatus != "Deleted"
| extend Haystack = strcat(tostring(Instructions), " ", tostring(RawAgentInfo.declarativeCopilotMetadata))
| where Haystack matches regex suspicious_patterns
| project Name, Platform, PublishedStatus,
          CreatorId = tostring(RawAgentInfo.creatorId), AgentId

Post-processing:

• Published agents with credential matches = immediate remediation required.
• Recommend Azure Key Vault + environment variables instead of hard-coded secrets.
• The JWT (eyJ...) and url://user:pass@host patterns can false-positive on example payloads — manually review each match.

Query 9: External Endpoint & HTTP Risk

🟠 Network risk query — inventories the external hosts that agent connectors reach, and flags insecure schemes or non-standard ports. External endpoints are declared in DCM apis[].serverUrls for OpenApi and RemoteMCPServer connector types (these are populated; api_action Power Platform connectors abstract the URL and are not covered).