# MDE Toolkit — Power BI Report Setup

## Fastest route: the template

Download **[MDE-Toolkit-Fleet-Health.pbit](../downloads/MDE-Toolkit-Fleet-Health.pbit)** and
open it in Power BI Desktop. It carries the query, all 110 columns, all 114 measures and six
report pages. You will be prompted for:

| Parameter | Example |
|---|---|
| `StorageAccountName` | `stmdetoolkit` |
| `TableName` | `HealthReports` |
| `EndpointSuffix` | `table.core.windows.net`, or `table.core.usgovcloudapi.net` for Government |
| `StaleAfterHours` | `24` |

Then supply the storage **account key** when asked to authenticate (see
[Step 3](#step-3-authenticate--account-key) — this connector does not accept Entra sign-in).
Finally **File → Save As** to turn the template into your own `.pbix`.

> **Why a `.pbit` and not a finished `.pbix`?** A `.pbix` stores its model as a compressed
> binary Analysis Services database that only Power BI Desktop can produce, so it cannot be
> generated from source or diffed in Git. A `.pbit` holds the same model as JSON and carries no
> data, which means it can be rebuilt from the query and measures in this folder whenever they
> change. Regenerate with `Tools\Build-PowerBiTemplate.ps1`, which parses the column lists out
> of `HealthReports-Query.m` and the measures out of `Measures.dax` so the model can never
> drift from them.

After regenerating, verify with:

```powershell
powershell.exe -NoProfile -File Tools\Test-PowerBiTemplate.ps1
```

That loads Power BI Desktop's own assemblies and validates at **two** levels, because the
first is not sufficient:

1. `PowerBIPackager.Open` — the package envelope and the `Version` part.
2. `PowerBIFileSerializer` — the `Report/Layout` and `DiagramLayout` parts, which
   `Open` never reads. **A file can pass level 1 and still refuse to open.**

Both scripts need **Windows PowerShell 5.1**, not PowerShell 7 — the assemblies target .NET
Framework.

> **The trap, if you ever hand-edit a `.pbit`:** **no part may carry a byte order mark.**
> Power BI reads parts with `Encoding.Unicode.GetString` rather than a `StreamReader`, so a
> BOM is never consumed as a preamble — it survives into the string as `U+FEFF` and breaks
> whatever parses next. `Version` fails in `Version.Parse`; `Report/Layout` and `DiagramLayout`
> fail in the JSON reader at position 0. Every one of them surfaces as
> *"Either the file is encrypted or corrupted"*, which points nowhere near the cause.
> Many editors add a BOM silently on save, so edit these parts programmatically.

If the report page will not open on your Desktop build, use
**[MDE-Toolkit-Fleet-Health-Blank.pbit](../downloads/MDE-Toolkit-Fleet-Health-Blank.pbit)** —
identical model and measures, one empty page to build on. The report layout is the most
version-sensitive part of the package; the model is not.

Columns are set to **no implicit aggregation** on purpose. Summing `HealthScore` across reports
is meaningless, so drag a measure rather than a raw numeric column.

### Apply the theme

Download **[MDE-Toolkit-Theme.json](MDE-Toolkit-Theme.json)** and apply it with
**View → Themes → Browse for themes**. It matches the toolkit's own palette and, more
usefully, wires up the semantic colours Power BI uses for conditional formatting:

| Theme slot | Colour | Use it for |
|---|---|---|
| `good` | `#107C10` green | RTP on, onboarded, firewall complete |
| `neutral` | `#CC8C00` amber | audit-mode ASR, configured-but-not-running |
| `bad` | `#C42B1C` red | tamper protection off, stale device, RDP without NLA |

With those set you can conditional-format a table on `HealthScore` using **Rules** and get
sensible colours without picking hex values by hand.

### Design notes that actually matter here

![Fleet Health dashboard](../images/PowerBI-Sample.jpg)

### The six pages

| Page | Answers | Key fields |
|---|---|---|
| **Fleet Health** | How many devices, how healthy, who stopped reporting | Health score, status, staleness, onboarding |
| **Defender & ASR** | Is the engine current, are the rules actually blocking? | Engine / signature / platform versions, scan times, ASR block vs audit, Network Protection, CFA |
| **Hardening** | Which enforcement controls are on, which only pretend | App Control mode & policy count, Device Control, SmartScreen, Exploit Protection DEP/ASLR/CFG |
| **Platform & ID** | Hardware root of trust, and how machines are joined | BitLocker, Secure Boot, TPM, VBS, HVCI, Credential Guard, Entra/Hybrid join |
| **OS & Local Risk** | Patch currency and local privilege | Windows Update age, PowerShell policy, RDP, LAPS, local admin counts, version drift |
| **Issues** | What is wrong, in the collector's own words | `TopIssues`, critical event counts |

`AV Engine Versions`, `Signature Versions`, `Agent Versions` and
`OS Builds` count **values, not devices** — they answer "how fragmented is the fleet".
On a managed estate, much above one or two usually means something stopped updating.

Not every stored field has a measure. The Function App writes 103 fields and the measures reach
63; most of the rest are the collector's own rollup labels (`DefenderAvStatus`, `FirewallStatus`,
`VbsStatus`) that duplicate booleans already shown. They remain in the model and make good
**slicers** — add `DeviceTag`, `OrgUnit` or `Environment` to any page.

### Worth adding yourself

- **A trend page.** Line chart of `GeneratedAtUtc` against `Avg Health Score (All Reports)`,
  `Reports Received` and `Devices Reporting In Period`. This is the one page that must *not* be
  scoped to the latest report, which is why those three measures are the only ones not filtered
  on `IsLatestPerDevice`.
- **Slicers**, as above.

### Layout principles

- **Build the device table from measures, not columns.** Put `Hostname` on a table visual and
  then use `Latest Status`, `Latest Health Score`, `Hours Since Last Report`, `Latest Device Tag`
  and `Latest Org Unit`. Dragging the raw columns instead gives you **one row per report** — a
  machine that has uploaded ninety times appears ninety times. With `Hostname` as the only
  grouping column, Power BI collapses to one row per device and each measure resolves to that
  device's most recent value, with no page filter needed.
- **Turn totals off on that table** (*Format → Totals*). A totals row aggregates the measures
  *across* devices — max of a status string, max of a score, min of an age — producing a line
  like `Total | Warning | 98 | 11` that means nothing. The template ships with it disabled.
- **`Device Count` is deliberately not `COALESCE`d**, unlike the other counts. It is the value
  of the status donut, and a category chart drops a category when the measure is blank but keeps
  it when the measure is zero. Forcing zero would list every status no current device holds in
  the legend with no slice.
- **Lead with the number, not the chart.** The top row is cards because the first question is
  always "how many devices, and how many are bad". Charts answer the second question.
- **Percentages need their denominator visible.** `RTP Enabled %` next to `Device Count`
  is honest; a lone 94% is not, because it hides whether that is 94% of 500 or of 17.
- **Watch `Unidentified Reports`.** Uploads that arrive with no hostname are stored under the
  literal `unknown` and group together as one synthetic device, inflating `Device Count` by one
  and dragging `Avg Health Score` down. See [Troubleshooting](#unidentified-reports-is-not-zero).
- **Use `HealthBucket`, not `HealthScore`, for grouping.** The score is a continuous value —
  bucketed into Excellent / Good / Needs Attention / Critical it becomes a legend.
- **Sort device tables by `Hours Since Last Report` descending.** The devices that stopped
  reporting are the ones you want at the top.
- **Slice by `DeviceTag` / `OrgUnit` / `Environment`**, which is exactly why those three are
  promoted to their own columns by the Function App.
- **Do not put `ReportJson` on a canvas.** It is up to 30,000 characters per row.
- **Keep the trend page separate** and unfiltered by `IsLatestPerDevice`, or it will show a
  single point per device.

---

## Manual route: build it yourself

## Quick Start (5 minutes)

### Step 1: Open Power BI Desktop
**Home → Get Data → Blank Query**

### Step 2: Paste the query
In the Power Query window click **Advanced Editor** and paste the contents of
`HealthReports-Query.m` (commercial cloud) or `HealthReports-Query-GovCloud.m`
(Azure Government). The two files are identical apart from the endpoint suffix and a
block of Government-specific notes in the header — **if you change one, change both**.

Update the **CONFIGURATION** block at the top:

| Setting | Notes |
|---|---|
| `StorageAccountName` | Your storage account, without the `https://` or suffix |
| `TableName` | `HealthReports` unless you set `StorageTableName` on the Function App |
| `EndpointSuffix` | `table.core.windows.net`, or `table.core.usgovcloudapi.net` for Gov |
| `IncludeReportJson` | Leave `false`. `ReportJson` is up to 30,000 characters per row |
| `StaleAfterHours` | How old the newest report must be before a device counts as stale |

### Step 3: Authenticate — Account Key

The Power Query **Azure Table Storage** connector supports exactly one authentication type:
**Account Key**. There is no *Organizational account* option, so Entra ID data-plane roles
such as **Storage Table Data Reader do not apply here** — granting one and expecting Power BI
to use it produces `Expression.Error: Access to the resource is forbidden.`

Azure portal → your storage account → **Security + networking → Access keys** → `key1` →
**Show** → copy the **Key** value, and paste it when Power BI prompts.

> **Treat the account key as a credential.** It grants full read/write access to every table,
> queue and blob in that storage account — there is no read-only or table-scoped variant that
> this connector accepts. Rotate it on a schedule. Power BI keeps it in its own credential
> store, not in the `.m` file, so the query is still safe to commit.

This applies to the **reporting side only**. Endpoints still hold no secrets: they POST to the
Function App with the signed-in user's Entra token, and the Function App writes to the table
with its Managed Identity. The account key never leaves the analyst's Power BI install.

**If an account key is not acceptable in your environment**, the realistic options are to put
the storage account in a dedicated subscription whose key has a narrow blast radius, to have
the Function App expose a read endpoint that Power BI calls with `Web.Contents` and Entra
auth, or to land the data in ADLS Gen2 / Blob as Parquet, whose connectors do support
Organizational account.

**Azure Government:** authentication is by account key, so there is no tenant sign-in to get
wrong. To publish, use Power BI for US Government (`app.powerbi.us`) — the commercial Power BI
service cannot reach Government storage without an on-premises data gateway.

### Step 4: Load
**Close & Apply**.

### Step 5: Add the measures
**Modeling → New Measure**, then paste each measure from `Measures.dax`.
Add `Device Count` first — most other measures reference it.

---

## Important: one row per report, not one row per device

The ingestion Function App builds `RowKey` as `<Hostname>_<ReportId>`, and the collector
does not send a `ReportId`, so a fresh GUID is generated on every upload. **Each upload
creates a new row.** A device reporting every 8 hours adds roughly 90 rows a month.

This is useful — you get real history for trending — but it has one consequence you must
respect when building visuals:

> `COUNTROWS(HealthReports)` counts **reports**, not devices. Any percentage divided by it
> is weighted by how often each device reported, so one chatty device can dominate the
> whole fleet number.

`HealthReports-Query.m` therefore adds an **`IsLatestPerDevice`** column, true only for the
newest report per hostname. Every posture measure in `Measures.dax` filters on it and counts
distinct hostnames.

**Rule of thumb:**

| You want | Do this |
|---|---|
| Current fleet posture ("how many devices have RTP on?") | Use the supplied measures, or filter visuals to `IsLatestPerDevice = True` |
| A trend over time ("has our health score improved?") | Use every row, plot against `GeneratedAtUtc` — see the TRENDS section of `Measures.dax` |
| A device inventory table | Filter the table visual to `IsLatestPerDevice = True` |

If you would rather have a true one-row-per-device table and do not need history, change
`RowKey` in `run.ps1` to just `$hostname`. The upsert will then overwrite in place, and
`IsLatestPerDevice` will simply be true for every row, so nothing else has to change.

---

## Schemaless columns

Azure Table Storage stores no schema, and the Function App omits any property whose value
is null. A device that has never run a full scan has **no `LastFullScanUtc` property at
all**, rather than a null one.

A plain `Table.ExpandRecordColumn` over a fixed field list fails on that with
*"The field '&lt;name&gt;' of the record wasn't found"*. The query avoids this by merging every
row onto a template record of nulls before expanding, so missing properties become nulls
instead of errors. **If you add columns to the query, add them to the appropriate
`TextColumns` / `BoolColumns` / `IntColumns` / `DateColumns` list** rather than expanding
them directly — that is what keeps the query resilient.

Columns must match `FunctionApp\HealthReportIngestion\run.ps1`. If you add a field there,
add it here too.

---

## Troubleshooting

### `App Control Enforcing %` is 0 but policies are deployed

Usually correct, and worth understanding. App Control has three states:

| Mode | Meaning |
|---|---|
| `Enforced` | Code Integrity is running the policies and blocking |
| `Audit` | Running and logging, but not blocking |
| `Deployed` | `.cip` files are on disk and Code Integrity is **not** running them |

`Deployed` is the dangerous one — someone shipped policies and believes the machines are
covered, but nothing is being blocked. `App Control Inert Devices` counts exactly those.
Compare `App Control Deployed %` against `App Control Enforcing %` to see the gap.

### Exploit Protection, local admin counts or App Control mode are empty

Those three were added to the collector after the first release of these dashboards. Older
agents never sent them, so the fields arrive empty and the cards read `0` — which looks like a
measured zero but means "never checked". Devices must upload at least once on the newer build.
`Agent Versions` on the *OS & Local Risk* page shows how far the rollout has got.

### The same device appears many times in a table

You have dragged raw columns onto the table visual, so you are seeing one row per *report*.
Use `Hostname` plus the `Latest ...` measures instead — see the design notes above.

### `Unidentified Reports` is not zero

Some uploads reached the Function App without a hostname and were stored under the literal
`unknown`. They group together as a single synthetic device, so `Device Count` is one too high
and `Avg Health Score` is dragged toward whatever those rows contain.

The Function App resolves the hostname in this order:

1. the `X-Device-Hostname` request header
2. `hostname` in the JSON payload
3. `machineName` in the JSON payload
4. the literal `"unknown"`

A non-zero count means a sender is supplying none of the first three — usually an old build or
a hand-crafted POST. Fix it at the source rather than filtering the rows out of the report: a
device you cannot name is a device you cannot chase. Existing rows can be deleted from the
table once you have confirmed what produced them.

### A card shows `(Blank)` instead of a number
The supplied measures wrap their counts in `COALESCE(..., 0)` so that "none" reads as a real
zero. If you have written your own measure, do the same — a blank card looks like broken
plumbing rather than a genuine result.

### A chart legend lists categories with no slice

The opposite problem. A category chart keeps a category whose measure is `0` and drops one
whose measure is blank, so a `COALESCE`d measure will list statuses that no current device
holds. Use a non-`COALESCE`d measure — such as `Device Count` — for chart values, and keep the
`COALESCE`d ones for cards.

### A totals row shows nonsense

Turn totals off (*Format → Totals*) on any table built from the `Latest ...` measures. There is
no meaningful aggregate of "the maximum status string across devices".

### `Expression.Error: Access to the resource is forbidden.`

A 403 from Azure Storage. The query is fine — it reached the service and was refused.
In order of likelihood:

1. **You authenticated with an Entra identity instead of an account key.** This connector
   only supports **Account Key** (see [Step 3](#step-3-authenticate--account-key)). No amount
   of *Storage Table Data Reader* will help. Clear the cached credential under
   **File → Options and settings → Data source settings → Clear Permissions**, refresh, and
   supply the account key when prompted.
2. **Wrong or rotated key.** Re-copy `key1` from the portal. If the key was rotated after you
   first connected, Power BI is still sending the old one.
3. **Storage firewall.** If the account has **Networking → Public network access** set to
   *Enabled from selected virtual networks and IP addresses*, add your client IP, or use a
   data gateway. Note that Power Query Online in the same Azure region as the storage account
   cannot reach it directly at all — Microsoft documents a gateway as the workaround.
4. **Wrong cloud.** A commercial endpoint suffix against a Government storage account, or vice
   versa, can surface as a 403 rather than a name resolution failure.

### `DataFormat.Error: OData: The format isn't supported`

Microsoft documents this for **mixed-case table names under Entra authentication**. If your
table is named something like `MDEToolkitReports` rather than all lowercase, either switch to
account key authentication (which is what this query expects anyway) or recreate the table
with a lowercase name.

### `Expression.Error: The field 'Content' of the record wasn't found.`

You are accessing the navigation record with `[Content]` instead of `[Data]`:

```m
// wrong
RawTable = Source{[Name = TableName]}[Content],

// right
Navigation = Source{[Name = TableName]},
RawTable   = Navigation[Data],
```

The name `Content` appears at **two different levels**, which is what makes this confusing:

| Level | Name | What it is |
|---|---|---|
| Navigation record from `AzureStorage.Tables` | `Data` | The table itself |
| Column inside that table | `Content` | A record per row holding every non-key property |

So you navigate with `[Data]`, then expand the `Content` **column**. The supplied query does
both correctly — this error means an older or hand-edited copy is in the Advanced Editor.

### `The field '<SomeColumn>' of the record wasn't found.`

A different error to the one above, and the reason the query merges onto a template record —
see [Schemaless columns](#schemaless-columns). If you hit it, you have added a column to the
expand step directly instead of to one of the `TextColumns` / `BoolColumns` / `IntColumns` /
`DateColumns` lists.

### `The key didn't match any rows in the table.`

`TableName` does not exist in that storage account. Check the value, and check the Function
App's `StorageTableName` application setting — it defaults to `HealthReports`.

### Percentages look wrong, or one device dominates the fleet

You are counting rows rather than devices. See
[one row per report](#important-one-row-per-report-not-one-row-per-device).

### Every date column is blank

Older Function App builds wrote non-ISO dates. The query's `ToDate` handles both, but if you
have replaced it with a plain `type datetimezone` conversion, locale-formatted values will
fail to parse and land as null. Redeploy the Function App to get ISO 8601 going forward.

### Date columns

Only `GeneratedAtUtc` and `UploadedAtUtc` have always been written as ISO 8601. On builds
before the fix, `LastBootTimeUtc`, `LastQuickScanUtc`, `LastFullScanUtc` and
`WindowsUpdateLastInstallDate` were written using the Function App's locale
(`08/20/2026 04:31:09`). The `ToDate` converter in the query accepts both formats, so
historical rows keep working after you redeploy the Function App.

---

## Suggested Report Layout

Filter every page except the trends page to `IsLatestPerDevice = True`, or use the
supplied measures which do it for you.

### Page 1: Fleet Health Overview
| Visual | Type | Fields |
|--------|------|--------|
| Health Score Gauge | Gauge | `[Avg Health Score]` |
| Device Count | Card | `[Device Count]` |
| Healthy % | Card | `[Healthy Device %]` |
| Status Breakdown | Donut | `OverallStatus` by `[Device Count]` |
| Health by Tag | Bar | `DeviceTag` or `OrgUnit` × `[Avg Health Score]` |
| Stale Devices | Table | `Hostname`, `HoursSinceReport`, filtered to `[Stale Device Count]` |

### Page 2: Defender & ASR
| Visual | Type | Fields |
|--------|------|--------|
| RTP / Tamper / Behaviour | Cards | `[RTP Enabled %]`, `[Tamper Protection %]`, `[Behavior Monitoring %]` |
| Signature Age | Histogram | `SignatureAgeHours` |
| Stale Signatures | Card | `[Stale Signatures Count]` |
| ASR Coverage | Stacked Bar | `Hostname` × `AsrBlockModeRules` / `AsrAuditModeRules` |
| ASR Audit Only | Card | `[ASR Audit Only Devices]` — configured but not blocking |
| MDE Onboarding | Card | `[MDE Onboarding %]`, `[Sense Not Running]` |

### Page 3: Firewall & Network
| Visual | Type | Fields |
|--------|------|--------|
| Firewall Matrix | Matrix | `Hostname` × Domain / Private / Public enabled |
| Firewall Gaps | Card | `[Firewall Gaps]` |
| Network Protection | Donut | `NetworkProtectionStatus` |

### Page 4: App & Device Control
| Visual | Type | Fields |
|--------|------|--------|
| WDAC | Cards | `[App Control Enabled %]`, `[App Control Not Enforcing]` |
| Device Control | Card | `[Device Control Enabled %]` |
| VBS / HVCI / Cred Guard | Cards | `[VBS Enabled %]`, `[HVCI Running %]`, `[Credential Guard %]` |
| Pending Reboot | Card | `[HVCI Pending Reboot]` |

### Page 5: Platform & OS Hygiene
| Visual | Type | Fields |
|--------|------|--------|
| BitLocker / Secure Boot / TPM | Cards | `[BitLocker Protected %]`, `[Secure Boot %]`, `[TPM Enabled %]` |
| Update Currency | Card | `[Avg Days Since Update]`, `[Behind On Updates]` |
| RDP Exposure | Card | `[RDP Exposed Devices]` — RDP on without NLA |

### Page 6: Trend
| Visual | Type | Fields |
|--------|------|--------|
| Health over time | Line | `GeneratedAtUtc` × `[Avg Health Score (All Reports)]` |
| Reporting volume | Column | `GeneratedAtUtc` × `[Reports Received]` |
| Coverage over time | Line | `GeneratedAtUtc` × `[Devices Reporting In Period]` |

### Page 7: Device Inventory
| Visual | Type | Fields |
|--------|------|--------|
| Full Table | Table | Key columns, filtered to `IsLatestPerDevice = True` |
| Slicers | Slicer | `DeviceTag`, `OrgUnit`, `Environment`, `OverallStatus`, `OsVersion` |