Offboarding staff who have set up automations

Run the app check as a script

You'll need

• —Windows PowerShell 5.1 or PowerShell 7.
• —The Microsoft Graph module. Install it with Install-Module Microsoft.Graph.Authentication -Scope CurrentUser.
• —An account with the Global Reader role or higher. The first run asks for consent to two read permissions, which needs an administrator.
• —Business Premium for the last sign-in dates. On Business Standard the script still runs and leaves that column blank.
• —A new PowerShell window. If the Exchange Online, Teams or Az modules are already loaded, sign-in can fail. Starting PowerShell with pwsh -NoProfile avoids this.

<#
.SYNOPSIS
    Lists every app that staff have connected to their Microsoft 365 account,
    the permissions each one was given, and whether the account is still
    active. Exports the result to CSV.

.DESCRIPTION
    Read only. The script makes GET requests to Microsoft Graph and changes
    nothing in the tenant.

    When someone signs in to a third-party app with their work account and
    accepts its permission request, Entra records a consent for that person.
    The app keeps that access until the consent is removed. Resetting the
    password doesn't remove it.

    Each row is one app connected by one person. Filter the CSV by the people
    who have left, or by someone who is about to.

    MicrosoftApp is Yes for apps registered in Microsoft's own tenants. A few
    Microsoft products, such as Bing Webmaster Tools, are registered elsewhere
    and show as No, so they appear among the apps to review.

    Last sign-in dates need Entra ID P1, which Business Premium includes. On
    Business Standard the script still runs and that column is left blank.

    Published by Internacious: https://internacious.com/what-changed/m365-offboarding-checklist

.PARAMETER OutputPath
    Where to write the CSV. Defaults to connected-apps-<date>.csv in the
    current folder.

.EXAMPLE
    .\Get-ConnectedAppsReport.ps1

.NOTES
    Requires the Microsoft.Graph.Authentication module:
        Install-Module Microsoft.Graph.Authentication -Scope CurrentUser

    Sign in with an account that holds the Global Reader role or higher. The
    first run asks for consent to two read permissions (Directory.Read.All
    and AuditLog.Read.All), which needs an administrator who can grant
    consent.
#>

[CmdletBinding()]
param(
    [string]$OutputPath = (Join-Path (Get-Location) ("connected-apps-{0}.csv" -f (Get-Date -Format "yyyy-MM-dd")))
)

$ErrorActionPreference = "Stop"

$scopes = @("Directory.Read.All", "AuditLog.Read.All")
try {
    Connect-MgGraph -Scopes $scopes -NoWelcome
}
catch {
    # Another module (Exchange Online, Teams, Az, PnP) has loaded a different
    # version of the Microsoft sign-in library into this session.
    if ($_.Exception.Message -match "Could not load (type|file or assembly)") {
        throw "Sign-in failed because another PowerShell module loaded a different version of the Microsoft sign-in library first. Open a new window with 'pwsh -NoProfile' and run this script before anything else."
    }
    throw
}

# Follows @odata.nextLink until every page has been read.
function Get-AllPages([string]$Uri) {
    $items = @()
    while ($Uri) {
        $response = Invoke-MgGraphRequest -Method GET -Uri $Uri
        $items += $response.value
        $Uri = $response.'@odata.nextLink'
    }
    return $items
}

# Microsoft's own tenants. Most Microsoft apps are registered in one of these;
# the few that aren't show as not Microsoft, which errs towards review.
$microsoftTenants = @("f8cdef31-a31e-4b4a-93e4-5f571e91255a", "72f988bf-86f1-41af-91ab-2d7cd011db47")

# Consents given by individual people, as opposed to by an admin for everyone.
$grants = @(Get-AllPages "https://graph.microsoft.com/v1.0/oauth2PermissionGrants?`$filter=consentType eq 'Principal'")

if ($grants.Count -eq 0) {
    Write-Host "No apps have been connected by individual users."
    return
}

# Users, with last sign-in where the tenant has Entra ID P1.
$baseSelect = "id,displayName,userPrincipalName,accountEnabled"
$signInAvailable = $true
try {
    $users = Get-AllPages "https://graph.microsoft.com/v1.0/users?`$select=$baseSelect,signInActivity&`$top=100"
}
catch {
    if ($_.Exception.Message -match "Premium|RequestFromNonPremiumTenant|Authorization_RequestDenied|403") {
        Write-Warning "Last sign-in dates aren't available in this tenant (they need Entra ID P1). Continuing without them."
        $signInAvailable = $false
        $users = Get-AllPages "https://graph.microsoft.com/v1.0/users?`$select=$baseSelect&`$top=999"
    }
    else {
        throw
    }
}

$userById = @{}
foreach ($u in $users) { $userById[$u.id] = $u }

# Service principals are looked up once each: the app that was connected,
# and the API it was given access to (usually Microsoft Graph).
$spById = @{}
function Get-ServicePrincipal([string]$Id) {
    if (-not $spById.ContainsKey($Id)) {
        try {
            $spById[$Id] = Invoke-MgGraphRequest -Method GET -Uri ("https://graph.microsoft.com/v1.0/servicePrincipals/{0}?`$select=id,displayName,publisherName,appOwnerOrganizationId,verifiedPublisher" -f $Id)
        }
        catch {
            $spById[$Id] = @{ displayName = "(not found)"; publisherName = ""; appOwnerOrganizationId = ""; verifiedPublisher = $null }
        }
    }
    return $spById[$Id]
}

$now = Get-Date
$rows = @()
$i = 0

foreach ($grant in $grants) {
    $i++
    Write-Progress -Activity "Reading connected apps" -PercentComplete (($i / $grants.Count) * 100)

    $user = $userById[$grant.principalId]
    $app = Get-ServicePrincipal $grant.clientId
    $api = Get-ServicePrincipal $grant.resourceId
    $scopeList = @(($grant.scope -split " ") | Where-Object { $_ })

    # A verified publisher is a company Microsoft has confirmed owns the app.
    # publisherName is often empty, so it is only the fallback.
    $verifiedName = if ($app.verifiedPublisher) { $app.verifiedPublisher.displayName } else { $null }

    $lastSignIn = $null
    if ($signInAvailable -and $user -and $user.signInActivity) {
        $dates = @($user.signInActivity.lastSignInDateTime, $user.signInActivity.lastNonInteractiveSignInDateTime) |
            Where-Object { $_ } | ForEach-Object { [datetime]$_ }
        if ($dates.Count -gt 0) {
            $lastSignIn = ($dates | Sort-Object -Descending)[0]
        }
    }

    $rows += [pscustomobject]@{
        User                = if ($user) { $user.displayName } else { "(deleted user)" }
        UserPrincipalName   = if ($user) { $user.userPrincipalName } else { $grant.principalId }
        AccountEnabled      = if ($user) { $user.accountEnabled } else { $null }
        UserLastSignIn      = if ($lastSignIn) { $lastSignIn.ToString("yyyy-MM-dd") } elseif ($signInAvailable -and $user) { "None recorded" } else { $null }
        App                 = $app.displayName
        Publisher           = if ($verifiedName) { $verifiedName } else { $app.publisherName }
        VerifiedPublisher   = if ($verifiedName) { "Yes" } else { "No" }
        MicrosoftApp        = if ($microsoftTenants -contains $app.appOwnerOrganizationId) { "Yes" } else { "No" }
        # offline_access lets the app keep working without the person signing in again.
        OfflineAccess       = if ($scopeList -contains "offline_access") { "Yes" } else { "No" }
        AccessTo            = $api.displayName
        Permissions         = $scopeList -join " "
    }
}

Write-Progress -Activity "Reading connected apps" -Completed

# Disabled accounts first, then other companies' apps, then by person.
$sorted = $rows | Sort-Object @{ Expression = { $_.AccountEnabled -eq $true } }, MicrosoftApp, User, App
$sorted | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8

$people = @($rows | Select-Object -ExpandProperty UserPrincipalName -Unique).Count
$thirdParty = @($rows | Where-Object { $_.MicrosoftApp -eq "No" }).Count
$disabled = @($rows | Where-Object { $_.AccountEnabled -eq $false }).Count
$unverified = @($rows | Where-Object { $_.MicrosoftApp -eq "No" -and $_.VerifiedPublisher -eq "No" }).Count

Write-Host ""
Write-Host ("{0,-50}{1}" -f "App connections by individual users:", $rows.Count)
Write-Host ("{0,-50}{1}" -f "People with at least one connected app:", $people)
Write-Host ("{0,-50}{1}" -f "Connections to apps not published by Microsoft:", $thirdParty)
Write-Host ("{0,-50}{1}" -f "  of which from an unverified publisher:", $unverified)
Write-Host ("{0,-50}{1}" -f "Connections held by disabled accounts:", $disabled)
if ($signInAvailable) {
    $idle = @($rows | Where-Object { $_.UserLastSignIn -and $_.UserLastSignIn -ne "None recorded" -and ($now - [datetime]$_.UserLastSignIn).TotalDays -ge 90 }).Count
    Write-Host ("{0,-50}{1}" -f "Connections by people idle 90 days or more:", $idle)
}
Write-Host ""
Write-Host "Saved to $OutputPath"

Save it, open PowerShell in the same folder and run .\Get-ConnectedAppsReport.ps1

Before the next person leaves