// ─────────────────────────────────────────────────────────────
// MDE Toolkit — Power Query (M) for Azure Table Storage
// ─────────────────────────────────────────────────────────────
// Paste this into Power BI Desktop → Get Data → Blank Query → Advanced Editor
// Update the StorageAccountName on line 3 to match your environment.
// ─────────────────────────────────────────────────────────────

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

    // ══════════════════════════════════════════════════════════
    // CONNECT TO TABLE STORAGE
    // ══════════════════════════════════════════════════════════
    Source = AzureStorage.Tables("https://" & StorageAccountName & ".table.core.windows.net"),
    HealthReports = Source{[Name = TableName]}[Content],

    // ══════════════════════════════════════════════════════════
    // EXPAND AND TYPE ALL COLUMNS
    // ══════════════════════════════════════════════════════════
    Expanded = Table.ExpandRecordColumn(HealthReports, "Content", {
        // Identity
        "Hostname", "OsVersion", "OsBuild", "OrganizationId", "DeviceGroup",
        "DomainName", "IpAddress", "ReportId",
        // Timestamps
        "GeneratedAtUtc", "UploadedAtUtc",
        // Entra ID
        "IsAzureAdJoined", "IsHybridJoined", "AzureAdDeviceId", "DeviceTrustType",
        // Health
        "HealthScore", "OverallStatus", "CriticalEventCount", "TopIssues",
        // Defender AV
        "DefenderEnabled", "RealTimeProtectionEnabled", "BehaviorMonitorEnabled",
        "TamperProtectionEnabled", "DefenderAvStatus", "SignatureAgeHours",
        // MDE
        "IsOnboarded", "SenseIsRunning", "MdeStatus",
        // ASR
        "AsrIsConfigured", "AsrTotalRules", "AsrBlockModeRules", "AsrStatus",
        // Firewall
        "FirewallDomainEnabled", "FirewallPrivateEnabled", "FirewallPublicEnabled", "FirewallStatus",
        // Device Control
        "DeviceControlEnabled", "DeviceControlRuleCount", "DeviceControlStatus",
        // App Control
        "AppControlIsConfigured", "AppControlIsEnabled", "AppControlStatus",
        // VBS
        "VbsEnabled", "HvciRunning", "CredentialGuardRunning", "VbsStatus",
        // Client
        "ServiceVersion", "SchemaVersion"
    }),

    // ── Type conversions ─────────────────────────────────────
    Typed = Table.TransformColumnTypes(Expanded, {
        // Strings
        {"Hostname", type text}, {"OsVersion", type text}, {"OsBuild", type text},
        {"OrganizationId", type text}, {"DeviceGroup", type text},
        {"DomainName", type text}, {"IpAddress", type text}, {"ReportId", type text},
        {"OverallStatus", type text}, {"TopIssues", type text},
        {"DefenderAvStatus", type text}, {"MdeStatus", type text},
        {"AsrStatus", type text}, {"FirewallStatus", type text},
        {"DeviceControlStatus", type text}, {"AppControlStatus", type text},
        {"VbsStatus", type text}, {"DeviceTrustType", type text},
        {"AzureAdDeviceId", type text}, {"ServiceVersion", type text},

        // Dates
        {"GeneratedAtUtc", type datetimezone}, {"UploadedAtUtc", type datetimezone},

        // Integers
        {"HealthScore", Int64.Type}, {"CriticalEventCount", Int64.Type},
        {"SignatureAgeHours", Int64.Type},
        {"AsrTotalRules", Int64.Type}, {"AsrBlockModeRules", Int64.Type},
        {"DeviceControlRuleCount", Int64.Type}, {"SchemaVersion", Int64.Type},

        // Booleans
        {"DefenderEnabled", type logical}, {"RealTimeProtectionEnabled", type logical},
        {"BehaviorMonitorEnabled", type logical}, {"TamperProtectionEnabled", type logical},
        {"IsOnboarded", type logical}, {"SenseIsRunning", type logical},
        {"IsAzureAdJoined", type logical}, {"IsHybridJoined", type logical},
        {"AsrIsConfigured", type logical},
        {"FirewallDomainEnabled", type logical}, {"FirewallPrivateEnabled", type logical},
        {"FirewallPublicEnabled", type logical},
        {"DeviceControlEnabled", type logical},
        {"AppControlIsConfigured", type logical}, {"AppControlIsEnabled", type logical},
        {"VbsEnabled", type logical}, {"HvciRunning", type logical},
        {"CredentialGuardRunning", type logical}
    }),

    // ── Computed columns ─────────────────────────────────────
    WithHoursSince = Table.AddColumn(Typed, "HoursSinceReport",
        each Duration.TotalHours(DateTimeZone.UtcNow() - [GeneratedAtUtc]), type number),

    WithIsStale = Table.AddColumn(WithHoursSince, "IsStale",
        each [HoursSinceReport] > 24, type logical),

    WithAllFirewallEnabled = Table.AddColumn(WithIsStale, "AllFirewallProfilesEnabled",
        each [FirewallDomainEnabled] and [FirewallPrivateEnabled] and [FirewallPublicEnabled], type logical),

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

    // ── Sort by freshest report per device ───────────────────
    Sorted = Table.Sort(WithHealthBucket, {{"GeneratedAtUtc", Order.Descending}})
in
    Sorted
