Workspace Preflight

Query

// Microsoft Sentinel / Log Analytics preflight for CAND-001.
// Run each numbered query separately in Microsoft Sentinel > Logs.
// These checks return aggregate metadata only; they do not return prompt text,
// account identifiers, IP addresses, or raw alert evidence.

// 1. Confirm that both required tables have recent data.
union isfuzzy=true withsource=TableName
(
    CloudAppEvents
    | where TimeGenerated > ago(14d)
    | project TimeGenerated
),
(
    AlertEvidence
    | where TimeGenerated > ago(14d)
    | project TimeGenerated
)
| summarize Rows=count(), FirstEvent=min(TimeGenerated), LastEvent=max(TimeGenerated) by TableName
| order by TableName asc

// 2. Confirm whether the report's exact Sentinel AI action type exists.
// If ExactMatches is zero, inspect the discovery query below before changing the rule.
CloudAppEvents
| where TimeGenerated > ago(30d)
| summarize
    TotalCloudAppEvents=count(),
    ExactMatches=countif(ActionType =~ "SentinelAIToolRunCompleted"),
    FirstExactMatch=minif(TimeGenerated, ActionType =~ "SentinelAIToolRunCompleted"),
    LastExactMatch=maxif(TimeGenerated, ActionType =~ "SentinelAIToolRunCompleted")

// 3. Discover nearby AI, Copilot, Sentinel, and tool action types.
// Review the result; do not assume that a similarly named action has equivalent semantics.
CloudAppEvents
| where TimeGenerated > ago(30d)
| where ActionType has_any ("AI", "Copilot", "Sentinel", "Tool")
    or Application has_any ("AI", "Copilot", "Sentinel")
| summarize Events=count(), FirstSeen=min(TimeGenerated), LastSeen=max(TimeGenerated)
    by ActionType, Application
| top 100 by Events desc

// 4. Measure required field coverage without returning sensitive values.
CloudAppEvents
| where TimeGenerated > ago(30d)
| where ActionType =~ "SentinelAIToolRunCompleted"
| summarize
    Events=count(),
    WithAccountObjectId=countif(isnotempty(AccountObjectId)),
    WithReportId=countif(isnotempty(ReportId)),
    WithInputParameters=countif(isnotempty(tostring(RawEventData.InputParameters))),
    WithToolName=countif(isnotempty(tostring(RawEventData.ToolName))),
    WithIPAddress=countif(isnotempty(IPAddress))

// 5. Measure AlertEvidence join-key coverage and discover category values.
AlertEvidence
| where TimeGenerated > ago(30d)
| summarize
    EvidenceRows=count(),
    WithAlertId=countif(isnotempty(AlertId)),
    WithAccountObjectId=countif(isnotempty(AccountObjectId)),
    DistinctAlerts=dcount(AlertId),
    DistinctAccounts=dcountif(AccountObjectId, isnotempty(AccountObjectId))

// 6. Review common downstream categories and sources before setting
// RequiredAlertCategories. Results contain aggregate labels only.
AlertEvidence
| where TimeGenerated > ago(30d)
| summarize EvidenceRows=count(), DistinctAlerts=dcount(AlertId)
    by Categories, ServiceSource, DetectionSource
| top 100 by EvidenceRows desc

// 7. Count-only historical replay of the broad correlation.
// Start with 24h, then expand to 14d only if the query is performant.
let ReplayLookback = 24h;
let CorrelationWindow = 4h;
let AIToolRuns =
    CloudAppEvents
    | where TimeGenerated > ago(ReplayLookback + CorrelationWindow)
    | where ActionType =~ "SentinelAIToolRunCompleted"
    | where isnotempty(AccountObjectId)
    | where isnotempty(tostring(RawEventData.InputParameters))
    | project AIToolRunTimestamp=TimeGenerated, ReportId, AccountObjectId;
AlertEvidence
| where TimeGenerated > ago(ReplayLookback)
| where isnotempty(AlertId) and isnotempty(AccountObjectId)
| project AlertTimestamp=TimeGenerated, AlertId, AccountObjectId
| join kind=inner (AIToolRuns) on AccountObjectId
| where AlertTimestamp between (AIToolRunTimestamp .. AIToolRunTimestamp + CorrelationWindow)
| summarize
    CorrelatedRows=count(),
    DistinctAlerts=dcount(AlertId),
    DistinctAccounts=dcount(AccountObjectId),
    DistinctToolRuns=dcount(ReportId)

Explanation

This query is a series of checks and analyses designed to ensure that certain data and actions are present and correctly logged in Microsoft Sentinel and Log Analytics. Here's a simplified breakdown of each part:

Check Recent Data in Tables: This part checks if two tables, CloudAppEvents and AlertEvidence, have data from the last 14 days. It counts the number of rows and identifies the first and last event times for each table.
Verify Specific Action Type: This query checks if a specific action type, "SentinelAIToolRunCompleted", has occurred in the last 30 days within the CloudAppEvents table. It counts the total events and those matching the specific action type, along with the first and last occurrence times.
Identify Related Action Types: This part looks for events related to AI, Copilot, Sentinel, and Tool actions in the last 30 days. It summarizes the number of events and their first and last occurrence times, grouped by action type and application.
Field Coverage Analysis: This query checks how many events with the action type "SentinelAIToolRunCompleted" have certain fields filled in, such as AccountObjectId, ReportId, InputParameters, ToolName, and IPAddress.
Alert Evidence Key Coverage: This part measures how many entries in the AlertEvidence table have certain key fields filled, like AlertId and AccountObjectId, and counts distinct alerts and accounts.
Common Categories and Sources: This query identifies the most common categories and sources of alerts in the AlertEvidence table, summarizing the number of evidence rows and distinct alerts for each category, service source, and detection source.
Historical Correlation Replay: This part performs a historical analysis to find correlations between tool runs and alerts over a specified lookback period (initially 24 hours, expandable to 14 days). It matches events from CloudAppEvents with AlertEvidence based on AccountObjectId and within a 4-hour correlation window, summarizing the number of correlated rows, distinct alerts, accounts, and tool runs.

Overall, these queries are designed to ensure data integrity, verify specific actions, and analyze relationships between different types of events in the system.