Although vRealize Operations provides a rich toolset of data analysis and visualization tools, it is sometimes desirable to export data to an external system or tool for further analysis. This simplest way of doing this is through reports. This is a simple and graphical way of building a data export and offers scheduling and forwarding through emails. The standard format for human reading is PDF, but the CSV-format is also available for importing into e.g. spreadsheets and data analysis software.
Report-based exports are great in most cases, but there are situations when the data we want to export is simply too complex or we want to the data export to happen as part of a script or workflow. To support these use cases, we built a simple open source tool for command-line based exporting. We also added some more advanced features, such as navigating parent-child relationships.
The vrops-export Tool
Before we start, we would like to point out that this is an unsupported open source tool and not an official VMware product. Although we have tested it internally, there is no guarantee it will work in your environment. Use with caution and report any issues you have here.
The Basic Idea
The motivation is simple: We needed a tool we could run as part of scripts and that would give us a bit more flexibility than just flat reports. So we wrote a program that can take a simple definition file in the YAML-file that defines the export. This file contains the names of the fields, metric rollup parameters and field aliases. This file is then fed into the export program, which uses the standard vRealizer Operations REST API to pull the data. You can send the resulting output to the terminal or a file specified on the command line. Currently, the tool only supports the CSV output format, but we are planning to support additional formats in the future.
Getting the Tool
The vrops-export tool is open source and available here in its entirety. Follow the instructions found here to install the tool. If you don’t intend to modify the code, you should download the binary version of the code.
A Quick Tour
If you are on a Linux or Mac operating system, you start the tool using the exporttool.sh script. On a Windows machine, you use the exporttool.bat file. The examples below were performed on a Mac, so if you are using Windows, you need to change the commands to exporttool.bat.
The -h option lists all the command options, which is useful if you ever get stuck.
The tool needs to connect to the vRealize Operations API, so we need to provide it with some basic connection parameters:
-H <host url> : Host URL e.g. https://myhost.mycompany.com -u <user> : User name, e.g. admin -p <password> : Password -i : Ignore invalid certs. Useful if your server uses self-signed certs.
The definition file determines the fields to be exported, along with rollups and data types. We will go into the details of the definition files in a minute. For now, we can use the one in the “samples” directory of the distribution. You specify the definition file with the -d option.
By default, the tool sends output to standard out, i.e. to your terminal window. You can, of course, use the redirection operator (>) or the pipe operator (|) to send the output to a file or another program. However, if your want the output to be written to a file, you can use the -o option.
Putting it All Together
Now we know the basic options. Let’s try it out using the sample definition file. This will extract some basic virtual machine metrics, along with some metrics from the hosts they are running on. Here is a command that will do just that:
./exporttool.sh -H https://vrops-01.pontus.lab -u admin -p topsecret -d ../samples/vmfields.yaml -o demo.csv -i
Depending on the size of your environment, the export will take anywhere between a few seconds to a few minutes. Once it’s done, you may either look at the resulting CSV file in a text editor or load it into Excel (or similar spreadsheet tool). In our lab, it looks something like this:
More About the Definition File
Let’s drill into the definition file a little deeper. Here is the sample file that comes with the distribution:
resourceType: VirtualMachine rollupType: AVG rollupMinutes: 5 dateFormat: "yyyy-MM-dd HH:mm:ss" fields: # CPU fields - alias: cpuDemand metric: cpu|demandPct - alias: cpuReady metric: cpu|readyPct - alias: cpuCostop metric: cpu|costopPct # Memory fields - alias: memDemand metric: mem|guest_demand - alias: memSwapOut metric: mem|swapoutRate_average - alias: memSwapIn metric: mem|swapinRate_average # Storage fields - alias: storageDemandKbps metric: storage|demandKBps # Network fields - alias: netBytesRx metric: net|bytesRx_average - alias: netBytesTx metric: net|bytesTx_average # Host CPU - alias: hostCPUDemand metric: $parent:HostSystem.cpu|demandmhz # Guest OS - alias: guestOS prop: config|guestFullName # Host CPU type - alias: hostCPUType prop: $parent:HostSystem.cpu|cpuModel
The definition file is written in the YAML format. For more information about the format itself, please refer to one of the many tutorials found online, for example this one. Let’s break down the first section of the file:
- resourceType – The type of vRealize Operations resource to export data for, e.g. VirtualMachine. For a full list of standard resource types, please see below.
- rollupType – The type of aggregation to use. Valid choices are AVG (average), MAX (maximum) and MIN (minimum).
- rollupMinutes – The granularity of the samples. The collection frequency is typically 5 minutes, so it’s rarely useful to go lower than that.
- dateFormat – The date format to use. This determines the output format as well as the format of any date parameters. The date format should be specified using the Java standard. Read more about it here.
Next follows a list of fields. Each field corresponds to a column in the output. Each field is either a metric or a property. Metrics are highly variable values, such as CPU utilization and memory utilization whereas properties are more static things, like make and model of the CPU. You can mix and match properties and metrics freely.
Each field definition looks like this:
- alias: theAlias metric: theMetricName
- alias: theAlias prop: thePropName
- alias – Defines the column name in the output
- metric – The internal metric name in vRealize Operations (see below for information)
- prop – The internal property name in vRealize Operations (see below for information)
It’s sometimes necessary to include metrics from a parent object. For example, you may want to include the CPU on the host together with the metrics from a virtual machine. You can specify references to the parent resource using the $parent keyword. Along with the keyword, you will have to specify the resource type of the parent you are referring to. The format is as follows:
For example, if the resource type you are exporting data for is a virtual machine and you want to make a reference to the parent host, you would use the following metric specifier:
Note: In the current version, you are only allowed to refer to a single parent type within the same definition file.
Other Useful Features
Listing Resource Types
The resource types names used by the tool are the internal names used by vRealize Operations and differ from the ones you would see in the user interface. To list the resource types for a specific adapter type you can use the -R switch to the exporttool command. You need to specify the adapter type. For the built-in vSphere adapter, you would specify VMWARE. For example, to list all the resource types from the vSphere adapter, you would type the following:
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -R VMWARE -i
Listing Metric Names
Metric names are also different from what you are used to seeing in the user interface since we use the internal versions of the names. To list available metric and property names you may use the -F option. You specify the adapter type and resource type separated by a colon, e.g. VMWARE:HostSystem. If you omit the adapter type, VMWARE will be used by default. To list all metrics and properties on a host system, you would type the following:
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -F HostSystem -i
Specifying Time Range Using the Lookback Option
By default, the tool exports 24 hours worth of data going back from the point in time the export was started. You can change this in two ways. The easiest is using the lookback (-l) option. This lets you specify how far back in time to go from when the export was started. Time can be expressed in different units as follows:
- d – Days
- h – Hours
- m – Minutes
- s – Seconds
For example, if we would like to export data going back three days, we would type the following:
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -d ../samples/vmfields.yaml -l 3d -o 3days.csv -i
Specifying Time Range Using Start and End Timestamps
Sometimes you want to specify an arbitrary time range instead of going back from the moment the export was started. In this case, you can specify the start and end dates using the -s and -e options. In this case, you specify the exact dates and times, rather than a lookback period. The date and time format used will be the one you specified in the definition file. If the date format is “yyyy-MM-dd HH:mm:ss” as specified in the sample definition file, you can do an export from April 20 to April 22 by typing the following. Notice that you will have to put quotes around the dates since they contain spaces.
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -d ../samples/vmfields.yaml -s "2017-04-20 00:00:00" -e "2017-04-22 00:00:00" -o export.csv -i
Exporting Only a Specified Resource
You can select a single resource to export data for using the -n option. For example, if you want to export data only for the “vrops-01” virtual machine, you would type the following:
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -n vrops-01 -d ../samples/vmfields.yaml -o demo.csv -i
Exporting Only Children of a Specified Resource
Sometimes you want to restrict the set of resources you’re exporting data for to e.g. only the virtual machines belonging to a certain host. You can used the -P option to specify the parent. To export metrics for virtual machines on the host esxi-04, you would type the following:
./exporttool.sh -H https://<your host> -u <your user> -p <your password> -P HostSystem:esxi-04 -d ../samples/vmfields.yaml -o demo.csv -i
- The vrops-export project on Github: https://github.com/prydin/vrops-export
- Installation instructions: https://github.com/prydin/vrops-export/blob/master/README.md
- Download binary distribution of the tool: https://www.dropbox.com/s/xg66qvr2n6qwqyj/vrops-export-1.0-SNAPSHOT-bin.zip?dl=0
- Report issues with the tool: https://github.com/prydin/vrops-export/issues