Is there a way to combine data from two tables in Kusto?

Question

Learning more about how to write a query in Kusto. I have a column in 2 tables that have different Roles, but the column header is Role, that I'd like to combine the data into one column called Roles.

I tried, adding this, | extend Roles = strcat (RoleName, Role), but that just combined the data.

Here is my query attempt, I'm joining 3 tables, 2 of which have the roles. The third is where I'm validating the user aliases.

(cluster('****').database('****').****_****** | where Discriminator == 'Service'| where DivisionOid == '******')

| join kind = leftouter cluster('****').database('****').Release_Users on SubscriptionId 

| join kind = leftouter (cluster('****').database('****').Release_AzureAccess
| where RoleId contains "****" and PrincipalType !contains "ServicePrincipal") on SubscriptionId

| join kind = leftouter cluster('****').database('****').Headtrax_PeopleHierarchyV1 on $left.PrincipalName == $right.EmailAddress and $left.LiveEmailId == $right.EmailAddress

| extend Roles = strcat (RoleName, Role)<<--this was my failed attempt at combining the Role columns. That just concatenated.

I want to validate each user is active from 2 different tables against a person table. I'm a novice and am struggling with how to get this right. I'm thinking I want to combine the 2 tables into one list rather than trying to combine one column out of the 2 tables. Anyone have any advice?

user_27483 · Answer

This seems like a job for the union operator.

Union takes two or more tables and returns the rows of all of them.

From: https://docs.microsoft.com/en-us/azure/kusto/query/unionoperator?pivots=azuredataexplorer

Here's an example query from the docs above - let View_1 = view () { print x=1 }; let View_2 = view () { print x=toint(2) }; let View_3 = view () { print x_long=3 }; union withsource=TableName View_1, View_2, View_3

produces:

user_26339 · Answer

The answer from Irwin will certainly work. If you want to take his solution and flatten it into one row, you could do that with the summarize function.

let View_1 = view () { print x=1 }; 
let View_2 = view () { print x=toint(2) }; 
let View_3 = view () { print x_long=3 }; 
union withsource=TableName View_1, View_2, View_3 
| summarize sum(x_long1), sum(x_int), sum(x_long)

Is there a way to combine data from two tables in Kusto?

2 Answers