After posting on twitter last week that I needed something to write a blog post about my good friend Christian Dadswell (Blog|Twitter) suggested I post the queries I wrote for him awhile back to dig him out of hole. Chris has dug me out of many a hole when I have needed advice on vSphere, Chris is a full time vSphere guru and part-time Android developer, which  he is very good at. Go and check out his BBC Browse Cast app for yourself. Not having an android phone myself I haven’t used his app, he refuses to get involved with Apple products which is a shame but if he gets enough encouragement I’m sure he could be persuaded. 

I have posted a query earlier this week that pulled out information from the vCentre database on VMs that had snapshots created on them that are older then 14 days. This was to allow the Sys Admins to find the snapshots before the snapshot got so big they caused issues.

Following on from this we had a bank holiday weekend (a  long weekend) and over that weekend one of the physical hosts in the vSphere stack failed. All the VMs running on the physical host HA’d and restarted on the other available physical hosts. Even though the outage was very short Chris wanted to be able to inform the business which servers would have been unavailable and hence which business services would have been affected by the outage. Unfortunately it was not obvious if the vCentre database stores this historical data (if you know otherwise please let me know in the comments) I was unable to find but it does store the current configuration. 

Chris wanted to know if there was a way to track, after the event what VMs were running on a particular physical host. In order to capture this information I needed to create table in a database that I could use to store the necessary data,  The query to pull out which VM is running on which host is as follows:

SELECT  e.NAME AS 'VirtualMACHINE',
        vm.dns_name,
        vm.[HOST_ID],
        h.dns_name AS 'HostName'


FROM    vpx_vm vm
INNER JOIN VPX_ENTITY e ON vm.id = e.id
INNER JOIN vpx_host h ON vm.HOST_ID = h.id

ORDER BY vm.HOST_ID

I then created a job to run once a day that ran the above query and inserted the results in the table created. I then developed and SSRS report that allowed a physical host and a date to be selected and the VM’s running on the host returned thus the sys admins would have a good idea of what VMs were running at a particular time and solve the above issue of what was running on what and when.

I hope you find this useful, if you have another way of doing this please do let me know in the comments