Delivery Optimization Report

This report displays Delivery Optimization data from Microsoft's Windows Update for Business Reports solution in Power BI.

Download the Power BI template from here.

Open opening the template, you'll be prompted for your Log Analytics Workspace Id and the Data retention period, which can also simply be the number of days of historical data you want to include in the report (up to the data retention period).

If it's the first time you've connected to the log analytics workspace with Power BI, you'll be prompted for credentials. Choose Organizational account, sign in with your organizational account and click Connect.

The account you sign in with should have at least the Log Analytics Reader role in the log analytics workspace.

If you publish the report to the Power BI service I recommend adding a service principal to authenticate after publishing.

Understanding the data

Delivery Optimization data is reported to Microsoft by telemetry events on the device. For example, a device downloads the latest Windows Updates after "patch Tuesday" and DO statistics for the downloads will be sent to MS via telemetry. The data for each device, grouped by content type, is added to the UCDOStatus table in the Log Analytics workspace. Content types include Quality Updates, Driver Updates, Office Updates etc. This table contains both current and historic data for each device.

Generally, for each active device, a new row will be added to this table each day summarizing data received for each content type in the previous 28 days. This means if you query for the latest date for a device you should get data for all content types where DO has been used in the previous 28 days.

Data gaps and dips have been observed in the UCDOStatus table! Sometimes several days of data can be missing. This doesn't appear to affect the UCDOAggregatedStatus data though.

The UCDOAggregatedStatus table takes the device data and aggregates it for each content type each day. Therefore each row in this table will contain aggregated data from all active devices that have send telemetry for DO in the last 28 days grouped by date and content type.

You can read more about the Delivery Optimization data in the Microsoft documentation.

Report pages

Here's a quick walkthrough of the report pages.

Content distribution (aggregated)

This page displays the data volumes, content types and efficiency percentages from the daily aggregated data in the UCDOAggregatedStatus table. Select the most recent data to view current data.

Content distribution (general)

This page displays the data volumes, content types and efficiency percentages summarized by querying the device data in the OCDOStatus table. The most recent date should give current data but sometimes this is not complete, so do check against other dates to be sure.

I've chosen to add TiB, GiB and MiB values separately to the report. Different units make more sense in different contexts. I personally don't like combining values with different units of measurement!

Content distribution (grouped)

This page is similar to the previous one but it allows grouping of devices by country, city, ISP or group Id - whichever may make the most sense for you and your download mode configuration. Unfortunately group Ids are encoded values and won't make much sense unless you decode and map them.

Device configuration and data

This page displays configuration information for devices, such as peering status and download mode. You can view some of the raw data for devices from the UCDOStatus table.

Daily aggregate (all content types)

On this page you can see the aggregated data from the UCDOAggregatedStatus table by day, with summarized values for all content types.

Daily aggregate (by content type)

Here you can view the daily aggregated data and filter by content type. In the example you can see that there were higher content values for Feature and Flight Updates at the beginning of the month than at the end of the month due to a previous feature update rollout.

The Bandwidth optimization and Device count charts only show true values if a single content type is selected.