問題描述

想對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

【Azure 環境】Azure Resource Graph Explorer 中實現動態陣列資料轉換成多行記錄模式

問題描述

問題解答

附錄一 ： NSG Rules 中 securityRules 的格式為：

參考資料

附錄一： NSG Rules 中 securityRules 的格式為：