Get VMware VM creation date (kind of)

VMware vSphere doesn’t seem to keep the VM creation date in its database anywhere, but if your VMs are all/mostly running Windows (and you have permission to do a WMI query against them) you can get the OS install date, which probably aligns fairly closely to the VM creation date. It won’t be perfect, but it might be good enough, and better than nothing.

This PowerShell/PowerCLI script attempts to pull information out of your VMs, and allows you to fairly easily graph (via Excel) how your VMware environment has grown over time. Note that in its current form the script assumes that the VM name is the same as the Windows computer name.

The script gives you the following columns in its CSV file output:

• VM name
• Windows OS install date (or a reason why it wasn’t able to get it)
• Number of vCPUs for the current VM
• vRAM allocated to the current VM

My suggestion for graphing is as follows: sort the output by the InstallDate column, ditch the rows where the date couldn’t be retrieved. Shift the CPU and RAM columns three columns to the right to give a three column gap. In the column immediately next to the date, have a “Count” column – a simple incrementing number to keep count of how many VMs you had on that date, starting at one and going up by one per row (i.e. 1, 2, 3, 4, …). Then in the other two columns use simple formulas to keep a running total of the amount of vRAM and vCPUs that you have. To get the graph, select the column heading for the InstallDate column, then Ctrl-click on the column headings for the Count, TotalvRAM and TotalvCPU columns one at a time. You can then insert a line graph, and it’ll look halfway decent (albeit needing some tidying up and adjustment of the labelling of the axes):

Clearly if you have converted any physical machines to VMs then the install date will be that of the physical server, not the VM creation, so you might want to ignore anything before whatever date you went 100% VM, but it’s still better than nothing!

function LoadVMwareSnapins{
    $snapinList = @( "VMware.VimAutomation.Core", "VMware.VimAutomation.License", "VMware.DeployAutomation", "VMware.ImageBuilder", "VMware.VimAutomation.Cloud")

    $loaded = Get-PSSnapin -Name $snapinList -ErrorAction SilentlyContinue | % {$_.Name}
    $registered = Get-PSSnapin -Name $snapinList -Registered -ErrorAction SilentlyContinue  | % {$_.Name}
    $notLoaded = $registered | ? {$loaded -notcontains $_}

    foreach ($snapin in $registered) {
        if ($loaded -notcontains $snapin) {
            Add-PSSnapin $snapin
        }
    }
}

function GetInstallDate([string]$Computer){
    Write-Host "Processing $Computer"
    if(Test-Connection -ComputerName $Computer -Count 1 -ErrorAction SilentlyContinue){
        $OS = Get-WmiObject -Class Win32_OperatingSystem -ComputerName $Computer -ErrorAction SilentlyContinue
        if($OS -ne $null){
            $InstallDate = $OS.ConvertToDateTime($OS.Installdate)
            Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name InstallDate -Value $InstallDate
        } else {
            Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name InstallDate -Value "WMI fail"
        }
    } else {
        Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name InstallDate -Value "Ping fail"
    }
}

$CSVFile = "$env:UserProfile\Desktop\VMAudit2.csv"
$TotalVMs = 0
$TotalvRAM = 0
$TotalCPUs = 0

LoadVMwareSnapins
Connect-VIServer -Server vsphere
$VMs = Get-VM

foreach($VM in $VMs){
    $ResultObject = New-Object -TypeName PSObject
    Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name Computer -Value $VM.Name
    if($vm.PowerState -eq "PoweredOn"){
        GetInstallDate $VM.Name
    } else {
        Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name InstallDate -Value "Powered off"
    }
    $TotalVMs = $TotalVMs + 1
    $TotalvRAM = $TotalvRAM + $VM.MemoryGB
    $TotalCPUs = $TotalCPUs + $VM.NumCpu
    Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name vCPUs -Value $VM.NumCpu
    Add-Member -InputObject $ResultObject -MemberType NoteProperty -Name vRAM -Value $VM.MemoryGB
    Export-Csv -InputObject $ResultObject -Path $CSVFile -Append -NoTypeInformation
    $ResultObject | Format-Table -AutoSize
}
Write-Host "Total VMs: $TotalVMs, Total vRAM: $TotalvRAM, Total vCPUs: $TotalCPUs"
Disconnect-VIServer -Server vsphere