kql_queries_v22_E5V3.kql

// ═══════════════════════════════════════════════════════════════════════════════
// AI Solutions Intelligence Dashboard — Data Collection Query Pack
// Version: v26.2 — Manual/PAX parity validated (September 5, 2026)
// ═══════════════════════════════════════════════════════════════════════════════
//
// This file contains all PowerShell scripts and KQL (Kusto Query Language)
// queries needed to populate the dashboard's 13 CSV data files.
//
// v26.2 changes vs. v26.1 (credential-free static/contract validation):
//   A4  — Preserve successful-CA and non-successful-CA sign-ins as separate
//          aggregates, matching Collect-AISolutionsGraph.ps1.
//   B2–B6, B8 — Synchronize AI application/domain catalogs with the current
//          PAX presets. B2 preserves any allowlisted app via the Application
//          fallback so newly added tools are not silently discarded.
//   B7  — Resolve known Cloud Discovery portal column-name variants
//          automatically instead of requiring users to edit the script.
//
// v26.1 changes vs. original v26 (validated against a live tenant):
//   B2  — Removed over-broad ActionType OR filter (was pulling SharePoint/Teams/
//          OneDrive); changed AISolution fallback from `Application` to `""`;
//          replaced AccountObjectId with IdentityInfo UPN lookup.
//          Performance: 30 s / Critical → 4 s / Medium.
//   B4  — Fixed `LocationDetails.countryOrRegion` (column doesn't exist in either
//          Sentinel or native Defender XDR schemas) → use direct `Country` column;
//          fixed `dynamic([PrimaryCountry])` → `pack_array(PrimaryCountry)` (the
//          original produced a parse error and empty anomaly results).
//   B6  — Fixed `AdditionalFields.AppName` (Sentinel stores AdditionalFields as
//          string, not dynamic) → `parse_json(tostring(AdditionalFields)).AppName`.
//   B8  — Replaced AccountObjectId with IdentityInfo UPN lookup (same as B2).
//   B3, B5 — No changes. Validated clean.
//
// Section A = PowerShell / Graph scripts (run in PowerShell 7+)
// Section B = KQL queries (run in Defender XDR → Advanced Hunting)
//
// NOTE ON ENVIRONMENT: Queries target Microsoft Defender XDR Advanced Hunting.
// Table availability depends on licensed and connected products:
// CloudAppEvents requires Defender for Cloud Apps; DeviceFileEvents and
// DeviceNetworkEvents require Defender for Endpoint; EntraIdSignInEvents
// requires Microsoft Entra ID P2 and retained Entra data in Defender XDR.
//
// IMPORTANT: Each query's output columns must EXACTLY match the schemas below.
//            Do NOT rename columns — Power Query expects these exact headers.
//
// Prerequisites:
//   - PowerShell 7+ with Microsoft.Graph and ExchangeOnlineManagement modules
//   - Microsoft Defender XDR portal access (Security Reader minimum)
//   - Search-UnifiedAuditLog: View-Only Audit Logs or Audit Logs in Exchange Online
//   - Purview portal search/export: Audit Reader or Audit Manager
//   - Permissions: User.Read.All, LicenseAssignment.Read.All,
//                  AuditLog.Read.All, ThreatHunting.Read.All
//
// ═══════════════════════════════════════════════════════════════════════════════


// ─────────────────────────────────────────────────────────────────────────────
// SECTION A1 — EntraUsers.csv (PowerShell / Microsoft Graph)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: PowerShell 7+
// Output: EntraUsers.csv
// Schema: userPrincipalName, displayName, department, jobTitle, city, country,
//         companyName, accountEnabled, userType, createdDateTime, hasLicense,
//         assignedLicenses, manager_displayName, manager_userPrincipalName
//
// <powershell>
// Connect-MgGraph -Scopes "User.Read.All","LicenseAssignment.Read.All"
// $skuById = @{}
// Get-MgSubscribedSku -All | ForEach-Object {
//     $skuById[$_.SkuId.ToString()] = $_.SkuPartNumber
// }
//
// Get-MgUser -All `
//   -Property "userPrincipalName,displayName,department,jobTitle,city,country,companyName,accountEnabled,userType,createdDateTime,assignedLicenses,manager" `
//   -ExpandProperty manager |
//   Select-Object userPrincipalName, displayName, department, jobTitle, city, country, companyName,
//                 accountEnabled, userType, createdDateTime,
//     @{n='hasLicense';e={ if ($_.AssignedLicenses.Count -gt 0) {'TRUE'} else {'FALSE'} }},
//     @{n='assignedLicenses';e={
//         ($_.AssignedLicenses | ForEach-Object {
//             $id = $_.SkuId.ToString()
//             if ($skuById.ContainsKey($id)) { $skuById[$id] } else { $id }
//         }) -join ';'
//     }},
//     @{n='manager_displayName';      e={ $_.Manager.AdditionalProperties.displayName }},
//     @{n='manager_userPrincipalName';e={ $_.Manager.AdditionalProperties.userPrincipalName }} |
//   Export-Csv -NoTypeInformation -Encoding UTF8 EntraUsers.csv
//
// </powershell>


// ─────────────────────────────────────────────────────────────────────────────
// SECTION A2 — Copilot usage CSVs (PowerShell / Purview Audit)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: PowerShell 7+ (requires ExchangeOnlineManagement module)
// Outputs: ai_copilot_usage_graph.csv, ai_copilot_surface_usage.csv
// Schema: UserPrincipalName, YearMonth, TeamsPrompts, WordPrompts, ExcelPrompts,
//         OutlookPrompts, PowerPointPrompts, ChatPrompts, TotalPrompts,
//         ActiveDays, LastActivityDate
//
// This query searches Purview Audit for CopilotInteraction events. AppHost is
// nested under CopilotEventData in the current audit schema; Workload is retained
// as source metadata and used only as a fallback when AppHost is absent.
//
// NOTE: The Power Query table is named AI_CopilotUsage and loads from
//       "ai_copilot_usage_graph.csv". Name your output file accordingly.
//
// The Graph Reports user-detail endpoint does NOT return this count schema and
// cannot be saved directly under this filename.
//
// <powershell>
// # 1. Connect to Exchange Online (Purview audit uses EXO cmdlets)
// Connect-ExchangeOnline
//
// # 2. Search for Copilot interaction events (adjust dates as needed)
// $startDate = (Get-Date).AddDays(-180).ToString("yyyy-MM-dd")
// $endDate   = (Get-Date).ToString("yyyy-MM-dd")
//
// # 3. Paginate to collect all records
// $allEvents = @()
// $sessionId = [guid]::NewGuid().ToString()
// do {
//     $batch = Search-UnifiedAuditLog `
//         -StartDate $startDate -EndDate $endDate `
//         -Operations "CopilotInteraction" `
//         -ResultSize 5000 `
//         -SessionId $sessionId `
//         -SessionCommand ReturnLargeSet
//     if ($batch) { $allEvents += $batch }
// } while ($batch.Count -gt 0)
// if ($allEvents.Count -ge 50000) {
//     throw "Purview reached its 50,000-record session limit. Use PAX_Exporter\Collect-AICopilotUsage.ps1, which recursively splits saturated windows."
// }
//
// Write-Host "Retrieved $($allEvents.Count) CopilotInteraction events"
//
// # 4. Parse audit data and extract surface + user
// $parsed = $allEvents | ForEach-Object {
//     $data = $_.AuditData | ConvertFrom-Json
//     $appHost = [string]$data.CopilotEventData.AppHost
//     if ([string]::IsNullOrWhiteSpace($appHost)) {
//         $appHost = [string]$data.AppHost # Backward compatibility for older exports.
//     }
//     $rawSurface = if ([string]::IsNullOrWhiteSpace($appHost)) {
//         [string]$data.Workload
//     } else {
//         $appHost
//     }
//     [PSCustomObject]@{
//         UPN            = $data.UserId.ToLower()
//         YearMonth      = ([datetime]$data.CreationTime).ToString("yyyy-MM")
//         Date           = ([datetime]$data.CreationTime).ToString("yyyy-MM-dd")
//         SourceWorkload = [string]$data.Workload
//         SourceAppHost  = $appHost
//         Surface        = switch ($rawSurface) {
//             "MicrosoftTeams" { "Teams" }
//             "Teams"          { "Teams" }
//             "Word"           { "Word" }
//             "Excel"          { "Excel" }
//             "Outlook"        { "Outlook" }
//             "PowerPoint"     { "PowerPoint" }
//             "BizChat"        { "Chat" }
//             "Bing"           { "Chat" }
//             "Office"         { "Chat" }
//             "M365App"        { "Chat" }
//             "M365Chat"       { "Chat" }
//             "Microsoft365"   { "Chat" }
//             default          { $rawSurface }
//         }
//         Prompts        = if ($data.CopilotEventData.Prompts) {
//                         $data.CopilotEventData.Prompts.Count
//                     } else { 1 }
//     }
// }
//
// # 5. Pivot: one row per user × month with per-surface prompt counts
// $pivoted = $parsed | Group-Object UPN, YearMonth | ForEach-Object {
//     $grp      = $_.Group
//     $upn      = $grp[0].UPN
//     $ym       = $grp[0].YearMonth
//     $days     = ($grp | Select-Object -ExpandProperty Date -Unique).Count
//     $lastDate = ($grp | Sort-Object Date | Select-Object -Last 1).Date
//
//     $teams = ($grp | Where-Object Surface -eq "Teams" |
//               Measure-Object Prompts -Sum).Sum
//     $word  = ($grp | Where-Object Surface -eq "Word" |
//               Measure-Object Prompts -Sum).Sum
//     $excel = ($grp | Where-Object Surface -eq "Excel" |
//               Measure-Object Prompts -Sum).Sum
//     $outl  = ($grp | Where-Object Surface -eq "Outlook" |
//               Measure-Object Prompts -Sum).Sum
//     $ppt   = ($grp | Where-Object Surface -eq "PowerPoint" |
//               Measure-Object Prompts -Sum).Sum
//     $chat  = ($grp | Where-Object Surface -eq "Chat" |
//               Measure-Object Prompts -Sum).Sum
//
//     [PSCustomObject]@{
//         UserPrincipalName = $upn
//         YearMonth         = $ym
//         TeamsPrompts      = [int]$teams
//         WordPrompts       = [int]$word
//         ExcelPrompts      = [int]$excel
//         OutlookPrompts    = [int]$outl
//         PowerPointPrompts = [int]$ppt
//         ChatPrompts       = [int]$chat
//         TotalPrompts      = [int](($grp | Measure-Object Prompts -Sum).Sum)
//         ActiveDays        = $days
//         LastActivityDate  = $lastDate
//     }
// }
//
// $pivoted | Export-Csv -NoTypeInformation -Encoding UTF8 ai_copilot_usage_graph.csv
// Write-Host "Exported $($pivoted.Count) user-month rows to ai_copilot_usage_graph.csv"
//
// $normalized = $parsed |
//   Group-Object UPN, YearMonth, Surface, SourceWorkload, SourceAppHost |
//   ForEach-Object {
//       $grp = $_.Group
//       [PSCustomObject]@{
//           UserPrincipalName = $grp[0].UPN
//           YearMonth         = $grp[0].YearMonth
//           Surface           = $grp[0].Surface
//           SourceWorkload    = $grp[0].SourceWorkload
//           SourceAppHost     = $grp[0].SourceAppHost
//           PromptCount       = [int](($grp | Measure-Object Prompts -Sum).Sum)
//           ActiveDays        = ($grp | Select-Object -ExpandProperty Date -Unique).Count
//           LastActivityDate  = ($grp | Sort-Object Date | Select-Object -Last 1).Date
//       }
//   }
// $normalized | Export-Csv -NoTypeInformation -Encoding UTF8 ai_copilot_surface_usage.csv
// Write-Host "Exported $($normalized.Count) surface rows to ai_copilot_surface_usage.csv"
// </powershell>


// ─────────────────────────────────────────────────────────────────────────────
// SECTION A3 — ai_oauth_consents.csv (PowerShell / Entra Audit Logs)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: PowerShell 7+
// Output: ai_oauth_consents.csv
// Schema: UPN, AppName, YearMonth, ConsentCount, LastConsent,
//         PermissionWeight, Permissions
//
// <powershell>
// Connect-MgGraph -Scopes "AuditLog.Read.All"
//
// $startDate = (Get-Date).AddDays(-180).ToString("yyyy-MM-ddTHH:mm:ssZ")
//
// # Fetch consent-grant audit events
// $consentLogs = Get-MgAuditLogDirectoryAudit -All `
//     -Filter "activityDisplayName eq 'Consent to application' and activityDateTime ge $startDate" `
//     -Property "activityDateTime,targetResources,initiatedBy"
//
// # AI app keywords to filter on — extend this list for your tenant
// $aiKeywords = @("openai","copilot","chatgpt","claude","anthropic","gemini",
//                 "bard","midjourney","perplexity","hugging","stability",
//                 "github copilot","bing chat","dall-e","jasper","grammarly",
//                 "notion ai","adobe firefly","canva ai","synthesia","runway")
//
// $parsed = $consentLogs | ForEach-Object {
//     $appName = ($_.TargetResources | Where-Object Type -eq "ServicePrincipal" |
//                 Select-Object -First 1).DisplayName
//     $upn     = $_.InitiatedBy.User.UserPrincipalName
//
//     # Check if app name matches any AI keyword
//     $isAI = $false
//     foreach ($kw in $aiKeywords) {
//         if ($appName -like "*$kw*") { $isAI = $true; break }
//     }
//
//     if ($isAI -and $upn) {
//         $perms = ($_.TargetResources |
//             Where-Object Type -eq "ServicePrincipal" |
//             Select-Object -ExpandProperty ModifiedProperties |
//             Where-Object DisplayName -eq "DelegatedPermissionGrant.Scope" |
//             Select-Object -ExpandProperty NewValue) -join ", "
//
//         # Simple permission weight: count distinct scopes, add bonus for
//         # high-privilege scopes (Mail, Files, Sites)
//         $scopeList = $perms -split "[,;\s]+" | Where-Object { $_ }
//         $weight = $scopeList.Count
//         foreach ($s in $scopeList) {
//             if ($s -match "Mail\.|Files\.|Sites\.") { $weight += 5 }
//             if ($s -match "\.ReadWrite") { $weight += 2 }
//         }
//
//         [PSCustomObject]@{
//             UPN       = $upn.ToLower()
//             AppName   = $appName
//             Timestamp = $_.ActivityDateTime
//             YearMonth = $_.ActivityDateTime.ToString("yyyy-MM")
//             Weight    = $weight
//             Perms     = if ($perms) { $perms } else { "Unknown" }
//         }
//     }
// }
//
// # Aggregate to user × app × month
// $aggregated = $parsed | Group-Object UPN, AppName, YearMonth | ForEach-Object {
//     $grp   = $_.Group
//     $last  = ($grp | Sort-Object Timestamp | Select-Object -Last 1).Timestamp
//     $perms = ($grp | Select-Object -ExpandProperty Perms -Unique) -join "; "
//     $wt    = ($grp | Measure-Object Weight -Max).Maximum
//
//     [PSCustomObject]@{
//         UPN              = $grp[0].UPN
//         AppName          = $grp[0].AppName
//         YearMonth        = $grp[0].YearMonth
//         ConsentCount     = $grp.Count
//         LastConsent      = $last.ToString("yyyy-MM-dd")
//         PermissionWeight = $wt
//         Permissions      = $perms
//     }
// }
//
// $aggregated | Export-Csv -NoTypeInformation -Encoding UTF8 ai_oauth_consents.csv
// Write-Host "Exported $($aggregated.Count) rows to ai_oauth_consents.csv"
// </powershell>


// ─────────────────────────────────────────────────────────────────────────────
// SECTION A4 — ai_sso_signins.csv (PowerShell / Entra Sign-In Logs)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: PowerShell 7+
// Output: ai_sso_signins.csv
// Schema: UPN, Application, YearMonth, SignInCount, DistinctDays, IsGuest,
//         Countries, HasConditionalAccess, LastSignIn
//
// <powershell>
// Connect-MgGraph -Scopes "AuditLog.Read.All","User.Read.All"
// $userTypeByUpn = @{}
// Get-MgUser -All -Property "userPrincipalName,userType" | ForEach-Object {
//     $userTypeByUpn[$_.UserPrincipalName.ToLower()] = $_.UserType
// }
//
// $startDate = (Get-Date).AddDays(-90).ToString("yyyy-MM-ddTHH:mm:ssZ")
//
// # AI app keywords (same list as A3)
// $aiKeywords = @("openai","copilot","chatgpt","claude","anthropic","gemini",
//                 "bard","midjourney","perplexity","hugging","stability",
//                 "github copilot","bing chat","dall-e","jasper","grammarly",
//                 "notion ai","adobe firefly","canva ai","synthesia","runway")
//
// # Fetch sign-in logs (may take a few minutes for large tenants)
// $signIns = Get-MgAuditLogSignIn -All `
//     -Filter "createdDateTime ge $startDate" `
//     -Property "userPrincipalName,appDisplayName,createdDateTime,location,conditionalAccessStatus,status"
//
// # Filter to AI apps and successful sign-ins
// $aiSignIns = $signIns | Where-Object {
//     $app = $_.AppDisplayName
//     $isAI = $false
//     foreach ($kw in $aiKeywords) {
//         if ($app -like "*$kw*") { $isAI = $true; break }
//     }
//     $isAI -and $_.Status.ErrorCode -eq 0
// } | ForEach-Object {
//     [PSCustomObject]@{
//         UPN       = $_.UserPrincipalName.ToLower()
//         App       = $_.AppDisplayName
//         Date      = $_.CreatedDateTime.ToString("yyyy-MM-dd")
//         YearMonth = $_.CreatedDateTime.ToString("yyyy-MM")
//         Country   = $_.Location.CountryOrRegion
//         IsGuest   = if ($userTypeByUpn[$_.UserPrincipalName.ToLower()] -eq "Guest" -or
//                        $_.UserPrincipalName -like "*#EXT#*") { "TRUE" } else { "FALSE" }
//         HasCA     = if ($_.ConditionalAccessStatus -eq "success") { "TRUE" } else { "FALSE" }
//     }
// }
//
// # Aggregate to user × app × month × CA status. Keeping CA states separate
// # prevents one protected sign-in from marking every sign-in in the month protected.
// $aggregated = $aiSignIns | Group-Object UPN, App, YearMonth, HasCA | ForEach-Object {
//     $grp       = $_.Group
//     $days      = ($grp | Select-Object -ExpandProperty Date -Unique).Count
//     $countries = ($grp | Select-Object -ExpandProperty Country -Unique |
//                   Where-Object { $_ }) -join "; "
//     $lastDate  = ($grp | Sort-Object Date | Select-Object -Last 1).Date
//
//     [PSCustomObject]@{
//         UPN                  = $grp[0].UPN
//         Application          = $grp[0].App
//         YearMonth            = $grp[0].YearMonth
//         SignInCount          = $grp.Count
//         DistinctDays         = $days
//         IsGuest              = $grp[0].IsGuest
//         Countries            = if ($countries) { $countries } else { "Unknown" }
//         HasConditionalAccess = $grp[0].HasCA
//         LastSignIn           = $lastDate
//     }
// }
//
// $aggregated | Export-Csv -NoTypeInformation -Encoding UTF8 ai_sso_signins.csv
// Write-Host "Exported $($aggregated.Count) rows to ai_sso_signins.csv"
// </powershell>


// ─────────────────────────────────────────────────────────────────────────────
// SECTION A5 — ai_solutions_catalog.csv (Reference / Manual)
// ─────────────────────────────────────────────────────────────────────────────
// Create manually in Excel or a text editor.
// Output: ai_solutions_catalog.csv
// Schema: AISolution, Category, Vendor, RiskTier, DefaultDataHandling, SolutionGroup
//
// RiskTier values: Sanctioned | Conditional | Unsanctioned
// SolutionGroup: a display grouping label (e.g. "Microsoft Copilot", "Third-Party AI")
//
// The LicenseStatus column is computed in DAX:
//   Sanctioned or Conditional → "Licensed (Org-Provided)"
//   Unsanctioned → "Unlicensed (Shadow AI)"
//
// Seed example (copy and extend for your tenant):
//
// AISolution,Category,Vendor,RiskTier,DefaultDataHandling,SolutionGroup
// Microsoft 365 Copilot,Productivity,Microsoft,Sanctioned,Internal Only,Microsoft Copilot
// GitHub Copilot,Development,Microsoft,Sanctioned,Code Context,Microsoft Copilot
// Bing Chat Enterprise,Search,Microsoft,Sanctioned,Internal Only,Microsoft Copilot
// ChatGPT Enterprise,General AI,OpenAI,Conditional,Org Managed,Licensed Third-Party
// ChatGPT Free,General AI,OpenAI,Unsanctioned,Public Cloud,Shadow AI
// ChatGPT Plus,General AI,OpenAI,Unsanctioned,Public Cloud,Shadow AI
// Claude,General AI,Anthropic,Unsanctioned,Public Cloud,Shadow AI
// Gemini,General AI,Google,Unsanctioned,Public Cloud,Shadow AI
// Perplexity,Search AI,Perplexity,Unsanctioned,Public Cloud,Shadow AI
// Midjourney,Image Generation,Midjourney,Unsanctioned,Public Cloud,Shadow AI
// DALL-E,Image Generation,OpenAI,Conditional,Org Managed,Licensed Third-Party
// Grammarly,Writing,Grammarly,Conditional,Mixed,Licensed Third-Party
// Notion AI,Productivity,Notion,Unsanctioned,Public Cloud,Shadow AI
// Adobe Firefly,Design,Adobe,Conditional,Org Managed,Licensed Third-Party
// Jasper,Marketing,Jasper,Unsanctioned,Public Cloud,Shadow AI
// Synthesia,Video,Synthesia,Unsanctioned,Public Cloud,Shadow AI
// Runway,Video,Runway,Unsanctioned,Public Cloud,Shadow AI
// Stability AI,Image Generation,Stability AI,Unsanctioned,Public Cloud,Shadow AI
// Hugging Face,ML Platform,Hugging Face,Unsanctioned,Public Cloud,Shadow AI
// Canva AI,Design,Canva,Conditional,Org Managed,Licensed Third-Party


// ═══════════════════════════════════════════════════════════════════════════════
// SECTION B — KQL Queries (run in Defender XDR → Advanced Hunting)
// ═══════════════════════════════════════════════════════════════════════════════
//
// Instructions:
//   1. Go to https://security.microsoft.com → Hunting → Advanced Hunting
//   2. Paste the KQL query for the section you need
//   3. Click "Run query"
//   4. Click "Export" → "Export to CSV"
//   5. Rename the downloaded file to match the expected filename
//
// The AI domain list below is used across multiple queries. Customize it for
// your tenant by adding/removing domains as needed.
//
// ═══════════════════════════════════════════════════════════════════════════════


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B2 — ai_activity_sessions.csv (CloudAppEvents)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_activity_sessions.csv
// Schema: UPN, AISolution, YearMonth, Sessions, ActiveDays, EstimatedPrompts,
//         DistinctDevices, Category, RiskTier
//
// This query identifies AI tool usage from CloudAppEvents, mapping MDA-connected
// app names to the solutions catalog.
//
// FIXES applied (validated June 2026 against live tenant):
//   1. Initial filter changed to Application has_any (AIAppNames) only.
//      The original ActionType OR arm captured all SharePoint/Teams/OneDrive
//      traffic, polluting results with ~1,400+ non-AI rows.
//      RawEventData has_any() removed — caused 100% resource exhaustion (30s+).
//   2. AISolution case() fallback preserves `Application` so every app admitted
//      by AIAppNames remains in the export, including newly added tools.
//   3. UPN resolved via IdentityInfo lookup. AccountObjectId (Entra GUID) is
//      used as fallback when IdentityInfo has no match (guest/service accounts).
//      Rows that fall back to GUID will not join EntraUsers in the PBIT but
//      will still contribute to total session counts — this is expected.
//
// NOTE: EstimatedPrompts will be 0 for Microsoft 365 Copilot rows — MDA does
//       not surface MessageSent/SearchPerformed for in-app Copilot surfaces.
//       Real prompt counts come from ai_copilot_usage_graph.csv (Section A2).
//       EstimatedPrompts is most useful for third-party AI apps (ChatGPT, Claude)
//       when they are reverse-proxied through MDA Conditional Access App Control.

let AIAppNames = dynamic([
    "Microsoft 365 Copilot", "Microsoft Copilot", "Copilot", "GitHub Copilot",
    "Copilot Studio", "Security Copilot", "Bing Chat", "Bing Chat Enterprise",
    "ChatGPT", "ChatGPT Enterprise", "OpenAI",
    "Claude", "Anthropic", "Gemini", "Google Gemini", "Bard",
    "Perplexity", "Poe", "Mistral", "Le Chat", "DeepSeek", "Grok",
    "Meta AI", "Llama",
    "Cursor", "Codeium", "Windsurf", "Tabnine", "Amazon Q", "CodeWhisperer",
    "Replit", "Cody", "JetBrains AI",
    "Grammarly", "Notion", "Notion AI", "Jasper", "Copy.ai", "Writesonic",
    "Otter.ai", "Fireflies", "Gamma",
    "Midjourney", "DALL-E", "Adobe Firefly", "Firefly", "Stable Diffusion",
    "Stability", "Leonardo.Ai", "Canva", "Synthesia", "Runway", "Pika",
    "Sora", "Ideogram", "HeyGen", "ElevenLabs", "Descript", "Luma",
    "Glean", "You.com", "Hugging Face", "Replicate", "Cohere",
    "Character.AI", "Harvey", "Hebbia"
]);
// Resolve Entra ObjectId → UPN. Rows with no IdentityInfo match fall back to
// the raw ObjectId GUID — those rows won't join EntraUsers in Power BI.
let UserUpns = IdentityInfo
    | summarize take_any(AccountUpn) by AccountObjectId;
CloudAppEvents
| where Timestamp > ago(30d)
| where Application has_any (AIAppNames)
| extend AISolution = case(
    Application has "GitHub Copilot", "GitHub Copilot",
    Application has "Copilot Studio", "Copilot Studio",
    Application has "Security Copilot", "Security Copilot",
    Application has "Microsoft 365 Copilot" or Application has "Microsoft Copilot" or Application has "Copilot", "Microsoft 365 Copilot",
    Application has "ChatGPT" or Application has "OpenAI", "ChatGPT",
    Application has "Claude" or Application has "Anthropic", "Claude",
    Application has "Gemini" or Application has "Bard", "Gemini",
    Application has "Perplexity", "Perplexity",
    Application has "Midjourney", "Midjourney",
    Application has "Bing Chat" or Application has "Bing Copilot", "Bing Chat Enterprise",
    Application has "Grammarly", "Grammarly",
    Application has "Notion", "Notion AI",
    Application has "Firefly" or Application has "Adobe Firefly", "Adobe Firefly",
    Application has "Jasper", "Jasper",
    Application has "Synthesia", "Synthesia",
    Application has "Runway", "Runway",
    Application has "Hugging Face", "Hugging Face",
    Application has "Canva", "Canva AI",
    Application has "Stability", "Stability AI",
    Application
)
| where isnotempty(AISolution)
| lookup kind=leftouter UserUpns on AccountObjectId
| extend UPN = tolower(coalesce(AccountUpn, AccountObjectId))
| extend YearMonth = format_datetime(Timestamp, "yyyy-MM")
| extend DeviceKey = coalesce(
    tostring(RawEventData.DeviceId),
    tostring(RawEventData.deviceId),
    tostring(IPAddress),
    tostring(DeviceType),
    "Unknown"
)
| summarize
    Sessions = count(),
    ActiveDays = dcount(bin(Timestamp, 1d)),
    EstimatedPrompts = countif(ActionType in ("MessageSent", "SearchPerformed", "AppAccessedViaAPI")),
    DistinctDevices = dcount(DeviceKey)
    by UPN, AISolution, YearMonth
| extend Category = case(
    AISolution in ("Microsoft 365 Copilot", "Bing Chat Enterprise"), "Productivity",
    AISolution == "GitHub Copilot", "Development",
    AISolution == "Copilot Studio", "Business Automation",
    AISolution == "Security Copilot", "Security AI",
    AISolution in ("ChatGPT", "Claude", "Gemini", "Perplexity"), "General AI",
    AISolution in ("Midjourney", "DALL-E", "Stability AI", "Adobe Firefly"), "Image Generation",
    AISolution in ("Grammarly", "Jasper"), "Writing",
    AISolution in ("Notion AI", "Canva AI"), "Productivity",
    AISolution in ("Synthesia", "Runway"), "Video",
    "Other"
)
| extend RiskTier = case(
    AISolution in ("Microsoft 365 Copilot", "GitHub Copilot", "Bing Chat Enterprise", "Copilot Studio", "Security Copilot"), "Sanctioned",
    AISolution in ("ChatGPT", "Adobe Firefly", "Grammarly", "DALL-E", "Canva AI"), "Conditional",
    "Unsanctioned"
)
| project UPN, AISolution, YearMonth, Sessions, ActiveDays, EstimatedPrompts,
          DistinctDevices, Category, RiskTier
| order by UPN asc, YearMonth asc


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B3 — ai_file_proximity.csv (DeviceFileEvents + DeviceNetworkEvents)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_file_proximity.csv
// Schema: Timestamp, UPN, AISolution, YearMonth, FileName, FolderCategory,
//         FolderPath, SecondsToAI, NameMatchesSensitivePattern,
//         FolderMatchesSensitive
//
// Requires: MDE Plan 2 (DeviceFileEvents + DeviceNetworkEvents tables)
//
// This query detects selected file events within 5 minutes of an AI-domain
// connection. The temporal correlation is an investigation signal; it does not
// prove upload, disclosure, causation, or data exfiltration.

let AIDomains = dynamic([
    "copilot.microsoft.com", "copilot.cloud.microsoft", "m365.cloud.microsoft",
    "chat.openai.com", "chatgpt.com", "api.openai.com", "openai.com", "labs.openai.com",
    "claude.ai", "api.anthropic.com", "anthropic.com",
    "gemini.google.com", "bard.google.com",
    "perplexity.ai", "poe.com", "mistral.ai", "chat.mistral.ai",
    "deepseek.com", "chat.deepseek.com", "grok.com", "x.ai", "meta.ai",
    "cursor.com", "cursor.sh", "codeium.com", "windsurf.com", "tabnine.com",
    "replit.com", "sourcegraph.com",
    "grammarly.com", "notion.so", "jasper.ai", "copy.ai", "writesonic.com",
    "otter.ai", "fireflies.ai", "gamma.app",
    "midjourney.com", "firefly.adobe.com", "stability.ai", "stablediffusionweb.com",
    "leonardo.ai", "canva.com", "app.synthesia.io", "runwayml.com", "pika.art",
    "ideogram.ai", "heygen.com", "elevenlabs.io", "descript.com", "lumalabs.ai",
    "glean.com", "you.com",
    "huggingface.co", "replicate.com", "cohere.com", "character.ai"
]);
let SensitiveNamePatterns = dynamic([
    "confidential", "secret", "password", "credential", "private",
    "restricted", "internal", "draft", "salary", "ssn", "pii",
    "financial", "budget", "forecast", "strategy", "merger",
    "acquisition", "termination", "layoff", "patent"
]);
let SensitiveFolderPatterns = dynamic([
    "confidential", "restricted", "hr", "legal", "finance",
    "executive", "board", "compliance", "audit", "security"
]);
// Step 1: Find AI site visits
let AIVisits =
    DeviceNetworkEvents
    | where Timestamp > ago(30d)
    | where ActionType == "ConnectionSuccess"
    | where RemoteUrl has_any (AIDomains)
    | extend AISolution = case(
        RemoteUrl has "copilot.microsoft.com", "Microsoft 365 Copilot",
        RemoteUrl has "chat.openai.com" or RemoteUrl has "chatgpt.com", "ChatGPT",
        RemoteUrl has "claude.ai", "Claude",
        RemoteUrl has "gemini.google.com" or RemoteUrl has "bard.google.com", "Gemini",
        RemoteUrl has "perplexity.ai", "Perplexity",
        RemoteUrl has "midjourney.com", "Midjourney",
        RemoteUrl has "huggingface.co", "Hugging Face",
        RemoteUrl has "stability.ai", "Stability AI",
        RemoteUrl has "jasper.ai", "Jasper",
        RemoteUrl has "grammarly.com", "Grammarly",
        RemoteUrl has "notion.so", "Notion AI",
        RemoteUrl has "firefly.adobe.com", "Adobe Firefly",
        RemoteUrl has "runwayml.com", "Runway",
        RemoteUrl has "canva.com", "Canva AI",
        RemoteUrl has "app.synthesia.io", "Synthesia",
        "Other AI"
    )
    | project AITimestamp = Timestamp, DeviceId, AISolution;
// Step 2: Find file access events
let FileAccess =
    DeviceFileEvents
    | where Timestamp > ago(30d)
    | where ActionType in ("FileCreated", "FileModified", "FileRenamed", "FileCopied")
    | where FileName !endswith ".tmp" and FileName !endswith ".log"
    | project FileTimestamp = Timestamp, DeviceId, FileName, FolderPath,
              InitiatingProcessAccountUpn;
// Step 3: Join - selected file events within 5 minutes of an AI-domain connection
AIVisits
| join kind=inner FileAccess on DeviceId
| where FileTimestamp between (AITimestamp .. (AITimestamp + 5m))
| extend SecondsToAI = datetime_diff("second", FileTimestamp, AITimestamp)
| extend UPN = tolower(InitiatingProcessAccountUpn)
| extend YearMonth = format_datetime(AITimestamp, "yyyy-MM")
| extend FolderCategory = case(
    FolderPath has "Desktop", "Desktop",
    FolderPath has "Downloads", "Downloads",
    FolderPath has "Documents", "Documents",
    FolderPath has "OneDrive", "OneDrive",
    FolderPath has "SharePoint", "SharePoint",
    "Other"
)
| extend NameMatchesSensitivePattern = iff(FileName has_any (SensitiveNamePatterns), 1, 0)
| extend FolderMatchesSensitive = iff(FolderPath has_any (SensitiveFolderPatterns), 1, 0)
| project Timestamp = AITimestamp, UPN, AISolution, YearMonth, FileName,
          FolderCategory, FolderPath, SecondsToAI,
          NameMatchesSensitivePattern, FolderMatchesSensitive
| order by Timestamp asc


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B4 — ai_offhours_geo.csv (EntraIdSignInEvents)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_offhours_geo.csv
// Schema: UPN, YearMonth, TotalSessions, OffHoursSessions, OffHoursPct,
//         DistinctCountries, AnomalousCountryCount, AnomalousCountries
//
// Off-hours defined as before 7 AM or after 7 PM UTC — adjust for your org's
// primary timezone if needed.
// Anomalous country = any country other than the user's most frequent sign-in country.
//
// Uses the supported EntraIdSignInEvents table. pack_array(PrimaryCountry)
// accepts the column value used by set_difference().

let AIAppNames = dynamic([
    "Microsoft 365 Copilot", "Microsoft Copilot", "Copilot", "GitHub Copilot",
    "Copilot Studio", "Security Copilot", "Bing Chat", "Bing Chat Enterprise",
    "ChatGPT", "ChatGPT Enterprise", "OpenAI",
    "Claude", "Anthropic", "Gemini", "Google Gemini", "Bard",
    "Perplexity", "Poe", "Mistral", "Le Chat", "DeepSeek", "Grok",
    "Meta AI", "Llama",
    "Cursor", "Codeium", "Windsurf", "Tabnine", "Amazon Q", "CodeWhisperer",
    "Replit", "Cody", "JetBrains AI",
    "Grammarly", "Notion", "Notion AI", "Jasper", "Copy.ai", "Writesonic",
    "Otter.ai", "Fireflies", "Gamma",
    "Midjourney", "DALL-E", "Adobe Firefly", "Firefly", "Stable Diffusion",
    "Stability", "Leonardo.Ai", "Canva", "Synthesia", "Runway", "Pika",
    "Sora", "Ideogram", "HeyGen", "ElevenLabs", "Descript", "Luma",
    "Glean", "You.com", "Hugging Face", "Replicate", "Cohere",
    "Character.AI", "Harvey", "Hebbia"
]);
// Step 1: Identify AI-related sign-ins
let AISignIns =
    EntraIdSignInEvents
    | where Timestamp > ago(30d)
    | where ErrorCode == 0  // successful sign-ins only
    | where Application has_any (AIAppNames)
        or ResourceDisplayName has_any (AIAppNames)
    | extend UPN = tolower(AccountUpn)
    | extend YearMonth = format_datetime(Timestamp, "yyyy-MM")
    | extend HourOfDay = hourofday(Timestamp)
    | extend IsOffHours = iff(HourOfDay < 7 or HourOfDay >= 19, 1, 0)
    | extend Country = coalesce(Country, "Unknown")
    | project UPN, YearMonth, Timestamp, IsOffHours, Country;
// Step 2: Compute per-user primary country (for anomaly detection)
let UserPrimaryCountry =
    AISignIns
    | summarize CountryCount = count() by UPN, Country
    | summarize TotalEvents = sum(CountryCount), arg_max(CountryCount, Country) by UPN
    | project UPN, PrimaryCountry = Country;
// Step 3: Aggregate
AISignIns
| summarize
    TotalSessions = count(),
    OffHoursSessions = countif(IsOffHours == 1),
    DistinctCountries = dcount(Country),
    Countries = make_set(Country)
    by UPN, YearMonth
| extend OffHoursPct = round(todouble(OffHoursSessions) / todouble(TotalSessions), 4)
| join kind=leftouter UserPrimaryCountry on UPN
| extend AnomalousCountries = set_difference(Countries, pack_array(PrimaryCountry))
| extend AnomalousCountryCount = array_length(AnomalousCountries)
| extend AnomalousCountries = iff(AnomalousCountryCount > 0,
    strcat_array(AnomalousCountries, "; "), "")
| project UPN, YearMonth, TotalSessions, OffHoursSessions, OffHoursPct,
          DistinctCountries, AnomalousCountryCount, AnomalousCountries
| order by UPN asc, YearMonth asc


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B5 — ai_client_channel.csv (Browser / Desktop / API split)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_client_channel.csv
// Schema: AISite, Channel, YearMonth, EventCount

let AIDomains = dynamic([
    "copilot.microsoft.com", "copilot.cloud.microsoft", "m365.cloud.microsoft",
    "chat.openai.com", "chatgpt.com", "api.openai.com", "openai.com", "labs.openai.com",
    "claude.ai", "api.anthropic.com", "anthropic.com",
    "gemini.google.com", "bard.google.com",
    "perplexity.ai", "poe.com", "mistral.ai", "chat.mistral.ai",
    "deepseek.com", "chat.deepseek.com", "grok.com", "x.ai", "meta.ai",
    "cursor.com", "cursor.sh", "codeium.com", "windsurf.com", "tabnine.com",
    "replit.com", "sourcegraph.com",
    "grammarly.com", "notion.so", "jasper.ai", "copy.ai", "writesonic.com",
    "otter.ai", "fireflies.ai", "gamma.app",
    "midjourney.com", "firefly.adobe.com", "stability.ai", "stablediffusionweb.com",
    "leonardo.ai", "canva.com", "app.synthesia.io", "runwayml.com", "pika.art",
    "ideogram.ai", "heygen.com", "elevenlabs.io", "descript.com", "lumalabs.ai",
    "glean.com", "you.com",
    "huggingface.co", "replicate.com", "cohere.com", "character.ai"
]);
DeviceNetworkEvents
| where Timestamp > ago(30d)
| where ActionType == "ConnectionSuccess"
| where RemoteUrl has_any (AIDomains)
| extend AISite = case(
    RemoteUrl has "copilot.microsoft.com" or RemoteUrl has "copilot.cloud.microsoft", "copilot.microsoft.com",
    RemoteUrl has "chat.openai.com" or RemoteUrl has "chatgpt.com", "chatgpt.com",
    RemoteUrl has "api.openai.com", "api.openai.com",
    RemoteUrl has "claude.ai", "claude.ai",
    RemoteUrl has "api.anthropic.com", "api.anthropic.com",
    RemoteUrl has "gemini.google.com" or RemoteUrl has "bard.google.com", "gemini.google.com",
    RemoteUrl has "perplexity.ai", "perplexity.ai",
    RemoteUrl has "midjourney.com", "midjourney.com",
    RemoteUrl has "huggingface.co", "huggingface.co",
    RemoteUrl has "stability.ai", "stability.ai",
    RemoteUrl has "jasper.ai", "jasper.ai",
    RemoteUrl has "grammarly.com", "grammarly.com",
    RemoteUrl has "notion.so", "notion.so",
    RemoteUrl has "firefly.adobe.com", "firefly.adobe.com",
    RemoteUrl has "runwayml.com", "runwayml.com",
    RemoteUrl has "canva.com", "canva.com",
    RemoteUrl has "app.synthesia.io", "app.synthesia.io",
    RemoteUrl
)
| extend Channel = case(
    InitiatingProcessFileName has_any ("chrome.exe", "msedge.exe", "firefox.exe",
        "brave.exe", "safari", "opera.exe", "iexplore.exe",
        "Chrome", "Safari", "Firefox"), "Browser",
    InitiatingProcessFileName has_any ("python", "node", "java", "curl",
        "powershell", "pwsh", "cmd.exe", "bash",
        "dotnet", "go"), "API",
    "Desktop"
)
| extend YearMonth = format_datetime(Timestamp, "yyyy-MM")
| summarize EventCount = count() by AISite, Channel, YearMonth
| order by AISite asc, YearMonth asc, Channel asc


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B6 — ai_appgov_alerts.csv (App Governance ML Alerts)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_appgov_alerts.csv
// Schema: Timestamp, YearMonth, UPN, AppName, AlertType, Severity, Description
//
// Requires: Microsoft Defender for Cloud Apps + App Governance enabled
// NOTE: If AlertInfo/AlertEvidence tables exist but return 0 rows, it means
//       your tenant has Defender XDR alerts but NOT MDA App Governance alerts.
//       The ServiceSource filter below is correct — only MDA-sourced alerts are
//       relevant for OAuth/app anomaly detection.
//       Tenants without MDA App Governance: use the header-only stub CSV.
//       See: https://learn.microsoft.com/defender-cloud-apps/app-governance-manage-app-governance
//
// FIXES applied (validated June 2026 against live tenant):
//   1. AdditionalFields.AppName: Sentinel workspace stores AdditionalFields as
//      type string, not dynamic. Wrapped with parse_json(tostring(...)) to
//      support both Sentinel and native Defender XDR Advanced Hunting.
//      Sentinel:        tostring(parse_json(tostring(AdditionalFields)).AppName)
//      Native Def XDR:  tostring(AdditionalFields.AppName)  [also works]
//   2. Validated: query runs cleanly with 0 rows on non-MDA tenants (correct).

let AIAppNames = dynamic([
    "Microsoft 365 Copilot", "Microsoft Copilot", "Copilot", "GitHub Copilot",
    "Copilot Studio", "Security Copilot", "Bing Chat", "Bing Chat Enterprise",
    "ChatGPT", "ChatGPT Enterprise", "OpenAI",
    "Claude", "Anthropic", "Gemini", "Google Gemini", "Bard",
    "Perplexity", "Poe", "Mistral", "Le Chat", "DeepSeek", "Grok",
    "Meta AI", "Llama",
    "Cursor", "Codeium", "Windsurf", "Tabnine", "Amazon Q", "CodeWhisperer",
    "Replit", "Cody", "JetBrains AI",
    "Grammarly", "Notion", "Notion AI", "Jasper", "Copy.ai", "Writesonic",
    "Otter.ai", "Fireflies", "Gamma",
    "Midjourney", "DALL-E", "Adobe Firefly", "Firefly", "Stable Diffusion",
    "Stability", "Leonardo.Ai", "Canva", "Synthesia", "Runway", "Pika",
    "Sora", "Ideogram", "HeyGen", "ElevenLabs", "Descript", "Luma",
    "Glean", "You.com", "Hugging Face", "Replicate", "Cohere",
    "Character.AI", "Harvey", "Hebbia"
]);
AlertInfo
| where Timestamp > ago(30d)
| where ServiceSource == "Microsoft Cloud App Security"
    or ServiceSource == "Microsoft Defender for Cloud Apps"
| join kind=inner (
    AlertEvidence
    | where Timestamp > ago(30d)
    | where EntityType == "User" or EntityType == "CloudApplication"
    | extend EvidenceDetail = case(
        EntityType == "User", AccountUpn,
        EntityType == "CloudApplication",
            tostring(parse_json(tostring(AdditionalFields)).AppName),  // FIX 1
        ""
    )
    | summarize
        Users = make_set_if(EvidenceDetail, EntityType == "User"),
        Apps  = make_set_if(EvidenceDetail, EntityType == "CloudApplication")
        by AlertId
) on AlertId
| where Apps has_any (AIAppNames)
| mv-expand UPN = Users to typeof(string)
| mv-expand AppName = Apps to typeof(string)
| where AppName has_any (AIAppNames)
| extend YearMonth = format_datetime(Timestamp, "yyyy-MM")
| project Timestamp = format_datetime(Timestamp, "yyyy-MM-dd HH:mm:ss"),
          YearMonth,
          UPN = tolower(UPN),
          AppName,
          AlertType = Category,
          Severity,
          Description = Title
| order by Timestamp asc


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B7 — ai_cloud_discovery.csv (PowerShell reshape)
// ─────────────────────────────────────────────────────────────────────────────
// This is NOT a KQL query — it's a PowerShell script to reshape the manual
// Cloud Discovery export from the MDA portal.
//
// Steps:
//   1. Go to https://security.microsoft.com → Cloud Apps → Cloud Discovery
//   2. Click "Discovered Apps" → Filter: Category = "Generative AI"
//   3. Set time range to last 90 days
//   4. Click "Export" → download the CSV
//   5. Run the PowerShell below to reshape it
//
// Output: ai_cloud_discovery.csv
// Schema: AIDomain, AppCategory, YearMonth, RiskScore, UploadVolumeMB,
//         DownloadVolumeMB, TransactionCount, DistinctUsers, SanctionStatus
//
// <powershell>
// # Load the raw Cloud Discovery export
// $rawPath = "DiscoveredApps_export.csv"  # ← rename to your downloaded file
// $raw = Import-Csv $rawPath
//
// function Get-CloudDiscoveryValue {
//     param(
//         [Parameter(Mandatory)] [psobject]$Row,
//         [Parameter(Mandatory)] [string[]]$Names,
//         [object]$Default = ""
//     )
//     foreach ($name in $Names) {
//         $property = $Row.PSObject.Properties[$name]
//         if ($null -ne $property -and $null -ne $property.Value -and
//             -not [string]::IsNullOrWhiteSpace([string]$property.Value)) {
//             return $property.Value
//         }
//     }
//     return $Default
// }
//
// # Cloud Discovery exports are range aggregates unless the portal includes a
// # YearMonth/Month column. Stamp range aggregates with the collection month.
// $collectionMonth = (Get-Date).ToString("yyyy-MM")
//
// # Reshape to match expected schema
// $reshaped = $raw | ForEach-Object {
//     $yearMonth = Get-CloudDiscoveryValue -Row $_ -Names @("YearMonth", "Month") -Default $collectionMonth
//     [PSCustomObject]@{
//         AIDomain         = Get-CloudDiscoveryValue -Row $_ -Names @("App domain", "Domain")
//         AppCategory      = Get-CloudDiscoveryValue -Row $_ -Names @("Category", "App category")
//         YearMonth        = $yearMonth
//         RiskScore        = [int](Get-CloudDiscoveryValue -Row $_ -Names @("Score", "Risk score") -Default 0)
//         UploadVolumeMB   = [math]::Round([double](Get-CloudDiscoveryValue -Row $_ -Names @("Upload (bytes)", "Upload traffic (Bytes)") -Default 0) / 1MB, 2)
//         DownloadVolumeMB = [math]::Round([double](Get-CloudDiscoveryValue -Row $_ -Names @("Download (bytes)", "Download traffic (Bytes)") -Default 0) / 1MB, 2)
//         TransactionCount = [int](Get-CloudDiscoveryValue -Row $_ -Names @("Transactions", "Total transactions") -Default 0)
//         DistinctUsers    = [int](Get-CloudDiscoveryValue -Row $_ -Names @("Users", "Total users") -Default 0)
//         SanctionStatus   = Get-CloudDiscoveryValue -Row $_ -Names @("Tag", "Sanction status", "App status") -Default "Untagged"
//     }
// }
//
// $reshaped | Export-Csv -NoTypeInformation -Encoding UTF8 ai_cloud_discovery.csv
// Write-Host "Exported $($reshaped.Count) rows to ai_cloud_discovery.csv"
//
// </powershell>


// ─────────────────────────────────────────────────────────────────────────────
// SECTION B8 — ai_mda_sessions.csv (MDA Session Intelligence)
// ─────────────────────────────────────────────────────────────────────────────
// Run in: Defender XDR → Advanced Hunting
// Output: ai_mda_sessions.csv
// Schema: Timestamp, YearMonth, UPN, AppName, ActionType, PolicyHit,
//         PolicyAction, IPAddress, CountryCode, EventCount
//
// Requires: MDA + Conditional Access App Control policies configured
// This query captures session-level DLP/policy enforcement actions.
// Zero rows = MDA CAAC not configured for AI apps (expected — use stub CSV).
//
// FIXES applied (validated June 2026 against live tenant):
//   1. UPN resolved via IdentityInfo lookup (same fix as B2).
//      AccountObjectId is an Entra GUID, not a UPN. Rows with no IdentityInfo
//      match fall back to the raw GUID — those rows won't join EntraUsers.
//   2. Validated: RawEventData.PolicyName and RawEventData.CountryCode resolve
//      correctly in both Sentinel and native Defender XDR (RawEventData is
//      stored as dynamic in both schemas — no parse_json() needed here).
//   3. Validated: 0 rows on non-MDA tenants is correct behavior.

let AIAppNames = dynamic([
    "Microsoft 365 Copilot", "Microsoft Copilot", "Copilot", "GitHub Copilot",
    "Copilot Studio", "Security Copilot", "Bing Chat", "Bing Chat Enterprise",
    "ChatGPT", "ChatGPT Enterprise", "OpenAI",
    "Claude", "Anthropic", "Gemini", "Google Gemini", "Bard",
    "Perplexity", "Poe", "Mistral", "Le Chat", "DeepSeek", "Grok",
    "Meta AI", "Llama",
    "Cursor", "Codeium", "Windsurf", "Tabnine", "Amazon Q", "CodeWhisperer",
    "Replit", "Cody", "JetBrains AI",
    "Grammarly", "Notion", "Notion AI", "Jasper", "Copy.ai", "Writesonic",
    "Otter.ai", "Fireflies", "Gamma",
    "Midjourney", "DALL-E", "Adobe Firefly", "Firefly", "Stable Diffusion",
    "Stability", "Leonardo.Ai", "Canva", "Synthesia", "Runway", "Pika",
    "Sora", "Ideogram", "HeyGen", "ElevenLabs", "Descript", "Luma",
    "Glean", "You.com", "Hugging Face", "Replicate", "Cohere",
    "Character.AI", "Harvey", "Hebbia"
]);
// Resolve Entra ObjectId → UPN (same pattern as B2)
let UserUpns = IdentityInfo
    | summarize take_any(AccountUpn) by AccountObjectId;
CloudAppEvents
| where Timestamp > ago(30d)
| where Application has_any (AIAppNames)
| where ActionType in (
    "FileUploaded", "FileDownloaded", "FilePreviewed",
    "PasteAction", "PrintAction", "CopyAction",
    "SessionLogon", "SessionLogoff",
    "AppAccessBlocked", "FileBlocked", "UploadBlocked"
)
| lookup kind=leftouter UserUpns on AccountObjectId          // FIX 1
| extend UPN = tolower(coalesce(AccountUpn, AccountObjectId))
| extend YearMonth = format_datetime(Timestamp, "yyyy-MM")
| extend PolicyName = tostring(RawEventData.PolicyName)
| extend PolicyHit = iff(isnotempty(PolicyName), "TRUE", "FALSE")
| extend PolicyAction = case(
    ActionType has "Blocked", "Block",
    ActionType has "Warn" or ActionType has "Monitor" or PolicyHit == "TRUE", "Warn",
    "Allow"
)
| extend IPAddress = tostring(IPAddress)
| extend CountryCode = tostring(RawEventData.CountryCode)
| summarize
    EventCount = count(),
    Timestamp = min(Timestamp)
    by YearMonth, UPN,
       AppName = Application,
       ActionType,
       PolicyHit,
       PolicyAction,
       IPAddress,
       CountryCode
| extend Timestamp = format_datetime(Timestamp, "yyyy-MM-dd HH:mm:ss")
| project Timestamp, YearMonth, UPN, AppName, ActionType, PolicyHit,
          PolicyAction, IPAddress, CountryCode, EventCount
| order by Timestamp asc


// ═══════════════════════════════════════════════════════════════════════════════
// END OF QUERY PACK
// ═══════════════════════════════════════════════════════════════════════════════
//
// After collecting all CSVs, place them in a single folder and open the PBIT.
// When prompted for AI_Data_Folder_Path, enter your folder path with trailing
// slash (e.g. C:\AI_Usage_Data\ or /Users/yourname/AI_Usage_Data/).
//
// See INSTRUCTIONS_v26.md for the complete setup guide.
// ═══════════════════════════════════════════════════════════════════════════════