Copilot Cowork Power Platform Analytics Dashboard Generation Skill

readme.md

Links to PowerShell scripts to create the two input csv files.

Uses a resource query to pull back all Copilot Studio and Agent Builder agents in the tenant. https://gist.github.com/joerodgers/1552c1cc130c4648fce8f57b1b13efaa

Creates a report mapping Power Platform environments to billing policies. Includes PAYGO details of each billing policy. https://gist.github.com/joerodgers/c2a56a5328ba2c2bd7d586eaf03a589d

Example screenshots from geneated dashboard

powerplatform-dashboard-SKILL.md

name	powerplatform-dashboard
description	Builds a single self-contained interactive HTML dashboard analyzing a Power Platform tenant from two exports — an environment report and an agent inventory. Use when the user says "build a Power Platform dashboard", "analyze my Power Platform environments", "Power Platform environment + agent report", "Copilot Studio / Agent Builder agent analytics", "dashboard from my environment and agent exports", or attaches a PowerPlatformEnvironmentReport plus an agent-inventory file and asks for analytics. Do NOT use for generic spreadsheet charts, non-Power-Platform data, building Power BI reports, or live tenant queries — this works only from the two attached export files.
cowork	category icon analysis DataBarVertical

Power Platform Environment + Agent Analytics Dashboard

Generate one downloadable, fully self-contained HTML dashboard from two attached exports: an environment report (one row per environment) and an agent inventory (one row per agent). The file embeds its data, loads Chart.js from a CDN, and opens in any browser with no server.

When NOT to Use

• The user wants a Power BI report, a live tenant query, or anything requiring a Graph/admin API — this skill only reads the two attached files.
• The data isn't Power Platform environments/agents (generic CSV charting → use the xlsx skill).
• Only one of the two files is available — ask for the missing export, or proceed with a clearly-labeled partial dashboard (agent charts need only the agent file; the environment join needs both).

Inputs

1. Environment report — CSV or Excel, one row per environment. Expected columns: EnvironmentId, EnvironmentName, EnvironmentLocation, EnvironmentCreatedDate, EnvironmentLastModifiedDate, EnvironmentSku, EnvironmentReleaseCycle, EnvironmentIsManaged, EnvironmentDataverseStorageMB (DB), EnvironmentDataverseFileMB, EnvironmentDataverseLogMB, EnvironmentVNetId, EnvironmentVNetPolicyName, EnvironmentVNetLinkStatus, EnvironmentVNetLocation, BillingPolicyId, BillingPolicyName, BillingPolicyType, BillingPolicyStatus, BillingResourceGroup, BillingPolicyLastModifiedOn, and seven pay-go flags (BillingDataversePayGoEnabled, BillingPowerAutomatePayGoEnabled, BillingPowerPagesPayGoEnabled, BillingPowerAppsPayGoEnabled, BillingPowerPlatformPayGoEnabled, BillingWindows365ForAgentsPayGoEnabled, BillingCopilotStudioPayGoEnabled).
2. Agent inventory — CSV, one row per agent. Expected columns: displayName, environmentId, environmentName, environmentType, environmentIsManaged, createdAt, createdBy, ownerId, lastPublishedAt, channels, model, createdIn, orchestration.

Find both files in the user's uploaded materials first (search input/). If a referenced file is an Excel that won't open (re-synced files can arrive as encrypted OLE2), try openpyxl, then soffice --headless --convert-to csv; if every attempt fails, say so plainly and ask for a plain .csv — never substitute sample data.

Critical Data Rules (follow exactly — these prevent silent wrong numbers)

1. Case-insensitive join. Join the two datasets on environmentId but lowercase both sides first. The same environment can appear as Default-<guid> in one file and default-<guid> in the other; an exact match silently drops large environments (e.g. a default environment holding most agents shows 0). After joining, assert that total matched agents ≈ agent row count and report any unmatched agents.
2. Agent category from createdIn. createdIn == "Copilot Studio Lite" → category "Agent Builder"; createdIn == "Copilot Studio" → category "Copilot Studio". Differentiate these two categories in EVERY agent chart and table. Use teal for Agent Builder and purple for Copilot Studio consistently.
3. channels is multi-valued (comma-separated) — split and count each channel separately; treat blank as "Unpublished".
4. VNet "linked" means EnvironmentVNetLinkStatus == "Linked".
5. Environment names are NOT unique — distinct environments can share a display name. Always key counts and joins on environmentId, never on name.
6. Scale + skew. Data is large (thousands of environments, tens of thousands of agents) and skewed (one environment may hold most agents). Pre-aggregate the agent data in code — do not embed raw agent rows; embed environment rows + compact aggregated agent series. Use logarithmic scales on per-environment / per-owner / per-model bar charts. Stacked bars do NOT render correctly on a log axis — use grouped bars when splitting those by category.
7. Never fabricate. Compute every number from the data with code (not by eye). Mark any gap clearly; do not invent names, counts, or dates.

Example

User: "Here are this quarter's exports — build me the Power Platform dashboard." (attaches PowerPlatformEnvironmentReport.csv and agent-inventory.csv)

Skill: locates both files in input/; loads them with Python; computes the case-insensitive environmentId join (recovering environments whose ID case differs between files); pre-aggregates the agent inventory by category, month, environment, owner, channel, and model; generates output/PowerPlatform-Dashboard.html with 8 tabs; confirms the file exists; and replies with the tab list plus any caveats (e.g. "412 agents unmatched by ID; 65 environment names are shared by multiple environments").

Tools

Each tool has one job: the Glob tool finds the two exports under input/; the Read tool inspects their schemas; the Bash tool runs Python to parse, join, aggregate, and compute every KPI (never calculate by hand); and the Write tool emits the final HTML. Load Chart.js from a CDN inside the generated file; no other runtime dependency.

The single most error-prone step is the join — always lowercase both sides of environmentId:

import collections
agent_count = collections.Counter(a["environmentId"].lower() for a in agents)
for env in environments:
    env["agents"] = agent_count.get(env["EnvironmentId"].lower(), 0)
# sanity check: sum(env["agents"]) should ≈ len(agents); report the shortfall

Workflow

1. Locate + load both files (Glob input/**/*, then Read/Python). Confirm schemas.
2. Prepare data in Python: normalize environment rows to a compact JSON record set; compute the case-insensitive agent-count join and attach an agents field per environment; pre-aggregate the agent inventory (by category, by month for creation/publishing, per environment, per owner top-N, per channel, per model, newest-N, last-7-days-by-category). Save intermediates to working/.
3. Compute KPIs/insights with code (Bash running Python): totals, %managed, %billed, VNet-linked count, storage composition, File:DB ratio, empty/unmanaged-production/FirstRelease counts, top consumers, category splits.
4. Generate the HTML with the data embedded (replace a __DATA__/__AGENTS__ placeholder, or write the JSON inline). Chart.js 4.x via CDN. Save to output/.
5. Delivery gate: Glob output/**/* to confirm the file exists before telling the user it's ready.

Dashboard Layout (8 tabs)

• Overview — exec "AI Agent Footprint" banner (total agents, Agent Builder, Copilot Studio with %); "Last 7 Days Activity" (KPIs: new environments, storage added, new agents per category; daily charts for new environments, storage added, new agents by category); SKU donut, Managed donut, Release pie, Billing Coverage pie; Agent Creation Trend (monthly, two lines by category).
• Storage — Top 15 storage consumers by DB/File/Log on a log scale; total storage by type over time as three separate cumulative charts (File, Database, Log); storage composition donut; DB-vs-File scatter.
• Creation Trend — cumulative environment creation by SKU; new environments per quarter by SKU.
• Agents — KPIs (Total, Agent Builder, Copilot Studio, Published %, Environments with agents); "Agents by Category" donut + a card defining the two categories; Top 15 environments by agent count (log, grouped by category); Top 15 builders/owners (log, grouped by category; label the all-zero owner GUID "System / unassigned"); publishing trend (monthly, by category); popular channels (stacked by category, incl. "Unpublished"); model usage (log, by category); 15 most-recently-created agents table (with Category column); filterable Agents-per-Environment table (Agent Builder / Copilot Studio / Total columns; search + type + category filters; CSV export).
• VNet Report — filterable table of VNet-linked environments (search, SKU, link status, location); CSV export.
• Billing Report — filterable table of environments with billing policies. Columns: Environment, Policy, Status, Resource Group, Pay-Go Services, Last Modified (no policy "Type" column). Filters: search, policy, resource group, status, pay-go service; CSV export.
• All Environments — full sortable, searchable table including an Agents column from the join; SKU + Managed filters; CSV export.
• Insights — auto-generated narrative grounded in the actual numbers: governance gaps (unmanaged production, FirstRelease ring, no-billing-policy, empty environments), storage insights (File:DB ratio, top consumers, total capacity), network/security (VNet-linked, geographic spread, % managed), agent footprint (category split, environments matched by the join), and recommended actions.

Every table supports text search, dropdown filters, column sorting, and CSV export of the filtered rows. Include a "Print / Save as PDF" button (window.print()).

Styling

Microsoft Fluent look: accent #0078d4; good #107c10, warn #c19c00, bad #a4262c. Category colors: Agent Builder teal #038387, Copilot Studio purple #5c2d91. Card-based layout, KPI strip, tabbed nav, responsive grid. Self-contained single file (embedded data + CDN Chart.js only).

Output

A single .html file in output/ (e.g. PowerPlatform-Dashboard.html). After the delivery-gate check, tell the user it's ready and briefly list the tabs and any data caveats (unmatched agents, duplicate environment names, files that couldn't be read).

Guardrails

• Do the environmentId join case-insensitively — this is the single most common source of wrong agent counts. Verify the matched-agent total and disclose unmatched rows.
• Compute all numbers in code; never hand-calculate or fabricate. Surface gaps explicitly.
• Pre-aggregate agents (never embed ~tens of thousands of raw rows) and use log scales on skewed charts; use grouped (not stacked) bars on any log axis.
• Confirm the output file exists with Glob output/**/* before reporting success.
• If a source file can't be parsed, say so and ask for a clean export — never fill with placeholder data.