Versions Compared

Version Old Version 2 New Version 3
Changes made by

Borja Moro Moreno

Borja Moro Moreno

Saved on

Key

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

...

Due to architectural differences, the LINQ syntax presents some particularities in Activeboards and the search window. Therefore, keep in mind that copying a LINQ query from the search window and pasting it into an Activeboard widget, 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.

...

Search window

Activeboards

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:

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

Related article: Data enrichment

Order operations

Order operations are slightly adjusted in queries used from the search window to Activeboards or vice versa. On a general basis, both constructions work fine in both areas but each area has one of them as the default. This can create confusion because of the automatic transformations that are carried out in several processes when bringing queries from one place to the other. Here you have the preferred syntax for each of them, both to filter and create column:

Search window

Activeboards

Syntax:

Create column: select column operator "value"/column as columnName
Filter: where column operator "value"/column 

Query example:

from demo.ecommerce.data
where method = "POST"
select timeTaken >= bytesTransferred

Syntax:

Create column: select operator (column, "value"/column) as columnName
Filter: where operator(column, "value"/column)

Query example:

query(from demo.ecommerce.data
where eq(method, "POST")
select ge(timeTaken, bytesTransferred))

Related articles: Order group

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 Activeboards so you need to be very careful not to confuse them when bringing them from one area to the other. 

...

Search window

...

Activeboards

...

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 halfCount

...

Syntax:

select numberORcolumn / numberORcolumn as columnName

Query example:

query(from demo.ecommerce.data
group every 5m by method, statusCode
select count() as count
select 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:

query(from demo.ecommerce.data
group every 5m by method, statusCode
select count() as count
select count \ 2 as halfRealCount

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

Collect distinct operation

...

Search window

Activeboards

Not supported

Syntax:

select collectdistinct(column) as columnName

Query Example:

query (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 column 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

Activeboards

Not supported

Syntax:

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

Query example:

query (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

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 out of Activeboards, your only option so far is to use the query API.

Search window

Activeboards

Not supported

Syntax:

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

Query example:

query(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)

query (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