Build an Identity Security Posture Dashboard

// WHAT: MFA coverage analysis - registration rate, usage rate, and method breakdown
// WHY: Measures three critical MFA dimensions:
//   1. Registration rate: % of users who HAVE MFA registered (can they use it?)
//   2. Usage rate: % of sign-ins that REQUIRED MFA (are policies enforcing it?)
//   3. Method breakdown: which MFA methods are most used (passwordless maturity)
// TABLES: AADSignInEventsBeta, IdentityInfo
// OUTPUT: Overall MFA coverage metrics for dashboard tiles
// --- Tile 1: MFA Usage Rate (last 30 days) ---
let mfaSignIns = AADSignInEventsBeta
    | where Timestamp > ago(30d)
    | where ErrorCode == 0  // successful sign-ins only
    | where AuthenticationRequirement == "multiFactorAuthentication"
    | summarize MFACount = count();
let totalSignIns = AADSignInEventsBeta
    | where Timestamp > ago(30d)
    | where ErrorCode == 0
    | summarize TotalCount = count();
print MFAUsageRate = round(todouble(toscalar(mfaSignIns)) / todouble(toscalar(totalSignIns)) * 100, 2)

// WHAT: MFA method distribution - which authentication methods are users actually using
// WHY: Tracks passwordless maturity. The goal is to shift from SMS/phone call
//      (weakest MFA) to Authenticator push notifications to FIDO2/Windows Hello
//      (strongest, phishing-resistant). This breakdown shows your current state.
// TABLE: AADSignInEventsBeta
// KEY FIELD: AuthenticationMethodsUsed - array of methods used during sign-in
//   Common values: "Mobile app notification", "Mobile app verification code",
//   "FIDO2 security key", "Windows Hello for Business", "SMS", "Phone call"
// OUTPUT: Method popularity ranking with user counts
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0
| where AuthenticationRequirement == "multiFactorAuthentication"
| mv-expand Method = parse_json(AuthenticationMethodsUsed)
| extend MethodName = tostring(Method)
| where isnotempty(MethodName)
| summarize UsageCount = count(), UniqueUsers = dcount(AccountUpn) by MethodName
| extend MethodStrength = case(
    MethodName in ("FIDO2 security key", "Windows Hello for Business"), "Phishing-Resistant",
    MethodName == "Mobile app notification", "Strong",
    MethodName == "Mobile app verification code", "Standard",
    MethodName in ("SMS", "Phone call"), "Weak",
    "Other")
| order by UsageCount desc

// WHAT: Conditional Access coverage gap analysis
// WHY: Identifies sign-ins that were NOT evaluated by any CA policy.
//      These represent unprotected sign-ins where no MFA, device compliance,
//      or risk-based controls were applied.
// TABLE: AADSignInEventsBeta
// HOW: Parses the ConditionalAccessPolicies JSON to find sign-ins where
//      every policy result was "notApplied" (no policy matched)
// OUTPUT: Top 20 apps with the most unprotected sign-ins
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where ErrorCode == 0  // successful sign-ins only
| extend CAPolicies = parse_json(ConditionalAccessPolicies)
| extend PolicyCount = array_length(CAPolicies)
| extend AppliedPolicies = countof(tostring(CAPolicies), '"success"')
            + countof(tostring(CAPolicies), '"failure"')
| where AppliedPolicies == 0  // no policy applied (all "notApplied" or empty)
| summarize UncoveredSignIns = count(), UniqueUsers = dcount(AccountUpn)
    by Application
| order by UncoveredSignIns desc
| take 20

// WHAT: Identify sign-ins using legacy authentication protocols over the past 30 days
// WHY: Legacy auth protocols (POP3, IMAP, SMTP, ActiveSync, MAPI) do NOT support MFA,
//      making them a prime target for password spray and brute force attacks.
//      Identifying these helps plan migration to modern auth before blocking.
// TABLE: AADSignInEventsBeta - Entra ID sign-in logs
// KEY FIELD: ClientAppUsed - the authentication client/protocol used:
//   "Browser"                     = modern auth (web browser)
//   "Mobile Apps and Desktop clients" = modern auth (rich client)
//   "Exchange ActiveSync"         = legacy protocol (mobile email)
//   "IMAP4" / "POP3" / "SMTP"     = legacy protocols (email clients)
//   "Other clients" / "MAPI"      = legacy protocols
// OUTPUT: Count of legacy sign-ins and unique users per protocol type
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients")
| summarize LegacySignIns = count(), UniqueUsers = dcount(AccountUpn) by ClientAppUsed
| order by LegacySignIns desc

// WHAT: Risky user trends over 30 days - daily risk detections and unique users affected
// WHY: Tracks whether your identity risk landscape is improving or deteriorating.
//      A decreasing trend indicates effective CA policies and security training.
//      An increasing trend may indicate a targeted campaign against your tenant.
// TABLE: AADSignInEventsBeta
// KEY FIELDS:
//   RiskLevelDuringSignIn - real-time risk: none | low | medium | high
//   RiskLevelAggregated - overall user risk at time of sign-in
// OUTPUT: Daily risk detection counts by severity for trend charting
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where RiskLevelDuringSignIn in ("low", "medium", "high")
| summarize
    TotalRiskySignIns = count(),
    HighRisk = countif(RiskLevelDuringSignIn == "high"),
    MediumRisk = countif(RiskLevelDuringSignIn == "medium"),
    LowRisk = countif(RiskLevelDuringSignIn == "low"),
    UniqueUsersAffected = dcount(AccountUpn)
    by bin(Timestamp, 1d)
| order by Timestamp asc

// WHAT: Auto-remediation effectiveness - CA policy remediation vs. manual admin action
// WHY: Measures what percentage of risky sign-ins are handled automatically by
//      CA policies (MFA challenge, block, password change) vs. requiring manual
//      admin investigation. Target: 95%+ auto-remediation.
// TABLE: AADSignInEventsBeta
// HOW: Sign-ins where CA policy fired (success or failure) = auto-handled.
//      Risk detections with no CA policy match = manual intervention needed.
// OUTPUT: Auto vs. manual remediation ratio
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where RiskLevelDuringSignIn in ("medium", "high")
| extend AutoRemediated = iff(ConditionalAccessStatus in ("success", "failure"), true, false)
| summarize
    Total = count(),
    AutoHandled = countif(AutoRemediated),
    ManualRequired = countif(not(AutoRemediated))
    by bin(Timestamp, 1w)
| extend AutoRemediationRate = round(todouble(AutoHandled) / todouble(Total) * 100, 1)
| order by Timestamp desc

// WHAT: Conditional Access effectiveness metrics for Sentinel workbook tile
// WHY: Provides a single dashboard view of CA policy performance:
//   - Total sign-ins evaluated by CA vs. not evaluated
//   - Block rate for high-risk sign-ins (should be ~100%)
//   - MFA challenge rate for medium-risk sign-ins
//   - Pass-through rate (sign-ins allowed without any control)
// TABLE: SigninLogs (Sentinel) or AADSignInEventsBeta (Defender XDR)
// NOTE: Use SigninLogs table if running in Sentinel workbook context
// OUTPUT: Weekly CA effectiveness summary for dashboard tile
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0 or ConditionalAccessStatus == "failure"  // include blocked sign-ins
| extend CAOutcome = case(
    ConditionalAccessStatus == "failure", "Blocked by CA",
    ConditionalAccessStatus == "success" and AuthenticationRequirement == "multiFactorAuthentication", "MFA Required",
    ConditionalAccessStatus == "success", "Allowed (controls met)",
    ConditionalAccessStatus == "notApplied", "No CA Policy",
    "Unknown")
| summarize Count = count() by CAOutcome, bin(Timestamp, 1w)
| order by Timestamp desc, Count desc

// WHAT: Legacy authentication block effectiveness - are legacy auth block policies working?
// WHY: Legacy auth protocols (IMAP, POP3, SMTP) cannot use MFA. Your CA policy
//      should be blocking 100% of legacy auth attempts. If any succeed, you have
//      a coverage gap that attackers can exploit for password spray attacks.
// OUTPUT: Legacy auth attempts that SUCCEEDED (these are the gaps to close)
AADSignInEventsBeta
| where Timestamp > ago(7d)
| where ClientAppUsed !in ("Browser", "Mobile Apps and Desktop clients")
| where ErrorCode == 0  // only successful (not blocked) legacy auth
| summarize SuccessfulLegacyAuth = count(), UniqueUsers = dcount(AccountUpn)
    by ClientAppUsed, Application
| order by SuccessfulLegacyAuth desc

// WHAT: Calculate the percentage of users authenticating with MFA
// WHY: MFA adoption rate is a critical identity security KPI.
//      Target: 99%+ of users should be using MFA regularly.
// HOW: Counts users whose sign-ins required MFA vs total user population
// KEY FIELD: AuthenticationRequirement - values:
//   "singleFactorAuthentication"    = password only (no MFA)
//   "multiFactorAuthentication"     = MFA was required and completed
// NOTE: This is a simplified calculation; some users may have MFA registered
//       but not triggered in the last 7 days. Check MFA registration separately.
let mfaUsers = AADSignInEventsBeta | where Timestamp > ago(7d) | where AuthenticationRequirement == "multiFactorAuthentication" | distinct AccountObjectId | count;
let totalUsers = IdentityInfo | distinct AccountObjectId | count;
print MFACoverage = todouble(mfaUsers) / todouble(totalUsers) * 100

# WHAT: Automated Identity Security Posture Report via Microsoft Graph PowerShell
# WHY: Generates a comprehensive HTML report with key identity KPIs:
#   - Total users and MFA-registered users (coverage %)
#   - Conditional Access policy inventory and status
#   - Current risky user count by risk level
#   - Identity Secure Score (current vs. max)
# SCHEDULE: Run weekly via Azure Automation or Task Scheduler
# SCOPES REQUIRED: Reports.Read.All, Policy.Read.All, IdentityRiskyUser.Read.All,
#                  SecurityEvents.Read.All, User.Read.All
# OUTPUT: HTML report saved to disk and optionally emailed

#Requires -Modules Microsoft.Graph.Reports, Microsoft.Graph.Identity.SignIns, Microsoft.Graph.Users

param(
    [string]$OutputPath = ".\IdentityPostureReport_$(Get-Date -Format 'yyyyMMdd').html",
    [string]$TenantName = "Contoso"
)

Connect-MgGraph -Scopes "Reports.Read.All","Policy.Read.All","IdentityRiskyUser.Read.All","User.Read.All" -NoWelcome

Write-Host "[1/5] Gathering user and MFA data..." -ForegroundColor Cyan
$allUsers = Get-MgUser -All -Property Id,UserPrincipalName,AccountEnabled |
    Where-Object { $_.AccountEnabled -eq $true }
$totalUsers = $allUsers.Count

# Get users with registered authentication methods (MFA-capable)
$mfaUsers = 0
foreach ($user in $allUsers | Select-Object -First 100) {  # Sample first 100 for performance
    $methods = Get-MgUserAuthenticationMethod -UserId $user.Id -ErrorAction SilentlyContinue
    if ($methods.Count -gt 1) { $mfaUsers++ }  # >1 means MFA method registered beyond password
}
$mfaRate = [math]::Round(($mfaUsers / [math]::Min(100, $totalUsers)) * 100, 1)

Write-Host "[2/5] Gathering Conditional Access policies..." -ForegroundColor Cyan
$caPolicies = Get-MgIdentityConditionalAccessPolicy -All
$caEnabled = ($caPolicies | Where-Object { $_.State -eq "enabled" }).Count
$caReportOnly = ($caPolicies | Where-Object { $_.State -eq "enabledForReportingButNotEnforced" }).Count
$caDisabled = ($caPolicies | Where-Object { $_.State -eq "disabled" }).Count

Write-Host "[3/5] Gathering risky user data..." -ForegroundColor Cyan
$riskyUsers = Get-MgRiskyUser -All
$highRisk = ($riskyUsers | Where-Object { $_.RiskLevel -eq "high" }).Count
$mediumRisk = ($riskyUsers | Where-Object { $_.RiskLevel -eq "medium" }).Count
$lowRisk = ($riskyUsers | Where-Object { $_.RiskLevel -eq "low" }).Count

Write-Host "[4/5] Building HTML report..." -ForegroundColor Cyan
$reportDate = Get-Date -Format "MMMM dd, yyyy"
$html = @"
<html><head><style>
  body { font-family: Segoe UI, sans-serif; max-width: 800px; margin: 2rem auto; }
  h1 { color: #2563eb; } h2 { color: #475569; border-bottom: 1px solid #e2e8f0; padding-bottom: .5rem; }
  .kpi { display: inline-block; padding: 1rem 2rem; margin: .5rem; border-radius: 8px; text-align: center; }
  .kpi-value { font-size: 2rem; font-weight: 700; } .kpi-label { font-size: .85rem; color: #64748b; }
  .green { background: #f0fdf4; color: #16a34a; } .red { background: #fef2f2; color: #dc2626; }
  .blue { background: #eff6ff; color: #2563eb; } .orange { background: #fff7ed; color: #ea580c; }
  table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
  th, td { padding: .5rem; text-align: left; border-bottom: 1px solid #e2e8f0; }
</style></head><body>
<h1>Identity Security Posture Report</h1>
<p>$TenantName | Generated: $reportDate</p>
<h2>Key Metrics</h2>
<div class="kpi green"><div class="kpi-value">${mfaRate}%</div><div class="kpi-label">MFA Coverage (sampled)</div></div>
<div class="kpi blue"><div class="kpi-value">$totalUsers</div><div class="kpi-label">Active Users</div></div>
<div class="kpi red"><div class="kpi-value">$highRisk</div><div class="kpi-label">High Risk Users</div></div>
<div class="kpi orange"><div class="kpi-value">$mediumRisk</div><div class="kpi-label">Medium Risk Users</div></div>
<h2>Conditional Access Policies</h2>
<table><tr><th>Status</th><th>Count</th></tr>
<tr><td>Enabled</td><td>$caEnabled</td></tr>
<tr><td>Report-only</td><td>$caReportOnly</td></tr>
<tr><td>Disabled</td><td>$caDisabled</td></tr></table>
<h2>Risky Users by Level</h2>
<table><tr><th>Risk Level</th><th>Count</th></tr>
<tr><td>High</td><td>$highRisk</td></tr>
<tr><td>Medium</td><td>$mediumRisk</td></tr>
<tr><td>Low</td><td>$lowRisk</td></tr></table>
</body></html>
"@

Write-Host "[5/5] Saving report to: $OutputPath" -ForegroundColor Green
$html | Out-File -FilePath $OutputPath -Encoding UTF8
Write-Host "Report generated successfully. Open in a browser to view." -ForegroundColor Green

// WHAT: Executive dashboard summary tile - all identity KPIs in one query
// WHY: Provides a single-pane-of-glass view for executive presentations.
//      Shows current values for every key identity security metric alongside
//      target thresholds, making it easy to identify gaps at a glance.
// TABLES: AADSignInEventsBeta, IdentityInfo
// OUTPUT: Single-row summary with all KPI values and RAG status
// --- Calculate all KPIs ---
let period = 7d;
let totalSignIns = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where ErrorCode == 0 | count);
let mfaSignIns = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where ErrorCode == 0 | where AuthenticationRequirement == "multiFactorAuthentication" | count);
let riskySignIns = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where RiskLevelDuringSignIn in ("medium","high") | count);
let blockedByCA = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where ConditionalAccessStatus == "failure" | count);
let legacyAuth = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where ErrorCode == 0 | where ClientAppUsed !in ("Browser","Mobile Apps and Desktop clients") | count);
let totalUsers = toscalar(IdentityInfo | distinct AccountObjectId | count);
let riskyUsers = toscalar(AADSignInEventsBeta | where Timestamp > ago(period) | where RiskLevelDuringSignIn in ("medium","high") | distinct AccountObjectId | count);
// --- Build summary ---
print
    MFA_Usage_Rate = strcat(round(todouble(mfaSignIns) / todouble(totalSignIns) * 100, 1), "%"),
    MFA_Target = "99%+",
    Legacy_Auth_SignIns = legacyAuth,
    Legacy_Auth_Target = "0",
    Risky_SignIns_7d = riskySignIns,
    CA_Blocks_7d = blockedByCA,
    Risky_Users_Active = riskyUsers,
    Total_Users = totalUsers,
    Risk_Exposure = strcat(round(todouble(riskyUsers) / todouble(totalUsers) * 100, 2), "%")