SOLVED

KQL query for vnet peering count and storage public Access

%3CLINGO-SUB%20id%3D%22lingo-sub-2465987%22%20slang%3D%22en-US%22%3EKQL%20query%20for%20public%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2465987%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20get%20the%20count%20of%20Vnet%20peering%20from%20specific%20subscriptions%20and%20storage%20container%20public%20access%2C%20Can%20someone%20please%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EDev%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2469818%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20query%20for%20vnet%20peering%20count%20and%20storage%20public%20Access%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2469818%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084141%22%20target%3D%22_blank%22%3E%40deb0093%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20%3CSTRONG%3Enetwork%20peering%3C%2FSTRONG%3E%20you%20could%20try%20something%20like%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3Eresources%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btype%26nbsp%3B%3D~%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E'microsoft.network%2FvirtualNetworks'%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bmv-expand%26nbsp%3Bpeering%3Dproperties%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EvirtualNetworkPeerings%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bpeering%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3Eproperties%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EremoteVirtualNetwork%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3Eid%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%2Fsubscriptions%2Fxxx-xxx-xxxx-xxxx%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EFor%20%3CSTRONG%3Eblobs%20with%20public%20access%3C%2FSTRONG%3E%3A%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EResources%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Btype%26nbsp%3B%3D~%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E'microsoft.storage%2Fstorageaccounts'%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3Bproperties%3C%2FSPAN%3E%3CSPAN%3E.%3C%2FSPAN%3E%3CSPAN%3EallowBlobPublicAccess%26nbsp%3B%3D%3D%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3Etrue%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Team,

 

I am looking to get the count of Vnet peering from specific subscriptions and storage container public access through KQL, Can someone please help me.

 

Regards

Dev

9 Replies

Hi @deb0093 ,

 

For network peering you could try something like:

resources
where type =~ 'microsoft.network/virtualNetworks'
| mv-expand peering=properties.virtualNetworkPeerings
where peering.properties.remoteVirtualNetwork.id contains  "/subscriptions/xxx-xxx-xxxx-xxxx"
 
For blobs with public access:
Resources
where type =~ 'microsoft.storage/storageaccounts'
where properties.allowBlobPublicAccess == true 
 

@David Pazdera ,

 

resources
where type =~ 'microsoft.network/virtualNetworks'
| mv-expand peering=properties.virtualNetworkPeerings
where peering.properties.remoteVirtualNetwork.id contains  "/subscriptions/xxx-xxx-xxxx-xxxx", Hope the "xxx-xxx-xxxx-xxxx" meant here as tenant id?

If I set my powershell to query 
$subscription = Get-AzSubscription -TenantId "Teanant-id" | where-object{$_.Name -like '*-required subscriptionname-*'}
$subscription | Set-AzContext

And after that If I run the Search-AzGraph queries from powershell, will that work for specific subscriptions as a set above?

Hi @deb0093,

 

Actually, the xxx-xxx-xxx-xxx string is a placeholder for a subscription ID (not a tenant ID). You said you wanted to query all VNET peerings coming from a particular subscription. You simply provide a subscription ID directly in the KQL query (if it's static).

 

The easiest way to test it is by using Azure Resource Graph Explorer directly in the Portal, where you select 1-n subscriptions from the drop-down as a scope for your query (i.e. subscriptions, where you want to look for peerings) and run the query (after you replace xxx-xxx... string with an actual subID you are interested in).

 

When you see it's working, you can switch to PowerShell or CLI to get the data programmatically.

 

I hope this answers your question.

I have tried to get run the query as single subscription selected :

resources
| where type =~ 'microsoft.network/virtualNetworks'
| mv-expand peering=properties.virtualNetworkPeerings
| where peering.properties.remoteVirtualNetwork.id contains "/subscriptions/subscription -id"

but it gives no result after running the query from Resource Group Explorer but when I see on portal peering do exist for that particular subscription id.

@deb0093 

 

Just to be clear on the scenario:

  • let's say you have VNETA and VNETB in subscriptionA
  • both VNETs are peered to VNETC that resides in subscriptionB

If you want the query to return two entries - VNETA and VNETB, you need to:

  • select subscriptionA in the Resource Graph Explorer as a scope for your query
  • write subscriptionB ID to the query itself as a replacement for xxx-xxx-xxx-xxx string

The query works in my environment. Perhaps if you send me a screenshot with the Graph Explorer, the query you tried and the result, I could look into it.

@David Pazdera 

 

May be I do not have access to Subscription B that's why no data, lets say I have Vnet peering in Subscription A  where I have access and I just would like to see the vnet peering names from that subscription , how to get that in KQL. I am attaching the image file just for reference.

best response confirmed by deb0093 (Occasional Contributor)
Solution

@deb0093 

 

Are you trying to get peering names or IDs of VNets the virtual networks you have access to are peered with? Or both?

 

Try this query, it should give you both properties and only list VNets that have some peering relationship:

 

resources
where type =~ 'microsoft.network/virtualNetworks'
| mv-expand peering=properties.virtualNetworkPeerings
where notempty(peering)
project vnetId = id, vnetName = name, peeringName=tostring(peering.name), peeredVnetId=tostring(peering.properties.remoteVirtualNetwork.id)