Generating a Power Automate Usage Report using PowerShell

Monitoring Power Automate usage is crucial for organizations to track automation efficiency, identify frequently used flows, and optimize resources. Microsoft provides PowerShell modules to retrieve Power Automate usage data through Microsoft Graph API and Power Platform Admin Center APIs.

In this guide, we will walk through a step-by-step process to generate a Power Automate usage report using PowerShell. This includes prerequisites, connecting to Microsoft 365, retrieving Power Automate flow details, filtering active flows, exporting the report to CSV, and visualizing the data.

---

Step 1: Prerequisites

Before running PowerShell scripts to generate the Power Automate usage report, ensure you have the following:

1. Administrator Access – You must have Power Platform admin or Microsoft 365 global admin permissions.
2. PowerShell Installed – Ensure the latest PowerShell version is installed.
3. Microsoft Graph PowerShell Module – Required to interact with Power Automate.
4. Power Platform PowerShell Module – Required for retrieving flow usage details.

---

Step 2: Install and Import Required Modules

Install the necessary PowerShell modules if they are not already installed.

# Install Microsoft Graph PowerShell module
Install-Module Microsoft.Graph -Scope CurrentUser

# Install Power Platform PowerShell module
Install-Module Microsoft.PowerPlatform.Administration -Scope CurrentUser
Install-Module Microsoft.PowerPlatform.Cds.Client -Scope CurrentUser

# Import the modules
Import-Module Microsoft.Graph
Import-Module Microsoft.PowerPlatform.Administration
Import-Module Microsoft.PowerPlatform.Cds.Client

---

Step 3: Connect to Microsoft Power Platform

To fetch Power Automate flow usage details, connect to the Microsoft 365 environment.

# Connect to Microsoft Graph
Connect-MgGraph -Scopes "User.Read.All", "Flow.Manage.All", "Flow.Read.All"

# Connect to Power Platform Admin
Add-PowerAppsAccount

You will be prompted to log in using your Microsoft 365 administrator credentials.

---

Step 4: Retrieve Power Automate Flow Details

List all flows in your environment along with their properties.

# Get all Power Automate flows in the tenant
$flows = Get-AdminFlow

# Display key details
$flows | Select-Object DisplayName, EnvironmentName, FlowEnabled, CreatedTime, LastModifiedTime

This will return:

• DisplayName – Name of the flow
• EnvironmentName – The environment where the flow is hosted
• FlowEnabled – Whether the flow is enabled or disabled
• CreatedTime – Flow creation date
• LastModifiedTime – Last updated date

---

Step 5: Retrieve Flow Run History

To track how often flows are being used, retrieve their execution history.

# Get flow run history for a specific environment
$environmentName = "Default-Environment-ID"
$flows = Get-AdminFlow -EnvironmentName $environmentName

foreach ($flow in $flows) {
    $flowId = $flow.FlowId
    $runHistory = Get-AdminFlowRun -EnvironmentName $environmentName -FlowName $flowId

    # Display relevant usage details
    $runHistory | Select-Object StartTime, EndTime, Status, FlowName
}

This provides details such as:

• StartTime – When the flow started
• EndTime – When the flow finished
• Status – Flow status (Succeeded, Failed, or Running)
• FlowName – The name of the flow

---

Step 6: Generate Power Automate Usage Report

Now, create a structured report and export it to a CSV file for further analysis.

# Define output file path
$reportPath = "C:\PowerAutomateUsageReport.csv"

# Create an array to store the data
$reportData = @()

foreach ($flow in $flows) {
    $flowId = $flow.FlowId
    $runHistory = Get-AdminFlowRun -EnvironmentName $environmentName -FlowName $flowId

    foreach ($run in $runHistory) {
        $reportData += [PSCustomObject]@{
            FlowName       = $flow.DisplayName
            Environment    = $flow.EnvironmentName
            StartTime      = $run.StartTime
            EndTime        = $run.EndTime
            Status         = $run.Status
        }
    }
}

# Export data to CSV
$reportData | Export-Csv -Path $reportPath -NoTypeInformation

Write-Host "Power Automate usage report has been generated: $reportPath"

---

Step 7: Filter and Analyze Flow Usage Data

To focus on specific flow statuses, apply filtering.

Filter by Active Flows

$activeFlows = $flows | Where-Object { $_.FlowEnabled -eq $true }
$activeFlows | Select-Object DisplayName, EnvironmentName, CreatedTime

Filter by Failed Runs

$failedRuns = $reportData | Where-Object { $_.Status -eq "Failed" }
$failedRuns | Export-Csv -Path "C:\FailedFlows.csv" -NoTypeInformation
Write-Host "Failed flow report generated: C:\FailedFlows.csv"

Filter by Flow Run Frequency

Find flows that ran more than 10 times in a day.

$highUsageFlows = $reportData | Group-Object -Property FlowName | Where-Object { $_.Count -gt 10 }
$highUsageFlows | Format-Table Name, Count

---

Step 8: Visualizing Flow Usage Data in Power BI

To analyze the data graphically, use Power BI:

1. Open Power BI Desktop.
2. Click Get Data > CSV.
3. Select C:\PowerAutomateUsageReport.csv.
4. Use Bar Charts and Pie Charts to visualize flow execution trends.

---

Step 9: Automate the Report Generation Using Task Scheduler

To run the script automatically at a scheduled time, use Task Scheduler:

1. Open Task Scheduler.
2. Click Create Basic Task.
3. Set the trigger to Daily or Weekly.
4. Set the action to Start a Program and enter: powershell.exe -File "C:\Scripts\PowerAutomateUsageReport.ps1"
5. Click Finish.

Now, the script will run at the scheduled time and generate updated reports.

---

Step 10: Disconnect from Microsoft 365

After retrieving the report, disconnect from Microsoft services.

Disconnect-MgGraph