Automating Intune Hardware & Managed App Inventory Telemetry Extraction to PowerBI via Microsoft Graph API

<#
.SYNOPSIS
    Production-Grade Intune Telemetry Harvester for PowerBI Analytics.
.DESCRIPTION
    Extracts hardware inventory, detected applications, and device-to-app 
    associations from Microsoft Graph API with automatic pagination, exponential
    backoff on HTTP 429 throttling, and outputs Star Schema CSVs for PowerBI.
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)]
    [string]$TenantId,

    [Parameter(Mandatory = $true)]
    [string]$ClientId,

    [Parameter(Mandatory = $true)]
    [string]$CertThumbprint,

    [Parameter(Mandatory = $false)]
    [string]$OutputDir = "$PSScriptRoot\Export",

    [Parameter(Mandatory = $false)]
    [switch]$IncludeAppBridge
)

# Ensure output directory structure exists
if (-not (Test-Path $OutputDir)) {
    New-Item -Path $OutputDir -ItemType Directory -Force | Out-Null
}

# --- Resilient Graph API Invocation with HTTP 429 & 503 Exponential Backoff ---
function Invoke-ResilientGraphRequest {
    param(
        [string]$Uri,
        [int]$MaxRetries = 5
    )
    $retryCount = 0
    while ($retryCount -lt $MaxRetries) {
        try {
            return (Invoke-MgGraphRequest -Method GET -Uri $Uri -ErrorAction Stop)
        } catch {
            $statusCode = 0
            if ($_.Exception.Response) {
                $statusCode = [int]$_.Exception.Response.StatusCode
            }

            if ($statusCode -eq 429 -or $statusCode -eq 503) {
                $retryAfter = $_.Exception.Response.Headers['Retry-After']
                $waitSec = if ($retryAfter) { [int]$retryAfter } else { [math]::Pow(2, $retryCount) * 2 }
                Write-Warning "[Rate Limited: HTTP $statusCode] Pausing for $waitSec seconds (Attempt $($retryCount + 1)/$MaxRetries)..."
                Start-Sleep -Seconds $waitSec
                $retryCount++
            } else {
                throw $_
            }
        }
    }
    throw "Fatal: Max Graph API retries exceeded for URI: $Uri"
}

Write-Host ">>> [1/4] Authenticating to Microsoft Graph via Certificate..." -ForegroundColor Cyan
try {
    Connect-MgGraph -TenantId $TenantId -ClientId $ClientId -CertificateThumbprint $CertThumbprint -NoWelcome -ErrorAction Stop
} catch {
    Write-Error "Authentication Failed: Ensure the certificate is installed in Cert:\CurrentUser\My or Cert:\LocalMachine\My."
    exit 1
}

# ==============================================================================
# 1. Harvest Dim_Devices (Hardware & Configuration Telemetry)
# ==============================================================================
Write-Host ">>> [2/4] Querying /deviceManagement/managedDevices with pagination..." -ForegroundColor Cyan
$deviceUri = "https://graph.microsoft.com/beta/deviceManagement/managedDevices?`$select=id,deviceName,serialNumber,manufacturer,model,operatingSystem,osVersion,totalStorageSpaceInBytes,freeStorageSpaceInBytes,userPrincipalName,complianceState,lastSyncDateTime,enrolledDateTime,chassisType,hardwareInformation"
$allDevices = [System.Collections.Generic.List[PSObject]]::new()

do {
    $response = Invoke-ResilientGraphRequest -Uri $deviceUri
    if ($response.value) {
        foreach ($d in $response.value) {
            $totalGB = if ($d.totalStorageSpaceInBytes) { [math]::Round(($d.totalStorageSpaceInBytes / 1GB), 2) } else { 0 }
            $freeGB  = if ($d.freeStorageSpaceInBytes) { [math]::Round(($d.freeStorageSpaceInBytes / 1GB), 2) } else { 0 }
            $usedPct = if ($totalGB -gt 0) { [math]::Round((($totalGB - $freeGB) / $totalGB) * 100, 1) } else { 0 }

            # Safe navigation for hardwareInformation (protects against null errors on mobile & VMs)
            $hw = $d.hardwareInformation
            $tpm = if ($hw) { $hw.tpmSpecificationVersion } else { $null }
            $battery = if ($hw) { $hw.batteryHealthPercentage } else { $null }

            $allDevices.Add([PSCustomObject]@{
                DeviceId          = $d.id
                DeviceName        = $d.deviceName
                SerialNumber      = $d.serialNumber
                Manufacturer      = $d.manufacturer
                Model             = $d.model
                OS                = $d.operatingSystem
                OSVersion         = $d.osVersion
                TotalStorageGB    = $totalGB
                FreeStorageGB     = $freeGB
                StorageUsedPct    = $usedPct
                UserPrincipalName = $d.userPrincipalName
                ComplianceState   = $d.complianceState
                LastSyncDateTime  = $d.lastSyncDateTime
                EnrolledDateTime  = $d.enrolledDateTime
                TPMVersion        = $tpm
                BatteryHealthPct  = $battery
            })
        }
    }
    $deviceUri = $response.'@odata.nextLink'
} while ($deviceUri)

$devicesCsv = "$OutputDir\Dim_Devices.csv"
$allDevices | Export-Csv -Path $devicesCsv -NoTypeInformation -Encoding UTF8
Write-Host "[+] Exported $($allDevices.Count) endpoint records to $devicesCsv" -ForegroundColor Green

# ==============================================================================
# 2. Harvest Dim_Applications (Detected Software Catalog)
# ==============================================================================
Write-Host ">>> [3/4] Querying /deviceManagement/detectedApps with pagination..." -ForegroundColor Cyan
$appUri = "https://graph.microsoft.com/beta/deviceManagement/detectedApps?`$select=id,displayName,version,publisher,sizeInByte,deviceCount"
$allApps = [System.Collections.Generic.List[PSObject]]::new()

do {
    $appResponse = Invoke-ResilientGraphRequest -Uri $appUri
    if ($appResponse.value) {
        foreach ($a in $appResponse.value) {
            $allApps.Add([PSCustomObject]@{
                AppId       = $a.id
                DisplayName = $a.displayName
                Version     = $a.version
                Publisher   = $a.publisher
                SizeMB      = if ($a.sizeInByte) { [math]::Round(($a.sizeInByte / 1MB), 2) } else { 0 }
                DeviceCount = $a.deviceCount
            })
        }
    }
    $appUri = $appResponse.'@odata.nextLink'
} while ($appUri)

$appsCsv = "$OutputDir\Dim_Applications.csv"
$allApps | Export-Csv -Path $appsCsv -NoTypeInformation -Encoding UTF8
Write-Host "[+] Exported $($allApps.Count) application titles to $appsCsv" -ForegroundColor Green

# ==============================================================================
# 3. Harvest Fact_DeviceAppBridge (Relational Association)
# ==============================================================================
if ($IncludeAppBridge) {
    Write-Host ">>> [4/4] Extracting Fact_DeviceAppBridge (Device-to-App mappings)..." -ForegroundColor Cyan
    $bridgeData = [System.Collections.Generic.List[PSObject]]::new()
    $processed = 0

    foreach ($dev in $allDevices) {
        $processed++
        if ($processed % 25 -eq 0) {
            Write-Host "   Processing associations: $processed of $($allDevices.Count) devices..." -ForegroundColor Gray
        }

        $devAppUri = "https://graph.microsoft.com/beta/deviceManagement/managedDevices('$($dev.DeviceId)')/detectedApps?`$select=id"
        try {
            $devApps = Invoke-ResilientGraphRequest -Uri $devAppUri
            if ($devApps.value) {
                foreach ($da in $devApps.value) {
                    $bridgeData.Add([PSCustomObject]@{
                        DeviceId = $dev.DeviceId
                        AppId    = $da.id
                    })
                }
            }
        } catch {
            Write-Warning "Skipped application inventory for device $($dev.DeviceName): $($_.Exception.Message)"
        }
    }

    $bridgeCsv = "$OutputDir\Fact_DeviceAppBridge.csv"
    $bridgeData | Export-Csv -Path $bridgeCsv -NoTypeInformation -Encoding UTF8
    Write-Host "[+] Exported $($bridgeData.Count) associations to $bridgeCsv" -ForegroundColor Green
}

Disconnect-MgGraph | Out-Null
Write-Host ">>> Telemetry Harvester Completed Successfully." -ForegroundColor Green

// 1. Total Managed Fleet Size
Total Enrolled Devices = 
COUNTROWS(Dim_Devices)

// 2. Storage Pressure Exposure (Endpoints with < 20 GB free space or >= 90% full)
Devices Low Storage = 
CALCULATE(
    COUNTROWS(Dim_Devices),
    Dim_Devices[FreeStorageGB] < 20 || Dim_Devices[StorageUsedPct] >= 90
)

// 3. Low Storage Fleet Percentage
Fleet Low Storage Rate = 
DIVIDE([Devices Low Storage], [Total Enrolled Devices], 0)

// 4. Critical Battery Degradation Exposure (Health < 75%)
Battery Replacement Required = 
CALCULATE(
    COUNTROWS(Dim_Devices),
    Dim_Devices[BatteryHealthPct] > 0 && Dim_Devices[BatteryHealthPct] < 75
)

// 5. Windows 11 Hardware TPM 2.0 Compliance Rate
TPM 2.0 Compliance % = 
DIVIDE(
    CALCULATE(
        COUNTROWS(Dim_Devices),
        Dim_Devices[TPMVersion] >= "2.0"
    ),
    CALCULATE(
        COUNTROWS(Dim_Devices),
        NOT(ISBLANK(Dim_Devices[TPMVersion]))
    ),
    0
)

// 6. Stale Inactive Endpoints (No sync in 30+ days)
Stale Inactive Devices = 
CALCULATE(
    COUNTROWS(Dim_Devices),
    DATEDIFF(Dim_Devices[LastSyncDateTime], TODAY(), DAY) > 30
)

let
    // Connect to Azure Blob Storage Container
    Source = AzureStorage.Blobs("yourstorageaccount"),
    TelemetryContainer = Source{[Name="intune-telemetry"]}[Data],
    
    // Filter to the latest device inventory snapshot
    DeviceFiles = Table.SelectRows(TelemetryContainer, each Text.Contains([Name], "Dim_Devices")),
    SortedFiles = Table.Sort(DeviceFiles, {{"Date modified", Order.Descending}}),
    LatestFile = SortedFiles{0}[Content],
    
    // Parse CSV with UTF-8 encoding
    ImportedCsv = Csv.Document(LatestFile, [Delimiter=",", Columns=16, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    PromotedHeaders = Table.PromoteHeaders(ImportedCsv, [PromoteAllScalars=true]),
    
    // Enforce Strongly Typed Schema
    TypedTable = Table.TransformColumnTypes(PromotedHeaders, {
        {"DeviceId", type text},
        {"DeviceName", type text},
        {"SerialNumber", type text},
        {"Manufacturer", type text},
        {"Model", type text},
        {"OS", type text},
        {"OSVersion", type text},
        {"TotalStorageGB", type number},
        {"FreeStorageGB", type number},
        {"StorageUsedPct", type number},
        {"UserPrincipalName", type text},
        {"ComplianceState", type text},
        {"LastSyncDateTime", type datetimezone},
        {"EnrolledDateTime", type datetimezone},
        {"TPMVersion", type text},
        {"BatteryHealthPct", Int64.Type}
    })
in
    TypedTable