Describe Azure/ResourceGraphExplorer here.
* get all VNG's with detals having basic IP's
resources
| where type =~ 'microsoft.network/virtualnetworkgateways'
| where isnotempty(properties.ipConfigurations)
| mv-expand ipConfig = properties.ipConfigurations
| extend publicIpId = tolower(tostring(ipConfig.properties.publicIPAddress.id))
| extend lowercaseGatewayId = tolower(id)
// 1. Join with Public IPs to get the actual Numeric IP address and SKU
| join kind=leftouter (
resources
| where type =~ 'microsoft.network/publicipaddresses'
| project publicIpId = tolower(id), publicIpSku = sku.name, publicIpAddress = properties.ipAddress
) on publicIpId
| where publicIpSku =~ 'Basic'
// 2. Join with Gateway Connections to pull structural connection data
| join kind=leftouter (
resources
| where type =~ 'microsoft.network/connections'
| extend lowercaseGatewayId = tolower(tostring(properties.virtualNetworkGateway1.id))
| extend connectionDetails = pack("Name", name, "Status", properties.connectionStatus, "Type", properties.connectionType, "Peer", coalesce(properties.localNetworkGateway2.id, properties.virtualNetworkGateway2.id, properties.peer.id))
| summarize connectionsList = make_list(connectionDetails) by lowercaseGatewayId
) on lowercaseGatewayId
// 3. Join with Subscriptions to populate clean friendly names
| join kind=leftouter (
resourcecontainers
| where type =~ 'microsoft.resources/subscriptions'
| project subscriptionId, subscriptionName = name
) on subscriptionId
| project subscriptionName, subscriptionId, resourceGroup, name, gatewayType = properties.gatewayType, gatewaySku = properties.sku.name, publicIpSku, publicIpAddress, connectionsList