Get-InstalledTeamsAppsPerUser.ps1

#requires -modules "Microsoft.Graph.Authentication", "Microsoft.Graph.Teams", "Microsoft.Graph.Users"

# context requires Microsoft Graph application permissions: User.Read.All + TeamsAppInstallation.ReadForUser.All
Connect-MgGraph `
    -ClientId              $env:CDX_CLIENTID `
    -CertificateThumbprint $env:CDX_THUMBPRINT `
    -TenantId              $env:CDX_TENANTID `
    -ErrorAction           Stop

$skuId = "06ebc4ee-1bb5-47dd-8120-11324bc54e06" # Microsoft 365 E5	
$skuId = "18a4bd3f-0b5b-4887-b04f-61dd0ee15f5e" # Microsoft 365 E5 (No Teams)

# get all users assigned the designated SKU
$users = Get-MgUser -Filter "assignedLicenses/any(i:i/SkuId eq $skuId)" -ConsistencyLevel Eventual -Property id, userPrincipalName -All -ErrorAction Stop

# used to track app
$dictionary = [System.Collections.Generic.Dictionary[string,string]]::new()

# scan each user
$installations = foreach( $user in $users )
{
    try
    {
        $installedApps = Get-MgUserTeamworkInstalledApp -UserId $user.id -ExpandProperty "teamsAppDefinition" -All -ErrorAction Stop

        if( $installedApps )
        {
            $uniqueInstalledUserApps = $installedApps | Select-Object @{ Name="TeamsAppId"; Expression={ $_.TeamsAppDefinition.teamsAppId }}, @{ Name="TeamsAppDisplayName"; Expression={ $_.TeamsAppDefinition.displayName }} -Unique

            $uniqueInstalledUserApps | ForEach-Object { 
            
                if( -not $dictionary.ContainsKey( $_.TeamsAppId ) )
                {
                    $displayName = $_.TeamsAppDisplayName

                    if( $dictionary.ContainsValue( $_.TeamsAppDisplayName ) )
                    {
                        $displayName = "{0} ({1})" -f $_.TeamsAppDisplayName, $_.TeamsAppId
                    }

                    $dictionary.Add( $_.TeamsAppId, $displayName )
                } 
            }

            [PSCustomObject] @{
                UserPrincipalName  = $user.UserPrincipalName
                InstalledTeamsApps = $uniqueInstalledUserApps
            }
        }
    }
    catch
    {
        Write-Host "Failed to scan user $($user.UserPrincipalName). Error: $_"
    }
}

# reshape the data to add a true/false flag for each app the user has installed
foreach( $installation in $installations )
{
    foreach( $kvp in $dictionary.GetEnumerator() )
    {
        $isAppInstalledForUser = $installation.InstalledTeamsApps.TeamsAppId -contains $kvp.key

        $installation | Add-Member -MemberType NoteProperty -Name $kvp.value -Value $isAppInstalledForUser
    }
}

# dump results to csv
$installations | Select-Object * -ExcludeProperty InstalledTeamsApps | Export-Csv -Path "InstalledTeamsAppsPerUser.csv" -NoTypeInformation

Get-ResilientInstalledTeamsAppsPerUser.ps1

Import-Module -Name PSSQLite

function Initialize-Database
{
    [CmdletBinding()]
    param
    (
        [parameter(mandatory=$true)]
        [string]
        $Path,

        [parameter(mandatory=$true)]
        [System.Collections.Specialized.OrderedDictionary]
        $Schema,

        [parameter(mandatory=$false)]
        [switch]
        $Force
    )

    begin
    {
    }
    process
    {
        if( (Test-Path -Path $Path -PathType Leaf) -and -not $Force )
        {
            return
        }
        else
        {
            foreach( $s in $Schema.GetEnumerator() )
            {
                Write-Verbose "Executing Statement: $($s.value)"

                Invoke-SqliteQuery -Database $Path -Query $s.value
            }
        }
    }
    end
    {
    }
}


# config

    $skuId = "06ebc4ee-1bb5-47dd-8120-11324bc54e06" # Microsoft 365 E5	
    $skuId = "18a4bd3f-0b5b-4887-b04f-61dd0ee15f5e" # Microsoft 365 E5 (No Teams)

    $clientId   = $env:CDX_CLIENTID
    $thumbprint = $env:CDX_THUMBPRINT
    $tenantId   = $env:CDX_TENANTID

    # file path for temp database
    $databasePath = "C:\_temp\teams-app-inventory.sqlite"

    # csv output path
    $timestamp = Get-Date -Format FileDateTime
    $csvOutputPath = "C:\_temp\InstalledTeamsAppsPerUser_$timestamp.csv"


# connect to tenant

    # context requires Microsoft Graph application permissions: User.Read.All + TeamsAppInstallation.ReadForUser.All
    Connect-MgGraph `
        -ClientId              $clientId `
        -CertificateThumbprint $thumbprint `
        -TenantId              $tenantId `
        -ErrorAction           Stop


# initialize sqlite database & table schema

    if( -not (Test-Path -Path $databasePath -PathType Leaf ) )
    {
        $schema = [ordered] @{ 
            "users" = "CREATE TABLE user ( upn VARCHAR(100) PRIMARY KEY, failed INT )" 
            "apps"  = "CREATE TABLE apps ( upn VARCHAR(100), appid VARCHAR(20), name VARCHAR(250) )"
        }

        Write-Host "[$(Get-Date)] - Initializing Sqlite database schema"

        Initialize-Database -Path $databasePath -Schema $schema -ErrorAction Stop
    }


# load user table

    # count total rows in both user and apps table
    $rowCount = Invoke-SqliteQuery -DataSource $databasePath -Query "SELECT SUM(rc) FROM (SELECT COUNT(upn) AS rc FROM user UNION ALL SELECT COUNT(upn) as RC FROM apps)" -As SingleValue

    if( $rowCount -eq 0 )
    {
        Write-Host "[$(Get-Date)] - Querying Microsoft Graph for all licensed users."

        # get all users assigned the designated SKU
        $users = Get-MgUser -Filter "assignedLicenses/any(i:i/SkuId eq $skuId)" -ConsistencyLevel Eventual -Property userPrincipalName -All -ErrorAction Stop

        # convert to datatable to bulk insert
        $datatable = $users | Select-Object @{ Name="upn"; Expression={ $_.userPrincipalName}}, failed | Out-DataTable -ErrorAction Stop

        Write-Host "[$(Get-Date)] - Bulk inserting $($users.Count) licensed users."

        # bulk insert users into the user table
        Invoke-SQLiteBulkCopy -DataTable $datatable -DataSource $databasePath -Table "user" -Confirm:$false -ErrorAction Stop
    }


# process workload
    
    $counter = 0

    # pull all users which have not failed
    $users = Invoke-SqliteQuery -DataSource $databasePath -Query "SELECT upn FROM user WHERE failed IS NULL ORDER BY upn COLLATE NOCASE ASC" -As PSObject -ErrorAction Stop

    Write-Host "[$(Get-Date)] - Discovered $($users.Count) existing users in the database."

    # enumerate users
    foreach( $user in $users )
    {
        $counter++ 

        Write-Host "[$(Get-Date)] - ($counter/$($users.Count)) - Processing user: $($user.upn)"

        try
        {
            # lookup apps the user has installed
            $installedApps = Get-MgUserTeamworkInstalledApp -UserId $user.upn -ExpandProperty "teamsAppDefinition" -All -ErrorAction Stop

            if( $installedApps )
            {
                # get unique apps per user & insert into table
                $datatable = $installedApps | Select-Object @{ Name="upn"; Expression={ $user.upn }}, @{ Name="appid"; Expression={ $_.TeamsAppDefinition.teamsAppId }}, @{ Name="name"; Expression={ $_.TeamsAppDefinition.displayName }} -Unique | Out-DataTable -ErrorAction Stop
                
                Invoke-SQLiteBulkCopy -DataTable $datatable -DataSource $databasePath -Table "apps" -Confirm:$false -ErrorAction Stop
            }
            
            # remove user from inventory so it's not processed again in the event of a script restart
            Invoke-SqliteQuery -DataSource $databasePath -Query "DELETE FROM user WHERE upn = @upn" -SqlParameters @{ upn = $user.upn } -ErrorAction Stop
        }
        catch
        {
            Write-Error "Failed to process user: $($user.upn). Error: $_"
            
            Invoke-SqliteQuery -DataSource $databasePath -Query "UPDATE user SET failed = 1 WHERE upn = @upn" -SqlParameters @{ upn = $user.upn }
        }
    }

# export results from sql to csv


    Write-Host "[$(Get-Date)] - Collating data from the database to csv"

    $uniqueapps = Invoke-SqliteQuery -DataSource $databasePath -Query "SELECT DISTINCT appid, name FROM apps ORDER BY name COLLATE NOCASE ASC" -As PSObject -ErrorAction Stop
        
    $uniqueUsers = Invoke-SqliteQuery -DataSource $databasePath -Query "SELECT DISTINCT upn FROM apps ORDER BY upn COLLATE NOCASE ASC" -As PSObject -ErrorAction Stop

    <#  LOAD TESTING
        
        $uniqueapps += 1..3000 | ForEach-Object { $g = (New-Guid).ToString(); [PSCustomObject] @{ appid = $g; name = $g } }
        $uniqueapps.Count 

        $uniqueUsers = 1..10000 | ForEach-Object { $users | ForEach-Object { $_ } }        
        $uniqueUsers.Count
    #>

    Write-Host "[$(Get-Date)] - Discovered $($uniqueapps.Count) unique Teams apps"
    Write-Host "[$(Get-Date)] - Discovered $($uniqueUsers.Count) unique users"

    Write-Host "[$(Get-Date)] - Creating Teams apps column mappings"

    $dictionary = [System.Collections.Generic.Dictionary[string,string]]::new()

    $template = [PSCustomObject] @{ UserPrincipalName = $null }

    # since app names are not unique, we need to make them unique so we have unique column names
    $uniqueapps | ForEach-Object { 
            
        if( -not $dictionary.ContainsKey( $_.appid ) )
        {
            $displayName = $_.name.ToLower()

            if( $dictionary.ContainsValue( $displayName ) )
            {
                $displayName = "{0} ({1})" -f $displayName, $_.appid
            }

            $dictionary.Add( $_.appid, $displayName )

            $template | Add-Member -MemberType NoteProperty -Name $displayName -Value $false -ErrorAction Stop
        }
    }

    # cache of results that will flush to disk every 5k entries
    $objects = New-Object System.Collections.Generic.List[object]

    $counter = 0

    $sw = [System.Diagnostics.StopWatch]::StartNew()

    # enumerate users from database
    foreach( $user in $uniqueUsers )
    {
        $counter++

        if( $counter % 1000 -eq 0 ) 
        {
            Write-Host "[$(Get-Date)] - Processed $counter users."
        }

        try
        {
            $result = $template.psobject.Copy()
            $result.UserPrincipalName = $user.upn

            # lookup all the apps for the current user
            $userapps = Invoke-SqliteQuery -DataSource $databasePath -Query "SELECT DISTINCT appid FROM apps WHERE upn = @upn" -SqlParameters @{ upn = $result.UserPrincipalName } -ErrorAction Stop

            $hashset = [System.Collections.Generic.Hashset[string]]$userapps.appid

            $intersections = [System.Linq.Enumerable]::Intersect( $dictionary.Keys, $hashset )

            foreach( $intersection in $intersections )
            {
                $value = $dictionary[$intersection]

                $result."$($value)" = $true
            }

            $objects.Add( $result )

            if( $objects.Count -ge 1000 )
            {
                Write-Host "[$(Get-Date)] - Flushing $($objects.Count) rows to disk. Elapsed Seconds: $($sw.Elapsed.TotalSeconds)"

                $objects | Select-Object @{ Name="UserPrincipalName"; Expression={ $_.UserPrincipalName }}, * -ExcludeProperty UserPrincipalName | Export-Csv -Path $csvOutputPath -Append -NoTypeInformation -ErrorAction Stop
            
                #foreach( $object in $objects )
                #{
                #    Invoke-SqliteQuery -DataSource $databasePath -Query "DELETE FROM apps WHERE upn = @upn" -SqlParameters @{ upn = $user.UserPrincipalName } -ErrorAction Stop
                #}

                $objects.Clear()
            }
        }
        catch
        {
            Write-Error "Error flushing report to disk. Error: $_"
        }
    }

    # flush remaining rows to disk
    if( $objects.Count -ge 0 )
    {
        Write-Host "[$(Get-Date)] - Flushing $($objects.Count) rows to disk"

        $objects | Select-Object @{ Name="UserPrincipalName"; Expression={ $_.UserPrincipalName }}, * -ExcludeProperty UserPrincipalName | Export-Csv -Path $csvOutputPath -Append -NoTypeInformation

        foreach( $object in $objects )
        {
            Invoke-SqliteQuery -DataSource $databasePath -Query "DELETE FROM apps WHERE upn = @upn" -SqlParameters @{ upn = $object.UserPrincipalName }
        }

        $objects.Clear()
    }


Write-Host "[$(Get-Date)] - Completed report generation in $($sw.Elapsed.TotalMinutes) minutes."