cfxdm - dm:functions

Data manipulation and transformation

dm:functions: This cfxdm tag provides very comprehensive data manipulation & transformation functions and below are the details about them and their usage.

  • any_non_null: Returns any non-null value from a list of input values, @param value is optional, if not specified, returns None when none of the listed values meet the criteria. Input must be a list (else treated as a single item list).

  • concat: Adds prefix and suffix to the specified string, @param prefix type is string (optional). @param suffix type is a string (optional). Input must be a string. If the input is null, it is treated as ' '

  • datetime: Parses input string and converts into an epoch milliseconds format number. Input must be a string. @param tzmap: type dict (optional). Dictionary of timezone mappings from custom/local to standard timezones. @param expr: type string (optional). A custom timestamp format with UTC/local timezone.

  • evaluate: Given an expression evaluates the expression string. If performed on the dataframe row, it evaluates by passing the row as a dictionary. If performed on a single value, it expects an additional argument 'key' to be used in the expression. @param expr: The expression to evaluate. @param key: An optional 'key' if evaluated on a single value instead of a dictionary.

  • fixed: Returns a fixed value specified by the 'value' parameter. @param value Type can be string or number. Input can be of any type.

  • formDecode: Decodes input string to remove any URL encoded values. Requires no parameters. Input must be a string.

  • highest: Returns highest non-null value from the list of integer values. @param default (optional), Type int. If provided none of the input values are non-null, returns default Input must be a number or list of numbers.

  • hours_between: Number of hours between two datetime strings. If only one specified, compare diff between now and that timestamp.

  • join: Joins input list using an optional separator. @param sep (optional), default value is ' ' . Input is expected to be a list. If the input is not a list, it returns the value without joining.

  • jsonDecode: Decodes input string into JSON object. Requires no parameters. Input must be a string.

  • lower: Converts to lowercase text, requires no parameters. Input must be a string.

  • lowest: Returns lowest non-null value from the list of int values. @param default (optional), Type int. If provided none of the input values are non-null, returns default, Input must be a number or list of numbers.

  • map_values: Maps input value using the specified name value dictionary. If no values match and ""*"" key is provided, it returns the ""*"" key's values, or else the original value will be maintained. Input must be a string.

  • match: Matches a regular expression and extracts a specific value (if matched). @param expr Type string. Regular expression @param flags List of optional flags (A I M L S X) Input must be a string.

  • minutes_between: Number of minutes between two date-time strings. If only one is specified, it compares the difference between it and the current timestamp.

  • replace: Replaces old value with new value in the input string @param oldvalue, Type string. @param new value, Type string. Input must be a string.

  • seconds_between: Number of seconds between two datetime strings. If only one is specified, it compares the difference between it and the current timestamp.

  • slice: Slices a string or an array using specified indices. @param from-index Type int. Default value 0 @param to-index Type int. The default value is None. Input can be a string or a list. If neither, it converts input to a string.

  • split: Splits the input using specified 'sep' separator. @param sep Type string. Optional. Default any whitespace characters. Input must be a string.

  • strip: Strips white spaces from both sides of a string, Requires no parameters. Input must be a string.

  • timediff:

  • to_numeric: Convert input value into numeric

  • ts_to_datetimestr: Processes input number with specified 'unit' (s,ms,ns,excel_date) and converts the value to datetime string specified by 'format', default is ISO format. Input must be a float or int. @param 'unit' (Type string), must be s,ms,ns,excel_date, default is 'ms' @param 'format' (Type string), default is None (ISO format)

  • upper: Converts to uppercase text Requires no parameters. Input must be a string.

  • valueRef: Extracts a specific item from the input dictionary object. @param path A dot '.' delineated path to the element within the dictionary, Input must be dictionary object.

  • when_null: If the specified value is null, it uses the value as per 'value' param @param value Type can be string or number. Input can be of any type.

Examples:

Convert Milliseconds to Human readable date & timestamp

Functions:

  • datetime

  • ts_to_datetimestr

In the below syntax example, 'Alert_Time' is a column name that has a timestamp value in milliseconds format.

dm:map attr = 'Alert_Time' & func = 'ts_to_datetimestr' & unit = 'ms'

From: (Time in Milliseconds)

To: (Time in Date & Time format)

In the above example, vrops-alerts is a named dataset that was created out of the VMware vROps extension's alerts tag.

Convert Human readable Date & Timestamp to Milliseconds

dm:map attr = 'Alert_Time' & func = 'datetime'

From: (Time in Date & Time format)

To: (Time in Milliseconds)

In the above example, prtg-alerts is a named dataset that was created out of the PRTG Monitor extension's alerts tag.

Join values from two to more columns into one.

Function:

  • join

In the below syntax example, 'resource_kind & adapter_kind' are columns and their values are joined together in a new column called 'resource_and_vendor' using the function 'join' (optional separator '-' is used in this example, when it is not specified, the default separator is space.

dm:map from = 'resource_kind,adapter_kind' & to = 'resource_and_vendor' & func = 'join' & sep = '-'

Source Columns & their values:

Destination Column (after join)

Arithmetic Operations

Function:

  • Evaluate

To perform arithmetic operations on columns, make sure the column's data type is set to 'Integer' (Numeric) or 'Float'. Additionally, column values should not contain NULL or Empty values.

Multiplication: In the below example, vm_cpu_sockets & vm_cpu_cores are columns which has numeric values, using 'evaluate' function, multiplied 2 column values together and saving the result in new column call 'vm_cpu_total'.

dm:map to ='vm_cpu_total' & func = 'evaluate' & expr = 'vm_cpu_sockets * vm_cpu_cores'

Bytes to GB: In the below example, vm_disk_size_bytes is a column that has disk capacity in bytes, using the 'evaluate' function, create a new column 'vm_disk_size_gb' and convert the bytes into GB.

dm:map from = 'vm_disk_size_bytes' & to ='vm_disk_size_gb' --> dm:map attr = 'vm_disk_size_gb' & func = 'evaluate' & expr = 'vm_disk_size_gb / 1024 / 1024 / 1024'

Last updated