Weekly License Cost Optimization Report
Aggregates premium connector usage per user from Dataverse inventory, cross-references with Office 365 Users license assignments, identifies users with premium licenses but zero premium connector usage in 60+ days, and exports a cost-savings opportunity report to Excel with an executive summary email.
Provided as-is, without warranty of any kind. Review and test each pattern in a non-production environment before deploying it to live automations. See our Terms.
Overview
FlowLibs - Weekly License Cost Optimization Report is a scheduled Power Automate Cloud Flow that identifies users with premium Power Platform licenses who have not used any premium connectors within a configurable inactivity threshold (default: 60 days). It logs optimization candidates to an Excel workbook on SharePoint and sends a formatted HTML summary email to an admin with estimated monthly savings.
Use Case
Organizations paying for per-user Power Platform premium licenses often have users who were provisioned a license but never actively use premium connectors. This flow automates the discovery of those idle licenses by querying Dataverse for active system users, checking each user's last activity date against a configurable cutoff, and compiling a cost-savings report. The report is written to a shared Excel workbook for historical tracking and emailed to the designated admin for action.
The flow is ideal for teams that:
- IT admins managing Power Platform premium license inventory
- Finance/operations teams tracking SaaS cost optimization
- Center of Excellence (CoE) teams enforcing license governance
- Executives needing periodic visibility into idle premium licenses
Flow Architecture
Recurrence Weekly Monday 8AM
RecurrenceTrigger fires every Monday at 08:00 UTC.
Initialize Variables (8 parallel)
InitializeVariableBinds 6 environment variable parameters (SharePoint site URL, Excel drive, Excel file path, Excel table name, admin notification email, inactive-days threshold) plus 2 working variables (varReportHtml, varOptimizationCount).
Compute Cutoff Date
ComposeComputes the inactivity boundary: addDays(utcNow(), mul(varInactiveDaysThreshold, -1)).
List Active Licensed Users
Dataverse ListRecordsQueries the systemusers table with $filter `isdisabled eq false and accessmode ne 4` and $top 500 to retrieve active, interactive users.
For Each Licensed User
Apply to eachIterates over Dataverse results. For each user: looks up displayName and department via Office 365 Users (UserProfile_V2), then evaluates the premium-inactive condition below.
If Premium License Inactive
If conditionCondition: email is not empty AND modifiedon < cutoff date.
- Add Optimization Row To Excel — Excel Online Business AddRowV2 appends a row with 8 columns (UserEmail, DisplayName, LicenseType, PremiumConnectorCount, LastPremiumUsageDate, DaysSinceLastUse, EstimatedMonthlyCost, ReportDate).
- Increment Optimization Count — IncrementVariable +1 on varOptimizationCount.
- Append Report Row — AppendToStringVariable adds an HTML <tr> row to varReportHtml for the email body.
Environment Variables
| Schema name | Type | Default | Description |
|---|---|---|---|
| flowlibs_SharePointSiteURL | String | https://your-tenant.sharepoint.com | SharePoint root site URL where the license optimization Excel workbook lives. |
| flowlibs_LicenseReportExcelDrive | String | <configure> | Microsoft Graph drive ID for the SharePoint document library that holds the Excel workbook. Discoverable via the SharePoint REST API _api/v2.0/drives endpoint. |
| flowlibs_LicenseReportExcelFile | String | /Shared Documents/FlowLibs - License Cost Optimization.xlsx | Server-relative path to the Excel workbook. |
| flowlibs_LicenseReportExcelTable | String | LicenseCostOptimization | Name of the Excel table inside the workbook where rows are appended. |
| flowlibs_AdminNotificationEmail | String | alerts@yourcompany.com | Recipient email for the weekly optimization report. |
| flowlibs_PremiumInactiveDaysThreshold | String | 60 | Days of inactivity before a licensed user is flagged as an optimization candidate (parsed to integer at runtime). |
Connectors & Connections
| Connector | API name | Actions used |
|---|---|---|
| Microsoft Dataverse | shared_commondataserviceforapps | ListRecords (systemusers table) |
| Office 365 Users | shared_office365users | UserProfile_V2 (lookup by email) |
| Excel Online (Business) | shared_excelonlinebusiness | AddRowV2 (appends row to License Cost Optimization workbook) |
| Office 365 Outlook | shared_office365 | SendEmailV2 (admin notification) |
Note — All connections are referenced as solution connection references; the flow is portable between environments as long as a connection is mapped at import time.
Customization Guide
Almost every realistic variant of this flow can be implemented by changing environment variable values. A few cases require small edits inside the flow definition — those are called out explicitly below.
- Change the inactivity threshold
- Update the flowlibs_PremiumInactiveDaysThreshold environment variable (default: 60 days). The flow parses this to an integer and computes the cutoff date dynamically.
- Change the notification recipient
- Update the flowlibs_AdminNotificationEmail environment variable to route the weekly report to a different admin or distribution list.
- Target a different SharePoint site
- Update flowlibs_SharePointSiteURL. You will also need to update flowlibs_LicenseReportExcelDrive with the Graph drive ID for the new site's document library (discoverable via the SharePoint REST API _api/v2.0/drives endpoint).
- Adjust the user filter
- Modify the $filter OData expression in the List_Active_Licensed_Users action. The current filter isdisabled eq false and accessmode ne 4 excludes disabled users and non-interactive/application users.
- Change estimated cost per license
- The flow hardcodes $40/month per premium license. To change this, update the EstimatedMonthlyCost value in the Add_Optimization_Row_To_Excel action and the mul(varOptimizationCount, 40) expressions in both the email subject and the Compose action.
Key Expressions
The flow is intentionally light on Power Fx / WDL gymnastics — the heaviest expressions are the branch-name concatenation and the approval outcome check. They are listed below in the order they appear in the flow.
EXPR.01Cutoff date
Computes the inactivity boundary used to compare modifiedon against.
EXPR.02Days since last use
Calculates the integer number of days between now and the user's last modified date.
EXPR.03Estimated savings
Multiplies the count of optimization candidates by the assumed $40/month per-license cost.
EXPR.04Null-safe last activity
Falls back to a fixed historical date if modifiedon is missing so the date math never fails.
Comments
Sign in to join the conversation.
Sign inNo comments yet. Be the first to share your experience with this flow.