New open source fling: vRealize Operations Export Tool

Summary

This is an Open Source fling. Find out about VMware Open Source initiative on https://www.vmware.com/opensource.html

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.

Requirements

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

Instructions

Installation on Linux, Mac or other UNIX-like OS

  1. Download the binaries from here: TODO: Link to binaries
  2. Unzip the files:
    mkdir ~/vrops-export
    cd ~/vrops-export
    unzip vrops-export-<version>-bin.zip
    cd vrops-export-<version>/bin
  3. Make the start script runnable
    chmod +x exporttool.sh
  4. Run it!
    ./exporttool.sh -d samples/vmfields.yaml -u admin -p password -H https://my.vrops.host -i

Installation on Windows

  1. Download the binaries from here: TODO: Add link
  2. Unzip the files into a directory of your choice, e.g. c:\vropsexport
  3. Open a command window and cd into the directory you created, e.g.
    cd c:\vropsexport
  4. Cd into the bin directory
    cd bin
  5. Run the tool
    exporttool.bat -d ..\samples\vmfields.yaml -u admin -p password -H https://my.vrops.host

Command syntax

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 management

As 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.

Notes

  • 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 file

The 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>

For example:

$parent:HostSystem.cpu|demandmhz

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 

Known issues

    • 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.
%d bloggers like this: