Simple Table Joins

From June 15 2020

I need to start off by explaining I work with some amazing talented people. Some things at Microsoft require team efforts and it never hurts asking a colleague for a specific ask and a second pair of eyes. Thankful some of those talented folks helped me recently as I was scratching my head around the results of a KQL Query.

I have two data tables, one with SecureScore_CL

and another with Subscription_CL.

Secure Score has the data I want to build a visualization however it only has the SubscriptionID in a ResourceID string. Luckily last time we used strcat to pull the exact field of data we want and extend a new column name for it

This time we do something like:

SecureScore_CL| extend ScorePrecent = round((round(properties_score_current_d, 0)*100)/properties_score_max_d, 0), SubscriptionId = strcat(split(id_s, '/')[2])

Now we have two tables with a subscriptionID column we can use to JOIN

Check out this handy visual to see how Joins and Kind= work:

My initial problems in my KQL query were do to the fact that I used join with no kind= and by default join does unique inner giving all kinds of wacky results, If you are not careful you can easily over look this in the Docs

Warning

The default join flavor, if kind is not specified, is innerunique. This is different than some other analytics products, which have inner as the default flavor. Please read carefully below to understand the differences between the different kinds and to make sure the query yields the intended results.

With this now in mind we want to get the Subscription logical name from Subscriptions_CL and produce a table with SecureScore_CL with that column so we have SubscriptionName and not just SubscriptionID, to do this we want to join on the columns that would match up on SubscriptionID:

| join kind = leftouter (Subscriptions_CL) on $left.SubscriptionId == $right.SubscriptionId

Also note we don't want default unique inner join so we specifically use | join kind = leftouter. This will bring in the columns from Subscriptions_CL table now

Our final query after the join will normalize the column names as we summarize to help with a quick visual

SecureScore_CL | extend ScorePrecent = round((round(properties_score_current_d, 0)*100)/properties_score_max_d, 0), SubscriptionId = strcat(split(id_s, '/')[2]) | join kind = leftouter (Subscriptions_CL)  on $left.SubscriptionId == $right.SubscriptionId | summarize by TimeGenerated, ScorePrecent, Subscription = displayName_s, SubscriptionId, CurrentScore = properties_score_current_d, MaxScore = properties_score_max_d

With this join and kind= we can now cover multiple days in our visualization and start trending a secure score.

Special thanks to:

@Will Brown - explaining joins and unions and lookups in great detail, and helping spot the obvious.

@Clive Watson - for the great join visuals.