Add connected_devices information in azure_virtual_network and azure_subnet tables

[marcbrlcln]

Describe the solution you'd like
We'd like to check that a virtual network or subnet has connected devices. To do this, it'd be useful to have a column for connected devices in these tables.

[ParthaI]

Hello @marcbrlcln,

Could you please provide a bit more detail about what you mean by connected_device? This will help us identify the relevant API support.

Are you referring to the NICs attached to the VM, or is there something else you're looking for?

[marcbrlcln]

Hello,

Sorry for the lack of information.

What I wanted is the information in the following screenshot where the type of the resources can be a NIC, a Load Balancer, an Application Gateway, a Firewall and more.

I thought it would be possible to have a "Connected devices" column in the azure_virtual_network and azure_subnet tables as in the screen. But, I realized that this information is already in the azure_subnet table in the ip_configurations column.

However, I am experiencing an issue with the ip_configurations column where some details are missing.

Context
In my subscription I have one Virtual Network with five subnets in which there are some NICs, a load balancer, a firewall and a Virtual network gateway. For exemple, here is the subnet with the firewall in Azure CLI :

az network vnet subnet list --resource-group rg-demo --vnet-name vnet-demo  
[
  {
    "addressPrefix": "10.48.0.192/26",
    "delegations": [],
    "etag": "W/\"eb.................................\"",
    "id": "/subscriptions/d3................................./resourceGroups/rg-demo/providers/Microsoft.Network/virtualNetworks/vnet-demo/subnets/AzureFirewallSubnet",
    "ipConfigurations": [
      {
        "id": "/subscriptions/d3................................./resourceGroups/rg-demo/providers/Microsoft.Network/azureFirewalls/afw-demo/azureFirewallIpConfigurations/IP_CONFIGURATION",
        "resourceGroup": "rg-demo"
      }
    ],
    "name": "AzureFirewallSubnet",
    "privateEndpointNetworkPolicies": "Enabled",
    "privateLinkServiceNetworkPolicies": "Enabled",
    "provisioningState": "Succeeded",
    "resourceGroup": "rg-demo",
    "routeTable": {
      "id": "/subscriptions/d3................................./resourceGroups/rg-demo/providers/Microsoft.Network/routeTables/udr-demo",
      "resourceGroup": "rg-demo"
    },
    "serviceEndpoints": [],
    "type": "Microsoft.Network/virtualNetworks/subnets"
  },
  {
  ...
  }
]

I attempt to query the ip_configurations details using Steampipe. The column should have the details of "ipConfigurations" but here is the result of the query :

> SELECT ip_configurations FROM azure_subnet WHERE name = 'AzureFirewallSubnet'
+-------------------+
| ip_configurations |
+-------------------+
| <null>            |
+-------------------+ 

The same happen for every subnet except those containing NICs.
The get API response contains a property called "ipConfigurations" which isn't null so the issue may be related to how the Steampipe Azure plugin retrieves and displays the ipConfigurations details.

[ParthaI]

Thanks for the detailed information! I will take a look.

[ParthaI]

Hello @marcbrlcln,

I apologize for the delay in addressing this issue. I’ve raised a PR to resolve it, and I'm now getting the expected results.

Here’s a snapshot of the connected devices with my subnet:

Query result:

> select name, jsonb_pretty(ip_configurations) from azure_subnet where name = 'turbottest38108'
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name            | jsonb_pretty                                                                                                                                                                                       |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| turbottest38108 | [                                                                                                                                                                                                  |
|                 |     {                                                                                                                                                                                              |
|                 |         "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/turbottest38108/providers/Microsoft.Network/loadBalancers/test53/frontendIPConfigurations/test53",               |
|                 |         "name": "test53",                                                                                                                                                                          |
|                 |         "type": "Microsoft.Network/loadBalancers/frontendIPConfigurations",                                                                                                                        |
|                 |         "properties": {                                                                                                                                                                            |
|                 |             "subnet": {                                                                                                                                                                            |
|                 |                 "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/turbottest38108/providers/Microsoft.Network/virtualNetworks/turbottest38108/subnets/turbottest38108"     |
|                 |             },                                                                                                                                                                                     |
|                 |             "privateIPAddress": "10.0.2.5",                                                                                                                                                        |
|                 |             "provisioningState": "Succeeded",                                                                                                                                                      |
|                 |             "privateIPAddressVersion": "IPv4",                                                                                                                                                     |
|                 |             "privateIPAllocationMethod": "Dynamic"                                                                                                                                                 |
|                 |         }                                                                                                                                                                                          |
|                 |     },                                                                                                                                                                                             |
|                 |     {                                                                                                                                                                                              |
|                 |         "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/turbottest38108/providers/Microsoft.Network/networkInterfaces/turbottest38108/ipConfigurations/turbottest38108", |
|                 |         "name": "turbottest38108",                                                                                                                                                                 |
|                 |         "type": "Microsoft.Network/networkInterfaces/ipConfigurations",                                                                                                                            |
|                 |         "properties": {                                                                                                                                                                            |
|                 |             "subnet": {                                                                                                                                                                            |
|                 |                 "id": "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/turbottest38108/providers/Microsoft.Network/virtualNetworks/turbottest38108/subnets/turbottest38108"     |
|                 |             },                                                                                                                                                                                     |
|                 |             "primary": true,                                                                                                                                                                       |
|                 |             "privateIPAddress": "10.0.2.4",                                                                                                                                                        |
|                 |             "provisioningState": "Succeeded",                                                                                                                                                      |
|                 |             "privateIPAddressVersion": "IPv4",                                                                                                                                                     |
|                 |             "privateIPAllocationMethod": "Dynamic"                                                                                                                                                 |
|                 |         }                                                                                                                                                                                          |
|                 |     }                                                                                                                                                                                              |
|                 | ]                                                                                                                                                                                                  |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Please let us know if you need any further assistance.

Thank you!

[marcbrlcln]

Thank you for your help !