Report Automation with RVtools and Excel

Recently I needed to extract some configuration info from vCenter and email it someone on a semi-regular basis.

They wanted specific information about the hosts and VMs in each vCenter. I figured the easiest way to get the required info was to use Rob de Veij’s excellent RVTools.

To start, I ran RVTools, exported the host tab and the vm tab for 3 vCenters. Not the most efficient way this could be done. So, I made a simple batch file to output the info I wanted.

Now I was left with 6 spreadsheets full of data, most of which I do not need. Again, initially, I deleted the extraneous columns manually. Manual processes are tedious, boring and uninspired… Powershell to the rescue!

Since I had two different sets of output (vms and hosts) I needed two separate scripts to process them (yes I could have combined them, but I really like making things modular) The only difference is in line 2 (columns to keep), so I have only listed one here.

All these scripts do are remove the columns I do not need. A simple loop gets all the files from an array and processes them.

The only thing left is to email the modified Excel files to the person who requested the information. Why not automate that as well? And since we could add or subtract a vCenter, why not have the script e-mail every spreadsheet it finds in the output directory?

With all the pieces in place, the last step is to tie all these disparate scripts together. I created a control script that executes all the other scripts and displays the status as it goes.

And there you have it! All completely automated and no more work anytime I need the report sent.

Something I have considered changing is getting the list of files to process. I could read the directory and put them into the array automatically. Maybe next time…

I’d like to thank all the people who’s code I borrowed (who I promise to credit once I go back though my history and find them)