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("&","&").Replace("<","<").Replace(">",">").Replace('"',""").Replace("'","'")
}
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
}
