Write-PsIntuneDeviceReport.ps1

function Write-psIntuneDeviceReport {
    <#
    .SYNOPSIS
        Export Inventory Data to Excel Workbook
    .DESCRIPTION
        Export Intune Device inventory data to Excel Workbook
    .PARAMETER IntuneDevices
        Device Data queried from Intune using Get-psIntuneDevice -Detail Full
        If not provided, data will be queried from Intune
    .PARAMETER IntuneApps
        Apps data returned from Get-psIntuneDeviceApps
        If not provided, data will be queried from Intune
    .PARAMETER AadDevices
        Device accounts from Azure AD. If not provided, this data set is
        simply excluded from the report.
    .PARAMETER OutputFolder
        Path for output file. Default is current user Documents path
    .PARAMETER Title
        Title to use for output filename, typically a customer or project name
    .PARAMETER DeviceOS
        Filter devices by operating system. Options: Android, iOS, Windows, All
        Default is All
    .PARAMETER StaleLimit
        Number of days since last Intune synchronization to consider as a stale account
        Default is 180
    .PARAMETER LowDiskGB
        Free disk space GB to indicate "low disk space".
        Default is 20
    .PARAMETER Overwrite
        If output file exists, with same name, it will be overwritten.
        Default is to abort if idential filename exists.
    .PARAMETER DateStamp
        Include datestamp in the output filename (default is "_YYYY-MM-DD" suffix)
    .PARAMETER Show
        Display workbook when export is complete. Default is to not show
    .LINK
        https://github.com/Skatterbrainz/psIntune/blob/master/docs/Write-psIntuneDeviceReport.md
    #>

    [CmdletBinding()]
    param (
        [parameter(Mandatory)] [ValidateNotNullOrEmpty()] $IntuneDevices,
        [parameter(Mandatory)] [ValidateNotNullOrEmpty()] $IntuneApps,
        [parameter()] $AadDevices, 
        [parameter()][string] $OutputFolder = "$([System.Environment]::GetFolderPath('Personal'))",
        [parameter()][string] $Title = "",
        [parameter()][string][ValidateSet('All','Windows','Android','iOS')] $DeviceOS = 'All',
        [parameter()][ValidateRange(1,1000)][int] $StaleLimit = 180,
        [parameter()][ValidateRange(0,100)][int] $LowDiskGB = 20,
# [parameter()][switch] $AzureAD,
        [parameter()][switch] $Overwrite,
        [parameter()][switch] $DateStamp,
        [parameter()][switch] $Show
    )
    $time1 = Get-Date
    Write-Host "Gathering data to generate report"
    if ($null -ne $AadDevices) {
        $AzureAD = $True
        $aadevs = $AadDevices
        #$aadevs = Get-psIntuneAzureADDevices -UserName $global:psintuneuser
    }
    if (!$DateStamp) {
        $xlFile = "$OutputFolder\IntuneDevices`_$Title.xlsx"
    }
    else {
        $xlFile = "$OutputFolder\IntuneDevices`_$Title`_$(Get-Date -f 'yyyy-MM-dd').xlsx"
    }
    
    Write-Verbose "output file = $xlFile"
    if ((Test-Path $xlFile) -and (!$Overwrite)) {
        Write-Warning "Output file exists [$xlFile]. Use -Overwrite to replace."
        break
    }
    if ($null -eq $IntuneDevices) {
        Write-Host "Requesting new query results..."
        $devs = Get-psIntuneDevice -Detail Detailed -ShowProgress
    }
    else {
        $devs = $IntuneDevices
    }

    if ($null -eq $IntuneApps) {
        Write-Host "Requesting application inventory for each device..."
        $apps = Get-psIntuneDeviceApps -Devices $devs -UserName $UserName -ShowProgress
    }
    else {
        $apps = $IntuneApps
    }

    Write-Host "Returned $($devs.Count) devices"
    if ($DeviceOS -ne 'All') {
        Write-Verbose "filtering devices on OSName = $DeviceOS"
        $devs = $devs | Where-Object {$_.OSName -match $DeviceOS}
        Write-Verbose "filtered to $($devs.Count) devices with $DeviceOS"
    }

    Write-Host "Applying filter rule: Base Devices"
    $intdevs   = $devs | Select-Object * -ExcludeProperty Apps
    Write-Host "Applying filter rule: Device Models"
    $models    = @($devs | Select-Object Manufacturer,Model | Group-Object -Property Manufacturer,Model | Select-Object Count,Name | Sort-Object Count -Descending)
    Write-Host "Applying filter rule: Orphaned Devices"
    $deldevs   = @($devs | Where-Object {$_.DeviceName -eq 'User deleted for this device'} | Select-Object * -ExcludeProperty Apps)
    Write-Host "Applying filter rule: Duplicate Devices"
    $dupedevs  = @($devs | Select-Object DeviceName,DeviceID | Group-Object -Property DeviceName | Where-Object {$_.Count -gt 1} | Select-Object Count,Name)
    Write-Host "Applying filter rule: Stale Devices"
    $staledevs = @($devs | Where-Object {$_.LastSyncDays -ge $StaleLimit} | Select-Object DeviceName,DeviceID,Category,Ownership,Manufacturer,Model,UserName,SerialNumber,LastSyncTime,LastSyncDays,EnrollDate)
    Write-Host "Applying filter rule: Devices with Low Disk Space"
    $lowdisk   = @($devs | Where-Object {$_.FreeSpaceGB -lt $LowDiskGB} | Select-Object * -ExcludeProperty Apps)
    Write-Host "Applying filter rule: Software"
    #$apps = Get-psIntuneInstalledApps -DataSet $devs

    #$allapps = @($apps.apps | Where-Object {(![string]::IsNullOrEmpty($_.displayName)) -and ($_.displayName -notmatch '\. \.')} | Sort-Object displayName)
    $allapps = $apps | Foreach-Object {
        $dn = $_.DeviceName
        $da = $_.apps
        $da | Foreach-Object {
            if (![string]::IsNullOrEmpty($_.displayName) -and ($_.displayName -notmatch '\. \.')) {
                [pscustomobject]@{
                    DeviceName = $dn
                    Product    = $_.displayName
                    Version    = $_.version
                }
            }
        }
    }
    Write-Host "Applying filter rule: Software Install Counts"
    #$appcounts = $apps | Group-Object -Property ProductName | Select-Object Count,Name | Sort-Object Name -Unique
    $appcounts = $apps.apps | Where-Object {$_.DisplayName -notmatch '\. \.'} | Group-Object -Property DisplayName | Select-Object Count,Name | Sort-Object Count -Descending
    Write-Host "Applying filter rule: Distinct Software Products"
    #$distapps = $apps | Select-Object ProductName,ProductType,DeviceName | Sort-Object ProductName -Unique
    $distapps = $apps.apps | Select-Object displayName,version | Sort-Object displayName -Unique
    if ($AzureAD) {
        if ($DeviceOS -ne 'All') {
            $aadevs = $aadevs | Where-Object { $_.OSName -match $DeviceOS } | Sort-Object Name 
            Write-Verbose "returned $($aadevs.Count) AzureAD devices running $DeviceOS"
        }
        $aadx = $aadevs | Select-Object Name,OSName,OSVersion | Sort-Object Name -Unique
        $aadupes = $aadevs | Group-Object Name | Where-Object {$_.Count -gt 1} | Select-Object Count,Name
    }

    Write-Host "Crunching statistics and stuff..."
    $stats = @()
    $stats += $intdevs | Group-Object -Property OSName,OSVersion | Sort-Object Count -Descending | Select-Object Name,Count | ForEach-Object {[pscustomobject]@{Name = 'OperatingSystem'; Property = $_.Name; Value = $_.Count}}
    $stats += $intdevs | Group-Object -Property Manufacturer,Model | Select-Object Name,Count | Sort-Object Count -Descending | ForEach-Object {[pscustomobject]@{Name = 'Models'; Property = $_.Name; Value = $_.Count}}
    $stats += $intdevs | Group-Object -Property Ownership | Select-Object Name,Count | Sort-Object Count -Descending | ForEach-Object {[pscustomobject]@{Name = 'Ownership'; Property = $_.Name; Value = $_.Count}}
    $stats += $intdevs | Group-Object -Property Category | Select-Object Name,Count | Sort-Object Count -Descending | ForEach-Object {[pscustomobject]@{Name = 'Category'; Property = $_.Name; Value = $_.Count}}
    $stats += $intdevs | Group-Object -Property UserName | Select-Object Name,Count | Where-Object {$_.Count -gt 1 -and $_.Name -ne ''} | Sort-Object Count -Descending | Select-Object -First 25 | ForEach-Object {[pscustomobject]@{Name = 'UserName'; Property = $_.Name; Value = $_.Count}}
    if ($AzureAD) {
        $stats += $aadevs | Group-Object -Property IsCompliant | Select-Object Name,Count | ForEach-Object {[pscustomobject]@{Name = 'Compliant'; Property = $_.Name; Value = $_.Count}}
        $stats += $aadevs | Group-Object -Property DirSyncEnabled | Select-Object Name,Count | ForEach-Object {[pscustomobject]@{Name = 'DirSyncEnabled'; Property = $_.Name; Value = $_.Count}}
        $stats += $aadevs | Group-Object -Property TrustType | Select-Object Name,Count | ForEach-Object {[pscustomobject]@{Name = 'TrustType'; Property = $_.Name; Value = $_.Count}}
        $stats += $aadevs | Group-Object -Property OSVersion | Select-Object Name,Count | ForEach-Object {$_.Name -ne '' -and $_.Count -gt 5} | Sort-Object Count -Descending | ForEach-Object {[pscustomobject]@{Name = 'OSVersion'; Property = $_.Name; Value = $_.Count}}
        $stats += $aadevs | Group-Object -Property LastLogonDays | ForEach-Object {$_.Name -gt 180 -and $_.Count -gt 10} | ForEach-Object {[pscustomobject]@{Name = 'DaysSinceLogon'; Count = $_.Count; Property = [int]$_.Name}} | Sort-Object Property -Descending
        $iMissing = $aadevs | Where-Object {$_.Name -notin $intdevs.DeviceName}
        $aMissing = $intdevs | Where-Object {$_.DeviceName -notin $aadevs.Name}
    }

    Write-Host "Exporting datasets: Summary"
    $stats | Export-Excel -Path $xlFile -WorksheetName "Summary" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
    Write-Host "Exporting datasets: Intune Devices ($($intdevs.Count))"
    $intdevs | Export-Excel -Path $xlFile -WorksheetName "IntuneDevices" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    if ($AzureAD) {
        Write-Host "Exporting datasets: AzureAD devices ($($aadevs.Count))"
        $aadevs | Export-Excel -Path $xlFile -WorksheetName "AaDevices" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
        Write-Host "Exporting datasets: AzureAD unique devices ($($aadx.Count))"
        $aadx | Export-Excel -Path $xlFile -WorksheetName "AaDevicesUnique" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
        Write-Host "Exporting datasets: AzureAD duplicate devices ($($aadupes.Count))"
        $aadupes | Export-Excel -Path $xlFile -WorksheetName "AaDevicesDuplicates" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    }
    Write-Host "Exporting datasets: Intune Device Models ($($models.Count))"
    $models | Export-Excel -Path $xlFile -WorksheetName "IntuneModels" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
    Write-Host "Exporting datasets: Intune Stale Devices ($($staledevs.Count))"
    $staledevs | Export-Excel -Path $xlFile -WorksheetName "IntuneStaleDevices" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    Write-Host "Exporting datasets: Intune Duplicate Devices ($($dupedevs.Count))"
    $dupedevs | Export-Excel -Path $xlFile -WorksheetName "IntuneDuplicates" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
    Write-Host "Exporting datasets: Intune Orphaned Devices ($($deldevs.Count))"
    $deldevs | Export-Excel -Path $xlFile -WorksheetName "IntuneOrphaned" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    Write-Host "Exporting datasets: Intune Devices with Low Disk Space ($($lowdisk.Count))"
    $lowdisk | Export-Excel -Path $xlFile -WorksheetName "IntuneLowDisk" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    Write-Host "Exporting datasets: Intune Installed Software ($($apps.apps.Count))"
    $allapps | Sort-Object DeviceName,Product | Export-Excel -Path $xlFile -WorksheetName "IntuneSoftware" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    Write-Host "Exporting datasets: Intune Software Install Counts ($($appcounts.Count))"
    $appcounts | Export-Excel -Path $xlFile -WorksheetName "IntuneInstallCounts" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
    Write-Host "Exporting datasets: Intune Software Distinct Products ($($distapps.Count))"
    $distapps | Export-Excel -Path $xlFile -WorksheetName "IntuneSoftwareUnique" -ClearSheet -AutoSize -AutoFilter -FreezeTopRow
    if ($AzureAD) {
        Write-Host "Exporting datasets: Devices missing from Intune ($($iMissing.Count))"
        $iMissing | Export-Excel -Path $xlFile -WorksheetName "IntuneMissing" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
        Write-Host "Exporting datasets: Devices missing from Azure AD ($($aMissing.Count))"
        $aMissing | Export-Excel -Path $xlFile -WorksheetName "AADMissing" -ClearSheet -AutoSize -AutoFilter -FreezeTopRowFirstColumn
    }
    if ($Show) { Start-Process -FilePath "$xlFile" }
    Write-Host "Export saved to $xlFile" -ForegroundColor Green

    $time2 = Get-Date
    $rt = New-TimeSpan -Start $time1 -End $time2
    Write-Host "Total runtime: $($rt.Hours)`:$($rt.Minutes)`:$($rt.Seconds) (hh`:mm`:ss)" -ForegroundColor Cyan
}