Skip to content

Guide to CFXQL: Robotic Data Automation's Query Language

CFXQL is a SQL like data query language. RDA Low-Code developers use CFXQL to convey their intent of what each bot should do with the data. CFXQL helps RDA Low-Code developers to consistently interact with hundreds of different data sources and destinations in a unified manner.

RDA Bot Types

Before we get into the details of CFXQL, let us take look at the types of RDA Bots and how they use CFXQL:

Bot Type Bot Name Prefix Description
Source Filtering Bots # Bots starting with # in the name are considered source filtering bots.
They accept Full CFXQL and translate into a Query or API call that is sent to the remote data source (such as a Database).
This helps to filter the data at the source itself.

For example #splunkv2:search-index is a source filtering bot.
Destination Filtering Bots * Bots starting with * in the name are considered destination filtering bots.
They accept Full CFXQL and filter the data that is already in memory, or the data source does not provide a flexible query mechanism.

For example *dm:filter is a destination filtering bot.
It filters the data that is already in the pipeline.
API Bots @ Bots starting with @ in the name are the API Based bots.
They accept a simple Restricted CFXQL and extract API parameters from the query
The API parameters for each bot control the behavior of that bot.

For example @watson:summarize is an API based bot.
It sends the input data to IBM Watson and API parameters specified in CFXQL control the bot's behavior.

Full CFXQL

Full CFXQL supports several SQL like operators.

Below is a simple example of CFXQL Query:

    device_category is 'SWITCH' and 
        ( severity is not in [ 'INFO', 'DEBUG' ] or status != 'CLOSED' )
    GET
        device_category, severity, status as 'Incident Status'
Full CFXQL has two parts: Query and Result Format

Query

  • Query can be any of the operations with a combination of AND OR logical groupings.
  • If the query is * it means match all. Equivalent to SELECT * in SQL
  • All column names MUST be only made up of letters, digits and underscore(_). If the name has other characters, it can be escaped using backquote characters: `Column with Spaces` == 'value'

Result Format

  • Optionally Result format can be specified to select subset of columns from the query response.
  • Result format uses GET (keyword, case insensitive) to seperate Query and Result Format.
  • Example Result formats
    • * GET ColumnA, ColumnB, `Column-C` as 'Column C'
    • * GET columnA as 'A', columnB as 'B'

Equality Operators

Operator Description and Examples
= Compares if the left side column value is equal to right side constant or column value.

device = '10.10.10.1'
device == '10.10.10.1'
device is equal to '10.10.10.1'
device is '10.10.10.1'
device equals '10.10.10.1'
port == 80
!= Compares if the left side column value is not equals to right side constant or column value.

device != "10.10.10.1"
device is not equal to '10.10.10.1'
device is not '10.10.10.1'
device not equals '10.10.10.1'

Numerical Operators

Operator Description and Examples
> Compares if the left side numerical column value is greater to right side numerical constant or column value.

port > 80
port is greater than 80
port gt 80
>= Compares if the left side numerical column value is greater or equal to right side numerical constant or column value.

port >= 80
port is greater than or equal to 80
port gte 80
< Compares if the left side numerical column value is less than right side numerical constant or column value.

port > 80
port is greater than 80
port lt 80
<= Compares if the left side numerical column value is less than or equal to right side numerical constant or column value.

port <= 80
port is less than or equal to 80
port lte 80

String Operators

Operator Description and Examples
~ Contains operator.

device ~ '10.10'
device contains '10.10'
device has '10.10'
!~ Does not contain operator.

device !~ '10.10'
device does not contains '10.10'
device not contains '10.10'
? Regex match operator.

device ? '^10.*'
device matches '^10.*'
!? Regex does not match operator.

device !? '^10.*'
device does not match '^10.*'
^~ Starts-with operator.

device ^~ '10'
device starts with '10.'
!^~ Not-starts-with operator.

device !^~ '10'
device does not start with '10.'
~$ Ends-with operator.

device ~$ '.1'
device ends with '.1'
!~$ Not-ends-with operator.

device !~$ '10'
device does not end with '.1'

List Operators

Operator Description and Examples
in List IN operator.

device in ['10.10.10.1', '10.10.10.2']
device in '10.10.10.1', '10.10.10.2'
port in 80, 443
port is in [ 80, 443 ]
port is one of [ 80, 443 ]
port is among [ 80, 443 ]
port is any of [ 80, 443 ]
not in List NOT IN operator.

device not in ['10.10.10.1', '10.10.10.2']
device not-in '10.10.10.1', '10.10.10.2'
port not in 80, 443
port is not in [ 80, 443 ]
port is not one of [ 80, 443 ]
port is not among [ 80, 443 ]

Unary Operators for NULL Value Checks

Operator Description and Examples
null Checks if the value is NULL or empty.

device is null
device is none
device is empty
not null Checks if the value is not NULL or not empty.

device is not null
device notnull
device is not none

Time Operators

Operator Description and Examples
before Operator to test if given timestamp column is less than an absolute or relative timestamp.

timestamp is before 'Jan 1, 2022 10am'
timestamp is before -10 days
timestamp is before -15 minutes
timestamp is before this month
after Operator to test if given timestamp column is greater than an absolute or relative timestamp.

timestamp is after 'Jan 1, 2022 10am'
timestamp after -10 days
timestamp after -15 minutes
expiry_date is after this month
during Operator to test if given timestamp column is between two absolute or relative timestamps.

resolved_date is during 'Jan 1, 2022 10am' to 'Jan 5, 2022 10am'
resolved_date is during today
resolved_date is during this year
not during Operator to test if given timestamp column is not between two absolute or relative timestamps.

resolved_date is not during 'Jan 1, 2022 10am' to 'Jan 5, 2022 10am'
resolved_date notduring this month
resolved_date is not during this CY2022

Logical Operators

Logical Operators AND and OR can be nested using parenthesis.

Operator Description and Examples
AND Logical AND Operator

timestamp is before 'Jan 1, 2022 10am' AND status is 'OK'
device is '10.10.10.10' & status is not 'ok'
A != B & C = 20
OR Logical OR Operator

timestamp is before 'Jan 1, 2022 10am' OR status is 'OK'
device is '10.10.10.10' or status is not 'ok'
(device is '10.10.10.10' ) or (status is not 'ok')
A != B | C == 20

Restricted CFXQL

Restricted CFXQL supports only = operator and AND logical operator. Does not support 'Result Format'

Below are simple examples of restricted CFXQL Query:

    device_category is 'SWITCH' and severity = 'INFO'

    device_category = 'SWITCH' & 
    severity = 'INFO' &
    status = 200

Equality Operators

Operator Description and Examples
= Compares if the left side column value is equals to right side constant or column value.

device = '10.10.10.1'
device == '10.10.10.1'
device is equal to '10.10.10.1'
device is '10.10.10.1'
device equals '10.10.10.1'
port = 80

Logical Operators

Operator Description and Examples
AND Logical AND Operator

device = '10.10.10.1' AND status is 'OK'
device is '10.10.10.10' & status = 'ok'
A = 100 & C = 20