VMware: PowerShell script for finding unused virtual machines

This script is designed to find virtual machines on VMware vCenter Server that have not had connections via the VMRC or HTML5 console by users for a long time. The algorithm uses the server's "Events", so it is important that the "Event retention" policy is configured for a long period (preferably 365 days).

This setting is performed in vCenter->Settings->General->Edit->Database. There you need to increase the "Event retention (days)" value and restart the vCenter server to apply the parameters.

Below is the script itself, which works as follows: for each virtual machine from the specified folder and subfolders, it finds the latest event of two types (VmAcquiredTicketEvent, VmAcquiredMksTicketEvent) and writes one row to the report.

<#
.SYNOPSIS
    Last console connection for each VM from a vCenter folder (VMRC + HTML5).
.DESCRIPTION
    Recursively collects VMs from the specified folder, finds the latest event
    VmAcquiredTicketEvent or VmAcquiredMksTicketEvent for each VM
    and saves the report to .xls (SpreadsheetML) without external modules.
#>

# ======================== SETTINGS ========================
$vCenterServer = "vcenter.mydomain.local"
$FolderName    = "VM_Folder"
$Days          = 360
$OutputPath    = Join-Path -Path $PSScriptRoot -ChildPath "vm_console_last_event.xls"
# ==========================================================

$eventTypes = @("VmAcquiredTicketEvent", "VmAcquiredMksTicketEvent")
$startTime  = (Get-Date).AddDays(-$Days)
$finishTime = Get-Date

function Get-VMsRecursive {
    param($Folder)
    $vms = @(Get-VM -Location $Folder -ErrorAction SilentlyContinue)
    foreach ($sub in @(Get-Folder -Location $Folder -ErrorAction SilentlyContinue)) {
        $vms += Get-VMsRecursive -Folder $sub
    }
    return $vms
}

function ConvertTo-XmlSafe {
    param([string]$s)
    if ($null -eq $s) { return "" }
    return $s.Replace("&","&amp;").Replace("<","&lt;").Replace(">","&gt;").Replace('"',"&quot;").Replace("'","&apos;")
}

function Export-ToSpreadsheetML {
    param([object[]]$Rows, [string]$Path, [string]$Title)

    $columns = @(
        @{ Name = 'VM';                          Width = 200 },
        @{ Name = 'User';                Width = 160 },
        @{ Name = 'Last connection time'; Width = 170 },
        @{ Name = 'Message';                   Width = 500 }
    )

    $sb = New-Object System.Text.StringBuilder
    [void]$sb.AppendLine('<?xml version="1.0" encoding="UTF-8"?>')
    [void]$sb.AppendLine('<?mso-application progid="Excel.Sheet"?>')
    [void]$sb.AppendLine('<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"')
    [void]$sb.AppendLine(' xmlns:o="urn:schemas-microsoft-com:office:office"')
    [void]$sb.AppendLine(' xmlns:x="urn:schemas-microsoft-com:office:excel"')
    [void]$sb.AppendLine(' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">')
    [void]$sb.AppendLine(@'
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Top"/>
   <Font ss:FontName="Calibri" ss:Size="11"/>
  </Style>
  <Style ss:ID="Header">
   <Alignment ss:Vertical="Center" ss:Horizontal="Center" ss:WrapText="1"/>
   <Font ss:FontName="Calibri" ss:Size="11" ss:Bold="1" ss:Color="#FFFFFF"/>
   <Interior ss:Color="#4472C4" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="CellWrap">
   <Alignment ss:Vertical="Top" ss:WrapText="1"/>
   <Font ss:FontName="Calibri" ss:Size="11"/>
  </Style>
  <Style ss:ID="CellNoEvent">
   <Font ss:FontName="Calibri" ss:Size="11" ss:Color="#C00000" ss:Italic="1"/>
  </Style>
  <Style ss:ID="Title">
   <Font ss:FontName="Calibri" ss:Size="12" ss:Bold="1" ss:Color="#1F3864"/>
  </Style>
 </Styles>
'@)

    [void]$sb.AppendLine(' <Worksheet ss:Name="Console_Connections">')
    [void]$sb.AppendLine('  <Table>')
    foreach ($c in $columns) { [void]$sb.AppendLine("   <Column ss:Width=`"$($c.Width)`"/>") }

    $rowNum = 1
    if ($Title) {
        [void]$sb.AppendLine("   <Row ss:Height=`"22`"><Cell ss:StyleID=`"Title`" ss:MergeAcross=`"$($columns.Count - 1)`"><Data ss:Type=`"String`">$(ConvertTo-XmlSafe $Title)</Data></Cell></Row>")
        [void]$sb.AppendLine('   <Row/>')
        $rowNum += 2
    }

    [void]$sb.AppendLine('   <Row ss:Height="28">')
    foreach ($c in $columns) { [void]$sb.AppendLine("    <Cell ss:StyleID=`"Header`"><Data ss:Type=`"String`">$(ConvertTo-XmlSafe $c.Name)</Data></Cell>") }
    [void]$sb.AppendLine('   </Row>')
    $headerRow = $rowNum++

    foreach ($r in $Rows) {
        $style = if ($r.'Last connection time' -eq '-') { "CellNoEvent" } else { "CellWrap" }
        [void]$sb.AppendLine('   <Row>')
        foreach ($c in $columns) { [void]$sb.AppendLine("    <Cell ss:StyleID=`"$style`"><Data ss:Type=`"String`">$(ConvertTo-XmlSafe ([string]$r.($c.Name)))</Data></Cell>") }
        [void]$sb.AppendLine('   </Row>')
        $rowNum++
    }

    [void]$sb.AppendLine('  </Table>')
    [void]$sb.AppendLine("  <AutoFilter x:Range=`"R${headerRow}C1:R$($rowNum - 1)C$($columns.Count)`" xmlns=`"urn:schemas-microsoft-com:office:excel`"/>")
    [void]$sb.AppendLine('  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">')
    [void]$sb.AppendLine('   <FreezePanes/><FrozenNoSplit/>')
    [void]$sb.AppendLine("   <SplitHorizontal>$headerRow</SplitHorizontal><TopRowBottomPane>$headerRow</TopRowBottomPane><ActivePane>2</ActivePane>")
    [void]$sb.AppendLine('  </WorksheetOptions>')
    [void]$sb.AppendLine(' </Worksheet>')
    [void]$sb.AppendLine('</Workbook>')

    $sb.ToString() | Out-File -FilePath $Path -Encoding UTF8
}

# --- Connection ---
Write-Host "Connecting to $vCenterServer..." -ForegroundColor Cyan
try { Connect-VIServer -Server $vCenterServer -ErrorAction Stop | Out-Null }
catch { Write-Error "Failed to connect: $($_.Exception.Message)"; return }

try {
    $folder = @(Get-Folder -Name $FolderName -Type VM -ErrorAction SilentlyContinue)[0]
    if (-not $folder) {
        Write-Error "Folder '$FolderName' not found."
        Get-Folder -Type VM | Select-Object Name, Id | Format-Table -AutoSize | Out-Host
        return
    }

    Write-Host "Folder: $($folder.Name) | Period: $($startTime.ToString('yyyy-MM-dd')) — $($finishTime.ToString('yyyy-MM-dd'))" -ForegroundColor Green

    $vms = Get-VMsRecursive -Folder $folder | Group-Object Id | ForEach-Object { $_.Group[0] }
    if ($vms.Count -eq 0) { Write-Host "No VMs found." -ForegroundColor Yellow; return }
    Write-Host "VMs found: $($vms.Count)`n" -ForegroundColor Green

    $rows = New-Object System.Collections.Generic.List[object]
    $i = 0

    foreach ($vm in $vms) {
        $i++
        Write-Progress -Activity "Processing VMs" -Status "$($vm.Name) ($i of $($vms.Count))" -PercentComplete (($i / $vms.Count) * 100)

        try {
            $last = Get-VIEvent -Entity $vm -Start $startTime -Finish $finishTime -ErrorAction Stop |
                    Where-Object { $eventTypes -contains $_.GetType().Name } |
                    Sort-Object CreatedTime -Descending |
                    Select-Object -First 1
        }
        catch {
            $rows.Add([PSCustomObject]@{
                'VM' = $vm.Name; 'User' = '-'
                'Last connection time' = 'Error'
                'Message' = $_.Exception.Message
            })
            continue
        }

        if ($last) {
            $rows.Add([PSCustomObject]@{
                'VM'                          = $vm.Name
                'User'                = if ($last.UserName) { $last.UserName } else { '-' }
                'Last connection time' = $last.CreatedTime.ToString('yyyy-MM-dd HH:mm:ss')
                'Message'                   = [string]$last.FullFormattedMessage
            })
        }
        else {
            $rows.Add([PSCustomObject]@{
                'VM'                          = $vm.Name
                'User'                = '-'
                'Last connection time' = '-'
                'Message'                   = 'No connection events'
            })
        }
    }

    Write-Progress -Activity "Processing VMs" -Completed

    $outDir = Split-Path -Parent $OutputPath
    if ($outDir -and -not (Test-Path $outDir)) { New-Item -ItemType Directory -Path $outDir -Force | Out-Null }

    $title = "Last console connection — vCenter: $vCenterServer, folder: '$FolderName', period: $($startTime.ToString('yyyy-MM-dd')) — $($finishTime.ToString('yyyy-MM-dd'))"
    Export-ToSpreadsheetML -Rows $rows -Path $OutputPath -Title $title

    $withEvents = @($rows | Where-Object { $_.'Last connection time' -ne '-' }).Count
    Write-Host "`nDone. VMs: $($vms.Count), with connections: $withEvents, without: $($vms.Count - $withEvents)" -ForegroundColor Green
    Write-Host "File: $OutputPath" -ForegroundColor Green
}
finally {
    Disconnect-VIServer -Server $vCenterServer -Confirm:$false -ErrorAction SilentlyContinue | Out-Null
}