KQL is my new SQL

As i continue on my cloud journey i find the odd occasion where something i'm working with throws me right back to my days as a DBA working with Oracle (other relational database are available.....).

This has never been more true than when i talk about KQL (yes KQL , that's not a typo)

KQL or "Kusto Query Language" to give it it's full name is the language used to work with a lot of the data sources Azure provides - everything from log analytics, to Azure Sentinel to Azure resource explorer - all of these data sources are in the same underlying format which we can then access using KQL.

Now I'm not going to give a full overview here of everything that can be done  - you can read the Microsoft docs for that - instead what I'm going to do is just give you a simple example and then explain what that is doing translating the KQL into what that would look like in SQL.

So here is the 'raw' KQL code

 (Resources |where type == "microsoft.web/sites" | project name,resourceGroup=toupper(resourceGroup),sub=toupper(subscriptionId)
| join kind=inner (ResourceContainers |where type=='microsoft.resources/subscriptions/resourcegroups' |project name=toupper(name),tags.IteraplanID,coalesce(tags.Owner,tags.owner)) on $left.resourceGroup==$right.name)
| join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub

or as a nicer picture this

When run it outputs this dataset (with lots more rows of course):

Which i do appreciate when redacted is actually pretty useless as a picture.

What it's actually showing is a 'CMDB' type query where i take information from 3 sources - subscriptions/resource groups/websites and join then together so i can see all the detail in one place.

In our case we are tagging everything at the resource group level (and not on every resource) - so to know which person this belongs to we need information from the parent resource group to supplement what we have a just the resource group level. We further want to identify the subscription (by name) that this belongs to.

So to breakdown the query a little more

This first section just fetches data from the 'Resources' datasource - which i'm just considering as a table really, we then only want the 'rows' that are about websites and we only want to display the name,resourcegroup and subscription id columns from that

 (Resources |where type == "microsoft.web/sites" | project name,resourceGroup=toupper(resourceGroup),sub=toupper(subscriptionId)

The next line then joins to the resourcegroups table and we want just the rg name, the iteraplan tag and the owner tag columns (note my use of the coalesce function as the column names are case sensitive and our tags are not in consistent case and have 2 varieties- we join to that on the resource group name

| join kind=inner (ResourceContainers |where type=='microsoft.resources/subscriptions/resourcegroups' |project name=toupper(name),tags.IteraplanID,coalesce(tags.Owner,tags.owner)) on $left.resourceGroup==$right.name)

We finally join to the subscriptions table where we just want the name and id of the subscription - we do that join on the subscription id

| join kind=leftouter (ResourceContainers | where type=='microsoft.resources/subscriptions' | project SubName=name,sub=toupper(subscriptionId)) on $left.sub==$right.sub


In other words this query would look something like this in Oracle style SQL (I'm not translating into ANSI as i can never remember the join syntax after 20 years doing it the oracle way :-))

Select w.website_name,r.resourcegroup,s.subscription,coalesce(r."owner",r."Owner") as owner, iteraplanid
from
subscriptions a, resourcegroup r, websites w
where w.resourcegroup=r.resourcegroup
and s.subid=r.subid

Which i think will be much more familiar to my DBA colleagues

There is a while load of stuff to read up here and this is quite a nice doc on some of the KQL vs SQL comparisons

https://docs.microsoft.com/en-us/azure/kusto/query/sqlcheatsheet

The use of this stuff seems to be getting more and more in the Azure space and there are whole lot of visuals it can do also. This really looks like an interesting area to get involved in and prior SQL knowledge is very useful - it's just learning a new syntax.....