LINQ syntax differences: Devo APIs vs search window

[ 1 Lookup operations ] [ 2 Division vs real division operations ] [ 3 Collect distinct operation ] [ 4 Array operation ] [ 5 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.

Lookup 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:

from demo.ecommerce.data
select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Syntax:

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

Query example:

from demo.ecommerce.data
select lu("demo", "IP_list", "StreetAddress", clientIpAddress) as `IP street address`

Related article: Data enrichment

Division vs real division operations

There are two types of division operations in Devo, each of them with a different syntax. One of the two syntaxes (the one using the symbol operator) is transposed in the search window and our APIs so you need to be very careful not to confuse them when bringing them from one area to the other.

Search window + Alerts API	Other Devo APIs

Search window + Alerts API	Other Devo APIs
Division
Syntax: `select numberORcolumn \ numberORcolumn as columnName` Query example: `from demo.ecommerce.data` `group every 5m by method, statusCode` `select count() as countselect count \ 2 as halfCount`	Syntax: `select numberORcolumn / numberORcolumn as columnName` Query example: `from demo.ecommerce.data` `group every 5m by method, statusCode` `select count() as countselect count / 2 as halfCount`
Real division
Syntax: `select numberORcolumn / numberORcolumn as columnName` Query example: `from demo.ecommerce.data` `group every 5m by method, statusCode` `select count() as count` `select count / 2 as halfRealCount`	Syntax: `select numberORcolumn \ numberORcolumn as columnName` Query example: `from demo.ecommerce.data` `group every 5m by method, statusCode` `select count() as countselect count \ 2 as halfRealCount`

Related articles: Division (div, \), Real division (rdiv, /)

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:

from demo.ecommerce.data
group every 5m by method, statusCode
select collectdistinct(bytesTransferred) as distinctBytesTransferred

Related articles: Query API

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 columnNameFilter: where column operator array(column) [valuePosition]

Query example:

from demo.ecommerce.data
group every 1h by method, statusCode
select collectdistinct(timeTaken) as DisTimeTaken
select array(DisTimeTaken) [1] as Array2Timewhere statusCode >= array(DisTimeTaken) [1]

Related articles: Query API

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 example:

from siem.logtrust.web.activity
select ((
from siem.logtrust.web.navigation
group every - by userEmail
select count()) as inner)
select inner[username] as nav
group by username, nav

from demo.ecommerce.data
where statusCode in
(from demo.ecommerce.data
where statusCode = "404"
where now()- 5m < eventdate < now()
group every - by statusCode)
select method, statusCode, eventdate

Related articles: Subqueries, Query API