Exchange - Server Update Checklist Script

 A while back (a long while, 2015) I wrote a post where I created a Word doc checklist to help keep track of tasks when updating on-premises Exchange servers. It's helpful when you have a lot of servers to manage, but it required some manual editing if you have lots of servers.

I have several customers in the process of updating their servers since Exchange SE has been released, and Exchange 2016/2019 EOL is looming on 14 OCT 25.

A few of those customers have more than a handful of servers - one has 175 in multiple DAGs. So I thought it would be helpful to create an "interactive" spreadsheet where you can check off certain tasks when completed on each server, and what better way to build that spreadsheet than with PowerShell?

So, I created a little script that will prompt for the number of EXCH servers, then prompt for the server names.

It will then automatically build the spreadsheet in the folder where you run the script from, with the server names as the header, and the tasks in the first column. Then it adds drop-down cells with the status of each task, like: Pending, Complete, Complete With Issues or Not Applicable (for ones like Edge Servers that don't require tasks like load balancer pool removal or DAG work).

The drop-downs are color-coded for easier readability and there's a "notes" column that autowraps if you need to add notes for any issues.

The Script

Copy the text block and save as something like EXCH_Update_Checklist.ps1

# Prompt for number of Exchange servers

$numServers = Read-Host "Enter the number of Exchange servers"

# Collect server names

$serverNames = @()

for ($i = 1; $i -le $numServers; $i++) {

    $serverName = Read-Host "Enter name for server $i"

    $serverNames += $serverName

}

# Define Exchange maintenance tasks

$tasks = @(

    "Backup",

    "Disable in Load Balancer",

    "Stop Extra Services (A/V, backup)",

    "Stop Monitoring",

    "Start Exchange maintenance mode",

    "Uninstall old CU Language Pack (IF APPLICABLE)",

    "Install Windows Updates",

    "Reboot",

    "Run CU/SU Install",

    "Verify CU/SU",

    "Reboot",

    "Check Build Number",

    "Install new CU Language Pack (IF APPLICABLE)",

    "Stop Exchange maintenance mode",

    "Enable in Load Balancer",

    "Rebalance DAG DB’s",

    "Re-enable Monitoring",

    "Start Services (A/V, backup)"

)

# Create Excel COM object

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $false

$workbook = $excel.Workbooks.Add()

$sheet = $workbook.Worksheets.Item(1)

# Write headers and bold them

$sheet.Cells.Item(1, 1).Value2 = "Tasks"

$sheet.Cells.Item(1, 1).Font.Bold = $true

for ($i = 0; $i -lt $serverNames.Count; $i++) {

    $cell = $sheet.Cells.Item(1, $i + 2)

    $cell.Value2 = $serverNames[$i]

    $cell.Font.Bold = $true

}

# Add Notes column header

$notesCol = $serverNames.Count + 2

$sheet.Cells.Item(1, $notesCol).Value2 = "Notes"

$sheet.Cells.Item(1, $notesCol).Font.Bold = $true

# Write tasks in first column and bold them

for ($i = 0; $i -lt $tasks.Count; $i++) {

    $cell = $sheet.Cells.Item($i + 2, 1)

    $cell.Value2 = $tasks[$i]

    $cell.Font.Bold = $true

}

# Add dropdowns and default value "Status [Dropdown]" to each cell

$dropdownOptions = 'Pending,Not Applicable,Complete,Complete with Issues'

for ($row = 2; $row -le $tasks.Count + 1; $row++) {

    for ($col = 2; $col -le $serverNames.Count + 1; $col++) {

        $cell = $sheet.Cells.Item($row, $col)

        $cell.Value2 = "Status [Dropdown]"

        $range = $sheet.Range($cell, $cell)

        $range.Validation.Delete()

        $range.Validation.Add(3, 1, 1, $dropdownOptions)

        $range.Validation.IgnoreBlank = $true

        $range.Validation.InCellDropdown = $true

        $cell.Interior.ColorIndex = -4142  # No fill

    }

    # Add empty Notes cell

    $sheet.Cells.Item($row, $notesCol).Value2 = ""

}

# Apply conditional formatting to dropdown cells

$xlCellValue = 1

$xlEqual = 3

$startCell = $sheet.Cells.Item(2, 2)

$endCell = $sheet.Cells.Item($tasks.Count + 1, $serverNames.Count + 1)

$dropdownRange = $sheet.Range($startCell, $endCell)

$dropdownRange.FormatConditions.Delete()

# "Pending" = Yellow

$formatPending = $dropdownRange.FormatConditions.Add($xlCellValue, $xlEqual, "Pending")

if ($formatPending) { $formatPending.Interior.Color = 65535 }

# "Complete" = Light Green

$formatComplete = $dropdownRange.FormatConditions.Add($xlCellValue, $xlEqual, "Complete")

if ($formatComplete) { $formatComplete.Interior.Color = 5296274 }

# "Complete with Issues" = Light Red

$formatIssues = $dropdownRange.FormatConditions.Add($xlCellValue, $xlEqual, "Complete with Issues")

if ($formatIssues) { $formatIssues.Interior.Color = 13408767 }

# Auto-fit columns (except Notes)

$usedRange = $sheet.UsedRange

$usedRange.Columns.AutoFit()

# Set Notes column width to 45 and enable text wrapping

$notesColumn = $sheet.Columns.Item($notesCol)

$notesColumn.ColumnWidth = 45

$notesColumn.WrapText = $true

# Save the workbook in the script's current directory with date in filename

$currentDate = Get-Date -Format "yyyy-MM-dd"

$scriptDirectory = Split-Path -Parent $MyInvocation.MyCommand.Definition

$savePath = Join-Path $scriptDirectory "Exchange Maintenance Checklist_$currentDate.xlsx"

$workbook.SaveAs($savePath)

# Cleanup

$workbook.Close($false)

$excel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

[GC]::Collect()

[GC]::WaitForPendingFinalizers()

# Automatically open the saved Excel file

Start-Process $savePath

Write-Output "Excel spreadsheet saved to $savePath"

When you run it, as stated above, it will ask for the server count, then ask for server names, save the spreadsheet and automatically open it with your pre-populated servers.

The output will look like so:

Hopefully that'll make it easier to keep track of what's been done, and if you have a team of engineers working on each task, they can update the columns if you share it out on SharePoint. If I get some time, I'll make it where you can pipe in a CSV of server names to populate the script quickly.