This document explains in detail how to create a VMware Capacity and Performance Reporting Dashboard using Athene’s enterprise strength automatic reporting functionality and Excel. We make the assumption that VMware data is being successfully captured and stored into Athene’s central capacity database called the PDB and that Microsoft Excel has been installed and is accessible by the Athene reporting applications.
The production of an Athene Capacity Management Dashboard makes extensive use of Athene’s Application View functionality which automatically groups IT resources into logical business, service and infrastructure entities for reporting purposes. Once Application Views have been defined Athene can automatically produce an almost limitless range of reports and views of data.
The Dashboard itself can be tailored to individual site needs and provides a RAG status of capacity and the ability to drill down to graphs and analysis on the behaviour of individual resources.
This document is used in enterprise organizations where capacity management is one of the critical tools for managing virtual infrastructure.
Both VCPs and Capacity Management professionals are audiences for this proven practice.
- Create Site Application Views
- Create User Defined Metrics
- Create Updateable Series using both Metron metrics captured via VMware VirtualCenter and User Defined Metrics
- Schedule and Dispatch Updateable Series to Excel
- Use Excel formatting to create final dashboard view
- Saving the Dashboard template
In this proven practice we will be reporting the following information:-
- Site Name
- Number of ESX Hosts per site
- Average number of Guest VMs per site
- Peak or Maximum CPU Utilization % per Site
- Peak or Maximum Memory Utilization % per site
- Find out more about the Athene product
- Find out more about the Metron company
Metron is a privately owned limited company which was founded in 1986. Metron-Athene Inc is a wholly owned subsidiary of Metron technology Ltd. The company is Europe's foremost Capacity Planning and Systems Performance Management specialist. Metron's flagship product, Athene, provides fully integrated ITIL-compliant capacity management, automatic performance analysis and reporting for UNIX, Linux, Windows and Mainframe Servers.
Find out more about Metron
You use this proven practice at your discretion. VMware and the author do not guarantee any results from the use of this proven practice. This proven practice is provided on an as-is basis and is for demonstration purposes only.
Creating a VMware Capacity Management Dashboard with Metron Athene
1. Creating Site Application Views
The first step in this process is to create your sites (location or business group) using Athene Application Views and once the sites have been created you can then appropriately assign ESX hosts to their chosen physical site.
In this demonstration we have data from 15 ESX Hosts, name vh-gbesx001 through to vh-gbesx015. We will create two sites called Fred and Wilma.
Fred will host the ESX vh-gbesx001 – 009 servers and Wilma vh-gbesx010-015.
1.1 Creating the Application View for sites Fred & Wilma
1. Open the Reporting Configuration Manager application.
2. Once open, select File -> Open PDB and choose your Athene PDB.
3. Then select Options -> Analysis Period and make sure to select ‘No Start Date’ as the Start Option and ‘No End Date’ as the End Option, then press OK.
4. In the left hand pane, right-click the word ‘PDB’ and the option ‘New Application View’ appears, select this option.
5. In the Application View name box, enter a suitable name. In this example we have called the view ‘VMware Host Groups’.
6. Once created you will see your new view appear in the tree below the Default View provided as shown in Figure 1.
Figure 1 – New Application View (VMware Host Groups)
7. Right-click the newly created view and select the option ‘New Application’.
8. Enter in the name of your first site, i.e. Fred and then repeat the process for any subsequent sites.
9. When your sites have been created they will appear in the right-hand pane as shown in Figure 2.
Figure 2 – Sites Fred & Wilma created
1.2 Assign ESX Hosts to Sites
The sites have now been created within the Application View and the next stage is to assign the ESX hosts to the appropriate site.
1. Expand the tree under your Application View in the left-hand pane.
2. Then select the category VMware Host, in the right-hand pane you will see three tabs, Instances, Applications, Metrics. The Instances tab will be highlighted and will display all ESX hosts defined within the PDB.
3. Select the Applications tab; you will see your sites displayed here.
4. Right-click the site name and select the option ‘Define Application Instance’.
5. In the VMware Host box, enter your criteria for assigning hosts to this site. Wildcards can be used here as shown in Figure 3, as we are assigning all hosts starting vh-gbesx00 to site Fred.
Figure 3 – Assign ESX hosts to site Fred
6. Repeat the process for your other sites. In this example we have assigned all vh-gbesx01* hosts to Wilma.
7. In the Applications tab, we will need to hide the Remainder. Right-click the Remainder and choose Hide.
1.3 Assign Guest VM’s to Sites
Like assigning ESX hosts to sites we also need to assign Guest VM’s to their sites and is achieved the same way as with adding hosts to sites. It is therefore necessary to know what Guests are hosted on what ESX server.
The end result of this is to give an average number of VM’s across sites and therefore does not require or take into account VMotion.
1. In Reporting Configuration Manager highlight the VMware Virtual Machines category in the left-hand pane.
2. Select the Applications tab in the right-hand pane.
3. Right-click the site name and select ‘Define Application Instance’
4. Enter the criteria in the VMware VM box, remembering wildcards can be used here.
5. Repeat for all sites until all Guest VM’s have been allocated to a site.
6. In the Applications tab, we will need to hide the Remainder. Right-click the Remainder and choose Hide.
NB: Keep the Reporting Configuration Manager open as this will be needed for the next section.
2. Create User Defined Metrics
The VMware Dashboard displays information about the number of ESX Hosts per site and the Average number of Guest VMs per site. This information is provided within Athene by using a User Defined Metric.
We create two User Defined count metrics, one for the ESX hosts and one for the Guest VMs.
1. In Reporting Configuration Manager, highlight VMware Host in the left-hand pane.
2. In the right-hand pane, select the Metrics tab.
3. Right-click an area of whitespace in the metrics tab and select New Metric.
4. In the New Metric window call the new metric Host Count, enter 1 for the expression and select Count as the Metric Type as shown in Figure 4.
Figure 4 – Host Count User Defined Metric
5. Highlight the VMware Virtual Machine category in the left-hand pane and choose the Metrics tab.
6. Right-click an area of whitespace in the metrics tab and select the New Metric option. Name the metric Guest Count and then enter the same parameters as the Host Count metric.
7. Finally select File -> Commit Changes to save all of the changes made within the Reporting Configuration Manager.
8. Exit Reporting Configuration Manager.
3. Create Updateable Series
The next stage is to create the template to extract the relevant data from the PDB and it is within this stage that we will use both the Application View and User Defined metrics created in the previous sections.
1. Open Define a Report and select File -> New Series
2. Select your PDB and set your Analysis Period
3. In the Metrics window, select VMware Host and choose the metrics Total VM CPU Time Utilization % and Max Memory usage of total available memory %
4. In the Select VMware Host Instances window, change the Application View from Default View to VMware Host Groups using the dropdown box and you will see your sites appear.
5. Select all instance (sites) as shown in Figure 5
Figure 5 – Select sites from Application View
6. In the Aggregation section, right click Instance Aggregation and select Edit.
7. Check the box ‘Aggregate VMware Host instances together’ and check that the aggregation option ‘Each Application View’ is selected.
8. Click next until you get to Time Summarization and then enable Time Summarization by checking the box.
9. Select the option ‘Peak or Maximum’ and click next
10. In the Time Aggregation options, select Equal Time Periods and set to 1 Day.
11. Now click on the Finish button, an Explorer series screen is displayed as shown in Figure 6.
Figure 6 – Updateable Series
The next stage is to add to this series the User Defined metrics for both Host Count and Guest Count.
1. Right-click the whitespace and select Add Selection
2. In the Wizard, select the PDB then click next (Analysis Period is already selected)
3. Highlight VMware Host in the Metrics screen and then select the User Defined tab on the right-hand side.
4. Select the Host Count metric, click next.
5. Select the VMware Host Groups view and select the sites, then click next.
6. In Aggregation, Instance Aggregation should be set.
7. In Time Summarization, select Peak or Maximum and Equal Time Periods to 1 Day.
8. Click Finish.
9. Repeat the above and select the Guest Count User Defined metric which is located under the VMware Virtual Machine – User Defined tab.
This will now leave you with three selections within your Updateable Series as shown in Figure 7.
Figure 7 – Selections within Updateable Series
Selection 1 – CPU & Memory Peak Utilization for ESX Hosts
Selection 2 – Host Count
Selection 3 - Guest VM Count
10. Save the Updateable Series.
4. Generate Series and Dispatch to Excel
The building blocks are now in place to generate the report and export the results into an Excel spreadsheet. This section explains in detail how to set up Schedule Editor to generate and dispatch the report.
1. Open Microsoft Excel, name the blank sheet Metric Values then choose File -> Save As and save the blank sheet into the Automatic Reports\Spreadsheets directory, with an appropriate name.
2. Close Excel.
3. Open the Schedule Editor application
4. Select File -> New, a new schedule is opened and a General tab is displayed.
5. If required enter a description for the Schedule in the box provided.
6. If you wish to run the report periodically, select the Recurrence tab and enter your chosen date/times. Please make sure the checkbox ‘Schedule is Active’ is checked.
7. Select the Reports tab, right-click the Spreadsheets icon and choose Add
8. Choose your spreadsheet you created in step1, and uncheck the box ‘Review before Dispatch’, press OK
9. Expand the tree under the Spreadsheets icon and under your spreadsheet until you see Updateable Series.
10. Right-click Updateable Series and choose Add. Then select your Updateable Series.
11. In the Properties window, under General tab, change the name of the sheet to Metric Values.
12. Click on the Analysis Period tab, by default the period is set to Back 1 Day. Change accordingly remembering that it is back from the Production Date either manually specified or when the schedule runs automatically.
13. Press OK when finished.
14. Right-click the Dispatch icon in the tree and choose Add -> File Save and select a location for hosting the generated spreadsheet.
15. Save the Schedule, choose File ->Save.
16. Manually generate the report by selecting File -> Run Schedule
17. Enter the Production Date in as specified based on your Analysis Period chosen and click run. Provided the Analysis Period and Production Date are correct you will see output as shown in Figure 8.
Analysis Period – Back 1 Day
Production Date – 31/05/2007
Period extracted from PDB is 30/05/2007.
Figure 9 – Manual generation of report output
18. Verify in the log that there are no errors and that the dispatch has completed successfully.
5. Excel Report Formatting
The report is has now been generated into an Excel spreadsheet. This section shows you how to format the generated output into a presentable Dashboard view.
1. Open the generated spreadsheet, in Sheet 1 you will see the output for the Metron and User Defined metrics chosen within the Updateable Series as shown in Figure 10.
Figure 10 – Generated report in Excel format
2. Create a second sheet within the spreadsheet and name it Dashboard
3. In the Dashboard sheet, create a template with Title and Metric Headings in a style which is acceptable for your business. See Figure 11 for an example.
Figure 11 – VMware Dashboard Template
4. We can now start to populate the Dashboard with values both text and metric.
5. In the cells directly below ‘Site Name’, use the MID formula (checking the cell reference) to strip out the site name from the first metric heading in Metric Values.
=mid(‘Metric Values’!B1,32,20) for Fred
=mid(‘Metric Values’!E1,32,20) for Wilma
6. In the cells directly below ‘No of ESX PER SITE’ for the first site (i.e. Fred) enter the equals sign ‘=’, then click on the Metric Values sheet and select the value for ‘Host Count (Site name) ’, repeat for all sites.
7. In the cells directly below ‘Ave No of VM’s PER ESX PER SITE’ for the first site use the ROUND formula (checking the cell reference) to take the number of VM’s in the site and divide by the number of ESX hosts.
=ROUND('Metric Values'!L2/Dashboard!B7,0) for Fred.
=ROUND('Metric Values'!P2/Dashboard!B8,0) for Wilma
8. In the cells directly below the ‘DATE’ heading, take one of the date/time cell values.
9. In the cells directly below ‘CPU Util %’ and ‘Memory Util %’ for each site, take these values from the Metric Values sheet.
10. Using format cells change the cell values to 2 decimal places.
11. This is optional, but if you wish to turn off Gridlines, you can under Tools, Options.
12. Your VMware Dashboard is now complete and should look similar to Figure 12.
Figure 12 – Final VMware Reporting Dashboard
6. Saving the Dashboard Template
Once you are happy with the style and content of your dashboard, you MUST save the spreadsheet as the original spreadsheet template to be used by Automatic Reporting. If this is not saved as the template you will lose the dashboard the next time the report is generated and dispatched.
Therefore save your final dashboard spreadsheet into the Automatic Reports/Spreadsheets directory overwriting the blank one which was originally created during this process.
Every time a new report is generated, whether it is Daily, Weekly or Monthly the dashboard template will be used and the cells populated accordingly using the formula in place. The report will be dispatched to the location specified in Schedule Editor or you may even wish to email the report to yourself, colleagues or management.