// ─────────────────────────────────────────────────────────────
// MDE Toolkit — DAX Measures for Power BI
// ─────────────────────────────────────────────────────────────
// Add each measure in Power BI: Modeling → New Measure, then paste the
// expression. The table is assumed to be named HealthReports.
//
// IMPORTANT — READ THIS FIRST
// ---------------------------
// The ingestion Function App writes ONE ROW PER REPORT, not one row per
// device. RowKey is "<Hostname>_<ReportId>" and the collector does not send a
// ReportId, so a fresh GUID is minted on every upload. A device reporting every
// 8 hours produces about 90 rows a month.
//
// That means COUNTROWS(HealthReports) counts *reports*, not devices, and any
// percentage divided by it is weighted by how chatty each device is. A single
// noisy device can dominate the whole fleet number.
//
// Every posture measure below therefore filters on IsLatestPerDevice = TRUE
// (a column added by HealthReports-Query.m) and counts DISTINCT hostnames.
// Measures intended to look across history are grouped at the bottom and
// clearly labelled.
//
// Counts are wrapped in COALESCE so that "none" shows as 0 rather than the
// "(Blank)" a card visual would otherwise display, which reads as missing data
// rather than as a real zero.
// ─────────────────────────────────────────────────────────────


// ══════════════════════════════════════════════════════════════
// BASE — everything else builds on these
// ══════════════════════════════════════════════════════════════

// Devices that have ever reported (denominator for every % measure)
//
// Deliberately NOT wrapped in COALESCE, unlike the counts below. This measure is
// also the value of the status donut, and a category chart drops a category when
// the measure is blank but keeps it when the measure is 0. Forcing 0 here makes
// statuses that no current device holds -- Critical, or a blank status from an
// old row -- appear in the legend with no slice. Blank is the honest answer for
// "no devices at all" anyway.
Device Count =
CALCULATE (
	DISTINCTCOUNT ( HealthReports[Hostname] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

// Total rows in the table — reports, not devices. Useful for sanity checks.
Report Count = COUNTROWS ( HealthReports )

// Uploads that arrived without a hostname. The Function App falls back to the
// literal "unknown", so these are real rows that cannot be attributed to a
// device and will inflate Device Count by exactly one. A non-zero value here is
// an ingestion problem, not a reporting one — see the README.
Unidentified Reports =
COALESCE (
	CALCULATE (
		COUNTROWS ( HealthReports ),
		HealthReports[Hostname] = "unknown"
	),
	0
)


// ══════════════════════════════════════════════════════════════
// LATEST VALUE PER DEVICE — use these to build a device table
// ══════════════════════════════════════════════════════════════
// Put Hostname on a table visual with these measures and you get exactly one
// row per device showing its most recent state. Dragging raw columns instead
// produces one row per *report*, which is why the same machine would otherwise
// appear a dozen times.

Latest Status =
CALCULATE (
	MAX ( HealthReports[OverallStatus] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Latest Health Score =
CALCULATE (
	MAX ( HealthReports[HealthScore] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Hours Since Last Report =
CALCULATE (
	MIN ( HealthReports[HoursSinceReport] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Latest Device Tag =
CALCULATE (
	MAX ( HealthReports[DeviceTag] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Latest Org Unit =
CALCULATE (
	MAX ( HealthReports[OrgUnit] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Latest OS Version =
CALCULATE (
	MAX ( HealthReports[OsVersion] ),
	HealthReports[IsLatestPerDevice] = TRUE
)


// ══════════════════════════════════════════════════════════════
// FLEET OVERVIEW
// ══════════════════════════════════════════════════════════════

// Average health score, one vote per device
Avg Health Score =
CALCULATE (
	AVERAGE ( HealthReports[HealthScore] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Healthy Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[HealthScore] >= 70
	),
	0
)

Healthy Device % = DIVIDE ( [Healthy Devices], [Device Count], 0 )

// Devices whose newest report is older than the staleness threshold
Stale Device Count =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[IsStale] = TRUE
	),
	0
)

Stale Device % = DIVIDE ( [Stale Device Count], [Device Count], 0 )


// ══════════════════════════════════════════════════════════════
// DEFENDER AV
// ══════════════════════════════════════════════════════════════

RTP Enabled Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[RealTimeProtectionEnabled] = TRUE
	),
	0
)

RTP Enabled % = DIVIDE ( [RTP Enabled Devices], [Device Count], 0 )

Tamper Protection Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[TamperProtectionEnabled] = TRUE
	),
	0
)

Tamper Protection % = DIVIDE ( [Tamper Protection Devices], [Device Count], 0 )

Behavior Monitoring Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[BehaviorMonitorEnabled] = TRUE
	),
	0
)

Behavior Monitoring % = DIVIDE ( [Behavior Monitoring Devices], [Device Count], 0 )

Avg Signature Age (hrs) =
CALCULATE (
	AVERAGE ( HealthReports[SignatureAgeHours] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Stale Signatures Count =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[SignatureAgeHours] > 48
	),
	0
)


// ══════════════════════════════════════════════════════════════
// MDE / SENSE
// ══════════════════════════════════════════════════════════════

Onboarded Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[IsOnboarded] = TRUE
	),
	0
)

MDE Onboarding % = DIVIDE ( [Onboarded Devices], [Device Count], 0 )

// Onboarded but the Sense service is not running — the interesting failure.
// A device that was never onboarded is a separate problem, counted above.
Sense Not Running =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[IsOnboarded] = TRUE,
		HealthReports[SenseIsRunning] = FALSE
	),
	0
)


// ══════════════════════════════════════════════════════════════
// ASR RULES
// ══════════════════════════════════════════════════════════════

ASR Configured Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[AsrIsConfigured] = TRUE
	),
	0
)

ASR Configured % = DIVIDE ( [ASR Configured Devices], [Device Count], 0 )

Avg ASR Block Rules =
CALCULATE (
	AVERAGE ( HealthReports[AsrBlockModeRules] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Avg ASR Audit Rules =
CALCULATE (
	AVERAGE ( HealthReports[AsrAuditModeRules] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

// Devices with rules sitting in Audit but nothing in Block — configured, but
// not actually stopping anything.
ASR Audit Only Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[AsrAuditModeRules] > 0,
		HealthReports[AsrBlockModeRules] = 0
	),
	0
)


// ══════════════════════════════════════════════════════════════
// FIREWALL
// ══════════════════════════════════════════════════════════════

All Firewall Profiles Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[AllFirewallProfilesEnabled] = TRUE
	),
	0
)

All Firewall Profiles % = DIVIDE ( [All Firewall Profiles Devices], [Device Count], 0 )

Firewall Gaps = [Device Count] - [All Firewall Profiles Devices]


// ══════════════════════════════════════════════════════════════
// APP CONTROL (WDAC) & DEVICE CONTROL
// ══════════════════════════════════════════════════════════════

App Control Enabled Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[AppControlIsEnabled] = TRUE
	),
	0
)

App Control Enabled % = DIVIDE ( [App Control Enabled Devices], [Device Count], 0 )

// Policies deployed but not enforcing — audit mode or not yet applied
App Control Not Enforcing =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[AppControlIsConfigured] = TRUE,
		HealthReports[AppControlIsEnabled] = FALSE
	),
	0
)

Device Control Enabled Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[DeviceControlEnabled] = TRUE
	),
	0
)

Device Control Enabled % = DIVIDE ( [Device Control Enabled Devices], [Device Count], 0 )


// ══════════════════════════════════════════════════════════════
// VBS / HVCI / CREDENTIAL GUARD
// ══════════════════════════════════════════════════════════════

VBS Enabled Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[VbsEnabled] = TRUE
	),
	0
)

VBS Enabled % = DIVIDE ( [VBS Enabled Devices], [Device Count], 0 )

HVCI Running Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[HvciRunning] = TRUE
	),
	0
)

HVCI Running % = DIVIDE ( [HVCI Running Devices], [Device Count], 0 )

Credential Guard Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[CredentialGuardRunning] = TRUE
	),
	0
)

Credential Guard % = DIVIDE ( [Credential Guard Devices], [Device Count], 0 )

// Configured but not actually running — usually a pending reboot or an
// incompatible driver blocking it.
HVCI Pending Reboot =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[HvciConfigured] = TRUE,
		HealthReports[HvciRunning] = FALSE
	),
	0
)


// ══════════════════════════════════════════════════════════════
// PLATFORM SECURITY
// ══════════════════════════════════════════════════════════════

BitLocker Protected Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[BitLockerOsDriveProtected] = TRUE
	),
	0
)

BitLocker Protected % = DIVIDE ( [BitLocker Protected Devices], [Device Count], 0 )

Secure Boot Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[SecureBootEnabled] = TRUE
	),
	0
)

Secure Boot % = DIVIDE ( [Secure Boot Devices], [Device Count], 0 )

TPM Enabled Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[TpmIsEnabled] = TRUE
	),
	0
)

TPM Enabled % = DIVIDE ( [TPM Enabled Devices], [Device Count], 0 )


// ══════════════════════════════════════════════════════════════
// OS HYGIENE
// ══════════════════════════════════════════════════════════════

Avg Days Since Update =
CALCULATE (
	AVERAGE ( HealthReports[WindowsUpdateDaysSinceLastUpdate] ),
	HealthReports[IsLatestPerDevice] = TRUE
)

Behind On Updates =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[WindowsUpdateDaysSinceLastUpdate] > 30
	),
	0
)

RDP Exposed Devices =
COALESCE (
	CALCULATE (
		DISTINCTCOUNT ( HealthReports[Hostname] ),
		HealthReports[IsLatestPerDevice] = TRUE,
		HealthReports[RemoteDesktopEnabled] = TRUE,
		HealthReports[RemoteDesktopNlaRequired] = FALSE
	),
	0
)


// ══════════════════════════════════════════════════════════════
// TRENDS — these deliberately use every row, not just the latest
// ══════════════════════════════════════════════════════════════

// Plot against GeneratedAtUtc to see fleet posture move over time.
// Like Device Count, these are chart values rather than cards, so they are left
// un-COALESCEd -- a blank drops the point instead of drawing a false zero.
Avg Health Score (All Reports) = AVERAGE ( HealthReports[HealthScore] )

Reports Received = COUNTROWS ( HealthReports )

Devices Reporting In Period = DISTINCTCOUNT ( HealthReports[Hostname] )


// ══════════════════════════════════════════════════════════════
// CONDITIONAL FORMATTING HELPERS
// ══════════════════════════════════════════════════════════════

// Green >= 70, amber >= 50, red below
Health Color =
SWITCH (
	TRUE (),
	[Avg Health Score] >= 70, "#107C10",
	[Avg Health Score] >= 50, "#CC8C00",
	"#C42B1C"
)


// ══════════════════════════════════════════════════════════════
// LATEST VALUE PER DEVICE — inventory / agent versions
// ══════════════════════════════════════════════════════════════

Latest OS Build =
CALCULATE ( MAX ( HealthReports[OsBuild] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest IP Address =
CALCULATE ( MAX ( HealthReports[IpAddress] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Agent Version =
CALCULATE ( MAX ( HealthReports[ServiceVersion] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest AV Engine Version =
CALCULATE ( MAX ( HealthReports[AntivirusEngineVersion] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Signature Version =
CALCULATE ( MAX ( HealthReports[SignatureVersion] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Platform Version =
CALCULATE ( MAX ( HealthReports[PlatformVersion] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Top Issues =
CALCULATE ( MAX ( HealthReports[TopIssues] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Critical Events =
CALCULATE ( MAX ( HealthReports[CriticalEventCount] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Trust Type =
CALCULATE ( MAX ( HealthReports[DeviceTrustType] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Domain =
CALCULATE ( MAX ( HealthReports[DomainName] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Local Admin Count =
CALCULATE ( MAX ( HealthReports[LocalAdminCount] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest PowerShell Policy =
CALCULATE ( MAX ( HealthReports[PowerShellExecutionPolicy] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Windows Build =
CALCULATE ( MAX ( HealthReports[WindowsUpdateCurrentBuild] ), HealthReports[IsLatestPerDevice] = TRUE )


// ══════════════════════════════════════════════════════════════
// VERSION DRIFT — how many different builds are in the fleet?
// ══════════════════════════════════════════════════════════════
// More than one or two distinct versions across a managed fleet usually means
// something is not updating. These count DISTINCT values, so they answer
// "how fragmented are we", not "how many devices".

AV Engine Versions =
CALCULATE (
    DISTINCTCOUNT ( HealthReports[AntivirusEngineVersion] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

Signature Versions =
CALCULATE (
    DISTINCTCOUNT ( HealthReports[SignatureVersion] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

Agent Versions =
CALCULATE (
    DISTINCTCOUNT ( HealthReports[ServiceVersion] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

OS Builds =
CALCULATE (
    DISTINCTCOUNT ( HealthReports[OsBuild] ),
    HealthReports[IsLatestPerDevice] = TRUE
)


// ══════════════════════════════════════════════════════════════
// DEFENDER — engine state beyond real-time protection
// ══════════════════════════════════════════════════════════════

Defender Enabled Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[DefenderEnabled] = TRUE
    ),
    0
)

Defender Enabled % = DIVIDE ( [Defender Enabled Devices], [Device Count], 0 )

// Network Protection mode: 0 = off, 1 = block, 2 = audit.
// Audit is worth separating from block -- it looks configured but stops nothing.
Network Protection Block Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[NetworkProtectionMode] = 1
    ),
    0
)

Net Protection Block % = DIVIDE ( [Network Protection Block Devices], [Device Count], 0 )

Net Protection Audit =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[NetworkProtectionMode] = 2
    ),
    0
)

// Controlled Folder Access mode: 0 = off, 1 = block, 2 = audit.
CFA Enabled Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[ControlledFolderAccessMode] = 1
    ),
    0
)

CFA Enabled % = DIVIDE ( [CFA Enabled Devices], [Device Count], 0 )

Avg ASR Total Rules =
CALCULATE (
    AVERAGE ( HealthReports[AsrTotalRules] ),
    HealthReports[IsLatestPerDevice] = TRUE
)


// ══════════════════════════════════════════════════════════════
// HARDENING — SmartScreen, Exploit Protection, control counts
// ══════════════════════════════════════════════════════════════

SmartScreen Edge Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[SmartScreenEdgeEnabled] = TRUE
    ),
    0
)

SmartScreen Edge % = DIVIDE ( [SmartScreen Edge Devices], [Device Count], 0 )

Exploit Protection DEP Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[ExploitProtectionDepEnabled] = TRUE
    ),
    0
)

Exploit Protection DEP % = DIVIDE ( [Exploit Protection DEP Devices], [Device Count], 0 )

Exploit Protection ASLR Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[ExploitProtectionAslrEnabled] = TRUE
    ),
    0
)

Exploit Protection ASLR % = DIVIDE ( [Exploit Protection ASLR Devices], [Device Count], 0 )

Exploit Protection CFG Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[ExploitProtectionCfgEnabled] = TRUE
    ),
    0
)

Exploit Protection CFG % = DIVIDE ( [Exploit Protection CFG Devices], [Device Count], 0 )

Avg Device Control Rules =
CALCULATE (
    AVERAGE ( HealthReports[DeviceControlRuleCount] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

Avg App Control Policies =
CALCULATE (
    AVERAGE ( HealthReports[AppControlPolicyCount] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

Latest App Control Mode =
CALCULATE ( MAX ( HealthReports[AppControlEnforcementMode] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Device Control Mode =
CALCULATE ( MAX ( HealthReports[DeviceControlDefaultEnforcement] ), HealthReports[IsLatestPerDevice] = TRUE )


// ══════════════════════════════════════════════════════════════
// IDENTITY / JOIN POSTURE
// ══════════════════════════════════════════════════════════════

Entra Joined Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[IsAzureAdJoined] = TRUE
    ),
    0
)

Entra Joined % = DIVIDE ( [Entra Joined Devices], [Device Count], 0 )

Hybrid Joined Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[IsHybridJoined] = TRUE
    ),
    0
)

TPM Present Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[TpmIsPresent] = TRUE
    ),
    0
)

Credential Guard Configured Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[CredentialGuardConfigured] = TRUE
    ),
    0
)

// Configured but not running -- almost always a pending reboot.
Cred Guard Pending Reboot =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[CredentialGuardConfigured] = TRUE,
        HealthReports[CredentialGuardRunning] = FALSE
    ),
    0
)


// ══════════════════════════════════════════════════════════════
// LOCAL RISK — PowerShell, RDP, local admins, LAPS
// ══════════════════════════════════════════════════════════════

PowerShell Constrained Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[PowerShellConstrainedLanguageMode] = TRUE
    ),
    0
)

PowerShell Constrained % = DIVIDE ( [PowerShell Constrained Devices], [Device Count], 0 )

Script Block Logging Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[PowerShellScriptBlockLogging] = TRUE
    ),
    0
)

Script Block Logging % = DIVIDE ( [Script Block Logging Devices], [Device Count], 0 )

RDP Enabled Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[RemoteDesktopEnabled] = TRUE
    ),
    0
)

LAPS Enabled Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[LapsEnabled] = TRUE
    ),
    0
)

LAPS Enabled % = DIVIDE ( [LAPS Enabled Devices], [Device Count], 0 )

Avg Local Admins =
CALCULATE (
    AVERAGE ( HealthReports[LocalAdminCount] ),
    HealthReports[IsLatestPerDevice] = TRUE
)

// Three is an arbitrary but defensible line: built-in Administrator, one break
// glass account and one management agent. Beyond that is worth a look.
Excess Local Admins =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[LocalAdminCount] > 3
    ),
    0
)


// ══════════════════════════════════════════════════════════════
// SCAN FRESHNESS
// ══════════════════════════════════════════════════════════════

Latest Quick Scan =
CALCULATE ( MAX ( HealthReports[LastQuickScanUtc] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Full Scan =
CALCULATE ( MAX ( HealthReports[LastFullScanUtc] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Boot Time =
CALCULATE ( MAX ( HealthReports[LastBootTimeUtc] ), HealthReports[IsLatestPerDevice] = TRUE )

// A device that has never completed a full scan reports no timestamp at all.
Never Full Scanned =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        ISBLANK ( HealthReports[LastFullScanUtc] )
    ),
    0
)


// ══════════════════════════════════════════════════════════════
// APP CONTROL — deployed is not the same as enforcing
// ══════════════════════════════════════════════════════════════
// AppControlEnforcementMode is one of Enforced | Audit | Deployed | NotConfigured.
// "Deployed" means .cip policy files are present but Code Integrity is not
// running them, so they block nothing. A single "App Control Enabled %" card
// collapsed all of that into one number and read as a failure when it was
// really a deployment that had not taken effect.

App Control Enforcing Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[AppControlEnforcementMode] = "Enforced"
    ),
    0
)

App Control Enforcing % = DIVIDE ( [App Control Enforcing Devices], [Device Count], 0 )

App Control Audit Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[AppControlEnforcementMode] = "Audit"
    ),
    0
)

// Any policy present at all, in any state
App Control Deployed Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[AppControlIsConfigured] = TRUE
    ),
    0
)

App Control Deployed % = DIVIDE ( [App Control Deployed Devices], [Device Count], 0 )

// Policies on disk that Code Integrity is not running. This is the gap that
// matters: someone believes App Control is protecting these machines.
App Control Inert Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[AppControlEnforcementMode] = "Deployed"
    ),
    0
)


// ══════════════════════════════════════════════════════════════
// EXPLOIT PROTECTION — status rollup
// ══════════════════════════════════════════════════════════════
// Reported as Healthy / Warning / Critical / Unknown. "Unknown" means the
// mitigation state could not be read, which is different from "off".

Exploit Protection Unknown Devices =
COALESCE (
    CALCULATE (
        DISTINCTCOUNT ( HealthReports[Hostname] ),
        HealthReports[IsLatestPerDevice] = TRUE,
        HealthReports[ExploitProtectionStatus] = "Unknown"
    ),
    0
)

Latest Exploit Protection =
CALCULATE ( MAX ( HealthReports[ExploitProtectionStatus] ), HealthReports[IsLatestPerDevice] = TRUE )

Latest Local Admin Status =
CALCULATE ( MAX ( HealthReports[LocalAdminStatus] ), HealthReports[IsLatestPerDevice] = TRUE )