Search code examples
kqlazure-resource-graphdual-table

Is there a way to emulate a table in the Azure Resource Explorer reduced version of Kusto?


Question

In Kusto, is there a way to select constant values without having a source table; e.g. in a similar way to how we could use the DUAL table in Oracle (or do a SELECT without a FROM in many other query languages)?

E.g. () | project colA = 'hello', colB = 'world'... though this doesn't work.

NB: Specifically I'm looking for a solution that works with reduced version of Kusto that's available to Azure Resource Explorer.

Context

Note: this is an XY problem where the above question would allow me to implement a workaround... Below is the real issue I'm trying to solve.

I have a query to find whether a given CIDR overlaps with existing VNets in Azure, so we can easily test new CIDRs before they're allocated; or can use this to find which VNets a given IP sits under:

resources 
| where type =~ 'Microsoft.Network/virtualNetworks'
| project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes']
| mv-expand addressPrefixes
| extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
| extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
| extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
| project-away cidrSplit
// 10.11.12.13/28 defined in both rows below:
| where firstIpVal <= 10 * 16777216 + 11 * 65536 + 12 * 256 + 13 + pow(2,32-28)-1
  and lastIpVal >= 10 * 16777216 + 11 * 65536 + 12 * 256 + 13
| project subscriptionId, resourceGroup, name, addressPrefixes, firstIpVal, lastIpVal
| order by firstIpVal, lastIpVal

However, it's hard to update the above query for a differnt CIDR / not easy for someone reading it to see what CIDR we're querying.

I'd rather it were something like below; but variables don't work in Azure Resource Explorer:

let newCidr = "10.11.12.13/28";

let newCidrSplit = array_concat(split(split(newCidr, '/')[0],'.'), split(split(newCidr, '/')[1],'x'))

let newCidrFirst = toint(newCidrSplit[0]) * 16777216 + toint(newCidrSplit[1]) * 65536 + toint(newCidrSplit[2]) * 256 + toint(newCidrSplit[3]);

let newCidrLast = newCidrFirst + pow(2,32-newCidrSplit[4])-1;

resources 
| where type =~ 'Microsoft.Network/virtualNetworks'
| project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes']
| mv-expand addressPrefixes
| extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
| extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
| extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
| project-away cidrSplit
| where firstIpVal <= newCidrLast and lastIpVal >= newCidrFirst
| project subscriptionId, resourceGroup, name, addressPrefixes, firstIpVal, lastIpVal
| order by firstIpVal, lastIpVal

So my hope is there's some way to get the "variable" without defining a variable; e.g. if there were a dual table (a table that's guaranteed to always have exactly 1 row) I could do this (still a bit hacky, but at least it's easy for anyone to amend the IP beinng queried):

dual // i.e. a table with one row, where we don't really care about the row's contents
| project testCidr = "10.11.12.13/28" // set the "variable" here; one place in an easy to read format, near the top so it's easy to spot
| extend testCidrSplit = array_concat(split(split(testCidr, '/')[0],'.'), split(split(testCidr, '/')[1],'x'))
| extend testCidrFirstIp = toint(testCidrSplit[0]) * 16777216 + toint(testCidrSplit[1]) * 65536 + toint(testCidrSplit[2]) * 256 + toint(testCidrSplit[3])
| extend testCidrLastIp = testCidrFirstIp + pow(2,32-testCidrSplit[4])-1
| extend joinhack = 1
| join kind = inner
(
    resources 
    | where type =~ 'Microsoft.Network/virtualNetworks'
    | project id, subscriptionId, resourceGroup, name, addressPrefixes = properties['addressSpace'].['addressPrefixes'], joinhack = 1
    | mv-expand addressPrefixes
    | extend cidrSplit = array_concat(split(split(addressPrefixes, '/')[0],'.'), split(split(addressPrefixes, '/')[1],'x'))
    | extend firstIpVal = toint(cidrSplit[0]) * 16777216 + toint(cidrSplit[1]) * 65536 + toint(cidrSplit[2]) * 256 + toint(cidrSplit[3])
    | extend lastIpVal = firstIpVal + pow(2,32-cidrSplit[4])-1
    | project-away cidrSplit
)
on joinhack
| where firstIpVal <= testCidrLastIp and lastIpVal >= testCidrfirstIp
| project subscriptionId, resourceGroup, name, addressPrefixes, firstIpVal, lastIpVal
| order by firstIpVal, lastIpVal

Note: I can of course cheat and replace dual in the above with a query for a subscription (if we're querying we can assume there's at least one) then use limit to just fetch one... but this is very hacky / I'm hoping for a solution from the language rather than having to hack it.

resourcecontainers
| where type == "microsoft.resources/subscriptions"
| limit 1
| project colA = 'hello', colB = 'world'

Solution

  • In Kusto, is there a way to select constant values without having a source table. E.g. () | project colA = 'hello', colB = 'world', though this doesn't work.

    In kusto you can use the print operator.

    for example:

    print colA = 'hello', colB = 'world'

    However, that is not available in Azure Resource Graph's restricted version of KQL.

    As such, workarounds such as your limit 1 approach are currently the only way to achieve this.