Search code examples
azureloggingazure-data-exploreralerts

How to index a column in kusto query after pivot for azure log alerts

I have the following kusto queury working as a log query in Azure

traces
| where message contains "SWSE"
| extend d=parse_json(message)
| extend Info=tostring(d.message)
| where Info startswith "Borrow Token" or Info startswith "Return Token" 
| extend  tAction = tostring( split(Info,' ',0)[0])
| summarize count_=count() by tAction, timebox=bin(timestamp, 10m)
| evaluate pivot(tAction,sum(count_))
| extend  diff = abs(Borrow-Return)
| where diff>2

resulting in

Result of query in azure log explorer

However, this line is marked as an error when it is imported into log alerts, not when it is run against the Azure logs.

| extend  diff = abs(Borrow-Return)

with the error:

The request had some invalid properties

Is there another way to reference these columns?

Solution

  • the output schema of the pivot() plugin is not deterministic and depends on the input data - you may need to use column_ifexists() for cases in which the column you expect actually don't exist in the output schema.

    for example:

    | extend diff = abs(column_ifexists("Borrow", 0) - column_ifexists("Return", 0))