Wednesday, 15 June 2011

vCentre Useful Queries: Part II

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

Tuesday, 14 June 2011

vCentre Useful Queries: Part I

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. 

Chris needed me to get some information out of the vSphere vCentre database. Firstly he wanted a quick and dirty way of getting information from vCentre database  on VM snapshots. Snapshots are great tool that can allow you take a snapshot or ‘point in time’ view of the VM. I find it easiest to imagine vSphere snapshots to be similar to database snapshots in SQL Server. We don’t go into the specific  details of how these work here but if you create a snapshot on a VM, as the VM changes the changes are written to the snapshot. This enables you to restore the VM to the state it was when the snapshot was taken. This can be useful when applying OS patches and the like.

You can get problems  when you forget that the snapshot exists and it can fill up the space on disk. If this happens it can cause your VM to stop working properly. Chris wanted to find all VMs that had a snapshot over 14 days old. The theory behind it was if a snapshot was older than 14 days it was unlikely to ever be used to restore the VM and he could then get onto the person who created it and hopefully delete it before it causes problems.

This is the query that was used

select  e.NAME,
s.SNAPSHOT_NAME,
s.CREATE_TIME

from VPX_SNAPSHOT s
INNER JOIN VPX_ENTITY e ON s.VM_ID = e.ID
WHERE s.CREATE_TIME < GETDATE()-14
ORDER BY s.CREATE_TIME asc

I hope you find it useful. I ended up plugging this query into a SSRS report to allow all sys admins find old snapshots.


There will be a second post in this series looking at monitoring which VMs are running on what host and why that can be useful

Friday, 10 June 2011

Planes Trains and Automobiles

I was going to write a short post showing some useful queries that can help you manage your vCentre environment, by querying the vCentre database you can get some really useful information about your virtual setup. That was going to occupy my time as I travel back to South Wales after spending the week in London. That though will have to wait. (Don’t worry that will follow shortly) I have had a very busy week but now have lots of things to write posts about. The last few months for me have been extremely busy and I haven’t had a lot of time to post much that will hopefully change now.

Anyway as usual I have digressed. The train home is not much short of a Joke, you are not allowed by law to overfill a car, if a car has five seats you can carry a ,maximum of five people, health and safety and it’s the law. Ryanair once joked about stand-up seats but that has never been taken seriously and there is no way that they would let you overfill and aircraft.. “I’m sorry Mr Ellis you flight has been over booked and you have been chosen as the victim, you’ll have to get the next one!” Buses have a maximum capacity too. Trains though it seems do not have to obey the same rules, there is about 100 seats in this carriage, all full (I’m lucky I have one). yet somehow the train people seem to think that it can fit 250 people with the rest standing. I know trains are one the safest modes of transport but if there were an accident on this train, a lot of people would have very little to cling onto.

I don’t have a solution, it is Friday, people want to get home for the weekend, hey I’m one of those but I’m just saying, that’s all. Maybe an extra train or even and extra carriage would be useful.