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.
|
1 2 3 4 5 6 |
"C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvinfo2xls -d d:\exports\rvtools -f 1-vinfo.xls "C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvhost2xls -d d:\exports\rvtools -f 1-vhost.xls "C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvinfo2xls -d d:\exports\rvtools -f 2-vinfo.xls "C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvhost2xls -d d:\exports\rvtools -f 2-vhost.xls "C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvinfo2xls -d d:\exports\rvtools -f 3-vinfo.xls "C:\Program Files (x86)\Robware\RVTools 3.9.5\rvtools" -u username@vsphere.local -p Password -s vcenter.yourdomain.local -c exportvhost2xls -d d:\exports\rvtools -f 3-vhost.xls |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
$files = "file1.xlsx","file2.xlsx","file3.xlsx" $ColumnsToKeep = 1,2,3,5,7,8,9,10,11,21 $pathtofiles = "d:\exports\rvtools\" $pathtosave = "d:\exports\rvtools\report\" foreach ($file in $files) { $savename = $pathtosave + "complete-" + $file # Create the com object $excel = New-Object -comobject Excel.Application $excel.DisplayAlerts = $False $excel.visible = $False # Open the CSV File $workbook = $excel.Workbooks.Open($pathtofiles+$file) $sheet = $workbook.Sheets.Item(1) # Determine the number of rows in use $maxColumns = $sheet.UsedRange.Columns.Count $ColumnsToRemove = Compare-Object $ColumnsToKeep (1..$maxColumns) | Where-Object{$_.SideIndicator -eq "=>"} | Select-Object -ExpandProperty InputObject 0..($ColumnsToRemove.Count - 1) | %{$ColumnsToRemove[$_] = $ColumnsToRemove[$_] - $_} $ColumnsToRemove | ForEach-Object{ [void]$sheet.Cells.Item(1,$_).EntireColumn.Delete() } # Save the edited file $workbook.SaveAs($savename, 51) # Close excel and release the com object. $workbook.Close($true) } $excel.Quit() [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) Remove-Variable excel |
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?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
#Get all Excel files in the specified location $csvs = Get-ChildItem D:\exports\rvtools\report\* -recurse -Include *.xlsx $y=$csvs.Count $o = New-Object -com Outlook.Application $mail = $o.CreateItem(0) #2 = high importance email header $mail.importance = 2 $mail.subject = “TEST - Report auto mail“ $mail.body = “Here is the requested report `n`nIt should contain ($y) Excel Files“ #for multiple email, use semi-colon ; to separate $mail.To = “mark@noway.com“ #Attach all files in the specified location Write-Host “Attaching the following ($y) Excel file(s).” -ForegroundColor Cyan foreach ($csv in $csvs) { Write-Host ” “$csv.Fullname -ForegroundColor Green $mail.Attachments.Add($csv.FullName) } #Send the e-mail $mail.Send() # $o.Quit() |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
#Report generation #call RVtools from a batch file to get the initial excel exports Write-host "Running RVTools Batch File" -ForegroundColor Green Start-Process D:\Scripts\vmware-report.bat -Wait Write-Host "RVTools Complete" -ForegroundColor Green #call script to delete all columns except the ones we want Write-Host "Processing vHost Spreadsheets" -ForegroundColor Cyan & D:\Scripts\delete-columns-vhost-loop.ps1 #call script to delete all columns except the ones we want Write-Host "Processing VMinfo Spreadsheets" -ForegroundColor Cyan & D:\Scripts\delete-columns-vminfo-loop.ps1 #call script to E-Mail all the above processed Spreadsheets Write-Host "Calling E-Mail Script" -ForegroundColor Yellow & D:\Scripts\email-all-reports.ps1 |
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)
