When asked to produce detailed customer reports, I had some initial struggles with vROPs output format when using multiple views.
Outputting the details report to CSV and messing around with some Visual Basic achieved exactly what I was looking for. All-be-it, I didn’t know VBA, but like all good pieces of code, can be found online and modified to your needs.
What did I want to see from my report?
One spreadsheet neatly broken down into tabs, with bold headings on each sheet containing the following information/metrics:
- Host Overview
- VM Overview
- vDisk Overview
- Cluster Overview
- Datastore Overview
- Snapshot Overview
- Cluster Performance
- Host Performance
- VM Performance
How did I achieve this?
I enabled all the metrics required in the vROPs Policy and created a view for each bullet point listed above.
Note, my first view created was a “Blank Sheet”, which was required for correct formatting.
This was then all combined into a single vROPs Report, which was then run/save as a CSV file.
The VBA Code
Attribute VB_Name = "vReporter" Sub vReporter() Dim hdr As Range, rng As Range, ws As Worksheet, wsn As Worksheet Dim rw As Long, lr As Long, b As Long, blks As Long Set ws = ActiveSheet With ws Set hdr = .Cells(1, 1) lr = .Cells(Rows.Count, 5).End(xlUp).Row rw = 2 blks = Application.CountBlank(.Range(.Cells(rw, 1), .Cells(lr, 1))) + 1 For b = 1 To blks Set rng = .Cells(rw, 1).CurrentRegion Set rng = rng.Offset(-CBool(b = 1), 0) Set wsn = Worksheets.Add(after:=Sheets(Sheets.Count)) With wsn hdr.Copy Destination:=.Cells(1, 1) rng.Copy Destination:=.Cells(2, 1) End With rw = rw + rng.Rows.Count + 1 Set rng = Nothing Set wsn = Nothing If rw > lr Then Exit For Next b End With Set rng = Nothing Set ws = Nothing Worksheets("Sheet1").Rows(1).EntireRow.Delete Sheets("Sheet1").Name = "Hosts Overview" Sheets("Sheet2").Name = "VMs Overview" Sheets("Sheet3").Name = "vDisks Overview" Sheets("Sheet4").Name = "Clusters Overview" Sheets("Sheet5").Name = "Datastores Overview" Sheets("Sheet6").Name = "Snapshots Overview" Sheets("Sheet7").Name = "Cluster Performance" Sheets("Sheet8").Name = "Hosts Performance" Sheets("Sheet9").Name = "VMs Performance" For Each sht In ThisWorkbook.Worksheets sht.Cells.EntireColumn.AutoFit Next sht For Each ws In ThisWorkbook.Sheets ws.Range("A1").EntireRow.Delete Next ws For Each ws In ThisWorkbook.Sheets ws.Range("A1:Z1").AutoFilter Next ws For Each ws In ThisWorkbook.Sheets ws.Range("A1:Z1").Font.Bold = True Next ws End Sub
If you have any questions or would like any assistance with this process, leave a comment below and I’ll get back to you ASAP.