CODEWITHSUNDEEP
azureazure-data-explorerazure-monitoring
Jay
Jay

Reputation: 319

How do I exclude List B from List A in Kusto?

I would like to get an overview of recent SpecialEvents, the ones that already have a comment named 'Skip' need to be excluded from list A. Since comments is an array I can't simply put everything in one query with a where clause (it will not process Comments since it only contains value: '[]'). How do I combine these two tables (Show everything from List A except the ones that are in List B)?

// List A: Show all Event created less than 1 hour ago SpecialEvent | where TimeGenerated < ago(1h) | distinct uniqueNumber | project uniqueNumber

// List B: Don't add the ones that contain 'skip' SpecialEvent | mvexpand parsejson(Comments) | extend commentMsg = Comments.message | where commentMsg contains 'SKIP' | distinct uniqueNumber | project uniqueNumber

Upvotes: 0

Views: 4274

Answers (1)

Yoni L.
Yoni L.

Reputation: 25905

If I understand your question correctly, you could use the !in() operator or an anti-join.

For example:

let list_a = 
   SpecialEvent 
   | where TimeGenerated < ago(1h) 
   | distinct uniqueNumber
;
SpecialEvent 
| where uniqueNumber !in(list_a)
| mv-expand parsejson(Comments) // you could also use 'mv-apply' and perform the filters on 'SKIP' under that scope
| extend commentMsg = Comments.message
| where commentMsg contains 'SKIP'
| distinct uniqueNumber

Upvotes: 3

Related Questions