CODEWITHSUNDEEP
azurejoinleft-joinkql
vpn
vpn

Reputation: 53

KQL join operator returning only left table columns

I have a KQL query which joins two tables (table1 and table2), but is returning only columns for the left table (table1). However, if I run these table queries individually, they both return their respective columns and data.

The catch here is, both tables 1 and 2 have two more tables joined inside them. table1 is a result set of left outer join of table3 and table 4. table2 is a result set of left outer join of table5 and table6.

When I do a left outer join of (or and kind of join perhaps) I do see only columns on the first table and vice versa but not both tables column data.

let table1 = view() {
let table3 = view() {requests
| project name, itemCount
          | where name  ==  "ProcessApplicationLogs"
};
let table4 = view() {exceptions
| project operation_Name, itemCount
          | where operation_Name  ==  "ProcessApplicationLogs"
};
table3
| join kind=leftouter table4 on $left.name == $right.operation_Name
| summarize TotalExceptions = count() by tostring(name), tostring(itemCount)
};
table1;
let table2 = view() {
let table5 = view() {requests
| project name, itemCount
          | where name  ==  "ProcessApplicationLogs"
};
let table6 = view() {exceptions
| project operation_Name, itemCount
          | where operation_Name  ==  "ProcessApplicationLogs"
};
table5
| join kind=leftouter table6 on $left.name == $right.operation_Name
| summarize TotalExceptions = count() by tostring(operation_Name), tostring(itemCount)
};
table2
| join kind=leftouter table1 on $left.operation_Name == $right.name

Upvotes: 0

Views: 69

Answers (1)

Jahnavi
Jahnavi

Reputation: 8018

I got similar results, when I try your code with below table data.

let requests = datatable(name: string, itemCount: int)
[
    "ProcessApplicationLogs", 100,
    "OtherOperation1", 50,
    "ProcessApplicationLogs", 75
];

let exceptions = datatable(operation_Name: string, itemCount: int)
[
    "OtherOperation3", 5,
    "OtherOperation1", 10,
    "OtherOperation3", 7
];

let table1 = view() {
let table3 = view() {requests
| project name, itemCount
          | where name  ==  "ProcessApplicationLogs"
};
let table4 = view() {exceptions
| project operation_Name, itemCount
          | where operation_Name  ==  "ProcessApplicationLogs"
};
table3
| join kind=leftouter table4 on $left.name == $right.operation_Name
| summarize TotalExceptions = count() by tostring(name), tostring(itemCount)
};

let table2 = view() {
let table5 = view() {requests
| project name, itemCount
          | where name  ==  "ProcessApplicationLogs"
};
let table6 = view() {exceptions
| project operation_Name, itemCount
          | where operation_Name  ==  "ProcessApplicationLogs"
};
table5
| join kind=leftouter table6 on $left.name == $right.operation_Name
| summarize TotalExceptions = count() by tostring(operation_Name), tostring(itemCount)
};
table1
| join kind=leftouter table2 on $left.name == $right.operation_Name

enter image description here

When there is no data in one of the tables, you might get the results like above. Also, after clearing the cache, I got the expected results. Re-verify after clearing the cache. If still not working, you can make it work using project to include the right table columns like

table1
| join kind=leftouter table2 on $left.name == $right.operation_Name
| project name,itemCount,TotalException, new_name=$right.operation_Name, new_count=itemCount,new_exceptions=$right.TotalExceptions

Upvotes: 1

Related Questions