Transformations

One of the power features of ZbxWizz is the fact that it allows usage of JavaScript for various tasks: perform transformations on columns, create dynamic request templates or write entire scripts to address complex scenarios. In this chapter I will cover the transformations part.

In a typical spreadsheet editor we use expressions inside the cells to perform calculations and if we want to perform the same calculation over a range of cells one must copy it over and over again. In ZbxWizz the transformations are always applied on the column level, on the visible set of rows and they are ireversible. So make sure that when perfoming a transformation on a column you don't need that data anymore.

A transformation consists of a JavaScript expression which is applied iteratively on each cell of the column in the current set of visible rows.

Inside the expression, on top of the JavaScript standard functions and objects the following properties, objects and functions are available.

Properties

$columnNumber

Returns the value of the cell at columnNumber. Column numbers start at 0.

_columnName

Returns the value of the cell at columnName as it is visible in the table header

self

Return the current value of the cell in the column being transformed

data[csv]

Returns an object with contents of the current rows identified by fields name, of the initialy imported data (either from file or from API)

data[labelName]

When pulling data from Zabbix API, one must provide a labelName under which the pulled data is stored and can be later on retrieved using this variable

lastResponse

Return the last response of an API call to Zabbix for that specific row. It is populatate when pulling or pushing data to and from Zabbix.

One possible use case is you create a set of new resources, you can retrieve the resourceId from this variable. For hosts, you can use this expression: lastResponse.result.hostids[0]

ws[sheetName]

It returns the worksheet object (see bellow) of sheetName.

Functions

json

Wrapper function for the JavaScript JSON.stringify() method to create a JSON string from a given object.

obj

Wrapper function for the JavaScript JSON.parse() method to parse a JSON string and convert it into a JavaScript object

array2obj(array,keyProp, valProp)

Helper function to convert an array of objects into an object having the following parameters

  • array: array of objects to be converted
  • keyProp: key of array member object of which value will be set as key in the returned object
  • valPro:key of array member object of which value will be set as value in the returned object

Example

array2obj([{tag:"ENV",value:"PROD"},{tag:"LOCATION",value:"London"}], "tag", "value")

will output

{ENV:"PROD",LOCATION:"London"}

obj2array(obj, keyLbl, valLbl)

Helper function to convert objects into array of objects having the following parameters:

  • obj: object to be converted
  • keyLbl: target object property name which will be set to current key name being iterated
  • valLbl: target object property name which will be set to current property value being iterated

Example

obj2array( {ENV:"PROD",LOCATION:"London"}, "tag", "value")

will output

[{tag:"ENV",value:"PROD"},{tag:"LOCATION",value:"London"}]

Objects

Worksheet

Properties

rows

It returns an array with all the row object

Methods

lookup(searchValue,searchColumn,returnValueColumn=null)

It searches in the target worksheet on column searchColumn for the value and it when found, it returns the value in the column identified by returnValueColumn.

Both searchColumn and returnValueColumn can be either numerical (indicating the column index) or string, indicating the column name. The searchValue can be a number, a string, a JavaScript regular expression or one of the variables mentioned above

If more rows are matching the criteria it will return an array of values.

If returnValueColumn is omited it will return the row object of the matching row.

lookup2(filterObject,returnValueColumn=null)

It searches for a combination of search terms as provided in the filterObject

The filter object has the format {searchColumn1: searchValue1, searchColumn2: searchValue2.....} when searchColumn can be either the column name or the column index. The searchValue can be a