# Nightly Automated Script To Gather VM and Host Information, and Output To A CSV

Admittedly this was my first attempt at creating a Powershell script, but thought I would share my journey.  We wanted a way to track the location of customer VMs running on our ESXi hosts, and their power state in the event of a host failure.  Ideally this would be automated to run multiple times during the day, and the output would be saved to a csv on a network share.

We had discovered a bug in vCenter 5.5 where if the ESXi 5.5 host was in a disconnected state, and an attempt was made to reconnect it using the vSphere client without knowing that the host was actually network isolated, HA would not restart the VMs on another host as expected.  We would later test this in a lab to find that if we had not used the reconnect option, HA would restart the VMs as expected on other hosts.  We again tested this scenario in vCenter 6 update 2, and the bug was not present.

So the first powercli one-liner I came up with was the following:

> get-vm | select VMhost, Name, @{N="IP Address";E={@($_.guest.IPaddress[0])}}, PowerState | where {$_.PowerState -ne "PoweredOff"} | sort VMhost

I wanted a list of powered on VMs, their IPs, what host they were running on, and I wanted to sort the output by the host name.  Knowing I was on the right track, now I wanted to be able to connect to multiple data centers, and have each data center’s output saved to a CSV on a network share.  I didn’t really need to hang on to the CSVs in that network share for more than seven days, so I also wanted to build in logic so that it would essentially cleanup after itself.

That script looks something like this:

#Initial variables
$vCenter = @()$sites = @("vcenter01","vcenter02","vcenter03")

#get array of sites and establishes connections to vCenters
foreach ($site in$sites) {
$vCenter =$site + "domain.net"

Connect-VIServer $vCenter #get list of not equal to powered off VMs, there IP and which hosts they're running on get-vm | select VMhost, Name, @{N="IP Address";E={@($_.guest.IPaddress[0])}}, PowerState | where {$_.PowerState -ne "PoweredOff"} | sort VMhost | Export-Csv -Path "c:\path\to\output\$site $((Get-Date).ToString('MM-dd-yyyy_hhmm')).csv" -NotypeInformation -useculture #Disconnect from vCenters Disconnect-VIServer -Force -Confirm:$false -Server $vCenter } #Cleanup old csv after 7 days.$limit = (Get-Date).AddDays(-7)
$path = "c:\path\to\output\" Get-ChildItem -Path$path -Recurse -Force | Where-Object { !$_.PSIsContainer -and$_.CreationTime -lt \$limit } | Remove-Item -Force



Something I did not know until after running this in a large production environment, is that the get-vm call is heavy and not very efficient.  When I ran this script in my lab it took less than 15 seconds to run.  However in a production environment, connecting to data centers all over the globe, it took over 40 minutes to run.

A colleague of mine who had automation experience, exposed me to another cmdlet called get-view, and said it would be much faster to run as it was more efficient gathering the data needed.  So I rewrote my script and now it looks like:

_________________________________________________________________

_________________________________________________________________

The new code took less than a couple of minutes to run in my production environment.  I have a Windows VM deployed that’s running the VMware poweractions fling, and it also runs some scheduled scripts.  This script would be running from that server, so I added an additional function to the script that creates a WIN event entry so it could be tracked from a syslog server.

So the final script can be downloaded here.  *Disclaimer – test this in a lab first as the code will need to be updated to suit your needs.