Page Comparison

...

Note

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

Syntax:

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

...

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

...

Syntax:

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

Query example:

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

Related article: Data enrichment

Aggregation operations

Maximum (max) operations

...

Related articles: Multiplication, product (mul, *)

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

...

Not supported

Syntax:

...

)

...

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.

...

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:

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

Related articles: Query API

Subqueries

...

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 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 APIsit, you can do so with the Query API.

Search window + Alerts API	Other Devo APIs
`Not supported`	Syntax:

Create column:

select

(from tag1.tag2.tag3.tag4

collectdistinct(column) as columnName

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

Query

examples

example:

Code Block
from

siem

demo.

logtrust

ecommerce.

web.activity

data

select

group

((from siem.logtrust.web.navigationgroup every - by userEmailselect count()) as inner) select inner[username] as navgroup by username, nav

every 5m by method, statusCode
select collectdistinct(bytesTransferred) as distinctBytesTransferred

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

Syntax:

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

Query example:

Code Block
from demo.ecommerce.data

where statusCode in(


select `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Syntax:

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

Query example:

Code Block
from demo.ecommerce.data

where statusCode = "404"where now()- 5m < eventdate < now() group every - by statusCode) select method, statusCode, eventdate

...

select lu("IP_list", "StreetAddress", clientIpAddress) as `IP street address`

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

Syntax:

select `lu/lookupName`(field) as newColumnName

Query example:

Code Block
from demo.ecommerce.data select `lu/IP_list`(clientIpAddress) as `jsonField`

Syntax:

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

Query example:

Code Block
from demo.ecommerce.data select hlurjson("IP_list", clientIpAddress, eventdate) as `json`

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

Not supported

Query examples:

Code Block
from "datatable" select "fields" mlevalmodel("domain", "ModelName", "ModelFields") as "NameNewField"

Example:

Code Block

from demo.ecommerce.data
select
  split(referralUri, "/",2) as domain,
  float(lenght(domain)) as lenght
  shannonentropy(domain) as entropy
  float(countbyfilter(domain, "aeiuoAEIOU")) as p_vowels,
  mlevamodel("self", "example_test", lenght, entropy, p_vowels) as prob
  ifthenelse(prob>0.8, "dga", "legit") as type

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

Not supported

Syntax:

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

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

Query examples:

Code Block
from siem.logtrust.web.activity select ((from siem.logtrust.web.navigationgroup every - by userEmailselect count()) as inner) select inner[username] as navgroup by username, nav

Code Block
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

Versions Compared

Old Version 12

New Version 13

Key

Lookup operations

Aggregation operations

Maximum (max) operations

Collect distinct operation

Array operation

Subqueries

Collect distinct operation

Lookup operations

Regular operations

JSON operations

Mlevalmodel operation

Subqueries