【Azure 环境】Azure Resource Graph Explorer 中实现动态数组数据转换成多行记录模式 – mv-expand

问题描述

想对Azure中全部VM的NSG资源进行收集，如果只是查看一个VM的NSG设定，可以在门户页面中查看表格模式，但是如果想把导出成表格，可以在Azure Resource Graph Explorer中查找到资源，但是，它的格式是JSON数组，在一行中显示，那么如何把一行数据中的数组转换成多行记录(提高可读性)呢？

VM NSG门户显示为表格模式：

Azure Resource Graph Explorer中显示为JSON 数组：

 

问题解答

在Azure Resource Graph Explorer 中，所使用的Kusto Query 语言中，有一个运算符 :  mv-expand （Expands multi-value dynamic arrays or property bags into multiple records.） ， 它可以将多值动态数组或属性包扩展为多个记录。

如把值

1,   [10,20] 2,   [a,b]

两行数据转换4行数据：

1， 10 1， 20 2， a 2， b

详细的解说可以参考MV-EXPAND文档(https://docs.microsoft.com/zh-cn/azure/data-explorer/kusto/query/mvexpandoperator#examples)。

 

回归到本文问题解答，是列举出NSG Rule。

首先，通过Resource Graph Explorer获取到全部的NSG信息：

resources | where type =~ "microsoft.network/networksecuritygroups" 

因为NSG Rules的结果是JSON数组格式，所以需要对它进行转换(MV-EXPAND)。然后，通过extend方式获取到Properties中的属性值：

resources | where type =~ "microsoft.network/networksecuritygroups" and subscriptionId =="a9dc7515-7692-4316-9ad4-762f383eec10" |mv-expand rules=properties.securityRules |extend direction=tostring(rules.properties.direction) |extend priority=toint(rules.properties.priority) |extend rule_name = rules.name |extend nsg_name = name |extend description=rules.properties.description |extend destination_prefix=iif(rules.properties.destinationAddressPrefixes=='[]', rules.properties.destinationAddressPrefix, strcat_array(rules.properties.destinationAddressPrefixes, ",")) |extend destination_asgs=iif(isempty(rules.properties.destinationApplicationSecurityGroups), '', strcat_array(parse_json(rules.properties.destinationApplicationSecurityGroups), ",")) |extend destination=iif(isempty(destination_asgs), destination_prefix, destination_asgs) |extend destination=iif(destination=='*', "Any", destination) |extend destination_port=iif(isempty(rules.properties.destinationPortRange), strcat_array(rules.properties.destinationPortRanges,","), rules.properties.destinationPortRange) |extend source_prefix=iif(rules.properties.sourceAddressPrefixes=='[]', rules.properties.sourceAddressPrefix, strcat_array(rules.properties.sourceAddressPrefixes, ",")) |extend source_asgs=iif(isempty(rules.properties.sourceApplicationSecurityGroups), "", strcat_array(parse_json(rules.properties.sourceApplicationSecurityGroups), ",")) |extend source=iif(isempty(source_asgs), source_prefix, tostring(source_asgs)) |extend source=iif(source=='*', 'Any', source) |extend source_port=iif(isempty(rules.properties.sourcePortRange), strcat_array(rules.properties.sourcePortRanges,","), rules.properties.sourcePortRange) |extend action=rules.properties.access |extend subnets = strcat_array(properties.subnets, ",") |project resourceGroup, nsg_name, rule_name, subnets, direction, priority, action, source, source_port, destination, destination_port, description, subscriptionId, id |sort by resourceGroup asc, nsg_name, direction asc, priority asc

获取到的结果为：

 

 

 

######################################################################################################

附录一 ： NSG Rules 中  securityRules 的格式为：

"securityRules": [     {         "properties": {             "provisioningState": "Succeeded",             "destinationAddressPrefixes": [],             "destinationAddressPrefix": "VirtualNetwork",             "sourceAddressPrefixes": [],             "destinationPortRanges": [],             "destinationPortRange": "*",             "sourceAddressPrefix": "VirtualNetwork",             "sourcePortRanges": [],             "sourcePortRange": "*",             "priority": 65000,             "protocol": "*",             "direction": "Inbound",             "access": "Allow",             "description": "Allow inbound traffic from all VMs in VNET"         },         "id": ““     },  … …      {         "properties": {             "provisioningState": "Succeeded",             "destinationAddressPrefixes": [],             "destinationAddressPrefix": "*",             "sourceAddressPrefixes": [],             "destinationPortRanges": [],             "destinationPortRange": "*",             "sourceAddressPrefix": "*",             "sourcePortRanges": [],             "sourcePortRange": "*",             "priority": 65500,             "protocol": "*",             "direction": "Outbound",             "access": "Deny",             "description": "Deny all outbound traffic"         },         "id": "/“     } ]

 

 

参考资料

mv-expand 运算符 : https://docs.microsoft.com/zh-cn/azure/data-explorer/kusto/query/mvexpandoperator

Azure Resource Graph Query For Network Security Group Rules ： https://blog.tyang.org/2021/12/08/azure-resource-graph-query-for-nsg-rules