Power Platform PowerShell scripts

#Requires -Version 5.1
#Requires -Modules Microsoft.Graph.Authentication

<#
.SYNOPSIS
    Finds Power Platform and Dynamics 365 licences assigned to users who have not signed
    in recently. Read-only: it reports, it never releases a licence.

.DESCRIPTION
    The fastest money in a Power Platform tenant is a licence nobody is using. This script
    reads every licensed user from Microsoft Graph together with their sign-in activity,
    keeps the Power Platform and Dynamics 365 SKUs, and reports the ones whose owner has
    been quiet for longer than -DaysInactive.

    It flags three things, in the order they are worth chasing:

      1. Licences still assigned to disabled accounts. Disabling an account does not
         release its licences. Most tenants have some. This is the cheapest win there is.
      2. Licences on accounts that have never signed in at all. Usually a leaver who was
         provisioned and never started, or a test account somebody forgot.
      3. Licences on accounts whose last sign-in is older than -DaysInactive.

    Sign-in activity comes from the signInActivity property on the user object, which
    carries both interactive and non-interactive sign-ins. The script takes the later of
    the two, so a service-style account that only ever authenticates non-interactively is
    not wrongly reported as idle. That distinction matters: reporting an integration
    account as unused is how you cause an outage while trying to save money.

    Nothing here writes. Removing a licence needs business context this script does not
    have, so the output is a review list and a CSV, not an action.

.PARAMETER DaysInactive
    How many days of silence make a licence a candidate. Default 90.

.PARAMETER OutputPath
    Optional path to a CSV file. The full result set is written there.

.PARAMETER SkuPattern
    Regular expression matched against the SKU part number to decide what counts as a
    Power Platform or Dynamics 365 licence. The default covers the Power Apps, Power
    Automate, Power Pages, Copilot Studio and Dynamics 365 families. Pass '.' to report
    every SKU in the tenant.

.PARAMETER TenantId
    Optional tenant id or domain passed through to Connect-MgGraph.

.PARAMETER SkipConnect
    Reuse the Microsoft Graph session that is already open instead of calling
    Connect-MgGraph. Useful when you are running several of these scripts in one session.

.EXAMPLE
    .\Get-IdlePowerPlatformLicenses.ps1

    Connects, then lists every Power Platform and Dynamics 365 licence whose owner has
    not signed in for 90 days.

.EXAMPLE
    .\Get-IdlePowerPlatformLicenses.ps1 -DaysInactive 30 -OutputPath .\idle-licences.csv

    Tightens the window to 30 days and writes the full result set to CSV for a licence
    review meeting.

.EXAMPLE
    .\Get-IdlePowerPlatformLicenses.ps1 -SkuPattern '^DYN365' -Verbose |
        Format-Table Reason, DisplayName, LicenceName, DaysSinceSignIn -AutoSize

    Dynamics 365 SKUs only, with progress, formatted for a report.

.NOTES
    Modules      Microsoft.Graph.Authentication, Microsoft.Graph.Users and
                 Microsoft.Graph.Identity.DirectoryManagement (v2.0 or later).
                 Install-Module Microsoft.Graph -Scope CurrentUser

    Scopes       User.Read.All, AuditLog.Read.All, Organization.Read.All

                 AuditLog.Read.All is the one people forget. Reading signInActivity
                 needs it on top of User.Read.All, and Graph normally rejects the whole
                 query rather than answering it without the sign-in data. The script
                 checks the session for the scope up front and says so, because the
                 failure mode people invent for themselves - dropping signInActivity
                 from the query to make the error go away - produces a report in which
                 every account looks dormant.

    Licensing    signInActivity requires a Microsoft Entra ID P1 or P2 licence in the
                 tenant. Without one the property is not available at all. That is a
                 Microsoft restriction, not a bug in this script, and it is the usual
                 reason a run comes back with sign-in dates missing everywhere.

    Read-only    GET requests only. It does not assign, remove or modify a licence and
                 it does not touch an account. The only thing it writes anywhere is the
                 CSV you ask for with -OutputPath, on your own disk.

    Run time     Around one to three minutes for 10,000 users. Reading signInActivity is
                 slower than a plain user list, so budget more time than you expect on a
                 large tenant.

    Author       VerseBlocks - https://www.verseblocks.com
#>

[CmdletBinding()]
param(
    [ValidateRange(1, 3650)]
    [int]$DaysInactive = 90,

    [string]$OutputPath,

    # Part numbers are inconsistent about underscores between families, so the optional
    # ones here are not decoration: POWER_VIRTUAL_AGENTS_VIRAL_TRIAL and POWERAPPS_PER_USER
    # both have to match, and an anchored pattern without them silently drops a family.
    [string]$SkuPattern = '^(POWER_?APPS|POWERFLOW|POWER_?AUTOMATE|FLOW_|POWER_?PAGES|POWER_?VIRTUAL_?AGENT|VIRTUAL_AGENT|CCIBOTS|CDSAICAPACITY|DYN365|D365_|Dynamics_365)',

    [string]$TenantId,

    [switch]$SkipConnect
)

# ---------------------------------------------------------------------------
# Friendly names for the SKU part numbers you actually meet in the wild.
# Anything not listed here is still reported, by its raw part number, so the
# script never hides a licence just because the map is incomplete. Microsoft
# publishes the full list at:
#   https://learn.microsoft.com/en-us/entra/identity/users/licensing-service-plan-reference
# Add rows as you meet them.
# ---------------------------------------------------------------------------
$SkuFriendlyName = @{
    'POWERAPPS_PER_USER'                 = 'Power Apps Premium (per user)'
    'POWERAPPS_PER_APP'                  = 'Power Apps per app plan'
    'POWERAPPS_PER_APP_IWTRIAL'          = 'Power Apps per app baseline access'
    'POWERAPPS_DEV'                      = 'Power Apps Developer Plan (free)'
    'POWERAPPS_VIRAL'                    = 'Power Apps Plan 2 Trial'
    'POWERFLOW_P2'                       = 'Power Apps Plan 2'
    'FLOW_PER_USER'                      = 'Power Automate per user plan'
    'FLOW_PER_USER_DEPT'                 = 'Power Automate per user plan (department)'
    'FLOW_PER_FLOW'                      = 'Power Automate per flow plan'
    'FLOW_BUSINESS_PROCESS'              = 'Power Automate per flow plan'
    'FLOW_FREE'                          = 'Power Automate Free'
    'POWERAUTOMATE_ATTENDED_RPA'         = 'Power Automate Premium'
    'POWERAUTOMATE_UNATTENDED_RPA'       = 'Power Automate unattended RPA add-on'
    'CDSAICAPACITY'                      = 'AI Builder capacity add-on'
    'DYN365_ENTERPRISE_PLAN1'            = 'Dynamics 365 Customer Engagement Plan'
    'DYN365_ENTERPRISE_SALES'            = 'Dynamics 365 Sales Enterprise'
    'DYN365_SALES_PREMIUM'               = 'Dynamics 365 Sales Premium'
    'D365_SALES_PRO'                     = 'Dynamics 365 Sales Professional'
    'DYN365_ENTERPRISE_CUSTOMER_SERVICE' = 'Dynamics 365 Customer Service Enterprise'
    'DYN365_CUSTOMER_SERVICE_PRO'        = 'Dynamics 365 Customer Service Professional'
    'DYN365_ENTERPRISE_FIELD_SERVICE'    = 'Dynamics 365 Field Service Enterprise'
    'DYN365_ENTERPRISE_TEAM_MEMBERS'     = 'Dynamics 365 Enterprise Team Members'
    'DYN365_TEAM_MEMBERS'                = 'Dynamics 365 Team Members'
    'DYN365_FINANCE'                     = 'Dynamics 365 Finance'
    'DYN365_SCM'                         = 'Dynamics 365 Supply Chain Management'
    'DYN365_BUSCENTRAL_ESSENTIAL'        = 'Dynamics 365 Business Central Essentials'
    'DYN365_BUSCENTRAL_PREMIUM'          = 'Dynamics 365 Business Central Premium'
    'DYN365_BUSCENTRAL_TEAM_MEMBER'      = 'Dynamics 365 Business Central Team Members'
    'DYN365_MARKETING_APP'               = 'Dynamics 365 Customer Insights - Journeys'
}

# --- Module check ----------------------------------------------------------
$requiredModules = @(
    'Microsoft.Graph.Authentication'
    'Microsoft.Graph.Users'
    'Microsoft.Graph.Identity.DirectoryManagement'
)
$missingModules = @($requiredModules | Where-Object { -not (Get-Module -ListAvailable -Name $_) })
if ($missingModules.Count -gt 0) {
    Write-Error ("Missing PowerShell module(s): {0}. Install the Microsoft Graph SDK, then run this script again:  Install-Module Microsoft.Graph -Scope CurrentUser -Repository PSGallery" -f ($missingModules -join ', '))
    return
}

# --- Connect ---------------------------------------------------------------
$requiredScopes = @('User.Read.All', 'AuditLog.Read.All', 'Organization.Read.All')

if (-not $SkipConnect) {
    Write-Verbose 'Connecting to Microsoft Graph.'
    $connectArgs = @{ Scopes = $requiredScopes; ErrorAction = 'Stop' }
    if ($TenantId) { $connectArgs['TenantId'] = $TenantId }

    # -NoWelcome only exists on Microsoft.Graph 2.x and later. Ask before you pass it.
    $connectCommand = Get-Command Connect-MgGraph -ErrorAction SilentlyContinue
    if ($connectCommand -and $connectCommand.Parameters.ContainsKey('NoWelcome')) {
        $connectArgs['NoWelcome'] = $true
    }

    try {
        Connect-MgGraph @connectArgs
    }
    catch {
        Write-Error ("Could not connect to Microsoft Graph: {0}. Sign in with an account that already holds, or can consent to, User.Read.All, AuditLog.Read.All and Organization.Read.All." -f $_.Exception.Message)
        return
    }
}

$context = Get-MgContext
if (-not $context) {
    Write-Error 'No Microsoft Graph session. Run Connect-MgGraph first, or drop -SkipConnect and let this script connect for you.'
    return
}
Write-Verbose ("Connected to tenant {0} as {1}." -f $context.TenantId, $context.Account)

if ($context.Scopes -notcontains 'AuditLog.Read.All') {
    Write-Warning 'This Graph session does not hold AuditLog.Read.All. Reading signInActivity needs it on top of User.Read.All, so the user query will most likely be refused outright, and anywhere it is not, sign-in dates come back empty and every account looks dormant. Reconnect with: Connect-MgGraph -Scopes "User.Read.All","AuditLog.Read.All","Organization.Read.All"'
}

# --- SKU lookup ------------------------------------------------------------
Write-Verbose 'Reading subscribed SKUs.'
try {
    $subscribedSkus = Get-MgSubscribedSku -ErrorAction Stop
}
catch {
    Write-Error ("Could not read subscribed SKUs: {0}. This needs the Organization.Read.All or Directory.Read.All scope." -f $_.Exception.Message)
    return
}

$skuPartNumberById = @{}
foreach ($sku in $subscribedSkus) {
    $skuPartNumberById[[string]$sku.SkuId] = [string]$sku.SkuPartNumber
}
Write-Verbose ("Found {0} subscribed SKUs in the tenant." -f $skuPartNumberById.Count)

# --- Users -----------------------------------------------------------------
$userProperties = @(
    'id'
    'displayName'
    'userPrincipalName'
    'accountEnabled'
    'createdDateTime'
    'department'
    'usageLocation'
    'signInActivity'
    'assignedLicenses'
)

Write-Verbose 'Reading licensed users and sign-in activity. On a large tenant this is the slow part.'
$users = $null
try {
    $users = Get-MgUser -All -Property $userProperties -Filter 'assignedLicenses/$count ne 0' -ConsistencyLevel eventual -CountVariable licensedUserCount -ErrorAction Stop
    Write-Verbose ("Graph reports {0} licensed users." -f $licensedUserCount)
}
catch {
    Write-Verbose ("Advanced query filter was rejected ({0}). Falling back to reading every user and filtering locally." -f $_.Exception.Message)
    try {
        $users = Get-MgUser -All -Property $userProperties -ErrorAction Stop
    }
    catch {
        Write-Error ("Could not read users from Microsoft Graph: {0}. Check the signed-in account holds User.Read.All and AuditLog.Read.All." -f $_.Exception.Message)
        return
    }
}

if (-not $users) {
    Write-Warning 'Microsoft Graph returned no users. Nothing to report.'
    return
}

# --- Evaluate --------------------------------------------------------------
$now = Get-Date
$cutoff = $now.AddDays(-$DaysInactive)
$results = New-Object System.Collections.Generic.List[object]
$usersWithoutSignInData = 0
$examined = 0

foreach ($user in $users) {
    $examined++
    if ($examined % 500 -eq 0) { Write-Verbose ("Examined {0} users." -f $examined) }

    if (-not $user.AssignedLicenses -or @($user.AssignedLicenses).Count -eq 0) { continue }

    $lastInteractive = $null
    $lastNonInteractive = $null
    if ($user.SignInActivity) {
        $lastInteractive = $user.SignInActivity.LastSignInDateTime
        $lastNonInteractive = $user.SignInActivity.LastNonInteractiveSignInDateTime
    }

    # Take the later of the two. A non-interactive sign-in still means the account is
    # doing work, and switching off an integration account is not a saving.
    $lastSignIn = $null
    foreach ($candidate in @($lastInteractive, $lastNonInteractive)) {
        if ($candidate -and (-not $lastSignIn -or $candidate -gt $lastSignIn)) { $lastSignIn = $candidate }
    }
    if (-not $lastSignIn) { $usersWithoutSignInData++ }

    $daysSinceSignIn = $null
    if ($lastSignIn) { $daysSinceSignIn = [int][math]::Floor(($now - $lastSignIn).TotalDays) }

    $accountEnabled = [bool]$user.AccountEnabled
    $isIdle = (-not $lastSignIn) -or ($lastSignIn -lt $cutoff)

    # An enabled account that signed in inside the window is fine. A disabled account is
    # reported whatever its sign-in history says, because the licence is still being paid
    # for and cannot possibly be in use.
    if (-not $isIdle -and $accountEnabled) { continue }

    $priority = 3
    $reason = "No sign-in for $daysSinceSignIn days"
    if (-not $lastSignIn) {
        $priority = 2
        $reason = 'Never signed in'
    }
    if (-not $accountEnabled) {
        $priority = 1
        $reason = 'Account disabled, licence still assigned'
    }

    foreach ($assigned in $user.AssignedLicenses) {
        $skuId = [string]$assigned.SkuId
        if (-not $skuPartNumberById.ContainsKey($skuId)) { continue }

        $partNumber = $skuPartNumberById[$skuId]
        if ($partNumber -notmatch $SkuPattern) { continue }

        $friendlyName = $partNumber
        if ($SkuFriendlyName.ContainsKey($partNumber)) { $friendlyName = $SkuFriendlyName[$partNumber] }

        $results.Add([pscustomobject]@{
            Priority                 = $priority
            Reason                   = $reason
            DisplayName              = $user.DisplayName
            UserPrincipalName        = $user.UserPrincipalName
            AccountEnabled           = $accountEnabled
            LicenceName              = $friendlyName
            SkuPartNumber            = $partNumber
            LastSignIn               = $lastSignIn
            DaysSinceSignIn          = $daysSinceSignIn
            LastInteractiveSignIn    = $lastInteractive
            LastNonInteractiveSignIn = $lastNonInteractive
            Department               = $user.Department
            UsageLocation            = $user.UsageLocation
            AccountCreated           = $user.CreatedDateTime
            UserId                   = $user.Id
        })
    }
}

$sorted = @($results | Sort-Object Priority, @{ Expression = 'DaysSinceSignIn'; Descending = $true })

# --- Output ----------------------------------------------------------------
if ($OutputPath) {
    try {
        $sorted | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8 -ErrorAction Stop
        Write-Verbose ("Wrote {0} rows to {1}." -f $sorted.Count, $OutputPath)
    }
    catch {
        Write-Error ("Could not write the CSV to '{0}': {1}. Check the folder exists and is writable, then run again." -f $OutputPath, $_.Exception.Message)
    }
}

$sorted

Write-Host ''
Write-Host 'Idle Power Platform and Dynamics 365 licences' -ForegroundColor Cyan
Write-Host ('  Users examined           : {0}' -f $examined)
Write-Host ('  Inactivity window        : {0} days (nothing since {1:yyyy-MM-dd})' -f $DaysInactive, $cutoff)
Write-Host ('  Candidate licences found : {0}' -f $sorted.Count)

if ($sorted.Count -gt 0) {
    $onDisabled = @($sorted | Where-Object { $_.Priority -eq 1 }).Count
    $neverUsed = @($sorted | Where-Object { $_.Priority -eq 2 }).Count
    $staleUsed = @($sorted | Where-Object { $_.Priority -eq 3 }).Count
    Write-Host ('    On disabled accounts   : {0}' -f $onDisabled)
    Write-Host ('    Never signed in        : {0}' -f $neverUsed)
    Write-Host ('    Idle beyond the window : {0}' -f $staleUsed)
    Write-Host ''
    Write-Host '  By licence:'
    foreach ($group in ($sorted | Group-Object LicenceName | Sort-Object Count -Descending)) {
        Write-Host ('    {0,-52} {1}' -f $group.Name, $group.Count)
    }
}

if ($usersWithoutSignInData -gt 0) {
    Write-Host ''
    Write-Host ('  {0} accounts came back with no sign-in data at all.' -f $usersWithoutSignInData) -ForegroundColor Yellow
    Write-Host '  A handful is normal - those accounts really have never signed in. If it is most' -ForegroundColor Yellow
    Write-Host '  of the tenant, the cause is almost always no Entra ID P1/P2 licence, which means' -ForegroundColor Yellow
    Write-Host '  signInActivity is not available at all. Fix that before you act on this list.' -ForegroundColor Yellow
}

Write-Host ''
Write-Host '  Nothing was changed. Confirm each account with its owner before you reclaim.'
Write-Host ''

#Requires -Version 5.1

# There is deliberately no '#Requires -Modules Az.Accounts' here. Az is only used to fetch
# a token, and -AccessToken skips it entirely -- a #Requires line would refuse to start the
# script on a machine that never needed the module. The check further down does the same
# job at the point where it is actually true, and tells you what to install.

<#
.SYNOPSIS
    Reports row counts per Dataverse table, largest first, so you can see what is eating
    database capacity. Read-only: it queries metadata and counts, and writes nothing back.

.DESCRIPTION
    Dataverse database capacity is billed per gigabyte and it is almost never the tables
    you built that fill it. It is the plumbing: asyncoperation, syncerror,
    plugintracelog, activitypointer, duplicaterecord. Those grow quietly for years until
    somebody gets a capacity email, and by then the cheapest fix is a bulk delete job
    that should have been scheduled on day one.

    This script connects to a Dataverse environment with the Web API, reads the table
    metadata, and calls the RetrieveTotalRecordCount function to get the row count for
    every table it can. It sorts descending, estimates a size, and optionally writes CSV.

    Two things to understand before you trust the output:

      1. Row counts come from a snapshot Dataverse maintains, not a live COUNT(*). It is
         refreshed periodically, so a table you filled an hour ago can read low. It is
         accurate enough to rank tables; it is not an audit.

      2. Dataverse does not expose per-table byte counts through the Web API. The
         EstimatedMB and EstimatedGB columns are simply rows x -BytesPerRow. They are a
         planning aid for ranking, not a figure to put in a budget. The authoritative
         per-table storage numbers are in the Power Platform admin center under
         Manage > Environments > [environment] > Capacity. Calibrate -BytesPerRow by
         dividing the database GB shown there by the total row count this script reports,
         then re-run for a much better estimate.

    Some internal tables, including principalobjectaccess (the access cache that is
    often one of the largest consumers in a heavily shared environment), are not returned
    by RetrieveTotalRecordCount at all. If your totals here are far below the capacity
    reported in the admin center, that gap is where to look next.

.PARAMETER EnvironmentUrl
    The environment URL, for example https://contoso.crm.dynamics.com. No trailing path.

.PARAMETER OutputPath
    Optional path to a CSV file. Every row is written, regardless of -Top.

.PARAMETER BytesPerRow
    Average bytes per row used for the size estimate. Default 2048. This is a rough
    planning figure chosen by this script, not a Microsoft number. See the description
    for how to calibrate it against your own environment.

.PARAMETER MinimumRows
    Skip tables with fewer than this many rows. Default 1, which hides the several
    hundred empty system tables every environment carries.

.PARAMETER Top
    Show only the largest N tables in the console summary. 0 means all. The CSV and the
    pipeline output are never truncated.

.PARAMETER CustomTablesOnly
    Report only custom tables. Useful when you already know the platform tables are the
    problem and you want to see what your own solutions are contributing.

.PARAMETER ChunkSize
    How many table names to ask for per RetrieveTotalRecordCount call. Default 20. Lower
    it if you hit request size limits on an environment with very long table names.

.PARAMETER ApiVersion
    Dataverse Web API version. Default v9.2.

.PARAMETER AccessToken
    Supply your own bearer token for the environment instead of letting the script get
    one from the current Az session. Useful in a pipeline that already holds a token.

.EXAMPLE
    .\Get-DataverseCapacityByTable.ps1 -EnvironmentUrl https://contoso.crm.dynamics.com

    Signs in with Az if needed and prints the twenty-five largest tables.

.EXAMPLE
    .\Get-DataverseCapacityByTable.ps1 -EnvironmentUrl https://contoso.crm.dynamics.com `
        -OutputPath .\contoso-tables.csv -Top 50 -Verbose

    Writes every table to CSV and shows the fifty largest, with progress.

.EXAMPLE
    .\Get-DataverseCapacityByTable.ps1 -EnvironmentUrl https://contoso.crm.dynamics.com |
        Where-Object RowCount -gt 1000000 |
        Select-Object LogicalName, RowCount, IsCustom

    Just the tables over a million rows, as objects you can keep working with.

.NOTES
    Modules      Az.Accounts, only to acquire a token.
                 Install-Module Az.Accounts -Scope CurrentUser
                 If you would rather not install Az, pass -AccessToken and the module is
                 never called.

    Permissions  A Dataverse security role that can read entity metadata and record
                 counts. System Administrator or System Customizer both work. There is
                 no separate API permission to grant: the token is issued for the
                 environment URL and Dataverse applies your existing role.

    Read-only    GET requests to /EntityDefinitions and the RetrieveTotalRecordCount
                 function only. No create, update, delete or bulk delete is issued. The
                 only thing it writes anywhere is the CSV you ask for with -OutputPath,
                 on your own disk.

    Run time     Under a minute for a typical environment. Roughly one request per 20
                 tables, so 1,000 tables is about 50 calls.

    Author       VerseBlocks - https://www.verseblocks.com
#>

[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)]
    [ValidatePattern('^https://')]
    [string]$EnvironmentUrl,

    [string]$OutputPath,

    [ValidateRange(1, 1048576)]
    [int]$BytesPerRow = 2048,

    [ValidateRange(0, [int]::MaxValue)]
    [int]$MinimumRows = 1,

    [ValidateRange(0, 10000)]
    [int]$Top = 25,

    [switch]$CustomTablesOnly,

    [ValidateRange(1, 100)]
    [int]$ChunkSize = 20,

    [string]$ApiVersion = 'v9.2',

    [string]$AccessToken
)

$resourceUrl = $EnvironmentUrl.TrimEnd('/')
$apiBase = "$resourceUrl/api/data/$ApiVersion/"

# --- Token -----------------------------------------------------------------
$token = $AccessToken

if (-not $token) {
    if (-not (Get-Module -ListAvailable -Name Az.Accounts)) {
        Write-Error 'Az.Accounts is not installed, and no -AccessToken was supplied. Install it with:  Install-Module Az.Accounts -Scope CurrentUser -Repository PSGallery   Or acquire a token yourself and pass it with -AccessToken.'
        return
    }

    try {
        Import-Module Az.Accounts -ErrorAction Stop
    }
    catch {
        Write-Error ("Could not load Az.Accounts: {0}. Try Install-Module Az.Accounts -Scope CurrentUser -Force." -f $_.Exception.Message)
        return
    }

    if (-not (Get-AzContext -ErrorAction SilentlyContinue)) {
        Write-Verbose 'No Az context found. Signing in.'
        try {
            Connect-AzAccount -ErrorAction Stop | Out-Null
        }
        catch {
            Write-Error ("Sign-in failed: {0}. Run Connect-AzAccount manually, then run this script again." -f $_.Exception.Message)
            return
        }
    }

    Write-Verbose ("Requesting a token for {0}." -f $resourceUrl)
    try {
        $tokenResponse = Get-AzAccessToken -ResourceUrl $resourceUrl -ErrorAction Stop
    }
    catch {
        Write-Error ("Could not get a token for '{0}': {1}. Check the environment URL is exactly the one shown in the Power Platform admin center, and that your Az session is in the same tenant." -f $resourceUrl, $_.Exception.Message)
        return
    }

    # Az.Accounts 5.x returns a SecureString by default; earlier versions return a plain
    # string. Handle both so the script does not break on a module upgrade.
    if ($tokenResponse.Token -is [System.Security.SecureString]) {
        $token = [System.Net.NetworkCredential]::new('', $tokenResponse.Token).Password
    }
    else {
        $token = [string]$tokenResponse.Token
    }
}

if (-not $token) {
    Write-Error 'No access token was obtained. Pass -AccessToken, or sign in with Connect-AzAccount and run again.'
    return
}

$headers = @{
    'Authorization'    = "Bearer $token"
    'Accept'           = 'application/json'
    'OData-MaxVersion' = '4.0'
    'OData-Version'    = '4.0'
}

# --- Table metadata --------------------------------------------------------
$metadataQuery = 'EntityDefinitions?$select=LogicalName,SchemaName,DisplayName,IsCustomEntity,IsActivity,IsIntersect,IsPrivate,IsManaged'
$uri = $apiBase + $metadataQuery
$tables = New-Object System.Collections.Generic.List[object]

Write-Verbose 'Reading table metadata.'
try {
    while ($uri) {
        $response = Invoke-RestMethod -Method Get -Uri $uri -Headers $headers -ErrorAction Stop
        foreach ($item in $response.value) { $tables.Add($item) }
        $uri = $response.'@odata.nextLink'
    }
}
catch {
    $statusCode = $null
    if ($_.Exception.Response) { $statusCode = [int]$_.Exception.Response.StatusCode }
    if ($statusCode -eq 401 -or $statusCode -eq 403) {
        Write-Error ("Dataverse rejected the token ({0}). The account is authenticated but has no user record or no read access in this environment. Add it as a user with a security role, then run again." -f $statusCode)
    }
    else {
        Write-Error ("Could not read table metadata from {0}: {1}. Confirm the environment URL and that the environment has a Dataverse database." -f $resourceUrl, $_.Exception.Message)
    }
    return
}

Write-Verbose ("Metadata returned {0} tables." -f $tables.Count)

# Relationship and private tables are never countable. Virtual tables backed by an
# external data provider are not either, but there is no reliable metadata flag for
# them here, so they fall out in the per-table retry below instead of being guessed at.
$candidates = @($tables | Where-Object {
    -not $_.IsIntersect -and
    -not $_.IsPrivate -and
    (-not $CustomTablesOnly -or $_.IsCustomEntity)
})

if ($candidates.Count -eq 0) {
    Write-Warning 'No countable tables matched. If you used -CustomTablesOnly, this environment may have no custom tables.'
    return
}

Write-Verbose ("Counting rows for {0} tables in chunks of {1}." -f $candidates.Count, $ChunkSize)

# --- Row counts ------------------------------------------------------------
function Get-RecordCountBatch {
    [CmdletBinding()]
    param(
        [string[]]$LogicalName,
        [string]$ApiBase,
        [hashtable]$Headers
    )

    $counts = @{}
    if (-not $LogicalName -or $LogicalName.Count -eq 0) { return $counts }

    $nameList = ($LogicalName | ForEach-Object { "'" + $_ + "'" }) -join ','
    $requestUri = "$ApiBase" + "RetrieveTotalRecordCount(EntityNames=[$nameList])"

    $response = Invoke-RestMethod -Method Get -Uri $requestUri -Headers $Headers -ErrorAction Stop
    $collection = $response.EntityRecordCountCollection
    if (-not $collection) { return $counts }

    $keys = @($collection.Keys)
    $values = @($collection.Values)
    for ($i = 0; $i -lt $keys.Count; $i++) {
        $counts[[string]$keys[$i]] = [long]$values[$i]
    }
    return $counts
}

$rowCounts = @{}
$uncountable = New-Object System.Collections.Generic.List[string]
$processed = 0

for ($index = 0; $index -lt $candidates.Count; $index += $ChunkSize) {
    $last = [math]::Min($index + $ChunkSize - 1, $candidates.Count - 1)
    $chunk = @($candidates[$index..$last] | ForEach-Object { $_.LogicalName })

    try {
        $batch = Get-RecordCountBatch -LogicalName $chunk -ApiBase $apiBase -Headers $headers
        foreach ($key in $batch.Keys) { $rowCounts[$key] = $batch[$key] }
    }
    catch {
        # One bad table poisons the whole batch, so fall back to counting the chunk
        # one table at a time and skip only the ones that genuinely refuse.
        Write-Verbose ("Batch starting at {0} failed ({1}). Retrying those tables individually." -f $index, $_.Exception.Message)
        foreach ($name in $chunk) {
            try {
                $single = Get-RecordCountBatch -LogicalName @($name) -ApiBase $apiBase -Headers $headers
                foreach ($key in $single.Keys) { $rowCounts[$key] = $single[$key] }
            }
            catch {
                $uncountable.Add($name)
                Write-Verbose ("Skipping {0}: {1}" -f $name, $_.Exception.Message)
            }
        }
    }

    $processed += $chunk.Count
    Write-Progress -Activity 'Counting Dataverse rows' -Status ("{0} of {1} tables" -f $processed, $candidates.Count) -PercentComplete ([int](100 * $processed / $candidates.Count))
}

Write-Progress -Activity 'Counting Dataverse rows' -Completed

# --- Shape the result ------------------------------------------------------
$results = New-Object System.Collections.Generic.List[object]

foreach ($table in $candidates) {
    $logicalName = [string]$table.LogicalName
    if (-not $rowCounts.ContainsKey($logicalName)) { continue }

    $rows = [long]$rowCounts[$logicalName]
    if ($rows -lt $MinimumRows) { continue }

    $displayName = $logicalName
    if ($table.DisplayName -and $table.DisplayName.UserLocalizedLabel -and $table.DisplayName.UserLocalizedLabel.Label) {
        $displayName = [string]$table.DisplayName.UserLocalizedLabel.Label
    }

    $estimatedBytes = [double]$rows * $BytesPerRow

    $results.Add([pscustomobject]@{
        DisplayName  = $displayName
        LogicalName  = $logicalName
        SchemaName   = [string]$table.SchemaName
        RowCount     = $rows
        EstimatedMB  = [math]::Round($estimatedBytes / 1MB, 2)
        EstimatedGB  = [math]::Round($estimatedBytes / 1GB, 3)
        IsCustom     = [bool]$table.IsCustomEntity
        IsActivity   = [bool]$table.IsActivity
        IsManaged    = [bool]$table.IsManaged
    })
}

$sorted = @($results | Sort-Object RowCount -Descending)

if ($OutputPath) {
    try {
        $sorted | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8 -ErrorAction Stop
        Write-Verbose ("Wrote {0} rows to {1}." -f $sorted.Count, $OutputPath)
    }
    catch {
        Write-Error ("Could not write the CSV to '{0}': {1}. Check the folder exists and is writable." -f $OutputPath, $_.Exception.Message)
    }
}

$sorted

# --- Summary ---------------------------------------------------------------
$totalRows = 0
foreach ($row in $sorted) { $totalRows += $row.RowCount }
$totalGb = [math]::Round(($totalRows * [double]$BytesPerRow) / 1GB, 2)

Write-Host ''
Write-Host ("Dataverse table report - {0}" -f $resourceUrl) -ForegroundColor Cyan
Write-Host ('  Tables with rows      : {0} of {1} counted' -f $sorted.Count, $candidates.Count)
Write-Host ('  Total rows            : {0:N0}' -f $totalRows)
Write-Host ('  Estimated size        : {0:N2} GB at {1:N0} bytes per row' -f $totalGb, $BytesPerRow)

if ($sorted.Count -gt 0) {
    $showCount = $sorted.Count
    if ($Top -gt 0 -and $Top -lt $showCount) { $showCount = $Top }
    Write-Host ''
    Write-Host ('  Largest {0} tables:' -f $showCount)
    Write-Host ('    {0,-42} {1,14}  {2}' -f 'Table', 'Rows', 'Share')
    foreach ($row in ($sorted | Select-Object -First $showCount)) {
        $share = 0
        if ($totalRows -gt 0) { $share = 100 * $row.RowCount / $totalRows }
        Write-Host ('    {0,-42} {1,14:N0}  {2,5:N1}%' -f $row.LogicalName, $row.RowCount, $share)
    }
}

if ($uncountable.Count -gt 0) {
    Write-Host ''
    Write-Host ('  {0} tables would not return a count and were skipped. Run with -Verbose to see them.' -f $uncountable.Count) -ForegroundColor Yellow
}

Write-Host ''
Write-Host '  Row counts come from a periodically refreshed snapshot, not a live count.' -ForegroundColor DarkGray
Write-Host '  Size columns are rows x BytesPerRow, not a Microsoft figure. The real per-table' -ForegroundColor DarkGray
Write-Host '  storage is in the admin center under Manage > Environments > Capacity.' -ForegroundColor DarkGray
Write-Host '  Nothing was modified.' -ForegroundColor DarkGray
Write-Host ''

#Requires -Version 5.1
#Requires -Modules Microsoft.PowerApps.Administration.PowerShell

<#
.SYNOPSIS
    Finds cloud flows whose owner has left, whose owner account is disabled, or that have
    been stopped, suspended or untouched for longer than a threshold. Read-only: it never
    turns a flow off, changes an owner or deletes anything.

.DESCRIPTION
    A cloud flow keeps running after the person who built it leaves. It keeps running
    until their connection expires, and then it fails silently in an environment nobody
    is watching. The invoice that stops going out is discovered by the customer, not by
    you.

    This script walks every environment in the tenant (or one, with -EnvironmentName),
    lists every cloud flow, resolves its owners, and flags four things:

      1. Owner missing from Microsoft Entra. The account has been deleted. Nobody can
         fix the connection when it breaks.
      2. Owner account disabled. A leaver whose account was disabled rather than
         deleted. Same outcome, slightly later.
      3. Flow suspended by Power Automate. Power Automate suspends flows that fail
         repeatedly. A suspended flow is not a flow that is off; it is a flow that broke
         and nobody noticed.
      4. Flow stopped, or unchanged, for more than -DaysInactive days.

    Be clear about what point 4 can and cannot tell you. Microsoft.PowerApps.Administration.PowerShell
    does not expose cloud flow run history, so this script cannot count runs. It uses the
    flow state and its last modified date as the staleness signal. That is a candidate
    list to review, not proof a flow is unused: a well-written flow that has run
    perfectly every night for two years has an old last-modified date too. For real run
    counts, use the Power Platform admin center flow analytics or query the run history
    in the environment itself.

    Nothing here writes. Turning off somebody else's flow is a decision that needs a
    conversation, so the output is a list and a CSV.

.PARAMETER EnvironmentName
    One or more environment GUIDs to check. Omit to walk every environment in the tenant.

.PARAMETER DaysInactive
    How many days without a change makes a flow a candidate. Default 90.

.PARAMETER OutputPath
    Optional path to a CSV file. The full result set is written there.

.PARAMETER IncludeHealthy
    Emit every flow, including the ones with no findings, with an empty Findings column.
    Useful when you want a complete flow inventory rather than an exception report.

.PARAMETER SkipOwnerRoles
    Skip the per-flow Get-AdminFlowOwnerRole call and use the flow creator from the flow
    object instead. Much faster on a large tenant, but it misses flows whose ownership
    was transferred or shared after they were created.

.PARAMETER SkipEntraCheck
    Do not look owners up in Microsoft Entra. Owner ids are still reported, but disabled
    and deleted owners cannot be detected.

.PARAMETER TenantId
    Optional tenant id passed to Add-PowerAppsAccount and Connect-MgGraph.

.PARAMETER Endpoint
    Power Platform endpoint for Add-PowerAppsAccount. Default prod. Use usgov, usgovhigh
    or dod for sovereign clouds.

.PARAMETER SkipConnect
    Reuse the Power Platform session that is already open instead of calling
    Add-PowerAppsAccount.

.EXAMPLE
    .\Get-OrphanedFlows.ps1 -Verbose

    Walks every environment and reports flows with orphaned owners or 90 days of silence.

.EXAMPLE
    .\Get-OrphanedFlows.ps1 -EnvironmentName 'a1b2c3d4-0000-0000-0000-000000000000' `
        -DaysInactive 180 -OutputPath .\orphaned-flows.csv

    One environment, a six-month window, exported for review.

.EXAMPLE
    .\Get-OrphanedFlows.ps1 -SkipOwnerRoles |
        Where-Object { $_.Findings -match 'Suspended' } |
        Format-Table EnvironmentDisplayName, DisplayName, OwnerDisplayName, DaysSinceModified

    The fast pass, filtered down to flows Power Automate has suspended because they keep
    failing. This is usually the most urgent list.

.NOTES
    Modules      Microsoft.PowerApps.Administration.PowerShell
                 Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser

                 Optional: Microsoft.Graph.Users and Microsoft.Graph.Authentication for
                 the Entra owner check. Without them the script still runs, and simply
                 reports owner ids without saying whether the account still exists.

    Permissions  Power Platform Administrator, Dynamics 365 Administrator or Global
                 Administrator, so that Get-AdminFlow can see flows in environments you
                 do not personally own. The optional Entra check needs the User.Read.All
                 Microsoft Graph scope.

    Read-only    Get-AdminFlow, Get-AdminFlowOwnerRole, Get-AdminPowerAppEnvironment and
                 Get-MgUser only. No Enable, Disable, Remove or Set cmdlet is called. The
                 only thing it writes anywhere is the CSV you ask for with -OutputPath,
                 on your own disk.

    Run time     A few minutes for a small tenant. Get-AdminFlowOwnerRole is one call per
                 flow, so a tenant with 5,000 flows takes a while. Use -SkipOwnerRoles for
                 a fast first pass, then re-run properly on the environments that matter.

    Author       VerseBlocks - https://www.verseblocks.com
#>

[CmdletBinding()]
param(
    [string[]]$EnvironmentName,

    [ValidateRange(1, 3650)]
    [int]$DaysInactive = 90,

    [string]$OutputPath,

    [switch]$IncludeHealthy,

    [switch]$SkipOwnerRoles,

    [switch]$SkipEntraCheck,

    [string]$TenantId,

    [ValidateSet('prod', 'preview', 'tip1', 'tip2', 'usgov', 'usgovhigh', 'dod')]
    [string]$Endpoint = 'prod',

    [switch]$SkipConnect
)

# --- Helpers ---------------------------------------------------------------

# The admin cmdlets return loosely shaped objects whose Internal payload changes between
# module versions. Walk it defensively rather than assuming a property is there.
function Get-NestedValue {
    [CmdletBinding()]
    param(
        $InputObject,
        [string[]]$Path
    )

    $current = $InputObject
    foreach ($segment in $Path) {
        if ($null -eq $current) { return $null }
        $property = $current.PSObject.Properties[$segment]
        if (-not $property) { return $null }
        $current = $property.Value
    }
    return $current
}

function ConvertTo-NullableDate {
    [CmdletBinding()]
    param($Value)

    if (-not $Value) { return $null }
    if ($Value -is [datetime]) { return $Value }

    $parsed = [datetime]::MinValue
    if ([datetime]::TryParse([string]$Value, [ref]$parsed)) { return $parsed }
    return $null
}

# --- Module check ----------------------------------------------------------
if (-not (Get-Module -ListAvailable -Name Microsoft.PowerApps.Administration.PowerShell)) {
    Write-Error 'Microsoft.PowerApps.Administration.PowerShell is not installed. Install it, then run this script again:  Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser -Repository PSGallery -AllowClobber'
    return
}

try {
    Import-Module Microsoft.PowerApps.Administration.PowerShell -ErrorAction Stop
}
catch {
    Write-Error ("Could not load Microsoft.PowerApps.Administration.PowerShell: {0}. Try Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser -Force -AllowClobber." -f $_.Exception.Message)
    return
}

$graphAvailable = $false
if (-not $SkipEntraCheck) {
    $graphModules = @('Microsoft.Graph.Authentication', 'Microsoft.Graph.Users')
    $graphMissing = @($graphModules | Where-Object { -not (Get-Module -ListAvailable -Name $_) })
    if ($graphMissing.Count -gt 0) {
        Write-Warning ("Microsoft Graph module(s) not found: {0}. Owner ids will be reported, but disabled and deleted owners cannot be detected. Install with:  Install-Module Microsoft.Graph -Scope CurrentUser   Or pass -SkipEntraCheck to silence this." -f ($graphMissing -join ', '))
    }
    else {
        $graphAvailable = $true
    }
}

# --- Connect ---------------------------------------------------------------
if (-not $SkipConnect) {
    Write-Verbose 'Signing in to the Power Platform admin endpoint.'
    $accountArgs = @{ Endpoint = $Endpoint; ErrorAction = 'Stop' }
    if ($TenantId) { $accountArgs['TenantID'] = $TenantId }

    try {
        Add-PowerAppsAccount @accountArgs
    }
    catch {
        Write-Error ("Could not sign in to the Power Platform: {0}. Sign in as a Power Platform, Dynamics 365 or Global Administrator, or run Add-PowerAppsAccount manually and re-run with -SkipConnect." -f $_.Exception.Message)
        return
    }
}

if ($graphAvailable) {
    try {
        Import-Module Microsoft.Graph.Users -ErrorAction Stop
        if (-not (Get-MgContext)) {
            Write-Verbose 'Connecting to Microsoft Graph for the owner check.'
            $connectArgs = @{ Scopes = @('User.Read.All'); ErrorAction = 'Stop' }
            if ($TenantId) { $connectArgs['TenantId'] = $TenantId }

            # -NoWelcome only exists on Microsoft.Graph 2.x and later.
            $connectCommand = Get-Command Connect-MgGraph -ErrorAction SilentlyContinue
            if ($connectCommand -and $connectCommand.Parameters.ContainsKey('NoWelcome')) {
                $connectArgs['NoWelcome'] = $true
            }

            Connect-MgGraph @connectArgs
        }
    }
    catch {
        Write-Warning ("Could not connect to Microsoft Graph ({0}). Continuing without the owner check; owner ids will still be reported." -f $_.Exception.Message)
        $graphAvailable = $false
    }
}

# --- Environments ----------------------------------------------------------
Write-Verbose 'Listing environments.'
try {
    if ($EnvironmentName) {
        $environments = @()
        foreach ($name in $EnvironmentName) {
            $environments += Get-AdminPowerAppEnvironment -EnvironmentName $name -ErrorAction Stop
        }
    }
    else {
        $environments = @(Get-AdminPowerAppEnvironment -ErrorAction Stop)
    }
}
catch {
    Write-Error ("Could not list environments: {0}. Confirm the signed-in account has a Power Platform administrator role." -f $_.Exception.Message)
    return
}

if (-not $environments -or $environments.Count -eq 0) {
    Write-Warning 'No environments were returned. Nothing to check.'
    return
}
Write-Verbose ("Checking {0} environment(s)." -f $environments.Count)

# --- Walk the flows --------------------------------------------------------
$now = Get-Date
$cutoff = $now.AddDays(-$DaysInactive)
$results = New-Object System.Collections.Generic.List[object]
$principalCache = @{}
$totalFlows = 0
$environmentIndex = 0

foreach ($environment in $environments) {
    $environmentIndex++
    $envId = [string]$environment.EnvironmentName
    $envDisplayName = [string]$environment.DisplayName
    if (-not $envDisplayName) { $envDisplayName = $envId }

    Write-Progress -Activity 'Scanning environments' -Status $envDisplayName -PercentComplete ([int](100 * $environmentIndex / $environments.Count))
    Write-Verbose ("Environment {0} of {1}: {2}" -f $environmentIndex, $environments.Count, $envDisplayName)

    $flows = @()
    try {
        $flows = @(Get-AdminFlow -EnvironmentName $envId -ErrorAction Stop)
    }
    catch {
        Write-Warning ("Could not list flows in '{0}': {1}. Skipping this environment." -f $envDisplayName, $_.Exception.Message)
        continue
    }

    if ($flows.Count -eq 0) {
        Write-Verbose ("  No flows in {0}." -f $envDisplayName)
        continue
    }
    Write-Verbose ("  {0} flows." -f $flows.Count)
    $totalFlows += $flows.Count

    foreach ($flow in $flows) {
        $flowId = [string]$flow.FlowName
        $flowDisplayName = [string]$flow.DisplayName
        if (-not $flowDisplayName) { $flowDisplayName = $flowId }

        $state = [string](Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'state'))
        if (-not $state) {
            if ($flow.PSObject.Properties['Enabled'] -and $flow.Enabled) { $state = 'Started' } else { $state = 'Unknown' }
        }

        $suspensionReason = [string](Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'flowSuspensionReason'))

        $lastModified = ConvertTo-NullableDate (Get-NestedValue -InputObject $flow -Path @('LastModifiedTime'))
        if (-not $lastModified) {
            $lastModified = ConvertTo-NullableDate (Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'lastModifiedTime'))
        }
        $createdTime = ConvertTo-NullableDate (Get-NestedValue -InputObject $flow -Path @('CreatedTime'))
        if (-not $createdTime) {
            $createdTime = ConvertTo-NullableDate (Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'createdTime'))
        }

        $daysSinceModified = $null
        if ($lastModified) { $daysSinceModified = [int][math]::Floor(($now - $lastModified).TotalDays) }

        # --- Resolve owners ------------------------------------------------
        $ownerIds = New-Object System.Collections.Generic.List[string]
        $ownerSource = 'Creator'

        if (-not $SkipOwnerRoles) {
            try {
                $ownerRoles = @(Get-AdminFlowOwnerRole -EnvironmentName $envId -FlowName $flowId -ErrorAction Stop)
                foreach ($role in $ownerRoles) {
                    $roleType = [string](Get-NestedValue -InputObject $role -Path @('RoleType'))
                    if (-not $roleType) { $roleType = [string](Get-NestedValue -InputObject $role -Path @('Internal', 'properties', 'roleName')) }
                    if ($roleType -and $roleType -ne 'Owner') { continue }

                    $principalId = [string](Get-NestedValue -InputObject $role -Path @('PrincipalObjectId'))
                    if (-not $principalId) { $principalId = [string](Get-NestedValue -InputObject $role -Path @('Internal', 'properties', 'principal', 'id')) }
                    if ($principalId -and -not $ownerIds.Contains($principalId)) { $ownerIds.Add($principalId) }
                }
                if ($ownerIds.Count -gt 0) { $ownerSource = 'OwnerRole' }
            }
            catch {
                Write-Verbose ("  Could not read owner roles for '{0}': {1}" -f $flowDisplayName, $_.Exception.Message)
            }
        }

        if ($ownerIds.Count -eq 0) {
            $creatorId = [string](Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'creator', 'objectId'))
            if (-not $creatorId) { $creatorId = [string](Get-NestedValue -InputObject $flow -Path @('CreatedBy', 'objectId')) }
            if ($creatorId) { $ownerIds.Add($creatorId) }
        }

        # --- Check owners in Entra -----------------------------------------
        $ownerNames = New-Object System.Collections.Generic.List[string]
        $ownerStatuses = New-Object System.Collections.Generic.List[string]
        $hasMissingOwner = $false
        $hasDisabledOwner = $false

        foreach ($ownerId in $ownerIds) {
            if (-not $graphAvailable) {
                $ownerNames.Add($ownerId)
                $ownerStatuses.Add('Not checked')
                continue
            }

            if (-not $principalCache.ContainsKey($ownerId)) {
                $entry = [pscustomobject]@{ DisplayName = $ownerId; Status = 'Unknown' }
                try {
                    $graphUser = Get-MgUser -UserId $ownerId -Property 'id,displayName,userPrincipalName,accountEnabled' -ErrorAction Stop
                    $name = [string]$graphUser.UserPrincipalName
                    if (-not $name) { $name = [string]$graphUser.DisplayName }
                    $entry.DisplayName = $name
                    if ($graphUser.AccountEnabled) { $entry.Status = 'Enabled' } else { $entry.Status = 'Disabled' }
                }
                catch {
                    # A 404 here means the directory object is gone. Anything else is a
                    # lookup problem, and calling that a deleted owner would be a lie.
                    if ($_.Exception.Message -match '(?i)not\s*found|does not exist|Request_ResourceNotFound|\b404\b') {
                        $entry.Status = 'Missing'
                    }
                    else {
                        $entry.Status = 'Lookup failed'
                        Write-Verbose ("  Owner lookup failed for {0}: {1}" -f $ownerId, $_.Exception.Message)
                    }
                }
                $principalCache[$ownerId] = $entry
            }

            $cached = $principalCache[$ownerId]
            $ownerNames.Add([string]$cached.DisplayName)
            $ownerStatuses.Add([string]$cached.Status)
            if ($cached.Status -eq 'Missing') { $hasMissingOwner = $true }
            if ($cached.Status -eq 'Disabled') { $hasDisabledOwner = $true }
        }

        # --- Findings ------------------------------------------------------
        $findings = New-Object System.Collections.Generic.List[string]
        $severity = 5

        if ($ownerIds.Count -eq 0) {
            $findings.Add('No owner could be resolved')
            $severity = [math]::Min($severity, 2)
        }
        if ($hasMissingOwner) {
            $findings.Add('Owner missing from Entra')
            $severity = [math]::Min($severity, 1)
        }
        if ($hasDisabledOwner) {
            $findings.Add('Owner account disabled')
            $severity = [math]::Min($severity, 1)
        }
        if ($state -eq 'Suspended') {
            if ($suspensionReason) { $findings.Add("Suspended by Power Automate ($suspensionReason)") }
            else { $findings.Add('Suspended by Power Automate') }
            $severity = [math]::Min($severity, 2)
        }
        if ($state -eq 'Stopped' -and $lastModified -and $lastModified -lt $cutoff) {
            $findings.Add("Turned off and untouched for $daysSinceModified days")
            $severity = [math]::Min($severity, 3)
        }
        if ($state -eq 'Started' -and $lastModified -and $lastModified -lt $cutoff) {
            $findings.Add("On, but unchanged for $daysSinceModified days")
            $severity = [math]::Min($severity, 4)
        }

        if ($findings.Count -eq 0 -and -not $IncludeHealthy) { continue }

        $results.Add([pscustomobject]@{
            Severity              = $severity
            Findings              = ($findings -join '; ')
            EnvironmentDisplayName = $envDisplayName
            EnvironmentName       = $envId
            DisplayName           = $flowDisplayName
            FlowName              = $flowId
            State                 = $state
            SuspensionReason      = $suspensionReason
            OwnerDisplayName      = ($ownerNames -join '; ')
            OwnerStatus           = ($ownerStatuses -join '; ')
            OwnerObjectId         = ($ownerIds -join '; ')
            OwnerSource           = $ownerSource
            LastModified          = $lastModified
            DaysSinceModified     = $daysSinceModified
            CreatedTime           = $createdTime
        })
    }
}

Write-Progress -Activity 'Scanning environments' -Completed

$sorted = @($results | Sort-Object Severity, @{ Expression = 'DaysSinceModified'; Descending = $true })

if ($OutputPath) {
    try {
        $sorted | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8 -ErrorAction Stop
        Write-Verbose ("Wrote {0} rows to {1}." -f $sorted.Count, $OutputPath)
    }
    catch {
        Write-Error ("Could not write the CSV to '{0}': {1}. Check the folder exists and is writable." -f $OutputPath, $_.Exception.Message)
    }
}

$sorted

# --- Summary ---------------------------------------------------------------
Write-Host ''
Write-Host 'Cloud flow review' -ForegroundColor Cyan
Write-Host ('  Environments scanned : {0}' -f $environments.Count)
Write-Host ('  Flows examined       : {0}' -f $totalFlows)
Write-Host ('  Flows with findings  : {0}' -f @($sorted | Where-Object { $_.Findings }).Count)
Write-Host ('  Inactivity window    : {0} days (nothing changed since {1:yyyy-MM-dd})' -f $DaysInactive, $cutoff)

if ($sorted.Count -gt 0) {
    $orphaned = @($sorted | Where-Object { $_.Findings -match 'Owner missing|Owner account disabled|No owner' }).Count
    $suspended = @($sorted | Where-Object { $_.Findings -match 'Suspended' }).Count
    $stale = @($sorted | Where-Object { $_.Findings -match 'untouched|unchanged' }).Count
    Write-Host ''
    Write-Host ('    Orphaned owners    : {0}' -f $orphaned)
    Write-Host ('    Suspended flows    : {0}' -f $suspended)
    Write-Host ('    Stale flows        : {0}' -f $stale)
}

if (-not $graphAvailable -and -not $SkipEntraCheck) {
    Write-Host ''
    Write-Host '  Owners were not checked against Entra, so orphaned owners are not in these' -ForegroundColor Yellow
    Write-Host '  numbers. Install the Microsoft Graph module and run again for the full picture.' -ForegroundColor Yellow
}

Write-Host ''
Write-Host '  Run history is not available through the admin module, so "stale" means the flow' -ForegroundColor DarkGray
Write-Host '  has not been edited, not that it has not run. Confirm in flow analytics before you' -ForegroundColor DarkGray
Write-Host '  turn anything off. Nothing was changed by this script.' -ForegroundColor DarkGray
Write-Host ''

#Requires -Version 5.1
#Requires -Modules Microsoft.PowerApps.Administration.PowerShell

<#
.SYNOPSIS
    Exports a one-shot inventory of every Power Platform environment in the tenant, with
    its type, region, Dataverse details, governance settings and component counts.
    Read-only: it never creates, changes or deletes an environment.

.DESCRIPTION
    Most tenants cannot answer the first question an auditor asks: how many environments
    do you have, and who can get into them. This script answers it in one pass and writes
    a CSV you can hand over.

    For every environment it records:

      - Display name, environment id, type, region and creation details.
      - Whether it has a Dataverse database, and if so the instance URL, version and
        state.
      - The security group that restricts access, if there is one. An environment with
        no security group is open to every licensed user in the tenant. On a production
        environment that is usually an accident rather than a decision.
      - Whether the environment is a Managed Environment, from its governance
        protection level.
      - Counts of canvas and model-driven apps, cloud flows (with how many are stopped
        or suspended) and custom connectors.

    Environments with no apps and no flows are worth a second look. They are usually a
    trial somebody started, or a developer environment created by a person who has since
    left, and every one of them still counts toward your tenant capacity.

    Nothing here writes. This is an inventory, not a clean-up.

.PARAMETER OutputPath
    Path to the CSV. Defaults to .\PowerPlatformInventory-yyyyMMdd-HHmm.csv in the
    current directory. Pass an empty string to skip the CSV and only use the objects.

.PARAMETER EnvironmentName
    One or more environment GUIDs. Omit to inventory every environment in the tenant.

.PARAMETER SkipCounts
    Skip the per-environment app, flow and connector counts. Much faster, and enough when
    all you need is the environment list and its governance settings.

.PARAMETER TenantId
    Optional tenant id passed to Add-PowerAppsAccount.

.PARAMETER Endpoint
    Power Platform endpoint for Add-PowerAppsAccount. Default prod. Use usgov, usgovhigh
    or dod for sovereign clouds.

.PARAMETER SkipConnect
    Reuse the Power Platform session that is already open instead of calling
    Add-PowerAppsAccount.

.EXAMPLE
    .\Export-PowerPlatformInventory.ps1 -Verbose

    Full tenant inventory with counts, written to a timestamped CSV in the current
    directory.

.EXAMPLE
    .\Export-PowerPlatformInventory.ps1 -SkipCounts -OutputPath .\environments.csv

    Fast pass. Environment list, Dataverse details and governance settings only.

.EXAMPLE
    .\Export-PowerPlatformInventory.ps1 |
        Where-Object { $_.EnvironmentType -eq 'Production' -and -not $_.SecurityGroupId } |
        Select-Object DisplayName, DataverseUrl

    Production environments with no security group. Start here.

.NOTES
    Modules      Microsoft.PowerApps.Administration.PowerShell
                 Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser

    Permissions  Power Platform Administrator, Dynamics 365 Administrator or Global
                 Administrator. Without one of those the admin cmdlets only return the
                 environments you personally have access to, and the inventory will
                 quietly be incomplete.

    Capacity     Per-environment capacity is only filled in when the installed module
                 version exposes a capacity cmdlet. When it does, the numbers and their
                 unit are reported exactly as the API returns them; this script does not
                 convert them. When it does not, those columns are left empty rather
                 than guessed. The authoritative figures are always in the Power Platform
                 admin center under Manage > Environments > Capacity.

    Read-only    Only Get- cmdlets touch the tenant. Nothing is created, changed, reset
                 or copied. The CSV is the only file it produces, on your own disk.

    Run time     Under a minute with -SkipCounts. With counts, budget a few seconds per
                 environment; a tenant with 300 developer environments takes a while.

    Author       VerseBlocks - https://www.verseblocks.com
#>

[CmdletBinding()]
param(
    [string]$OutputPath = (Join-Path -Path (Get-Location).Path -ChildPath ("PowerPlatformInventory-{0:yyyyMMdd-HHmm}.csv" -f (Get-Date))),

    [string[]]$EnvironmentName,

    [switch]$SkipCounts,

    [string]$TenantId,

    [ValidateSet('prod', 'preview', 'tip1', 'tip2', 'usgov', 'usgovhigh', 'dod')]
    [string]$Endpoint = 'prod',

    [switch]$SkipConnect
)

# --- Helpers ---------------------------------------------------------------

# The admin cmdlets return loosely shaped objects whose Internal payload changes between
# module versions. Walk it defensively rather than assuming a property is there.
function Get-NestedValue {
    [CmdletBinding()]
    param(
        $InputObject,
        [string[]]$Path
    )

    $current = $InputObject
    foreach ($segment in $Path) {
        if ($null -eq $current) { return $null }
        $property = $current.PSObject.Properties[$segment]
        if (-not $property) { return $null }
        $current = $property.Value
    }
    return $current
}

# Pull capacity readings out of whatever shape the capacity cmdlet returns, by looking
# for an object that carries both a capacity type and a consumption figure. If the shape
# is not recognised nothing is reported, because an invented capacity number is worse
# than an empty column.
function Find-CapacityReading {
    [CmdletBinding()]
    param(
        $InputObject,
        [int]$Depth = 0
    )

    $found = @()
    if ($null -eq $InputObject -or $Depth -gt 6) { return $found }
    if ($InputObject -is [string] -or $InputObject -is [ValueType]) { return $found }

    if ($InputObject -is [System.Collections.IEnumerable]) {
        foreach ($item in $InputObject) {
            $found += Find-CapacityReading -InputObject $item -Depth ($Depth + 1)
        }
        return $found
    }

    if (-not $InputObject.PSObject -or -not $InputObject.PSObject.Properties) { return $found }

    $typeProperty = $InputObject.PSObject.Properties | Where-Object { $_.Name -eq 'capacityType' -or $_.Name -eq 'CapacityType' } | Select-Object -First 1
    $valueProperty = $InputObject.PSObject.Properties | Where-Object { $_.Name -eq 'actualConsumption' -or $_.Name -eq 'ActualConsumption' } | Select-Object -First 1

    if ($typeProperty -and $valueProperty) {
        $unitProperty = $InputObject.PSObject.Properties | Where-Object { $_.Name -eq 'units' -or $_.Name -eq 'Units' } | Select-Object -First 1
        $unit = ''
        if ($unitProperty) { $unit = [string]$unitProperty.Value }
        $found += [pscustomobject]@{
            Type  = [string]$typeProperty.Value
            Value = $valueProperty.Value
            Unit  = $unit
        }
    }

    foreach ($property in $InputObject.PSObject.Properties) {
        if ($property.Value -is [string] -or $property.Value -is [ValueType]) { continue }
        $found += Find-CapacityReading -InputObject $property.Value -Depth ($Depth + 1)
    }

    return $found
}

# --- Module check ----------------------------------------------------------
if (-not (Get-Module -ListAvailable -Name Microsoft.PowerApps.Administration.PowerShell)) {
    Write-Error 'Microsoft.PowerApps.Administration.PowerShell is not installed. Install it, then run this script again:  Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser -Repository PSGallery -AllowClobber'
    return
}

try {
    Import-Module Microsoft.PowerApps.Administration.PowerShell -ErrorAction Stop
}
catch {
    Write-Error ("Could not load Microsoft.PowerApps.Administration.PowerShell: {0}. Try Install-Module Microsoft.PowerApps.Administration.PowerShell -Scope CurrentUser -Force -AllowClobber." -f $_.Exception.Message)
    return
}

# --- Connect ---------------------------------------------------------------
if (-not $SkipConnect) {
    Write-Verbose 'Signing in to the Power Platform admin endpoint.'
    $accountArgs = @{ Endpoint = $Endpoint; ErrorAction = 'Stop' }
    if ($TenantId) { $accountArgs['TenantID'] = $TenantId }

    try {
        Add-PowerAppsAccount @accountArgs
    }
    catch {
        Write-Error ("Could not sign in to the Power Platform: {0}. Sign in as a Power Platform, Dynamics 365 or Global Administrator, or run Add-PowerAppsAccount manually and re-run with -SkipConnect." -f $_.Exception.Message)
        return
    }
}

# --- Capacity cmdlet probe -------------------------------------------------
$capacityCommand = $null
foreach ($candidate in @('Get-AdminPowerAppEnvironmentCapacity', 'Get-PowerAppEnvironmentCapacity')) {
    $resolved = Get-Command -Name $candidate -ErrorAction SilentlyContinue
    if ($resolved) {
        $capacityCommand = $resolved.Name
        break
    }
}
if ($capacityCommand) {
    Write-Verbose ("Capacity will be read with {0}." -f $capacityCommand)
}
else {
    Write-Verbose 'No capacity cmdlet in this module version. Capacity columns will be left empty.'
}

# --- Environments ----------------------------------------------------------
Write-Verbose 'Listing environments.'
try {
    if ($EnvironmentName) {
        $environments = @()
        foreach ($name in $EnvironmentName) {
            $environments += Get-AdminPowerAppEnvironment -EnvironmentName $name -ErrorAction Stop
        }
    }
    else {
        $environments = @(Get-AdminPowerAppEnvironment -ErrorAction Stop)
    }
}
catch {
    Write-Error ("Could not list environments: {0}. Confirm the signed-in account has a Power Platform administrator role." -f $_.Exception.Message)
    return
}

if (-not $environments -or $environments.Count -eq 0) {
    Write-Warning 'No environments were returned. If you expected some, the signed-in account probably is not a Power Platform administrator.'
    return
}
Write-Verbose ("Found {0} environment(s)." -f $environments.Count)

# --- Walk ------------------------------------------------------------------
$inventory = New-Object System.Collections.Generic.List[object]
$index = 0

foreach ($environment in $environments) {
    $index++
    $envId = [string]$environment.EnvironmentName
    $envDisplayName = [string]$environment.DisplayName
    if (-not $envDisplayName) { $envDisplayName = $envId }

    Write-Progress -Activity 'Building inventory' -Status $envDisplayName -PercentComplete ([int](100 * $index / $environments.Count))
    Write-Verbose ("Environment {0} of {1}: {2}" -f $index, $environments.Count, $envDisplayName)

    $properties = Get-NestedValue -InputObject $environment -Path @('Internal', 'properties')
    $dataverse = Get-NestedValue -InputObject $properties -Path @('linkedEnvironmentMetadata')

    $securityGroupId = [string](Get-NestedValue -InputObject $dataverse -Path @('securityGroupId'))
    if ($securityGroupId -eq '00000000-0000-0000-0000-000000000000') { $securityGroupId = '' }

    $protectionLevel = [string](Get-NestedValue -InputObject $properties -Path @('governanceConfiguration', 'protectionLevel'))

    $createdBy = [string](Get-NestedValue -InputObject $environment -Path @('CreatedBy', 'displayName'))
    if (-not $createdBy) { $createdBy = [string](Get-NestedValue -InputObject $properties -Path @('createdBy', 'displayName')) }

    $appCount = $null
    $flowCount = $null
    $flowsStopped = $null
    $flowsSuspended = $null
    $connectorCount = $null

    if (-not $SkipCounts) {
        try {
            $appCount = @(Get-AdminPowerApp -EnvironmentName $envId -ErrorAction Stop).Count
        }
        catch {
            Write-Verbose ("  Could not count apps in {0}: {1}" -f $envDisplayName, $_.Exception.Message)
        }

        try {
            $flows = @(Get-AdminFlow -EnvironmentName $envId -ErrorAction Stop)
            $flowCount = $flows.Count
            $flowsStopped = 0
            $flowsSuspended = 0
            foreach ($flow in $flows) {
                $state = [string](Get-NestedValue -InputObject $flow -Path @('Internal', 'properties', 'state'))
                if ($state -eq 'Stopped') { $flowsStopped++ }
                elseif ($state -eq 'Suspended') { $flowsSuspended++ }
            }
        }
        catch {
            Write-Verbose ("  Could not count flows in {0}: {1}" -f $envDisplayName, $_.Exception.Message)
        }

        try {
            $connectorCount = @(Get-AdminPowerAppConnector -EnvironmentName $envId -ErrorAction Stop).Count
        }
        catch {
            Write-Verbose ("  Could not count custom connectors in {0}: {1}" -f $envDisplayName, $_.Exception.Message)
        }
    }

    $databaseConsumption = $null
    $fileConsumption = $null
    $logConsumption = $null
    $capacityUnits = ''

    if ($capacityCommand) {
        try {
            $capacityRaw = & $capacityCommand -EnvironmentName $envId -ErrorAction Stop
            foreach ($reading in (Find-CapacityReading -InputObject $capacityRaw)) {
                if ($reading.Unit -and -not $capacityUnits) { $capacityUnits = $reading.Unit }
                switch -Regex ($reading.Type) {
                    '(?i)^database' { $databaseConsumption = $reading.Value }
                    '(?i)^file'     { $fileConsumption = $reading.Value }
                    '(?i)^log'      { $logConsumption = $reading.Value }
                }
            }
        }
        catch {
            Write-Verbose ("  Could not read capacity for {0}: {1}" -f $envDisplayName, $_.Exception.Message)
        }
    }

    $inventory.Add([pscustomobject]@{
        DisplayName                 = $envDisplayName
        EnvironmentName             = $envId
        EnvironmentType             = [string]$environment.EnvironmentType
        EnvironmentSku              = [string](Get-NestedValue -InputObject $properties -Path @('environmentSku'))
        IsDefault                   = [bool](Get-NestedValue -InputObject $properties -Path @('isDefault'))
        Region                      = [string]$environment.Location
        State                       = [string](Get-NestedValue -InputObject $properties -Path @('states', 'management', 'id'))
        CreatedTime                 = $environment.CreatedTime
        CreatedBy                   = $createdBy
        ExpirationTime              = Get-NestedValue -InputObject $properties -Path @('expirationTime')
        HasDataverse                = [bool]$dataverse
        DataverseUrl                = [string](Get-NestedValue -InputObject $dataverse -Path @('instanceUrl'))
        DataverseUniqueName         = [string](Get-NestedValue -InputObject $dataverse -Path @('uniqueName'))
        DataverseVersion            = [string](Get-NestedValue -InputObject $dataverse -Path @('version'))
        DataverseState              = [string](Get-NestedValue -InputObject $dataverse -Path @('instanceState'))
        SecurityGroupId             = $securityGroupId
        RestrictedBySecurityGroup   = [bool]$securityGroupId
        ManagedEnvironmentProtection = $protectionLevel
        AppCount                    = $appCount
        FlowCount                   = $flowCount
        FlowsStopped                = $flowsStopped
        FlowsSuspended              = $flowsSuspended
        CustomConnectorCount        = $connectorCount
        DatabaseConsumption         = $databaseConsumption
        FileConsumption             = $fileConsumption
        LogConsumption              = $logConsumption
        CapacityUnits               = $capacityUnits
    })
}

Write-Progress -Activity 'Building inventory' -Completed

$sorted = @($inventory | Sort-Object EnvironmentType, DisplayName)

if ($OutputPath) {
    try {
        $sorted | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8 -ErrorAction Stop
        Write-Verbose ("Wrote {0} rows to {1}." -f $sorted.Count, $OutputPath)
    }
    catch {
        Write-Error ("Could not write the CSV to '{0}': {1}. Check the folder exists and is writable." -f $OutputPath, $_.Exception.Message)
    }
}

$sorted

# --- Summary ---------------------------------------------------------------
$withDataverse = @($sorted | Where-Object { $_.HasDataverse }).Count
$unrestricted = @($sorted | Where-Object { -not $_.RestrictedBySecurityGroup -and $_.HasDataverse }).Count

Write-Host ''
Write-Host 'Power Platform tenant inventory' -ForegroundColor Cyan
Write-Host ('  Environments            : {0}' -f $sorted.Count)
Write-Host ('  With Dataverse          : {0}' -f $withDataverse)

Write-Host ''
Write-Host '  By type:'
foreach ($group in ($sorted | Group-Object EnvironmentType | Sort-Object Count -Descending)) {
    $typeName = $group.Name
    if (-not $typeName) { $typeName = '(unspecified)' }
    Write-Host ('    {0,-24} {1}' -f $typeName, $group.Count)
}

if (-not $SkipCounts) {
    $totalApps = 0
    $totalFlows = 0
    $totalConnectors = 0
    foreach ($row in $sorted) {
        if ($null -ne $row.AppCount) { $totalApps += $row.AppCount }
        if ($null -ne $row.FlowCount) { $totalFlows += $row.FlowCount }
        if ($null -ne $row.CustomConnectorCount) { $totalConnectors += $row.CustomConnectorCount }
    }
    $empty = @($sorted | Where-Object { $_.AppCount -eq 0 -and $_.FlowCount -eq 0 }).Count

    Write-Host ''
    Write-Host ('  Apps                    : {0:N0}' -f $totalApps)
    Write-Host ('  Cloud flows             : {0:N0}' -f $totalFlows)
    Write-Host ('  Custom connectors       : {0:N0}' -f $totalConnectors)
    Write-Host ('  Empty environments      : {0} (no apps and no flows)' -f $empty)
}

if ($unrestricted -gt 0) {
    Write-Host ''
    Write-Host ('  {0} Dataverse environments have no security group. Every licensed user in the' -f $unrestricted) -ForegroundColor Yellow
    Write-Host '  tenant can open them. Check that is deliberate on each one.' -ForegroundColor Yellow
}

if (-not $capacityCommand) {
    Write-Host ''
    Write-Host '  Capacity columns are empty: this module version exposes no capacity cmdlet.' -ForegroundColor DarkGray
    Write-Host '  Read capacity in the admin center under Manage > Environments > Capacity.' -ForegroundColor DarkGray
}

if ($OutputPath) {
    Write-Host ''
    Write-Host ('  CSV written to {0}' -f $OutputPath)
}

Write-Host ''
Write-Host '  Nothing was changed. This script only reads.' -ForegroundColor DarkGray
Write-Host ''