Search code examples
kql

Check two different tables

I would like to check two different tables and then ignore the matched results in the output. Example: If Table1 with Source1 has the Name "ABC" as well as Number "123" or "456" and at the same time if Table2 with Source1 has Type == "Misc" then ignore the matches and return everything else. thanks

Example:

Table 1
| where Source = 1 and Name == "ABC" and Number in ("123", "456")

Table 2
| where Source = 1 and Type == "Misc"
Solution

  • You could do an anti join and then a union, when Id is your matching property:

    union
  (Table1
  | join kind=anti(
    Table1
    | where Source = 1 and Name == "ABC" and Number in ("123", "456"))
    on Id),
  (Table2
    | join kind=anti(
      Table2
       | where Source = 1 and Type == "Misc")
    on Id)