vRealize Operations Export Tool
The vRealize Operations Export Tool allows users to both extract and export metrics and properties from vRealize Operations. The highly modular design of this tool allows almost any output format to be plugged in. Currently, output formats supported include CSV (comma separated files) and generic SQL (through JDBC). This export tool also supports advanced constructs such as, navigating through multi step relationships between objects as well as aliasing. The design is made to be very flexible and fast. Typical applications of this tool include exporting to data warehouse, reporting tools, and analytics tools.
The vRealize Operations Export Tool is designed to run on a separate machine (virtual or physical) with the following specifications:
- 2 CPU cores
- Memory: >1GB free
- Storage: >5GB free
- Any Linux with Kernel 2.6 or higher
- Windows 7,8,10
- Windows Server 2008, 2012 or 2016
- Java: JRE 1.8 or higher
- vRealize Operations: Tested with vRealize Operations 6.4, 6.5 and 6.6
Installation on Linux, Mac or other UNIX-like OS
- Download the binaries from here: TODO: Link to binaries
Unzip the files:
mkdir ~/vrops-export cd ~/vrops-export unzip vrops-export-<version>-bin.zip cd vrops-export-<version>/bin
Make the start script runnable
chmod +x exporttool.sh
./exporttool.sh -d samples/vmfields.yaml -u admin -p password -H https://my.vrops.host -i
Installation on Windows
- Download the binaries from here: TODO: Add link
- Unzip the files into a directory of your choice, e.g. c:\vropsexport
Open a command window and cd into the directory you created, e.g.
Cd into the bin directory
Run the tool
exporttool.bat -d ..\samples\vmfields.yaml -u admin -p password -H https://my.vrops.host
usage: exporttool [-d <arg>] [-e <arg>] [-F <arg>] [-H <arg>] [-h] [-i] [-l <arg>] [-m <arg>] [-n <arg>] [-o <arg>] [-P <arg>] [-p <arg>] [-q] [-R <arg>] [-s <arg>] [-S] [-t <arg>] [-T <arg>] [--trustpass <arg>] [-u <arg>] [-v] -d,--definition <arg> Path to definition file -e,--end <arg> Time period end (date format in definition file) -F,--list-fields <arg> Print name and keys of all fields to stdout -H,--host <arg> URL to vRealize Operations Host -h,--help Print a short help text -i,--ignore-cert Trust any cert (DEPRECATED!) -l,--lookback <arg> Lookback time -m,--max-rows <arg> Maximum number of rows to fetch from API (default=unlimited) -n,--namequery <arg> Name query -o,--output <arg> Output file -P,--parent <arg> Parent resource (ResourceKind:resourceName) -p,--password <arg> Password -q,--quiet Quiet mode (no progress counter) -R,--resource-kinds <arg> List resource kinds -s,--start <arg> Time period start (date format in definition file) -S,--streaming True streaming processing. Faster but less reliable -t,--threads <arg> Number of parallel processing threads (default=10) -T,--truststore <arg> Truststore filename --trustpass <arg> Truststore password (default=changeit) -u,--username <arg> Username -v,--verbose Print debug and timing information
Certificate and trust managementAs of version 2.1.0, the -i option has been deprecated for security reasons. Instead, the tool will prompt the user when it encounters an untrusted certificate. If the user chooses to trust the certificate, it is stored in a personal truststore and reused next time the tool is executed against that host. By default, the trusted certs are stored in $HOME/.vropsexport/truststore, but the location can be overridden using the -T flag.
- Start and end dates will use the date format specified in the definition file. Since dates tend to contain spaces and special characters, you probably want to put dates within double quotes (").
- If you're unsure of what the metric names are, use the -F option to print the metric names and keys for a specific resource type, e.g. -F VirtualMachine
- The -l (lookback) parameter is an alternative to the start and end dates. It sets the end date to the current time and goes back as far as you specify. You specify it as a number and a unit, e.g. 24h for 24 hours back. Valid unit are d=days, h=hours, m=minutes, s=seconds.
- The -P flag restricts the export to objects sharing a specified parent. Parents must be specified as resource kind and resource name, for example HostSystem:esxi-01 if you want to export only VMs on the host named "esxi-01".
Definition fileThe details on what fields to export and how to treat them is expressed in the definition file. This file follows the YAML format. Here is an example of a definition file:
resourceType: VirtualMachine # The resource type we're exporting rollupType: AVG # Rollup type: AVG, MAX, MIN, SUM, LATEST, COUNT rollupMinutes: 5 # Time scale granularity in minutes dateFormat: yyyy-MM-dd HH:mm:ss # Date format. See http://tinyurl.com/pscdf9g fields: # A list of fields # CPU fields - alias: cpuDemand # Name of the field in the output metric: cpu|demandPct # Reference to a metric field in vR Ops - alias: cpuReady metric: cpu|readyPct - alias: cpuCostop metric: cpu|costopPct # Memory fields - alias: memDemand metric: mem|object.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 # Reference to a metric in the parent. # Guest OS - alias: guestOS prop: config|guestFullName # Reference to a property (as opposed to metric) # Host CPU type - alias: hostCPUType prop: $parent:HostSystem.cpu|cpuModel # Reference to a metric in a parent
Special properties in the definition file
There are a number of special properties that are always available for use in a properties file for getting things like parent resources and resource names.
- $resdId - Internal ID of the current resource.
- $resName - Resource name of the current resource
- $parent - Reference to a parent resource.
Referencing parent resources
The syntax for referencing parent resources is as follows:
$parent:<Parent Kind>.<metric or property>
Notice that you can stack several $parent keywords. For example, this gets the total CPU demand of a parent cluster based on a VM: ``` $parent:HostSystem.$parent:ClusterComputeResource.cpu|demandmhz ```
Exporting to SQL
As of version 2.0, there is an option to export the data to a SQL database rather than a CSV file. For this reason, we have added a few new settings to the configuration file.
Configuration file settings
These are the new configuration file settings:
- outputFormat - Must be set to "sql" for SQL export
- connectionString - A JDBC connection string. The format depends on the database vendor.
- username - An optional database username
- password - An optional database password
- databaseType - Type of database. Currently supports postgres, mysql, mssql and oracle. Additional databases can be supported by using the driver option. Note that "driver" and "databaseType" are mutually exclusive.
- driver - The class name of the JDBC driver. Use this setting, along with the JDBC_JAR environment variable to export to a database type that's not included in the choices for "databaseType".
- sql - The SQL statement to use for inserting data into the database. See below for a full description.
Specifying the SQL statement
The data is inserted into the database using a user-specified SQL statement (typically an INSERT statement). Variable substitution is done using the metric or property alias preceded by a colon. For example:
sql = "INSERT INTO metrics(timestamp, resname, resId, cpuDemand, memDemand, hostCpuDemand) VALUES (:timestamp, :resName, :resId, :cpuDemand, :memDemand, :hostCPUDemand) ON CONFLICT DO NOTHING" . . . # Resource ID - alias: resId prop: $resId # CPU fields - alias: cpuDemand metric: cpu|demandPct # Memory fields - alias: memDemand metric: mem|guest_demand # Host CPU - alias: hostCPUDemand metric: $parent:HostSystem.cpu|demandmhz
Notice how the names in the VALUES clause map to the aliases in the list of fields. The fields "timestamp" and "resName" are always available by default. If you need the resource id, you can define that as a property field mapped to the internal property $resId as shown above. You typically want to use some kind of "upsert" semantics if your database supports it. In the example above, we're using the "ON CONFLICT DO NOTHING" option available in PostgreSQL.
Configuring JDBC drivers
The pre-built binaries come with JDBC drivers for PostgreSQL and MySQL. Due to licensing issues, you will have to download the JDBC drivers for any other database separately. When using a non-bundled database, you need to specify the location of the JDBC jar file in the JDBC_JAR environment variable. For example, to use a MS SQL database driver you have previously downloaded, simply type:
$ export JDBC_JAR=~/tmp/sqljdbc42.jar $ ./exporttool.sh -u demouser -p demopassword -H https://10.140.46.21 -i -d ../samples/sqlexport.yaml
- Very long time ranges in combination with small interval sizes can cause the server to prematurely close the connection, resulting in NoHttpResponseExceptions to be thrown. If this happens, consider shortening the time range. This seems to happen mostly when exporting over a slow connection.
- Only one parent resource type is supported. This will be fixed in a future release.