OP5 Monitor - How to extract columns from CSV export of a report with CLI tools.

The purpose of exporting a report from OP5 Monitor to the widely used data exchange format CSV is to enable the use of report data in other applications. CSV files can also be opened directly with spreadsheet software, such as Excel.

The exported CSV file will contain a lot of columns with detailed data that other applications using the CSV file as input can make use of.

These examples show how to extract specified columns from an availability report exported to CSV, using command-line tools to support automation of the task. The columns used in the examples contain the same data as displayed in the report UI in the browser. The available column names are in the first row in the CSV file.

We tested the examples on CentOS 7.6.

App::CSV

App::CSV is a Perl library, also available as a CLI application for most Linux systems. It can be installed from EPEL repositories in CentOS 7 with:

yum install csv

Hosts

Extract the named columns from input file "avail_hosts.csv" and output results to "avail_hosts_custom.csv":

$ csv --allow_whitespace 1 --always_quote 1 \
 -f HOST_NAME \
 -f PERCENT_KNOWN_TIME_UP \
 -f PERCENT_KNOWN_TIME_DOWN \
 -f PERCENT_KNOWN_TIME_UNREACHABLE \
 -f PERCENT_TOTAL_TIME_UNDETERMINED \
 -i avail_hosts.csv -o avail_hosts_custom.csv

$ cat avail_hosts_custom.csv
"testhost","7.002%","0%","0%","92.998%"
"monitor","100%","0%","0%","0%"

Services

Extract the named columns from input file "avail_services.csv" and output results to "avail_services_custom.csv":

$ csv --allow_whitespace 1 --always_quote 1 \
 -f HOST_NAME \
 -f SERVICE_DESCRIPTION \
 -f PERCENT_KNOWN_TIME_OK \
 -f PERCENT_KNOWN_TIME_WARNING \
 -f PERCENT_KNOWN_TIME_CRITICAL \
 -f PERCENT_KNOWN_TIME_UNKNOWN \
 -f PERCENT_TOTAL_TIME_UNDETERMINED \
 -i avail_services.csv -o avail_services_custom.csv

$ head avail_services_custom.csv
"HOST_NAME","SERVICE_DESCRIPTION","PERCENT_KNOWN_TIME_OK","PERCENT_KNOWN_TIME_WARNING","PERCENT_KNOWN_TIME_CRITICAL","PERCENT_KNOWN_TIME_UNKNOWN","PERCENT_TOTAL_TIME_UNDETERMINED"
"monitor","CPU - IOWait","100%","0%","0%","0%","0%"
"monitor","CPU - Idle","100%","0%","0%","0%","0%"
"monitor","CPU - System","100%","0%","0%","0%","0%"
"monitor","CPU - Total","100%","0%","0%","0%","0%"
"monitor","CPU - merlind","100%","0%","0%","0%","0%"
"monitor","CPU - naemon","100%","0%","0%","0%","0%"
"monitor","Disk IO read","100%","0%","0%","0%","0%"
"monitor","Disk IO written","100%","0%","0%","0%","0%"
"monitor","Disk usage /","100%","0%","0%","0%","0%"

csvkit

Another similar tool to csv above is csvkit, it should run all environments with Python installed (Windows, Linux, MacOS etc).

This example creates a Python virtual environment, installs csvkit in it and uses the tool csvcut to select host report columns for the same result as with csv above:

$ python3 -m venv venv-csvkit
$ venv-csvkit/bin/pip install csvkit
$ venv-csvkit/bin/csvcut --skipinitialspace \
  --columns HOST_NAME,PERCENT_KNOWN_TIME_UP,PERCENT_KNOWN_TIME_DOWN,PERCENT_KNOWN_TIME_UNREACHABLE,PERCENT_TOTAL_TIME_UNDETERMINED \
  avail_hosts.csv > avail_hosts_custom.csv

awk

We can also accomplish the task with general purpose text processing tools such as awk that are available on most platforms.

In these examples, columns are selected from the input file as column numbers.

Hosts

$ awk -F, 'BEGIN { OFS = "," } { print $1, $10, $19, $28, $34 }' \
  avail_hosts.csv > avail_hosts_custom.csv

Services

$ awk -F, 'BEGIN { OFS = "," } { print $1, $2, $11, $20, $38, $29, $44 }' \
  avail_services.csv > avail_services_custom.csv