Kusto Query to get the destination port from properties JSON in resources table where resource type is network security group

Question

Kusto Query to get the destination port from properties JSON in resources table where resource type is network security group

Saini, Sandeep 46

Hello Everyone,

I have extracted the list of network security groups by using:

 Resources  
        | where type =~ 'microsoft.network/networksecuritygroups'

The output has the properties column which contains JSON in the below format:

{  
    "provisioningState": "Succeeded",  
    "resourceGuid": "1111-2222-3333-4444-5555-6666",  
    "networkInterfaces": [],  
    "defaultSecurityRules": [],  
    "securityRules": [  
        {  
            "properties": {  
                "provisioningState": "Succeeded",  
                "destinationAddressPrefixes": [],  
                "destinationAddressPrefix": "*",  
                "sourceAddressPrefixes": [],  
                "destinationPortRanges": [],  
                "sourceAddressPrefix": "Internet",  
                "destinationPortRange": "22",  
                "sourcePortRanges": [],  
                "sourcePortRange": "*",  
                "protocol": "Tcp",  
                "description": "Allow SSH",  
                "direction": "Inbound",  
                "priority": 100,  
                "access": "Allow"  
            },  
            "name": "SSH-Rule",  
            "type": "Microsoft.Network/networkSecurityGroups/securityRules",  
            "id": "/subscriptions/1111-2222-3333-4444-5555-6666/resourceGroups/demoRG/providers/Microsoft.Network/networkSecurityGroups/demoSecurityGroup/securityRules/SSH-Rule",  
            "etag": "W/\"1111-2222-3333-4444-5555-6666\""  
        },  
        {  
            "properties": {  
                "provisioningState": "Succeeded",  
                "destinationAddressPrefixes": [],  
                "destinationAddressPrefix": "*",  
                "sourceAddressPrefixes": [],  
                "destinationPortRanges": [],  
                "sourceAddressPrefix": "Internet",  
                "destinationPortRange": "3389",  
                "sourcePortRanges": [],  
                "sourcePortRange": "*",  
                "protocol": "Tcp",  
                "description": "Allow RDP",  
                "direction": "Inbound",  
                "priority": 150,  
                "access": "Allow"  
            },  
            "name": "RDP-Rule",  
            "type": "Microsoft.Network/networkSecurityGroups/securityRules",  
            "id": "/subscriptions/1111-2222-3333-4444-5555-6666/resourceGroups/demoRG/providers/Microsoft.Network/networkSecurityGroups/demoSecurityGroup/securityRules/RDP-Rule",  
            "etag": "W/\"1111-2222-3333-4444-5555-6666\""  
        }  
    ]  
}

By using properties.securityRules I am able to get the JSON that contains the security rules, but I need to display "sourcePortRange" value for "destinationPortRange" values 22 & 3389 in two separate columns along with the "networkSecurityGroupId".

I have already tried achieving by using bag_unpack and mv-apply but not able to extract the required fields with the conditional clause of destination Port range values.
Any help would be highly appreciated.

Thanks!

@Subhash Vasarapu @ChaitanyaNaykodi-MSFT

Accepted answer

0 additional answers

Your answer

Answer 1

GitaraniSharma-MSFT 50,096 Microsoft Employee Moderator

Hello @Anonymous ,

Welcome to Microsoft Q&A Platform. Thank you for reaching out & hope you are doing well.

I understand that you would like to know how to use Kusto Query for resource type "network security group" within Azure Resource Graph Explorer to display "sourcePortRange" value for "destinationPortRange" values 22 & 3389 in two separate columns along with the "networkSecurityGroupId" from the NSG security rules.

Below is the Kusto query that I used in my lab which displays the sourcePortRange in a column for destinationPortRange values 22 & 3389 in another column with the networkSecurityGroupId, name and access type.

resources   
| where type startswith 'microsoft.network/networksecuritygroups'   
| mv-expand rules=properties.securityRules  
| where rules.properties.destinationPortRange in ("3389", "22")  
| project   
    name        = rules.name,  
    nsgid       = id,  
    access      = rules.properties.access,  
    DestPort    = rules.properties.destinationPortRange,  
    SourcePort  = rules.properties.sourcePortRange

Kindly let us know if the above helps or you need further assistance on this issue.

----------------------------------------------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Saini, Sandeep 46 Reputation points

2022-12-12T18:03:14.15+00:00

Hi @GitaraniSharma-MSFT ,

Thanks for the answer, this comes pretty close to my requirement but is it a possibility to maintain the uniqueness of the nsgid column by instead of having two different rows for Port 22 and Port 3389(wherever there are security rules for both), we have two different columns, something like this(considering only for the "access = allow"):
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2022-12-13T16:19:45.473+00:00

Hello @Anonymous ,

If I understand correctly, you would like to get the table in a way where the security rules are listed only when they have both the ports 3389 and 22 in their destination ports and display the source ports for each of those destination port while making sure that the nsgid is not duplicated, is that correct?

Regards,
Gita
Saini, Sandeep 46 Reputation points

2022-12-14T06:22:13.413+00:00

Hi @GitaraniSharma-MSFT ,

Not exactly when both both the ports ie. 22 & 3389 are mentioned in the destination port but a column for each in every row. In case the JSON doesn't contain any security rule for one of these two ports, we can display "N/A" for that. This way we will have one row per NSG and in each row will have one column for Source port where the destination Port is 22 and one column for Source Port where destination port is 3389.

I hope I am able to explain myself better.

Regards,
Sandeep
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2022-12-16T17:49:24.077+00:00

Hello @Anonymous ,

Thank you for the details.

Let me try to reproduce this in my lab and get back to you with an update.

Regards,
Gita

GitaraniSharma-MSFT 50,096 Microsoft Employee Moderator

Hello @Anonymous ,

Could you please try the below Kusto query and see if it meets your requirement?

resources    
| where type == 'microsoft.network/networksecuritygroups'    
| mv-expand rules=properties.securityRules   
| where rules.properties.destinationPortRange in ("3389", "22")  
| where rules.properties.access == "Allow"   
| extend SourcePortFor22 = iff(rules.properties.destinationPortRange == "22", rules.properties.sourcePortRange, "N/A"), SourcePortFor3389 = iff(rules.properties.destinationPortRange == "3389", rules.properties.sourcePortRange, "N/A")   
| project id, SourcePortFor22, SourcePortFor3389   
| distinct id, SourcePortFor22, SourcePortFor3389

I ran this in my lab and below was the result:

Kindly let us know if the above helps or you need further assistance on this issue.

----------------------------------------------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

Saini, Sandeep 46 Reputation points

2022-12-20T11:07:51.227+00:00

Thanks a lot @GitaraniSharma-MSFT , this is exactly what I had been looking for. :)
GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2022-12-20T13:35:17.553+00:00

@Anonymous , Glad to hear that and happy to help. :)

Ying Tse 0

Hi I am a beginner to Kusto, I used the query you provided but No results were display, only the fields were display like (name, nsgid, access, DestPort, SourcePort), what do I need to do to be able to see the data?

resources   
| where type startswith 'microsoft.network/networksecuritygroups'   
| mv-expand rules=properties.securityRules  
| where rules.properties.destinationPortRange in ("3389", "22")  
| project   
    name        = rules.name,  
    nsgid       = id,  
    access      = rules.properties.access,  
    DestPort    = rules.properties.destinationPortRange,  
    SourcePort  = rules.properties.sourcePortRange

GitaraniSharma-MSFT 50,096 Reputation points Microsoft Employee Moderator

2023-01-17T18:47:17.8566667+00:00

Hello @Ying Tse ,

As mentioned in the Azure Resource Graph permissions doc, To use Resource Graph, you must have appropriate rights in Azure role-based access control (Azure RBAC) with at least read access to the resources you want to query. Without at least read permissions to the Azure object or object group, results won't be returned.

Please make sure that you have read access to the resources you want to query.

Regards,
Gita

Share via

Kusto Query to get the destination port from properties JSON in resources table where resource type is network security group

0 additional answers

Your answer