How to join multiple tables in KUSTO and select values as columns?

Question

I've the following data which comes from multiple datasources (multiple application insight instances). Just for explanation, i've reduced this to datatables.

let table1 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "DEV",
    "company2", "Healthy", "DEV", 
    "company3", "Healthy", "DEV",
];
let table2 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "TEST",
    "company2", "Healthy", "TEST", 
    "company4", "Healthy", "TEST",
];
let table3 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "UAT",
    "company2", "Healthy", "UAT", 
    "company4", "Healthy", "UAT",
    "company5", "Healthy", "UAT",
];

Now I want to select all the values to get the following result table:

companyCode	DEV	TEST	UAT
company1	Healthy	Healthy	Healthy
company2	Healthy	Healthy	Healthy
company3	Healthy
company4		Healthy	Healthy
company5			Healthy

I've tried something like this with no luck, because i got one row per table for each company.

table1
| extend DEV = status
| union withsource=SourceTable kind=outer 
   ( table2 | extend TEST = status),
   ( table3 | extend UAT = status)
| project companyCode, DEV, TEST, UAT

How can I achieve this?

Answer 1

You're very nearly there :) You could do it with joins instead of union.

Here's how I'd approach it.

let table1 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "DEV",
    "company2", "Healthy", "DEV", 
    "company3", "Healthy", "DEV",
];
let table2 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "TEST",
    "company2", "Healthy", "TEST", 
    "company4", "Healthy", "TEST",
];
let table3 = datatable(companyCode:string, status:string, environment:string) 
[
    "company1", "Healthy", "UAT",
    "company2", "Healthy", "UAT", 
    "company4", "Healthy", "UAT",
    "company5", "Healthy", "UAT",
];
union table1, table2, table3
| summarize by companyCode
| join kind=leftouter ( table1 | extend DEV = status) on companyCode
| join kind=leftouter ( table2 | extend TEST = status) on companyCode
| join kind=leftouter ( table3 | extend UAT = status) on companyCode
| project companyCode, DEV, TEST, UAT

companyCode	DEV	TEST	UAT
company1	Healthy	Healthy	Healthy
company2	Healthy	Healthy	Healthy
company3	Healthy
company4		Healthy	Healthy
company5			Healthy