Query with join operator returns empty response

[mykolaichuk]

Hello,

I have the following query. The aim is to see the public IP which is attached to the AKS LB, but this IP is not a cluster public IP. It's used for ingress. I thought to get this data using join operator and to join these tables by the resource group name (AKS resources RG has the same name as public IP RG). But when I run this query I receive an empty response. Please, help to solve the issue. Note, that the query has been edited in order not to overcomplicate the query and not to show some internal data.

Resources
| where type == 'microsoft.containerservice/managedclusters'
| mv-expand properties.agentPoolProfiles
| where properties_agentPoolProfiles.name == "pool01"
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| where subscription name == 'Staging'
| extend NodeRG = tostring(properties.nodeResourceGroup)
| project subscriptionName, name, location, VMSize = properties_agentPoolProfiles.vmSize, NodePoolName = properties_agentPoolProfiles.name, NodeCount = properties_agentPoolProfiles.count, NodeRG
| join (
    Resources
    | where type =~ 'Microsoft.Network/publicIPAddresses'
    | where name =~ 'Ingress-PIP'
    | extend PIPRG = tostring(resourceGroup)
    | project IngrPIP = properties.ipAddress, PIPRG)
    on $left.NodeRG == $right.PIPRG
| project subscriptionName, name, location, VMSize = properties_agentPoolProfiles.vmSize, NodePoolName = properties_agentPoolProfiles.name, NodeCount = properties_agentPoolProfiles.count, IngrPIP
| sort by name asc

[sloutsky]

Hi Vadym, This code-base is for KQL parser - less about queries implementation. The best would be posting the question at StackOverflow, and tag with 'KQL'. When you do, please share data sample using datatable() operator to demonstrate the problem.

[mykolaichuk]

I found out what was the issue. The following query will work Posting this just in case someone will face the same issue.

Resources
| where type == 'microsoft.containerservice/managedclusters'
| mv-expand properties.agentPoolProfiles
| where properties_agentPoolProfiles.name == "pool01"
| join kind=inner (
    resourcecontainers
    | where type == 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName = name)
    on subscriptionId
| where subscription name == 'Staging'
| extend NodeRG = tolower(tostring(properties.nodeResourceGroup))
| project subscriptionName, name, location, VMSize = properties_agentPoolProfiles.vmSize, NodePoolName = properties_agentPoolProfiles.name, NodeCount = properties_agentPoolProfiles.count, NodeRG
| join (
    Resources
    | where type =~ 'Microsoft.Network/publicIPAddresses'
    | where name =~ 'Ingress-PIP'
    | extend PIPRG = tolower(tostring(resourceGroup))
    | project IngrPIP = properties.ipAddress, PIPRG)
    on $left.NodeRG == $right.PIPRG
| project subscriptionName, name, location, VMSize = properties_agentPoolProfiles.vmSize, NodePoolName = properties_agentPoolProfiles.name, NodeCount = properties_agentPoolProfiles.count, IngrPIP
| sort by name asc