...
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
Collect distinct operation
...
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
...
Search window + Alerts API | Other Devo APIs |
|---|
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` |
| 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
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.
...
