how to handle empty results in KQL query?-CodePudding

trying to write KQL query counting resources in Azure subscriptions. I'm having problem with subs with no resources since nothing is return. so i want to merge [left] all subscription with [right] all resources:

$queryAllOuter = "resourceContainers 
    | where type =~ 'microsoft.resources/subscriptions'
    | project subscriptionId, subName=name
    | join kind=leftouter resources on subscriptionId
    | summarize nrOfResources=count() by subscriptionId,subName
    | sort by nrOfResources asc"

the problem is that empty subs are summarized as having 1 resource. when i checked the records before summarize, such empty record looks like that:

subscriptionId : xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx subName : Visual Studio Professional Subscription subscriptionId1 : name : id : type : tenantId : kind : location : resourceGroup : managedBy : sku : plan : properties : tags : identity : zones : extendedLocation : ResourceId :

how to produce some output on null returns?

tried different combinations of using iff, isnull, extend - but it's IMHO deeper issue ... no idea how to approach it

CodePudding user response：

My preferred way would be to first count and then join.

resourceContainers 
| where type =~ 'microsoft.resources/subscriptions'
| project subscriptionId, subName=name
| join kind=leftouter 
  (
    resources
    | summarize nrOfResources = count() by subscriptionId
  ) on subscriptionId
| extend nrOfResources = coalesce(nrOfResources, 0)
| sort by nrOfResources asc

You can also join and then count those rows where the subscriptionId from the right side is not empty.

resourceContainers 
| where type =~ 'microsoft.resources/subscriptions'
| project subscriptionId, subName=name
| join kind=leftouter resources on subscriptionId
| summarize nrOfResources=countif(isnotempty(subscriptionId1)) by subscriptionId,subName
| sort by nrOfResources asc