Versions Compared

Old Version 14

changes.mady.by.user Borja Moro Moreno

Saved on

compared with

New Version Current

changes.mady.by.user Borja Moro Moreno

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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.

Aggregation operations

...

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

When you use this operation in the Data Search to create a new field, 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 maximum adding operations until you have obtained the maximum of added all the arguments you need.

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select

max

add(value1, value2, value3, value4...) as

maxField

totalField

 
Query example:

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

Syntax:

Create field: select

max

add(value1, value2) as

maxFieldA

totalFieldA,

max

add(

maxFieldA

totalFieldA, value3) as

maxFieldB

totalFieldB,

max

add(

maxFieldB

totalFieldB, value4) as

maxFieldC

totalFieldC...

Query example:

Code Block
from demo.ecommerce.data
select
max
add(bytesTransferred, timeTaken) as
maxFieldA
totalFieldA,
max
add(
maxFieldA
totalFieldA, statusCode) as
maxFieldTotal
totalFieldFinal

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 needAddition, sum, plus / Concatenation (add, +)

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:

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

 

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

Query example:

Code Block
from demo.ecommerce.data
select
min(bytesTransferred, timeTaken, statusCode) as `minField`
`lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`

Syntax:

Create field:

select

min

lu(

value1, value2) as minFieldA, min(minFieldA, value3) as minFieldB, min(minFieldB, value4) as minFieldC...

"lookupName", "lookupColumn", field) as newColumnName

Query example:

Code Block
from demo.ecommerce.data
select
min(bytesTransferred
lu("IP_list", "StreetAddress",
timeTaken
clientIpAddress) as
minFieldA, min(minFieldA, statusCode) as minFieldTotal)

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.

...

`IP street address`

Related article: Lookups

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

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

 

select `lu/lookupName`(field) as newColumnName

Query example:

Code Block
from demo.ecommerce.data
select
add(bytesTransferred, timeTaken, statusCode
`lu/IP_list`(clientIpAddress) as
`totalField`
`jsonField`

Syntax:

Create field:

select

add(value1, value2) as totalFieldA, add(totalFieldA, value3) as totalFieldB, add(totalFieldB, value4) as totalFieldC...

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

Query example:

Code Block
from demo.ecommerce.data
select
add
hlurjson(
bytesTransferred, timeTaken) as totalFieldA, add(totalFieldA, statusCode
"demo", "IP_list", clientIpAddress, eventdate) as
totalFieldFinal
`json`

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

...

article: Lookups

Maximum (max) operations → create field

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 multiplication maximum operations until you have multiplied obtained the maximum of all the arguments you need.

Search window + Alerts API

Other Devo APIs

Syntax:

Create field: select

mul

max(value1, value2, value3, value4...) as

resultField 

maxField

Query example:

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

Syntax:

Create field: select

mul

max(value1, value2) as

resultFieldA

maxFieldA,

mul

max(

resultFieldA

maxFieldA, value3) as

resultFieldB

maxFieldB,

mul

max(

resultFieldB

maxFieldB, value4) as

resultFieldC

maxFieldC...

Query example:

Code Block
from demo.ecommerce.data
select
mul
max(bytesTransferred, timeTaken) as
resultFieldA
maxFieldA,
mul
max(
resultFieldA
maxFieldA, statusCode) as
resultFieldTotal
maxFieldTotal

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 APIMaximum (max)

Minimum (min) operations → create field

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

Not supported

Syntax:

Create

column

field: select

array(column) [valuePosition] as columnNameFilter: where column operator array(column) [valuePosition]

min(value1, value2, value3, value4...) as minField

 
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

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:

select collectdistinct(column) as columnName

Query example:

Code Block
from demo.ecommerce.data
group 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

...

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

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

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:

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

Syntax:

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

Query example:

Code Blockfrom demo.ecommerce.data select hlurjson("IP_list", clientIpAddress, eventdate
minFieldA, min(minFieldA, statusCode) as
`json`
minFieldTotal)

Related article: Data enrichmentarticles: Minimum (min)

Mlevalmodel operation

Mlevalmodel 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

...

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

Syntax:

Create

column

field: select

(from tag1.tag2.tag3.tag4) as columnName

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

Query examples

mul(value1, value2, value3, value4...) as resultField


Query example:

Code Block
from
siem
demo.
logtrust
ecommerce.
web.activity
data
select mul(
(from siem.logtrust.web.navigationgroup every - by userEmailselect count()) as inner) select inner[username] as navgroup by username, nav
bytesTransferred, timeTaken, statusCode) as `resultField`

Syntax:

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

Query example:

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

...

bytesTransferred, timeTaken) as resultFieldA, mul(resultFieldA, statusCode) as resultFieldTotal

Related articles: Multiplication, product (mul, *)