CODEWITHSUNDEEP
azure-data-explorerkql
Mathias Rönnlund
Mathias Rönnlund

Reputation: 4845

Summarize dynamic values with Kusto query in Azure Data Explorer

I have this query that almost works:

datatable (timestamp:datetime, value:dynamic)
[
datetime("2021-04-19"), "a",
datetime("2021-04-19"), "b",
datetime("2021-04-20"), 1,
datetime("2021-04-20"), 2,
datetime("2021-04-21"), "b",
datetime("2021-04-22"), 2,
datetime("2021-04-22"), 3,
]
| project timestamp, stringvalue=iif(gettype(value)=="string", tostring(value), ""), numericvalue=iif(gettype(value)=="long", toint(value), int(null))
| summarize any(stringvalue), avg(numericvalue) by bin(timestamp, 1d)
| project timestamp, value=iif(isnan(avg_numericvalue), any_stringvalue, avg_numericvalue)

This splits the values in the value field into stringvalue if the value is string and numericvalue of the value is long. Then it summarizes the values based on day level, for the string values it just takes any value and for the numeric values is calculates the average.

After this I want to put the values back into the value field.

I was thinking that the last row could be like below but the dynamic function only wants literals

| project timestamp, value=iif(isnan(avg_numericvalue), dynamic(any_stringvalue), dynamic(avg_numericvalue))

If I do it like this it will actually work:

| project timestamp, value=iif(isnan(avg_numericvalue), parse_json(any_stringvalue), parse_json(tostring(avg_numericvalue)))

But is there a better way than converting it to json and back?

Upvotes: 0

Views: 6192

Answers (1)

Slavik N
Slavik N

Reputation: 5328

iff expects the type of the 2nd and 3rd arguments to match. In your case, one is a number, and the other one is a string. To fix the issue, just add tostring() around the number:

datatable (timestamp:datetime, value:dynamic)
[
datetime("2021-04-19"), "a",
datetime("2021-04-19"), "b",
datetime("2021-04-20"), 1,
datetime("2021-04-20"), 2,
datetime("2021-04-21"), "b",
datetime("2021-04-22"), 2,
datetime("2021-04-22"), 3,
]
| project timestamp, stringvalue=iif(gettype(value)=="string", tostring(value), ""), numericvalue=iif(gettype(value)=="long", toint(value), int(null))
| summarize any(stringvalue), avg(numericvalue) by bin(timestamp, 1d)
| project timestamp, value=iif(isnan(avg_numericvalue), any_stringvalue, tostring(avg_numericvalue))

Upvotes: 1

Related Questions