RVTools<\/a>.<\/p>\nTo 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.<\/p>\n
\"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\r\n\"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\r\n\"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\r\n\"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\r\n\"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\r\n\"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<\/pre>\nNow 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!<\/p>\n
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.<\/p>\n
$files = \"file1.xlsx\",\"file2.xlsx\",\"file3.xlsx\"\r\n$ColumnsToKeep = 1,2,3,5,7,8,9,10,11,21\r\n$pathtofiles = \"d:\\exports\\rvtools\\\"\r\n$pathtosave = \"d:\\exports\\rvtools\\report\\\"\r\n\r\nforeach ($file in $files) {\r\n\r\n$savename = $pathtosave + \"complete-\" + $file\r\n\r\n# Create the com object\r\n$excel = New-Object -comobject Excel.Application\r\n$excel.DisplayAlerts = $False\r\n$excel.visible = $False\r\n\r\n# Open the CSV File\r\n$workbook = $excel.Workbooks.Open($pathtofiles+$file)\r\n$sheet = $workbook.Sheets.Item(1)\r\n\r\n# Determine the number of rows in use\r\n$maxColumns = $sheet.UsedRange.Columns.Count\r\n\r\n$ColumnsToRemove = Compare-Object $ColumnsToKeep (1..$maxColumns) | Where-Object{$_.SideIndicator -eq \"=>\"} | Select-Object -ExpandProperty InputObject\r\n0..($ColumnsToRemove.Count - 1) | %{$ColumnsToRemove[$_] = $ColumnsToRemove[$_] - $_}\r\n$ColumnsToRemove | ForEach-Object{\r\n [void]$sheet.Cells.Item(1,$_).EntireColumn.Delete()\r\n}\r\n\r\n# Save the edited file\r\n$workbook.SaveAs($savename, 51)\r\n\r\n# Close excel and release the com object.\r\n$workbook.Close($true)\r\n}\r\n\r\n$excel.Quit()\r\n[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)\r\nRemove-Variable excel<\/pre>\nAll these scripts do are remove the columns I do not need. A simple loop gets all the files from an array and processes them.<\/p>\n
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?<\/p>\n
#Get all Excel files in the specified location\r\n\r\n$csvs = Get-ChildItem D:\\exports\\rvtools\\report\\* -recurse -Include *.xlsx\r\n $y=$csvs.Count\r\n\r\n\r\n$o = New-Object -com Outlook.Application\r\n\r\n$mail = $o.CreateItem(0)\r\n\r\n#2 = high importance email header\r\n$mail.importance = 2\r\n\r\n$mail.subject = \u201cTEST - Report auto mail\u201c\r\n\r\n$mail.body = \u201cHere is the requested report `n`nIt should contain ($y) Excel Files\u201c\r\n\r\n#for multiple email, use semi-colon ; to separate\r\n$mail.To = \u201cmark@noway.com\u201c\r\n\r\n#Attach all files in the specified location\r\n\r\n Write-Host \u201cAttaching the following ($y) Excel file(s).\u201d -ForegroundColor Cyan\r\nforeach ($csv in $csvs)\r\n {\r\n Write-Host \u201d \u201c$csv.Fullname -ForegroundColor Green\r\n $mail.Attachments.Add($csv.FullName)\r\n }\r\n\r\n#Send the e-mail\r\n\r\n$mail.Send()\r\n\r\n# $o.Quit()\r\n<\/pre>\nWith 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.<\/p>\n
#Report generation\r\n\r\n#call RVtools from a batch file to get the initial excel exports\r\n\r\nWrite-host \"Running RVTools Batch File\" -ForegroundColor Green\r\n\r\nStart-Process D:\\Scripts\\vmware-report.bat -Wait\r\n\r\nWrite-Host \"RVTools Complete\" -ForegroundColor Green\r\n\r\n#call script to delete all columns except the ones we want\r\n\r\nWrite-Host \"Processing vHost Spreadsheets\" -ForegroundColor Cyan\r\n\r\n& D:\\Scripts\\delete-columns-vhost-loop.ps1\r\n\r\n#call script to delete all columns except the ones we want\r\n\r\nWrite-Host \"Processing VMinfo Spreadsheets\" -ForegroundColor Cyan\r\n\r\n& D:\\Scripts\\delete-columns-vminfo-loop.ps1\r\n\r\n#call script to E-Mail all the above processed Spreadsheets\r\n\r\nWrite-Host \"Calling E-Mail Script\" -ForegroundColor Yellow\r\n\r\n& D:\\Scripts\\email-all-reports.ps1<\/pre>\nAnd there you have it! All completely automated and no more work anytime I need the report sent.<\/p>\n
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…<\/p>\n
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)<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8,4],"tags":[],"_links":{"self":[{"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/posts\/231"}],"collection":[{"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=231"}],"version-history":[{"count":8,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/posts\/231\/revisions"}],"predecessor-version":[{"id":277,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=\/wp\/v2\/posts\/231\/revisions\/277"}],"wp:attachment":[{"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=231"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=231"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.indycloudcover.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=231"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}