// ─────────────────────────────────────────────────────────────
// MDE Toolkit — Power Query (M) for Azure Government
// ─────────────────────────────────────────────────────────────
// Power BI Desktop → Get Data → Blank Query → Advanced Editor → paste this.
// Then update the CONFIGURATION block below.
//
// AUTHENTICATION — read this before you grant anyone a role
// ---------------------------------------------------------
// The Power Query Azure Table Storage connector supports exactly one
// authentication type: Account Key. It does not offer "Organizational account",
// so Entra ID data-plane roles such as Storage Table Data Reader do NOT apply.
// Granting one and expecting Power BI to use it produces:
//   "Expression.Error: Access to the resource is forbidden."
//
// Get the key from the Azure portal: storage account > Security + networking >
// Access keys > key1 > Show > Copy. Power BI stores it in its own credential
// store, not in this file.
//
// Treat it as a real credential: an account key grants full read/write access to
// every table, queue and blob in that account. Rotate it on a schedule.
//
// This affects the reporting side only. Endpoints still hold no secrets -- they
// POST to the Function App with the signed-in user's Entra token, and the
// Function App writes to the table with its Managed Identity.
//
// AZURE GOVERNMENT NOTES
// ----------------------
// * Authentication is by account key (see above), so there is no tenant sign-in
//   for this connector and no Gov-vs-commercial identity to get wrong. The key
//   comes from the Government storage account itself.
// * If Power BI Desktop has cached a bad or stale credential for this source,
//   clear it before retrying: File > Options and settings > Data source
//   settings > find the *.table.core.usgovcloudapi.net entry > Clear Permissions.
// * The storage suffix below covers US Gov Virginia / Arizona / Texas and the
//   DoD regions. It is NOT the same as the ARM or Entra endpoint, so this value
//   is unrelated to whatever AzureEnvironment / AuthorityHostOverride you set on
//   the endpoints in HKLM\SOFTWARE\Policies\MDE-Toolkit.
// * To publish, use Power BI for US Government (app.powerbi.us). The commercial
//   Power BI service cannot reach a Government storage account directly; it
//   would need an on-premises data gateway with line of sight to it.
// * Everything else in this file is identical to HealthReports-Query.m. If you
//   change one, change both.
//
// WHY THIS QUERY LOOKS THE WAY IT DOES
// ------------------------------------
// Azure Table Storage is schemaless. The ingestion Function App omits any
// property whose value is null, so a device that has never reported (say)
// LastFullScanUtc simply has no such property on its row. A plain
// Table.ExpandRecordColumn over a fixed field list therefore fails with
// "The field '<name>' of the record wasn't found" as soon as one row is
// missing one property. This query builds a template record of nulls and
// merges each row onto it first, so expansion can never fail regardless of
// which optional properties a given device happened to report.
// ─────────────────────────────────────────────────────────────

let
	// ══════════════════════════════════════════════════════════
	// CONFIGURATION — Update these values
	// ══════════════════════════════════════════════════════════
	StorageAccountName = "stmdetoolkit",
	TableName          = "HealthReports",

	// Azure Government. For the commercial cloud use HealthReports-Query.m,
	// or just change this to "table.core.windows.net".
	EndpointSuffix     = "table.core.usgovcloudapi.net",

	// ReportJson holds a trimmed copy of the full snapshot, up to 30,000
	// characters per row. Leaving it out keeps refreshes fast; set to true only
	// if you intend to parse it.
	IncludeReportJson  = false,

	// A device is considered stale if its newest report is older than this.
	StaleAfterHours    = 24,

	// ══════════════════════════════════════════════════════════
	// CONNECT
	// ══════════════════════════════════════════════════════════
	Source     = AzureStorage.Tables("https://" & StorageAccountName & "." & EndpointSuffix),
	Navigation = Source{[Name = TableName]},

	// The navigation record exposes the table under [Data]. Do not use [Content]
	// here: Content is the name of the per-row *column* inside the table (a record
	// holding every non-key property), expanded further down. Confusing the two
	// produces "Expression.Error: The field 'Content' of the record wasn't found."
	RawTable   = Navigation[Data],

	// ══════════════════════════════════════════════════════════
	// COLUMNS — must match FunctionApp\HealthReportIngestion\run.ps1
	// ══════════════════════════════════════════════════════════
	TextColumns = {
		// Identity
		"Hostname", "OsVersion", "OsBuild", "OrganizationId", "DeviceGroup",
		"DomainName", "IpAddress", "ReportId",
		// Enterprise tagging (HKLM\SOFTWARE\Policies\MDE-Toolkit)
		"DeviceTag", "OrgUnit", "Environment",
		// Entra ID
		"AzureAdDeviceId", "AzureAdTenantId", "DeviceTrustType",
		// Health
		"OverallStatus", "TopIssues",
		// Defender AV
		"DefenderAvStatus", "AntivirusEngineVersion", "SignatureVersion", "PlatformVersion",
		// MDE
		"MdeOrgId", "MdeStatus",
		// Policy surfaces
		"AsrStatus", "FirewallStatus",
		"NetworkProtectionStatus", "ControlledFolderAccessStatus",
		"DeviceControlStatus", "DeviceControlDefaultEnforcement",
		"AppControlStatus", "AppControlEnforcementMode",
		"SmartScreenExplorerMode", "SmartScreenStatus",
		"ExploitProtectionStatus",
		// Platform security
		"SecureBootStatus", "BitLockerStatus",
		"TpmVersion", "TpmStatus", "VbsStatus",
		// OS hygiene
		"WindowsUpdateCurrentBuild", "WindowsUpdateStatus",
		"PowerShellExecutionPolicy", "PowerShellStatus",
		"RemoteDesktopStatus", "LocalAdminStatus",
		// Client
		"ServiceVersion", "CloudSyncTarget"
	},

	BoolColumns = {
		"IsAzureAdJoined", "IsHybridJoined",
		"DefenderEnabled", "RealTimeProtectionEnabled", "BehaviorMonitorEnabled",
		"TamperProtectionEnabled",
		"IsOnboarded", "SenseIsRunning",
		"AsrIsConfigured",
		"FirewallDomainEnabled", "FirewallPrivateEnabled", "FirewallPublicEnabled",
		"DeviceControlEnabled",
		"AppControlIsConfigured", "AppControlIsEnabled",
		"SmartScreenEdgeEnabled",
		"ExploitProtectionDepEnabled", "ExploitProtectionAslrEnabled", "ExploitProtectionCfgEnabled",
		"SecureBootEnabled", "SecureBootIsUefi",
		"BitLockerOsDriveProtected",
		"TpmIsPresent", "TpmIsEnabled",
		"VbsEnabled", "HvciConfigured", "HvciRunning",
		"CredentialGuardConfigured", "CredentialGuardRunning",
		"WindowsUpdateAutoEnabled",
		"PowerShellConstrainedLanguageMode", "PowerShellScriptBlockLogging",
		"RemoteDesktopEnabled", "RemoteDesktopNlaRequired",
		"LapsConfigured", "LapsEnabled",
		"CloudSyncEnabled"
	},

	IntColumns = {
		"HealthScore", "CriticalEventCount", "SignatureAgeHours",
		"AsrTotalRules", "AsrBlockModeRules", "AsrAuditModeRules",
		"NetworkProtectionMode", "ControlledFolderAccessMode",
		"DeviceControlRuleCount", "AppControlPolicyCount",
		"WindowsUpdateDaysSinceLastUpdate", "LocalAdminCount",
		"SchemaVersion"
	},

	// Only GeneratedAtUtc and UploadedAtUtc are guaranteed ISO 8601. The others
	// were written with the Function App's locale formatting on older builds,
	// so the parser below accepts both.
	DateColumns = {
		"GeneratedAtUtc", "UploadedAtUtc",
		"LastBootTimeUtc", "LastQuickScanUtc", "LastFullScanUtc",
		"WindowsUpdateLastInstallDate"
	},

	AllColumns =
		List.Combine({TextColumns, BoolColumns, IntColumns, DateColumns})
		& (if IncludeReportJson then {"ReportJson"} else {}),

	// ══════════════════════════════════════════════════════════
	// EXPAND — null-safe against the schemaless table
	// ══════════════════════════════════════════════════════════
	Template   = Record.FromList(List.Repeat({null}, List.Count(AllColumns)), AllColumns),

	// Normal case: the connector nests every non-key property in a Content record.
	// Merging each row onto a template of nulls means a property that no device
	// happened to report expands to null instead of failing the whole refresh.
	HasContentColumn = List.Contains(Table.ColumnNames(RawTable), "Content"),

	Expanded =
		if HasContentColumn then
			Table.ExpandRecordColumn(
				Table.TransformColumns(
					RawTable,
					{{"Content", each Record.Combine({Template, _}), type record}}
				),
				"Content",
				AllColumns
			)
		else
			// Already flattened by the connector, or a table written by something
			// other than the Function App: add whatever columns are absent as nulls.
			List.Accumulate(
				List.Difference(AllColumns, Table.ColumnNames(RawTable)),
				RawTable,
				(state, col) => Table.AddColumn(state, col, each null)
			),

	// ══════════════════════════════════════════════════════════
	// TYPE CONVERSION — tolerant of text, typed and missing values
	// ══════════════════════════════════════════════════════════
	ToText = (v) as nullable text =>
		if v = null then null
		else let t = Text.Trim(Text.From(v)) in if t = "" then null else t,

	ToBool = (v) as nullable logical =>
		if v = null then null
		else if v is logical then v
		else try Logical.From(Text.Trim(Text.From(v))) otherwise null,

	ToInt = (v) as nullable number =>
		if v = null then null
		else if v is number then Number.RoundDown(v)
		else try Int64.From(Text.Trim(Text.From(v)), "en-US") otherwise null,

	ToDate = (v) as nullable datetimezone =>
		if v = null then null
		else if v is datetimezone then v
		else if v is datetime then DateTime.AddZone(v, 0)
		else
			let t = Text.Trim(Text.From(v))
			in
				if t = "" then null
				else
					try DateTimeZone.FromText(t)
					otherwise try DateTimeZone.FromText(t, "en-US")
					otherwise try DateTime.AddZone(DateTime.FromText(t, "en-US"), 0)
					otherwise null,

	Typed = Table.TransformColumns(
		Expanded,
		List.Transform(TextColumns, (c) => {c, ToText, type nullable text})
		& List.Transform(BoolColumns, (c) => {c, ToBool, type nullable logical})
		& List.Transform(IntColumns,  (c) => {c, ToInt,  Int64.Type})
		& List.Transform(DateColumns, (c) => {c, ToDate, type nullable datetimezone})
	),

	// ══════════════════════════════════════════════════════════
	// COMPUTED COLUMNS
	// ══════════════════════════════════════════════════════════
	WithHoursSince = Table.AddColumn(Typed, "HoursSinceReport",
		each if [GeneratedAtUtc] = null then null
			 else Duration.TotalHours(DateTimeZone.UtcNow() - DateTimeZone.ToUtc([GeneratedAtUtc])),
		type nullable number),

	WithIsStale = Table.AddColumn(WithHoursSince, "IsStale",
		each if [HoursSinceReport] = null then null else [HoursSinceReport] > StaleAfterHours,
		type nullable logical),

	// "= true" rather than "and" so a missing profile counts as not-enabled
	// instead of poisoning the whole expression with null.
	WithAllFirewallEnabled = Table.AddColumn(WithIsStale, "AllFirewallProfilesEnabled",
		each [FirewallDomainEnabled] = true
		 and [FirewallPrivateEnabled] = true
		 and [FirewallPublicEnabled] = true,
		type logical),

	WithHealthBucket = Table.AddColumn(WithAllFirewallEnabled, "HealthBucket",
		each if [HealthScore] = null then "Unknown"
			 else if [HealthScore] >= 90 then "Excellent"
			 else if [HealthScore] >= 70 then "Good"
			 else if [HealthScore] >= 50 then "Needs Attention"
			 else "Critical",
		type text),

	// ══════════════════════════════════════════════════════════
	// ONE ROW PER REPORT — flag the newest row per device
	// ══════════════════════════════════════════════════════════
	// The Function App builds RowKey as "<Hostname>_<ReportId>", and the
	// collector does not send a ReportId, so the Function mints a fresh GUID on
	// every upload. The result is a new row per report, not an upsert over a
	// single row per device. That history is useful for trending, but it means
	// any fleet percentage computed over all rows is weighted by how often each
	// device reported. Filter on IsLatestPerDevice for point-in-time posture;
	// use every row for trends over time.
	LatestPerDevice = Table.Group(
		WithHealthBucket, {"Hostname"},
		{{"MaxGeneratedAtUtc", each List.Max([GeneratedAtUtc]), type nullable datetimezone}}
	),
	JoinedLatest = Table.NestedJoin(
		WithHealthBucket, {"Hostname"}, LatestPerDevice, {"Hostname"}, "__latest", JoinKind.LeftOuter
	),
	ExpandedLatest = Table.ExpandTableColumn(JoinedLatest, "__latest", {"MaxGeneratedAtUtc"}),
	WithIsLatest = Table.AddColumn(ExpandedLatest, "IsLatestPerDevice",
		each [GeneratedAtUtc] <> null and [GeneratedAtUtc] = [MaxGeneratedAtUtc],
		type logical),
	Cleaned = Table.RemoveColumns(WithIsLatest, {"MaxGeneratedAtUtc"}),

	Sorted = Table.Sort(Cleaned, {{"GeneratedAtUtc", Order.Descending}})
in
	Sorted