LINQ syntax differences: Devo APIs vs search window

[ 1 Aggregation operations ] [ 2 Arithmetic operations ] [ 3 Array operation ] [ 4 Collect distinct operation ] [ 5 Lookup operations ] [ 6 Mlevalmodel operation ] [ 7 Subqueries ]

Due to architectural differences, the LINQ syntax presents some particularities in the Devo APIs and the search window of the Devo app.

Therefore, keep in mind that copying a LINQ query from the search window and pasting it into an API request, or vice versa, might not work. Before examining the differences explained below, you can visit Build a query using LINQ to know the standard procedures when working with LINQ.

Alerts API

Note that due to technical reasons, the Alerts API is the only API that uses the LINQ syntax used in the search window of the Devo app. This is indicated in the different sections below.

Aggregation operations

Maximum (max) operations

When you use this operation in the Data Search to create a new field, you can add as many arguments as needed, however, the number of arguments is limited to two when used in APIs.

As a workaround, you can perform subsequent maximum operations until you have obtained the maximum of all the arguments you need.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select max(value1, value2, value3, value4...) as maxField

Query example:

from demo.ecommerce.data
select max(bytesTransferred, timeTaken, statusCode) as `maxField`

Syntax:

Create field: select max(value1, value2) as maxFieldA, max(maxFieldA, value3) as maxFieldB, max(maxFieldB, value4) as maxFieldC...

Query example:

from demo.ecommerce.data
select max(bytesTransferred, timeTaken) as maxFieldA, max(maxFieldA, statusCode) as maxFieldTotal

Related articles: Maximum (max)

Minimum (min) operations

When you use this operation in the Data Search to create a new field, you can add as many arguments as needed, however, the number of arguments is limited to two when used in APIs.

As a workaround, you can perform subsequent minimum operations until you have obtained the minimum of all the arguments you need.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select min(value1, value2, value3, value4...) as minField

Query example:

from demo.ecommerce.data
select min(bytesTransferred, timeTaken, statusCode) as `minField`

Syntax:

Create field: select min(value1, value2) as minFieldA, min(minFieldA, value3) as minFieldB, min(minFieldB, value4) as minFieldC...

Query example:

Related articles: Minimum (min)

Arithmetic operations

Addition, sum, plus / Concatenation (add, +) operations

When you use this operation in the Data Search, you can add as many arguments as needed (where applicable), however, the number of arguments is limited to two when used in APIs.

As a workaround, you can perform subsequent adding operations until you have added all the arguments you need.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select add(value1, value2, value3, value4...) as totalField

Query example:

Syntax:

Create field: select add(value1, value2) as totalFieldA, add(totalFieldA, value3) as totalFieldB, add(totalFieldB, value4) as totalFieldC...

Query example:

Related articles: Addition, sum, plus / Concatenation (add, +)

Multiplication, product (mul, *) operations

When you use this operation in the Data Search, you can add as many arguments as needed, however, the number of arguments is limited to two when used in APIs.

As a workaround, you can perform subsequent multiplication operations until you have multiplied all the arguments you need.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select mul(value1, value2, value3, value4...) as resultField

Query example:

Syntax:

Create field: select mul(value1, value2) as resultFieldA, mul(resultFieldA, value3) as resultFieldB, mul(resultFieldB, value4) as resultFieldC...

Query example:

Related articles: Multiplication, product (mul, *)

Array operation

When you have fields that contain sets of values as opposed to single values, this operation transforms its data type into an array to be later used to invoke one of the values inside the set. This operation can be used both to create columns and as a filter. When used to create a column, the value invoked will be inserted in the new column while as a filter it will be used as filtering criteria.

This operation is not supported in the search window so you will not be able to bring queries from one area to the other. To use this operation, you need to use the Query API.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Not supported

Syntax:

Create column: select array(column) [valuePosition] as columnName

Filter: where column operator array(column) [valuePosition]

Query example:

Related articles: Query API

Collect distinct operation

This operation will return the set of distinct values for the specified field when grouping events. This operation is not supported in the search window so you need to be careful when using queries from one area to the other. If you want to use it, you can do so with the Query API.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Not supported

Syntax:

select collectdistinct(column) as columnName

Query example:

Related articles: Query API

Lookup operations

Regular operations

Queries that use lookup operations present some particularities that make them incompatible when used from the search window to APIs or vice versa. The use of symbols is different and the domain name is required in one of them.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

select `lu/lookupName/lookupColumn`(field) as newColumnName

Query example:

Syntax:

select lu("lookupName", "lookupColumn", field) as newColumnName

Query example:

Related article: Data enrichment

JSON operations

Queries that use lookup operations with JSON present some particularities that make them incompatible when used from the search window to APIs or vice versa. The use of symbols is different and a specific json command is required in one of them.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API

Other Devo APIs

Syntax:

select `lu/lookupName`(field) as newColumnName

Query example:

Syntax:

select hlurjson("lookup_name", field, eventdate) as json

Query example:

Related article: Data enrichment

Mlevalmodel operation

Mlevalmodel operation is not supported in search window. Use this operation in APIs when you want to work with models you uploaded in Model Management.

Search window	Devo APIs

Search window

Devo APIs

Not supported

Query examples:

Example:

Refer article: Model Management

Subqueries

Subqueries are not supported in the search window yet so you need to be careful when using queries from one area to the other because you will not be able to reproduce subqueries. If you want to use subqueries, your only option so far is to use the Devo APIs.

Search window	Devo APIs

Search window

Devo APIs

Not supported

Syntax:

Create column: select (from tag1.tag2.tag3.tag4) as columnName

Filter: where column in (from tag1.tag2.tag3.tag4)

Query examples:

Related articles: Subqueries, Query API