-
Notifications
You must be signed in to change notification settings - Fork 34
/
graph.azcli
193 lines (182 loc) · 23.2 KB
/
graph.azcli
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
##########################################################
# Examples of Azure Graph queries and useful commands
#
# Jose Moreno, October 2021
##########################################################
# Sample queries
# See more in https://docs.microsoft.com/azure/governance/resource-graph/samples/advanced?tabs=azure-cli
# Kubernetes
query="where type=~'microsoft.containerservice/managedclusters'"
# bluehorse
query="resources | where type=='microsoft.containerservice/managedclusters' | project name, resourceType=type, resourceId=id, subscriptionId, resourceGroup, location, tags, uniqueName=name"
query="where type=='microsoft.containerservice/managedclusters' | extend nodepool = properties.agentPoolProfiles | mv-expand nodepool | project id=strcat(id, '/agentPools/', nodepool.name), name=nodepool.name, resourceGroup, subscriptionId, location, tags, zones"
query="where type=='microsoft.containerservice/managedclusters' | extend nodepool = properties.agentPoolProfiles | mv-expand nodepool | project sourceResourceId=strcat(id, '/agentPools/', nodepool.name), targetResourceId=id"
query="where type=='microsoft.containerservice/managedclusters' | extend nodepool = properties.agentPoolProfiles | mv-expand nodepool | project sourceResourceId=strcat(id, '/agentPools/', nodepool.name), targetResourceId=nodepool.vnetSubnetID"
resources
| where type =~ 'microsoft.web/sites'
| where isnotnull(properties.virtualNetworkSubnetId)
| project sourceResourceId=id, targetResourceId=properties.virtualNetworkSubnetId, criticality='Runtime'
query="where type=='microsoft.containerservice/managedclusters' | distinct id"
query="where type=='microsoft.containerservice/managedclusters' | distinct name,resourceGroup"
# Paid SKU
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = (sku.tier=='Paid') | distinct id,compliant"
# API IP filters
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = not(properties.publicNetworkAccess==true and isnull(properties.apiServerAccessProfile)) | distinct id,compliant"
# Network policy addon
query="where type=='microsoft.containerservice/managedclusters' | extend compliant=(isnotnull(properties.networkProfile.networkPolicy)) | distinct id,compliant"
# No app routing addon
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = not(isnotnull(properties.addonProfiles.httpApplicationRouting)) | distinct id,compliant"
# OMS plugin deployed
query="where type=='microsoft.containerservice/managedclusters' | extend compliant=isnotnull(properties.addonProfiles.omsagent) | distinct id,compliant"
# ACI connector plugin deployed
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = (isnotnull(properties.addonProfiles.aciConnectorLinux) and properties.addonProfiles.aciConnectorLinux.enabled==true) | distinct id,compliant"
# Using custom infra RG names
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = (properties.nodeResourceGroup \!startswith 'MC_') | distinct id,compliant"
# ?
query="where type=='microsoft.containerservice/managedclusters' | project name=name+"-"properties.agentPoolProfiles[].name"
# Using managed disks
query="where type=='microsoft.containerservice/managedclusters' | project id,pools=properties.agentPoolProfiles | mv-expand pools | project id,ManagedDisk=(pools.osDiskType=='Managed')"
# Existing User Pool
query="where type=='microsoft.containerservice/managedclusters' | project resourceGroup,name,pools=properties.agentPoolProfiles | mv-expand pools | where pools.mode=='User' | distinct name,resourceGroup"
# Using >1 pool
query="where type=='microsoft.containerservice/managedclusters' | project id,pools=properties.agentPoolProfiles | extend poolcount=array_length(pools) | extend compliant = (poolcount > 1) | distinct id,compliant"
# AZs
query="where type=='microsoft.containerservice/managedclusters' | extend compliant = isnotnull(zones) | distinct id, compliant"
query="where type=='microsoft.containerservice/managedclusters' | where properties.servicePrincipalProfile.clientId=='msi' | distinct name,resourceGroup"
query="where type=='microsoft.containerservice/managedclusters' | where isnull(properties.aadProfile) | distinct name,resourceGroup"
query="where type=='microsoft.containerservice/managedclusters' | project resourceGroup,name,pools=properties.agentPoolProfiles | mv-expand pools | project subnetId=tostring(pools.vnetSubnetID) | where isnotempty(subnetId)"
query="where type=='microsoft.network/virtualnetworks' | project resourceGroup,name,enableDdosProtection=properties.enableDdosProtection,subnets=properties.subnets | mv-expand subnets | project resourceGroup,name,enableDdosProtection,subnetId=tostring(subnets.id)"
query="Resources | where type=~'microsoft.containerservice/managedclusters' | project resourceGroup,name,pools=properties.agentPoolProfiles
| mv-expand pools
| project subnetId=tostring(pools.vnetSubnetID)
| where isnotempty(subnetId)
| join (Resources | where type=='microsoft.network/virtualnetworks'
| project resourceGroup,name,enableDdosProtection=tostring(properties.enableDdosProtection),subnets=properties.subnets
| mv-expand subnets
| project resourceGroup,name,enableDdosProtection,subnetId=tostring(subnets.id)) on subnetId
| distinct resourceGroup,name,enableDdosProtection
| where enableDdosProtection == 'false'"
# Network
# Inspect availabe types
query="resources | where type contains 'microsoft.network' | distinct type"
# Misc
query="where type=='microsoft.network/routetables' | where properties.disableBgpRoutePropagation==true | distinct name,resourceGroup"
query="resources | where type=='microsoft.network/routetables' | mvexpand properties.routes | summarize routeCount = count() by id | extend compliant = (routeCount < 360) | distinct id,compliant"
query="where type=='microsoft.network/virtualnetworkgateways' | where tolower(properties.gatewayType) == 'vpn' | project name,resourceGroup,ipcount=array_length(properties.ipConfigurations) | where ipcount==1"
query="where type=='microsoft.network/networksecuritygroups'"
query="resources | where type == 'microsoft.network/virtualnetworks' | extend addressSpace = todynamic(properties.addressSpace) | extend addressPrefix = todynamic(properties.addressSpace.addressPrefixes) | mvexpand addressSpace | mvexpand addressPrefix | project name, id, location, resourceGroup, subscriptionId, cidr = addressPrefix | extend compliant = (cidr matches regex @'^(10\.|172\.(1[6-9]|2[0-9]|3[01])\.|192\.168\.)') | project id, compliant, cidr"
query="resources | where type == 'microsoft.network/virtualnetworks' | mvexpand properties.virtualNetworkPeerings | project id, peeringName=properties_virtualNetworkPeerings.name, compliant = (properties_virtualNetworkPeerings.properties.allowVirtualNetworkAccess == True)"
query="resources | where type == 'microsoft.network/virtualnetworks' | mvexpand properties.virtualNetworkPeerings | summarize peeringcount = count() by id | extend compliant = (peeringcount < 450) | distinct id,compliant"
# - AZ-redundant VNGs
query="resources | where type=='microsoft.network/virtualnetworkgateways' | where properties.gatewayType == 'Vpn' | extend compliant = (not (tolower(properties.sku.name) contains 'az')) | distinct id, compliant"
# - Check "disable gateway propagation for RTs attached to GatewaySubnet"
query="resources | where type=='microsoft.network/virtualnetworks' | project id,resourceGroup,name,subnets=properties.subnets | mv-expand subnets | project id,resourceGroup,name,subnetName=tostring(subnets.name),routeTableId=tostring(subnets.properties.routeTable.id) | where subnetName == 'GatewaySubnet' | join kind=leftouter (Resources | where type == 'microsoft.network/routetables' | project routeTableName=name,routeTableId=id, disableBgpRoutePropagation=properties.disableBgpRoutePropagation) on routeTableId | project id,compliant = (disableBgpRoutePropagation == False or isnull(disableBgpRoutePropagation))"
# Inbound deny-any in NSGs
query="where type=='microsoft.network/networksecuritygroups' | mvexpand properties.securityRules | project id,name,ruleAction=properties_securityRules.properties.access,rulePriority=properties_securityRules.properties.priority,ruleDst=properties_securityRules.properties.destinationAddressPrefix,ruleSrc=properties_securityRules.properties.sourceAddressPrefix,ruleProt=properties_securityRules.properties.protocol,ruleDirection=properties_securityRules.properties.direction,rulePort=properties_securityRules.properties.destinationPortRange | summarize StarDenies=countif(ruleAction=='Deny' and ruleDst=='*' and ruleSrc=='*' and ruleProt=='*' and rulePort=='*') by id,tostring(ruleDirection) | where ruleDirection == 'Inbound' | project id,compliant=(StarDenies>0)"
# NSGs with custom rules (not relying on default config)
query="where type=='microsoft.network/networksecuritygroups' | extend compliant=(array_length(properties.securityRules)==0) | project name,id,compliant"
# Union of both
query="resources | where type=='microsoft.network/networksecuritygroups' | mvexpand properties.securityRules | project id,name,ruleAction=properties_securityRules.properties.access,rulePriority=properties_securityRules.properties.priority,ruleDst=properties_securityRules.properties.destinationAddressPrefix,ruleSrc=properties_securityRules.properties.sourceAddressPrefix,ruleProt=properties_securityRules.properties.protocol,ruleDirection=properties_securityRules.properties.direction,rulePort=properties_securityRules.properties.destinationPortRange | summarize StarDenies=countif(ruleAction=='Deny' and ruleDst=='*' and ruleSrc=='*' and ruleProt=='*' and rulePort=='*') by id,tostring(ruleDirection) | where ruleDirection == 'Inbound' | project id,compliant=(StarDenies>0)
| union (resources | where type=='microsoft.network/networksecuritygroups' | where array_length(properties.securityRules)==0 | extend compliant=false | project id,compliant)"
# Subnets without NSGs
query="Resources | where type=='microsoft.network/virtualnetworks' | project id,resourceGroup,name,subnets=properties.subnets | mv-expand subnets | project id,name,subnetName=subnets.name,subnetNsg=subnets.properties.networkSecurityGroup | where not (subnetName in ('GatewaySubnet', 'AzureFirewallSubnet', 'RouteServerSubnet', 'AzureBastionSubnet')) | extend compliant = isnotnull(subnetNsg)"
# Subnets without UDR
query="Resources | where type=='microsoft.network/virtualnetworks' | project id,resourceGroup,name,subnets=properties.subnets | mv-expand subnets | project id,name,subnetName=tostring(subnets.name),subnetRT=subnets.properties.routeTable.id | where not (subnetName in ('GatewaySubnet', 'AzureFirewallSubnet', 'RouteServerSubnet', 'AzureBastionSubnet')) | extend compliant = isnotnull(subnetRT) | distinct id, compliant, subnetName"
# Subnets without UDR not peered to VWAN
query="resources | where type=='microsoft.network/virtualnetworks' | project id,resourceGroup,name,subnets=properties.subnets | mv-expand subnets | project id,name,subnetId=tostring(subnets.id), subnetName=tostring(subnets.name),subnetRT=subnets.properties.routeTable.id | where not (subnetName in ('GatewaySubnet', 'AzureFirewallSubnet', 'RouteServerSubnet', 'AzureBastionSubnet')) | extend hasRT = isnotnull(subnetRT) | distinct id, hasRT, subnetId | join kind=fullouter (resources | where type == 'microsoft.network/virtualnetworks' | mvexpand properties.virtualNetworkPeerings | extend isVWAN=(tolower(split(properties_virtualNetworkPeerings.name, '_')[0]) == 'remotevnettohubpeering') | mv-expand properties.subnets | project id, isVWAN, name, subnetId=tostring(properties_subnets.id), subnetName=tostring(properties_subnets.name) | summarize PeeredToVWAN=max(isVWAN) by id, subnetId | project id, subnetId, isVWANpeer = (PeeredToVWAN == true)) on subnetId | project id=iff(isnotempty(id), id, id1), subnetId=iff(isnotempty(subnetId), subnetId, subnetId1), hasRT, isVWANpeer | extend compliant = (hasRT==true or isVWANpeer==true) | distinct id, subnetId, compliant"
# AFD WAF enabled and in prevention mode
query="Resources | where type == 'microsoft.network/frontdoorwebapplicationfirewallpolicies' | project policyName=name, policyId=id,policySku=sku.name, links=properties.securityPolicyLinks, enabledState=properties.policySettings.enabledState, mode=properties.policySettings.mode | mvexpand links | extend securityPolicy=links.id | extend securityPolicyParts=split(securityPolicy, '/') | extend profileId=strcat_array(array_slice(securityPolicyParts, 0, -3), '/') | project id=profileId, compliant=((enabledState=='Enabled') and (mode=='Prevention')), enabledState, mode"
# AFD
query="Resources | where type == 'microsoft.network/frontdoorwebapplicationfirewallpolicies'"
query="Resources | where type =='microsoft.cdn/profiles/afdendpoints'"
query="Resources | where type =='microsoft.cdn/profiles'"
# Azure Bastion H&S
query="resources | where type=='microsoft.network/virtualnetworks' | project id,resourceGroup,name,subnets=properties.subnets | mv-expand subnets | project id,name,subnetName=subnets.name | summarize GatewaySubnets=countif(subnetName=='GatewaySubnet'),BastionSubnets=countif(subnetName=='AzureBastionSubnet') by id,name | extend compliant = (GatewaySubnets==0 or BastionSubnets==1)"
# Azure Bastion VWAN: no vnet connections over ARG!!! BTW, no route tables either
query="resources | where type=='microsoft.network/virtualhubs'"
# Azure Firewall
query="resources | where type=='microsoft.network/firewallpolicies' | extend compliant = (properties.sku.tier == 'Premium') | distinct id,compliant"
query="resources | where type=='microsoft.network/firewallpolicies' | extend compliant = (properties.threatIntelMode=='Deny') | distinct id,compliant"
query="resources | where type=='microsoft.network/firewallpolicies' | extend compliant = (properties.dnsSettings.enableProxy == true) | distinct id,compliant"
query="resources | where type=='microsoft.network/firewallpolicies' | extend compliant = (properties.intrusionDetection.mode == 'Deny') | project id, compliant"
query="resources | where type=='microsoft.network/virtualnetworks' | project id,subnets=properties.subnets | mv-expand subnets | project id, subnetName = subnets.name, subnetPrefix = subnets.properties.addressPrefix | extend subnetPrefixLength = split(subnetPrefix, '/')[1] | where subnetName == 'AzureFirewallSubnet' | extend compliant = (subnetPrefixLength == 26) | distinct id, compliant"
# VWAN: no vnet connections over ARG!!! BTW, no route tables either
query="resources | where type=='microsoft.network/virtualhubs'"
query="resources | where type=='microsoft.network/virtualwans'"
query="resources | where type=='microsoft.network/virtualhubs' | extend compliant = (properties.allowBranchToBranchTraffic == true) | project id, compliant"
query="resources | where type=='microsoft.network/virtualhubs' | extend compliant = (properties.hubRoutingPreference == 'ASPath') | project id, compliant"
query="resources | where type=='microsoft.network/virtualhubs' | extend addressPrefixLength=toint(split(properties.addressPrefix, '/')[1]) | extend compliant = (addressPrefixLength >= 22 and addressPrefixLength <= 23)"
query="resources | where type=='microsoft.network/virtualhubs' | extend compliant = isnotnull(properties.azureFirewall.id) | project id, compliant"
# Trying to get VNets peered to VWAN from the VNet side
query="resources | where type == 'microsoft.network/virtualnetworks' | mvexpand properties.virtualNetworkPeerings | extend isVWAN=(tolower(split(properties_virtualNetworkPeerings.name, '_')[0]) == 'remotevnettohubpeering') | summarize PeeredToVWAN=max(isVWAN) by id | project id, compliant = (PeeredToVWAN == true)"
# Subnets of the VNets peered to VWAN
query="resources | where type == 'microsoft.network/virtualnetworks' | mvexpand properties.virtualNetworkPeerings | extend isVWAN=(tolower(split(properties_virtualNetworkPeerings.name, '_')[0]) == 'remotevnettohubpeering') | mv-expand properties.subnets | project id, isVWAN, name, subnetId=tostring(properties_subnets.id), subnetName=tostring(properties_subnets.name) | summarize PeeredToVWAN=max(isVWAN) by id, subnetId | project id, subnetId, compliant = (PeeredToVWAN == true)"
# App Gateway
query="resources | where type=='microsoft.network/applicationgateways' | extend compliant = (properties.sku.tier == 'WAF_v2') | distinct id,compliant"
query="resources | where type=='microsoft.network/applicationgateways' | extend compliant = (isnotnull(properties.autoscaleConfiguration)) | distinct id,compliant"
query="resources | where type=='microsoft.network/applicationgateways' | extend subnetId = tostring(properties.gatewayIPConfigurations[0].properties.subnet.id) | project id, subnetId | join (resources | where type=='microsoft.network/virtualnetworks' | project id,subnets=properties.subnets | mv-expand subnets | project id, subnetId = tostring(subnets.id), subnetPrefixLength = split(subnets.properties.addressPrefix, '/')[1]) on subnetId | extend compliant = (subnetPrefixLength <= 26) | distinct id,compliant"
query="resources | where type=='microsoft.network/virtualnetworks' | project id,subnets=properties.subnets | mv-expand subnets | project id, subnetId = subnets.id, subnetPrefix = subnets.properties.addressPrefix"
query="resources | where type=='microsoft.network/applicationgateways' | mvexpand properties.backendAddressPools | project name, bepoolName=tostring(properties_backendAddressPools.name), backends=properties_backendAddressPools.properties.backendAddresses | mv-expand backends | summarize backendCount=count() by name,bepoolName | where backendCount==1"
# Load balancers
query="resources | where type=='microsoft.network/loadbalancers' | extend countOutRules=array_length(properties.outboundRules) | extend compliant = (countOutRules == 0) | distinct id,compliant"
query="resources | where type=~'microsoft.network/loadbalancers' | mvexpand rule=properties.loadBalancingRules | project id, ruleName=rule.name, ruleLoadDistribution=tolower(rule.properties.loadDistribution) | summarize rulesWithPersistency=countif(ruleLoadDistribution != 'default') by id"
# ExpressRoute
query="resources | where type=='microsoft.network/expressroutecircuits' | extend compliant = (tolower(sku.family) == 'metereddata' or tolower(sku.tier) == 'local') | distinct id,compliant"
# query="resources | where type=='microsoft.network/connections' | where properties.connectionType == 'ExpressRoute' | project id, gwid=tostring(properties.virtualNetworkGateway1.id), circuitid=tostring(properties.peer.id) | join (resources | where type=='microsoft.network/expressroutecircuits' | project circuitid=tostring(id), erlocation=properties.serviceProviderProperties.peeringLocation) on circuitid | join (resources | where type=='microsoft.network/virtualnetworkgateways' | where properties.gatewayType == 'ExpressRoute' | project gwid=tostring(id),location) on gwid | join (externaldata(er_location:string, local_regions:string) @'https://raw.githubusercontent.com/Azure/review-checklists/erjosito-expressroute/helper/erlocal.json' with(format='multijson', ingestionMapping='[{\"Column\":\"er_location\",\"Properties\":{\"Path\":\"\$['er_location']\"}},{\"Column\":\"local_regions\",\"Properties\":{\"Path\":\"\$['local_regions']\"}}]') on er_location"
query="resources | where type=='microsoft.network/connections' | where properties.connectionType == 'ExpressRoute' | project id, gwid=tostring(properties.virtualNetworkGateway1.id), circuitid=tostring(properties.peer.id) | join (resources | where type=='microsoft.network/expressroutecircuits' | project circuitid=tostring(id), circuitsku=sku.tier) on circuitid | project id=gwid, compliant = (circuitsku == 'Local') | summarize compliant=max(compliant) by id"
# Count of locations connected to each gateway
query="resources | where type=='microsoft.network/connections' | where properties.connectionType == 'ExpressRoute' | project cxId=id, gwId=tostring(properties.virtualNetworkGateway1.id), circuitId=tostring(properties.peer.id) | join (resources | where type=='microsoft.network/expressroutecircuits' | project circuitId=tostring(id), circuitLocation=tostring(properties.serviceProviderProperties.peeringLocation)) on circuitId | distinct gwId, circuitLocation | summarize countErLocations=count() by id=gwId | extend compliant = (countErLocations >= 2)"
#
query="resources | take 1 | join (externaldata(er_location:string, local_regions:string) @'https://raw.githubusercontent.com/Azure/review-checklists/erjosito-expressroute/helper/erlocal.json' with(format='multijson', ingestionMapping='[{\"Column\":\"er_location\",\"Properties\":{\"Path\":\"\$['er_location']\"}},{\"Column\":\"local_regions\",\"Properties\":{\"Path\":\"\$['local_regions']\"}}]')"
# ingestion mapping as extracted from ADX:
# '[{"column":"er_location", "Properties":{"Path":"$[\'er_location\']"}},{"column":"local_regions", "Properties":{"Path":"$[\'local_regions\']"}}]'
# To Do
# AFD with single origin, no probes
# AFD: same FQDN as origin
# AzFW
# See available types
query="distinct type"
# Advanced properties
query="Resources | where type == 'microsoft.compute/virtualmachines' | summarize count() by tostring(properties.extended.instanceView.powerState.code)
# Run query
az graph query -q "$query" --query data -o tsv
#############
# Other #
#############
# Create array with multiple tests
query_list=()
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant = (sku.tier=='Paid') | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant=(isnotnull(properties.networkProfile.networkPolicy)) | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant = not(isnotnull(properties.addonProfiles.httpApplicationRouting)) | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant=isnotnull(properties.addonProfiles.omsagent) | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant = (isnotnull(properties.addonProfiles.aciConnectorLinux) and properties.addonProfiles.aciConnectorLinux.enabled==true) | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant = (properties.nodeResourceGroup \!startswith 'MC_') | distinct id,compliant")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | project id,pools=properties.agentPoolProfiles | mv-expand pools | project id,ManagedDisk=(pools.osDiskType=='Managed')")
query_list+=("resources | where type=='microsoft.containerservice/managedclusters' | extend compliant = isnotnull(zones) | distinct id, compliant")
# Merge queries into one
i=0
uber_query=''
for query in $query_list; do
modified_query="${query} | extend query='Query$i'"
echo "Query ${i}: ${modified_query}"
# If this is not the first query, we need to append a union statement
if [[ "$i" != "0" ]]; then
uber_query+=" | union ("
fi
# Append query
uber_query+=$modified_query
# Close union statement
if [[ "$i" != "0" ]]; then
uber_query+=")"
fi
# Next
i=$((i + 1))
done
echo "Running uber query:"
echo $uber_query
az graph query -q "$uber_query" --query data -o table
# ERROR:
# {
# "code": "TooManyUnionLegs",
# "message": "The number of tabular expressions involved in union operators is 8, and has exceeded the limit of 3. Please consider involving less tabular expressions in union."
# }