Build an Enterprise DLP Dashboard & Compliance Program

// WHAT: Count DLP policy matches grouped by workload (Exchange, SharePoint,
//       OneDrive, Teams, Endpoints) for the executive summary tile.
// WHY:  Shows the CISO which workloads generate the most DLP activity,
//       revealing where sensitive data flows most frequently and where
//       additional policy tuning or user training is needed.
// PERIOD: Last 30 days for monthly executive reporting.
CloudAppEvents
| where Timestamp > ago(30d)
| where ActionType == "DlpRuleMatch"
| extend Workload = tostring(RawEventData.Workload)
| summarize
    TotalMatches   = count(),
    UniqueUsers    = dcount(tostring(RawEventData.UserId)),
    UniquePolicies = dcount(tostring(RawEventData.PolicyName))
    by Workload
| sort by TotalMatches desc

// WHAT: Break down DLP matches by enforcement action (Block, Warn, Audit)
//       and severity level for the operations dashboard.
// WHY:  A high ratio of "Audit" to "Block" means policies are detecting
//       but not preventing data loss. Executives need to see the
//       enforcement posture - not just detection volume.
CloudAppEvents
| where Timestamp > ago(30d)
| where ActionType == "DlpRuleMatch"
| extend
    PolicyAction = tostring(RawEventData.PolicyAction),
    Severity     = tostring(RawEventData.Severity),
    Workload     = tostring(RawEventData.Workload)
| summarize MatchCount = count() by PolicyAction, Severity
| sort by MatchCount desc

// WHAT: Identify the top 10 users generating the most DLP policy matches.
// WHY:  Repeat offenders may need targeted training, while sudden spikes
//       from a single user could indicate compromise or insider threat.
CloudAppEvents
| where Timestamp > ago(30d)
| where ActionType == "DlpRuleMatch"
| extend UserPrincipalName = tostring(RawEventData.UserId)
| summarize
    ViolationCount = count(),
    Workloads      = make_set(tostring(RawEventData.Workload)),
    Policies       = make_set(tostring(RawEventData.PolicyName)),
    Overrides      = countif(tostring(RawEventData.PolicyAction) == "Override")
    by UserPrincipalName
| top 10 by ViolationCount desc

# -------------------------------------------------------------------
# WHAT: Audit all DLP compliance policies and rules, then export a
#       comprehensive inventory to CSV for gap analysis.
# WHY:  You cannot unify policies if you do not know what exists.
#       This script creates a single source of truth showing:
#       - Policy name, mode, and priority
#       - Targeted workloads (Exchange, SPO, ODB, Teams, Endpoints)
#       - Sensitive information types and thresholds
#       - Enforcement actions per rule
# PREREQ: Connect-IPPSSession (Security & Compliance PowerShell)
# -------------------------------------------------------------------

# Connect to Security & Compliance PowerShell
Connect-IPPSSession

# Get all DLP policies with their configuration
$policies = Get-DlpCompliancePolicy | Select-Object \
    Name, Mode, Priority, Enabled, CreatedBy, WhenCreated,
    @{N='ExchangeLocation';    E={($_.ExchangeLocation    | Measure-Object).Count}},
    @{N='SharePointLocation';  E={($_.SharePointLocation  | Measure-Object).Count}},
    @{N='OneDriveLocation';    E={($_.OneDriveLocation    | Measure-Object).Count}},
    @{N='TeamsLocation';       E={($_.TeamsLocation       | Measure-Object).Count}},
    @{N='EndpointLocation';    E={($_.EndpointDlpLocation | Measure-Object).Count}},
    @{N='PowerBILocation';     E={($_.PowerBIDlpLocation  | Measure-Object).Count}}

# Export policy summary
$policies | Export-Csv -Path "DLP-Policy-Inventory.csv" -NoTypeInformation
Write-Host "[EXPORT] Policy inventory: DLP-Policy-Inventory.csv" -ForegroundColor Green
Write-Host "[TOTAL]  Policies found: $($policies.Count)" -ForegroundColor Cyan

# Get detailed rules for each policy
$allRules = foreach ($policy in $policies) {
    Get-DlpComplianceRule -Policy $policy.Name | Select-Object \
        Name, ParentPolicyName, Priority, Disabled,
        @{N='ContentContainsSIT'; E={
            ($_.ContentContainsSensitiveInformation |
             ForEach-Object { $_.name }) -join "; "
        }},
        @{N='Actions'; E={
            $actions = @()
            if ($_.BlockAccess)        { $actions += "Block" }
            if ($_.NotifyUser)         { $actions += "Notify" }
            if ($_.GenerateIncidentReport) { $actions += "Report" }
            $actions -join "; "
        }}
}
$allRules | Export-Csv -Path "DLP-Rules-Detail.csv" -NoTypeInformation
Write-Host "[EXPORT] Rule details: DLP-Rules-Detail.csv" -ForegroundColor Green
Write-Host "[TOTAL]  Rules found: $($allRules.Count)" -ForegroundColor Cyan

# Coverage gap analysis
Write-Host "`n--- Coverage Gap Analysis ---" -ForegroundColor Yellow
$policies | ForEach-Object {
    $gaps = @()
    if ($_.ExchangeLocation   -eq 0) { $gaps += "Exchange" }
    if ($_.SharePointLocation -eq 0) { $gaps += "SharePoint" }
    if ($_.OneDriveLocation   -eq 0) { $gaps += "OneDrive" }
    if ($_.TeamsLocation      -eq 0) { $gaps += "Teams" }
    if ($_.EndpointLocation   -eq 0) { $gaps += "Endpoints" }
    if ($gaps.Count -gt 0) {
        Write-Host "  $($_.Name): Missing coverage for $($gaps -join ', ')" -ForegroundColor Red
    }
}

// WHAT: Track weekly DLP violation trends to measure whether user behaviour
//       is improving after DLP deployment and training.
// WHY:  A declining violation trend proves DLP ROI to executives.
//       A flat or rising trend signals that policy tips are ineffective
//       or training is insufficient - actionable insight for the DLP team.
// PERIOD: Last 90 days, aggregated by week for trend visibility.
CloudAppEvents
| where Timestamp > ago(90d)
| where ActionType == "DlpRuleMatch"
| extend
    WeekStart     = startofweek(Timestamp),
    PolicyAction  = tostring(RawEventData.PolicyAction),
    User          = tostring(RawEventData.UserId)
| summarize
    TotalViolations = count(),
    UniqueUsers     = dcount(User),
    BlockedCount    = countif(PolicyAction == "Block"),
    WarnedCount     = countif(PolicyAction == "Warn"),
    OverrideCount   = countif(PolicyAction == "Override"),
    AuditOnlyCount  = countif(PolicyAction == "Audit")
    by WeekStart
| extend
    OverrideRate = round(100.0 * OverrideCount / TotalViolations, 1),
    BlockRate    = round(100.0 * BlockedCount / TotalViolations, 1)
| sort by WeekStart asc

// WHAT: Find users with 5+ DLP violations in the last 30 days who may
//       need targeted data handling training or manager escalation.
// WHY:  80% of DLP violations typically come from 20% of users. Targeted
//       training for repeat offenders is more effective than org-wide campaigns.
CloudAppEvents
| where Timestamp > ago(30d)
| where ActionType == "DlpRuleMatch"
| extend User = tostring(RawEventData.UserId)
| summarize
    ViolationCount = count(),
    Overrides      = countif(tostring(RawEventData.PolicyAction) == "Override"),
    Workloads      = make_set(tostring(RawEventData.Workload)),
    DataTypes      = make_set(tostring(RawEventData.SensitiveInfoTypeName), 5),
    LastViolation  = max(Timestamp)
    by User
| where ViolationCount >= 5
| extend OverrideRate = round(100.0 * Overrides / ViolationCount, 1)
| sort by ViolationCount desc

// WHAT: Compute all core DLP KPIs in a single query for the monthly
//       executive dashboard: false positive rate, override rate,
//       mean time to investigate (MTTI), and enforcement distribution.
// WHY:  A single query that produces all executive metrics eliminates
//       manual data collection and ensures consistent calculations.
// PERIOD: Last 30 days.
// --- KPI 1-3: Detection, override, and enforcement metrics ---
let kpiMetrics = CloudAppEvents
| where Timestamp > ago(30d)
| where ActionType == "DlpRuleMatch"
| extend
    PolicyAction = tostring(RawEventData.PolicyAction),
    Workload     = tostring(RawEventData.Workload)
| summarize
    TotalMatches     = count(),
    BlockedCount     = countif(PolicyAction == "Block"),
    WarnedCount      = countif(PolicyAction == "Warn"),
    AuditedCount     = countif(PolicyAction == "Audit"),
    OverrideCount    = countif(PolicyAction == "Override"),
    UniqueWorkloads  = dcount(Workload),
    UniqueUsers      = dcount(tostring(RawEventData.UserId))
| extend
    OverrideRate_Pct = round(100.0 * OverrideCount / TotalMatches, 1),
    BlockRate_Pct    = round(100.0 * BlockedCount / TotalMatches, 1),
    WarnRate_Pct     = round(100.0 * WarnedCount / TotalMatches, 1),
    AuditRate_Pct    = round(100.0 * AuditedCount / TotalMatches, 1);
kpiMetrics;
// --- KPI 4: Mean Time to Investigate (MTTI) ---
// Join DLP alerts with their resolution timestamps
AlertInfo
| where Timestamp > ago(30d)
| where ServiceSource == "Microsoft Data Loss Prevention"
| join kind=leftouter (
    AlertInfo
    | where Timestamp > ago(30d)
    | where ServiceSource == "Microsoft Data Loss Prevention"
    | project AlertId, ResolvedTime = Timestamp
) on AlertId
| extend InvestigationMinutes = datetime_diff('minute', ResolvedTime, Timestamp)
| where InvestigationMinutes > 0
| summarize
    MTTI_Minutes_Avg    = round(avg(InvestigationMinutes), 0),
    MTTI_Minutes_Median = round(percentile(InvestigationMinutes, 50), 0),
    MTTI_Minutes_P95    = round(percentile(InvestigationMinutes, 95), 0),
    TotalAlerts         = count()

# -------------------------------------------------------------------
# WHAT: Generate a PCI-DSS compliance evidence package from DLP policies,
#       including policy config, detection stats, and enforcement proof.
# WHY:  PCI-DSS Requirement 3.4 mandates rendering PAN unreadable;
#       Requirement 4.1 mandates encryption in transit. DLP policies
#       that detect and block cleartext PAN prove compliance.
# PREREQ: Connect-IPPSSession, appropriate Compliance Admin role.
# OUTPUT: Folder with CSV and JSON files ready for QSA review.
# -------------------------------------------------------------------

Connect-IPPSSession

$evidenceDir = "PCI-DSS-Evidence-$(Get-Date -Format 'yyyy-MM')" 
New-Item -ItemType Directory -Path $evidenceDir -Force | Out-Null

# --- Evidence 1: DLP policies targeting credit card / PAN data ---
$pciPolicies = Get-DlpCompliancePolicy | Where-Object { $_.Enabled -eq $true }
$pciRules = foreach ($pol in $pciPolicies) {
    Get-DlpComplianceRule -Policy $pol.Name | Where-Object {
        $_.ContentContainsSensitiveInformation -match "Credit Card|PAN"
    } | Select-Object \
        Name, ParentPolicyName, Disabled,
        @{N='SensitiveTypes'; E={
            ($_.ContentContainsSensitiveInformation |
             ForEach-Object { $_.name }) -join "; "
        }},
        @{N='BlockAccess';  E={ $_.BlockAccess }},
        @{N='NotifyUser';   E={ $_.NotifyUser }},
        @{N='MinCount';     E={
            ($_.ContentContainsSensitiveInformation |
             ForEach-Object { $_.mincount }) -join "; "
        }}
}
$pciRules | Export-Csv "$evidenceDir\PCI-DLP-Rules.csv" -NoTypeInformation

# --- Evidence 2: Policy configuration snapshot (JSON) ---
foreach ($pol in $pciPolicies) {
    $config = @{
        PolicyName   = $pol.Name
        Mode         = $pol.Mode
        Priority     = $pol.Priority
        Workloads    = @{
            Exchange   = ($pol.ExchangeLocation   | Measure-Object).Count -gt 0
            SharePoint = ($pol.SharePointLocation | Measure-Object).Count -gt 0
            OneDrive   = ($pol.OneDriveLocation   | Measure-Object).Count -gt 0
            Teams      = ($pol.TeamsLocation      | Measure-Object).Count -gt 0
            Endpoints  = ($pol.EndpointDlpLocation | Measure-Object).Count -gt 0
        }
        ExportDate   = (Get-Date -Format "o")
    }
    $config | ConvertTo-Json -Depth 3 |
        Out-File "$evidenceDir\Policy-$($pol.Name -replace '[^a-zA-Z0-9]','-').json"
}

Write-Host "[PCI-DSS] Evidence package exported to: $evidenceDir" -ForegroundColor Green
Write-Host "  - PCI-DLP-Rules.csv: $($pciRules.Count) rules" -ForegroundColor Cyan
Write-Host "  - Policy JSON configs: $($pciPolicies.Count) policies" -ForegroundColor Cyan

// WHAT: Generate a GDPR compliance report showing all DLP detections
//       of EU personal data types with enforcement actions taken.
// WHY:  GDPR Article 5(1)(f) and Article 32 require documented evidence
//       of technical measures protecting personal data. This query
//       proves that personal data is being detected and controlled.
// PERIOD: Last 90 days for quarterly DPA (Data Protection Authority) reporting.
CloudAppEvents
| where Timestamp > ago(90d)
| where ActionType == "DlpRuleMatch"
| extend
    SensitiveType = tostring(RawEventData.SensitiveInfoTypeName),
    PolicyAction  = tostring(RawEventData.PolicyAction),
    Workload      = tostring(RawEventData.Workload),
    User          = tostring(RawEventData.UserId)
// Filter for EU/GDPR-relevant sensitive information types
| where SensitiveType has_any (
    "EU", "National", "Passport", "IBAN", "Tax",
    "Health", "Social Security", "Driver", "Personal")
| summarize
    Detections      = count(),
    BlockedCount    = countif(PolicyAction == "Block"),
    WarnedCount     = countif(PolicyAction == "Warn"),
    AuditedCount    = countif(PolicyAction == "Audit"),
    AffectedUsers   = dcount(User)
    by SensitiveType, Workload
| extend ProtectionRate = round(100.0 * BlockedCount / Detections, 1)
| sort by Detections desc

# -------------------------------------------------------------------
# WHAT: Export DLP activity data from Microsoft Graph API to CSV
#       for Power BI dashboard ingestion.
# WHY:  Power BI cannot natively query advanced hunting. By exporting
#       data to CSV on a schedule (Task Scheduler / Azure Automation),
#       you create an automated pipeline that keeps dashboards current.
# PREREQ: App registration with Reports.Read.All and
#         SecurityEvents.Read.All permissions (application type).
# -------------------------------------------------------------------

# App registration credentials (store securely in Key Vault for production)
$tenantId     = "<your-tenant-id>"
$clientId     = "<your-client-id>"
$clientSecret = "<your-client-secret>"

# Authenticate to Microsoft Graph
$body = @{
    grant_type    = "client_credentials"
    client_id     = $clientId
    client_secret = $clientSecret
    scope         = "https://graph.microsoft.com/.default"
}
$token = (Invoke-RestMethod `
    -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
    -Method Post -Body $body).access_token

$headers = @{ Authorization = "Bearer $token" }

# --- Export DLP alerts from Security API ---
$dlpAlerts = Invoke-RestMethod `
    -Uri "https://graph.microsoft.com/v1.0/security/alerts_v2?`$filter=serviceSource eq 'microsoftDataLossPrevention'&`$top=500" `
    -Headers $headers

# Transform to flat CSV for Power BI
$exportData = $dlpAlerts.value | ForEach-Object {
    [PSCustomObject]@{
        AlertId      = $_.id
        Title        = $_.title
        Severity     = $_.severity
        Status       = $_.status
        CreatedDate  = $_.createdDateTime
        Category     = $_.category
        UserAccount  = ($_.evidence | Where-Object {
            $_.'@odata.type' -eq '#microsoft.graph.security.userEvidence'
        } | Select-Object -First 1).userAccount.accountName
        DetectionSource = $_.detectionSource
    }
}

$outputPath = "DLP-PowerBI-Export-$(Get-Date -Format 'yyyy-MM-dd').csv"
$exportData | Export-Csv -Path $outputPath -NoTypeInformation
Write-Host "[EXPORT] Power BI data: $outputPath ($($exportData.Count) alerts)" -ForegroundColor Green
Write-Host "[NEXT] Import CSV into Power BI Desktop > Get Data > Text/CSV" -ForegroundColor Yellow

# -------------------------------------------------------------------
# WHAT: Audit DLP policy age and identify policies overdue for review.
# WHY:  Policies older than 90 days without review may have drifted
#       from current data patterns, generating false positives or
#       missing new sensitive data types.
# PREREQ: Connect-IPPSSession
# -------------------------------------------------------------------

Connect-IPPSSession

$reviewThresholdDays = 90
$today = Get-Date

Get-DlpCompliancePolicy | ForEach-Object {
    $ageDays = ($today - $_.WhenCreated).Days
    $lastModifiedDays = if ($_.WhenChanged) {
        ($today - $_.WhenChanged).Days
    } else { $ageDays }

    [PSCustomObject]@{
        PolicyName     = $_.Name
        Mode           = $_.Mode
        Enabled        = $_.Enabled
        Created        = $_.WhenCreated.ToString("yyyy-MM-dd")
        LastModified   = if ($_.WhenChanged) { $_.WhenChanged.ToString("yyyy-MM-dd") } else { "Never" }
        AgeDays        = $ageDays
        DaysSinceReview = $lastModifiedDays
        ReviewStatus   = if ($lastModifiedDays -gt $reviewThresholdDays) {
            "OVERDUE"
        } elseif ($lastModifiedDays -gt 60) {
            "Due Soon"
        } else {
            "Current"
        }
    }
} | Sort-Object DaysSinceReview -Descending |
    Format-Table PolicyName, Mode, AgeDays, DaysSinceReview, ReviewStatus -AutoSize

// WHAT: Generate quarterly DLP metrics with period-over-period comparison
//       for the data protection committee review meeting.
// WHY:  Trend comparison is the most powerful metric for executives  - 
//       showing improvement (or regression) quarter-over-quarter makes
//       the value of DLP investment concrete and actionable.
// PERIOD: Current quarter vs. previous quarter.
let currentQStart = startofmonth(ago(90d));
let prevQStart    = startofmonth(ago(180d));
let currentQEnd   = now();
let prevQEnd      = currentQStart;
// Current quarter metrics
let currentQ = CloudAppEvents
| where Timestamp between (currentQStart .. currentQEnd)
| where ActionType == "DlpRuleMatch"
| extend PolicyAction = tostring(RawEventData.PolicyAction)
| summarize
    CQ_Total      = count(),
    CQ_Blocked    = countif(PolicyAction == "Block"),
    CQ_Warned     = countif(PolicyAction == "Warn"),
    CQ_Override   = countif(PolicyAction == "Override"),
    CQ_Users      = dcount(tostring(RawEventData.UserId)),
    CQ_Workloads  = dcount(tostring(RawEventData.Workload));
// Previous quarter metrics
let prevQ = CloudAppEvents
| where Timestamp between (prevQStart .. prevQEnd)
| where ActionType == "DlpRuleMatch"
| extend PolicyAction = tostring(RawEventData.PolicyAction)
| summarize
    PQ_Total      = count(),
    PQ_Blocked    = countif(PolicyAction == "Block"),
    PQ_Warned     = countif(PolicyAction == "Warn"),
    PQ_Override   = countif(PolicyAction == "Override"),
    PQ_Users      = dcount(tostring(RawEventData.UserId)),
    PQ_Workloads  = dcount(tostring(RawEventData.Workload));
// Combine and calculate deltas
currentQ | extend dummy = 1
| join kind=inner (prevQ | extend dummy = 1) on dummy
| project
    CurrentQ_Violations  = CQ_Total,
    PreviousQ_Violations = PQ_Total,
    Violation_Delta_Pct  = round(100.0 * (CQ_Total - PQ_Total) / PQ_Total, 1),
    CurrentQ_OverrideRate  = round(100.0 * CQ_Override / CQ_Total, 1),
    PreviousQ_OverrideRate = round(100.0 * PQ_Override / PQ_Total, 1),
    CurrentQ_UniqueUsers   = CQ_Users,
    PreviousQ_UniqueUsers  = PQ_Users,
    CurrentQ_Workloads     = CQ_Workloads