WaaS -IPU Progress Tracking Spreadsheet

As I write this post we are just days away from beginning the mass In-Place Upgrades (IPUs) of the entire enterprise (~35,000 devices). Of course everyone is going to want to know how the numbers are looking. To better show how the IPUs are progressing, I’ve created a tracking spreadsheet.

The spreadsheet is similar in concept to my Windows 7 to Windows 10 migration spreadsheet found here. I geared this spreadsheet more to tracking the changes to the populations of the various Windows 10 builds in the environment.

When you open the spreadsheet you’ll find 3 tabs:

Variables
Worksheet
Charts

Variables Tab

This tab is used to configure the spreadsheet for your deployment. There are three sets of fields that you will need to enter in the data for.

Initial Counts

Shaded Yellow

Here you will enter in the counts for each Windows 10 build in your environment as of today (or whenever you are going to start your WaaS project). These are the starting point of the roll-out and act as the snapshot of where things stood when you began.

WaaS IPU Start and End Dates

Shaded Green

Enter in the starting and ending dates of your IPU deployments. The spreadsheet will use these dates to calculate things like the number of weeks in the deployment and how many IPUs need to happen each week to meet the deadline.

Target Build

Shaded Blue

Enter the “friendly” build number you are upgrading to. The spreadsheet will use this data in formulas tallying up counts of devices that do and do not need to be upgraded.

Projections

The block of cells under Projections are all calculated based on the values you entered in the cells explained above.

The Starting Count will total up the number of devices (that you entered in the yellow “Initial Counts” cells) that are running Windows 10 builds prior to the targeted build (that you entered in the blue shaded cell).

The Time-frame cell is calculated based on the starting and ending dates you entered in the green shaded cells. That is then used to calculate the number of IPUs needed on average each week to complete all of the upgrades by the ending date.

The Starting Count is then used in formulas calculating the number of remaining upgrades at the 25%, 50%, 75% and 100% completion milestones.

Worksheet Tab

It is on this tab that all of the heavy lifting is done. The formulas on this tab will calculate running changes, weekly changes, actual vs. projected paces and estimated target dates for the 24%, 50%, 75% and 100% milestones. Each column of the spreadsheet will equate to a week of the deployment project and there are enough columns to handle a multiyear deployment.

This tab is broken down into 6 blocks.

Date/Project Timeline Information
Actual Machine Counts
Migration Progress [Percentage Complete]
Migration Progress
Forecasts Based on Actual Machine Counts
Projected Milestones

Date/Project Timeline Information

In this block will be the header information showing year, month and date headers. The current week will be highlighted in purple. It will also calculate the number of weeks and months remaining in the deployment project. This is all calculated based on the starting and ending dates you provided in the Variables tab.

Actual Machine Counts

Here is where you will be updating the counts of your various Windows 10 builds. In Configuration Manager (Microsoft Endpoint: Configuration Manager) I have collections for each build release, then I run a simple PowerShell script that pulls the counts and writes them to a date stamped CSV file. Then it’s a simple cut and paste into this spreadsheet.

The Totals, Weekly Change, Running Change and Average Weekly Change will reflect the data for the build that you specified in the blue shaded cell on the Variables tab. The row headings and formulas will automatically reflect that build you specified on the Variables tab. If you change the targeted build number in the blue shaded cell on the Variables tab, you’ll see all of the calculations and row headers change to reflect that.

Migration Progress [Percentage Complete]

The formulas in this block will show you the week by week reduction (shown as a percentage) in the number of devices running a Windows 10 build prior to your targeted build. It will also show week by week, the total Windows 10 population and the percentage of that population that is running a Windows 10 build at or newer than your targeted build version.

Migration Progress

This block attempts to boil it down to answer the simple question, “How is the roll-out going?”. The formulas here will show, based on the data you entered on the Variables tab, how far along you should be, how far ahead or behind that pace you are, and then it calculates an estimate of how many weeks ahead or behind the roll-out may be.

Forecasts Based on Actual Machine Counts

This block is a simple calculation that, based on the actual counts of the different builds, determines how many IPUs both per month and per week are required to meet the project deadline, the end date you entered in the Variables tab.

Projected Milestones

This last block will calculate an estimated date that each milestone (25%, 50%, 75% and 100%) will be reached. These dates are based on the actual number of devices that still need to be upgraded and the average weekly pace up to that point in time. When presenting this info I frame it with a statement similar to, “If we continue to maintain this pace, we are on track to hit 50% completion by <date> .”

Charts Tab

Here I’ve created a simple line chart showing the week by week counts of each of the Windows 10 builds being tracked. The intent is to provide a simple visual illustration on how the roll-out is progressing. The lines representing “old” Windows 10 builds drop towards 0, the line(s) representing your targeted build (or newer) will climb.

Downloads

You can download a copy of the spreadsheet here on my GitHub repository.

PowerShell Sample to “Pull the Numbers”

I use the following PowerShell code to extract the counts of each Windows 10 build in our environment. I’ve set up a series of collections named “WaaS Windows 10 – “, one for each build release. The query pulls in only those devices that are running the specified build.

The script enumerates the collections, pulls the membership count and writes it all to a CSV file that is date stamped with today’s date. All I have to do is run the script, open the CSV and copy the counts into the spreadsheet. At some point I may attempt to directly update the spreadsheet with PowerShell, but that’s a future me project.

[code language=”PowerShell”]

$Today = Get-Date -Format yyyy-MM-dd

$Windows10BuildCounts = “D:WaaSWindows10BuildCounts_$Today.csv”

$arrBuildNumbers = @( “1507” “1511” “1607” “1703” “1709” “1803” “1809” “1903” “1909” )

FOREACH ($build in $arrBuildNumbers)

{

$W10_Collection = $NULL

$W10_Collection = “WaaS Windows 10 – $build”

$AllW10 = $NULL

Set-Location “MM1:”

$AllW10 = Get-CMCollectionMember -CollectionName $W10_Collection

Write-Host $W10_Collection => $($AllW10.count)