CODEWITHSUNDEEP
azureazure-log-analyticsazure-data-explorerkql
Peter
Peter

Reputation: 3857

Kusto: remove non-matching rows when using the parse operator

I'm querying azure log analytics using Kusto, and extracting fields with the parse operator, then keeping only the records which parsed correctly:

traces
| parse message with "Search found " people " people in " groupCount " groups"
| where people != "" and groupCount != ""
| order by n desc

Is there a more terse way of parsing and dropping non-matching rows? If I am parsing out a lot of columns from a set of logs, maybe containing partial matches, this connascence between the parse and where gets fiddly.

By comparison, in SumoLogic, the parse operator automatically drops all rows which don't match a parsed pattern, which makes for really tidy pipelines:

*
| parse "Search found * people in * groups" as people, groupCount
| order by n desc

Upvotes: 0

Views: 3079

Answers (2)

Peter
Peter

Reputation: 3857

There's now a built in operator that will do this: parse-where

https://learn.microsoft.com/en-us/azure/kusto/query/parsewhereoperator

It has syntax just like parse, but will omit from its output any records which didn't match the parse pattern.

So the query:

traces
| parse message with "Search found " people " people in " groupCount " groups"
| where people != "" and groupCount != ""
| order by n desc

becomes:

traces
| parse-where message with "Search found " people " people in " groupCount " groups"
| order by n desc

Upvotes: 0

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

In Kusto: 'parse' operator does not auto-filter rows that does not match the provided pattern, and operator works as in mode of 'extend' - adding more columns. If you would like to filter specific row - the recommendation is to use 'where' operator before the 'parse': this will also improve performance as 'parse' will have fewer rows to scan.

traces
| where message startswith 'Search found'
| parse message with "Search found " people " people in " groupCount " groups"
 ...

Upvotes: 1

Related Questions