Kusto query could not be parsed

Question

I have SecurityLog with fields like DstIP_s and want to display records matching my trojanDst table

let trojanDst = datatable (DstIP_s:string)
    [    "1.1.1.1","2.2.2.2","3.3.3.3"
];
SecurityLog |
| join trojanDst  on DstIP_s

I am getting query could not be parsed error ?

Answer 1

The query you posted has a redundant pipe (|) before the join.

From an efficiency standpoint, make sure the left side of the join is the smaller one, as suggested here: https://docs.microsoft.com/en-us/azure/kusto/query/best-practices#join-operator

Answer 2

This is too long for a comment. As @Yoni L pointed the problem is doubled pipe operator.

---

For anyone with SQL background join may be a bit counterintuitive(in reality it is kind=innerunique):

JOIN operator:

kind unspecified, kind=innerunique

Only one row from the left side is matched for each value of the on key. The output contains a row for each match of this row with rows from the right.

Kind=inner

There's a row in the output for every combination of matching rows from left and right.

let t1 = datatable(key:long, value:string)  
[
1, "a",  
1, "b"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "c", 
1, "d"  
];
t1| join t2 on key;

Output:

┌─────┬───────┬──────┬────────┐
│ key │ value │ key1 │ value1 │
├─────┼───────┼──────┼────────┤
│   1 │ a     │    1 │ c      │
│   1 │ a     │    1 │ d      │
└─────┴───────┴──────┴────────┘

Demo

---

SQL style JOIN version:

let t1 = datatable(key:long, value:string)  
[
1, "a",  
1, "b"  
];
let t2 = datatable(key:long, value:string)  
[  
1, "c", 
1, "d"  
];
t1| join kind=inner t2 on key;

Output:

┌─────┬───────┬──────┬────────┐
│ key │ value │ key1 │ value1 │
├─────┼───────┼──────┼────────┤
│   1 │ b     │    1 │ c      │
│   1 │ a     │    1 │ c      │
│   1 │ b     │    1 │ d      │
│   1 │ a     │    1 │ d      │
└─────┴───────┴──────┴────────┘

Demo

Answer 3

There are many join types in KQL such as innerunique, inner, leftouter, rightouter, fullouter, anti and more. here you can find the full list