I have a requests
datatable with url
, name
, timestamp
columns, to which i add another computed column operationType
.
I want to filter and query requests
based on defined dynamic object attributes data
, in order to obtain Operations and their usage count, like this:
operationType | Counts
-------------------------
1.1 Operation A | 2400
-------------------------
1.2 Operation B | 1500
What i have:
let daysAgo = 100d;
let data = dynamic([
{
'searchKey': 'url',
'searchOperator': 'contains',
'searchValue': 'AjaxContent?option=something',
'operationName': '1.1 Operation A'
},
{
'searchKey': 'name',
'searchOperator': 'matches regex',
'searchValue': 'POST /api/repo/\\d+/filters',
'operationName': '1.2 Operation B'
}]);
let req = requests
| where (timestamp >= ago(daysAgo))
| where
//Issue #1: I want data[0].searchKey to be used as the requests column name
data[0].searchKey contains data[0].searchValue
//url contains data[0].searchValue //works
//Issue #2: 'doesn't work, error 'matches regex' operator requires string arguments
or name matches data[1].searchValue
//or name matches regex 'POST /api/repo/\\d+/filters' //works
| extend operationType=
case(name == data[2].searchValue, data[2].operationName,
url contains data[0].searchValue, data[0].operationName
'Other - please check' )
| order by timestamp desc;
req | summarize Counts = count() by operationType
The ideal would be to create a custom query, like bellow (and maybe even looping through dynamic array attributes data
) (Q #3):
requests | where data[0].searchKey data[0].searchOperator data[0].searchValue
In the same context, ideal would also be to extend the computed operationType
column to be somehow custom created (Q #4):
requests
| where
...
| extend operationType= case(url contains data[0].searchValue, data[0].operationName,
name matches regex data[0].searchValue, data[0].operationName)
or
requests
| where
...
| extend operationType= case(data[0].searchKey data[0].searchOperator data[0].searchValue, data[0].operationName,
data[1].searchKey data[1].searchOperator data[0].searchValue, data[0].operationName)
where
statement (Issue #1)?matches regex
statement (Issue #2)?where
statement, looping through dynamic array (Q #3)?case
statement (Q #4)?1) Can dynamic value be used as the datatable column name in a where statement?
Yes. Please check : https://learn.microsoft.com/en-us/azure/kusto/query/columnifexists
Example showing column_ifexists() and 'matches regex'
datatable (a:string, b:string)
['1', '2']
| where column_ifexists('a', '') matches regex '1'
2) Can dynamic value be used as right hand in the matches regex statement?
To some extent. In general, only constant values are supported, so you can't use row-context (where regex will vary for each row). You can, however, use toscalar() to calculate constant regular expressions as a sub-query, and use those as an argument to 'matches regex'. https://learn.microsoft.com/en-us/azure/kusto/query/toscalarfunction
3) Can create a dynamically custom query where statement, looping through dynamic array?
No. Dynamic KQL queries are not supported at this moment.
4) Can a computed extended column be custom created in a case statement?
Yes, as long as you use techniques described in answer #1 and #2, and not running into limitations of #3.