Okay, so this post is probably for me, and me alone, but that’s okay. If you read the About page then you know a major purpose for this blog is to remind myself of how to do things…this is definitely one of those, because this is Ugly with a capital U, and I bet there are plenty of better ways to do this. But this is what I came up with, I couldn’t find anything better that didn’t involve even more skills that I lack, and it worked. That, my friends, is the key thing…it worked. Maybe it will help one of you too. So what the hell am I talking about? I was tasked with what, at the time, seemed an impossible mission. Cue the music…
Good afternoon, Mr Fisher. In the interests of National Security, the defence council needs a list of all virtual machines created or cloned on the production farm, that includes their creation date. There are several hundred VMs in this farm, and the events log does not go back far enough in time to display that information. Your mission, should you choose to accept it (not that you have a choice,) is to extract that data using any means possible, and provide the list before the end of the day. As always, should you or any of your IM force be caught or killed, the Secretary will disavow any knowledge of your actions. This post will self-destruct in five seconds.
Now many of you probably already know that Virtual Center keeps everything in a SQL database. That is probably very helpful if you happen to know SQL…but I don’t. Oh, and this was not my farm, so I knew even less of what was going on or how it was set up, but it was my task, and the clock was ticking. So, having domain admin rights, and at least some idea of the VMware infrastructure, I logged onto the Windows server running Virtual Center, did a
netstat –a | findstr :1433 [enter]
to see what it was talking to over the MSSQL port to figure out which server was running SQL. Here are the steps I took to get the data out.
- Log on to the SQL server.
- Launch the SQL Server Management Studio and log on to SQL using an account with sufficient rights. (turns out I didn’t have them, but there are always methods around any such access denied errors <cough> SA </cough>)
- Expand databases
- Find the database that contains the Virtual Center data. If you don’t know what that is, look for a database named with VM or VC or something that indicates VMware or the Virtual Center server. If you are not sure you found the right one, expand that database, then expand tables. There will be many, named with VCI and VPX, so if you don’t see these, you picked the wrong database. Go try another database.
- Right-click the database, and select "New Query." That will bring up the query interface in the middle column.
- Paste this query into that, modifying the start date if necessary. select CREATE_TIME, USERNAME, VM_NAME, HOST_NAME, DATACENTER_NAME
from VPX_EVENT
Where EVENT_TYPE = ‘vim.event.VmCreatedEvent’ OR EVENT_TYPE = ‘vim.event.VMClonedEvent’ AND CREATE_TIME > ’2007-06-01′ - Right-click in that column, and select Execute.
- Down below the query, you should now see a list of all VMs with the creation date & time, the username, the VM_NAME (as it was originally set), the HOST_NAME, and the DATACENTER_NAME.
- Right-click in the results, and click "Save Results As…" to save the file as either a CSV or text file.
- Log out before someone decides you are now a SQL DBA.
- Open Excel, import data from text, massage it as necessary, and get it to the boss before time runs out.
Now, this may not have been the most efficient, elegant, or straight-forward way to do this, but cut me some slack…I had no time to do this, couldn’t ask/delegate/beg it from anyone else, and the best I could come up with from Google was enough SQL syntax to put together the above query. Considering that was the first time I ever executed a query against a database, I’m pretty darn happy I didn’t blow up the server, or at the very least, drop a table.
In honour of a mission accomplished, here’s a short walk down memory lane. Little known personal trivia: the later ones with Leonard Nimoy were always my favourites, but it was many years later before I realised that Paris and Mr. Spock were the same actor!
Do you know a better way to have extracted the required data? Have a Mission:Impossible you’d like to share. We’d love to hear from you either way. Leave a comment and share your story.
No related posts.
