NSG detail dashboard showing additional, not configured rule

[digitalbartimaeus]

Describe the bug For some NSG, the dashboard does not reflect the configured NSG rules. The underlying query is not working as expected. The concatenation section like jsonb_array_elements_text(sg -> 'properties' -> 'destinationPortRanges' || (sg -> 'properties' -> 'destinationPortRange') :: jsonb) dport is causing an additional entry owing to which we are getting an empty Destination Port Range.

Steampipe version v0.18.6

Plugin version v0.37.0

To reproduce Query on NSG:

select
    sg -> 'properties' ->> 'access' as "Access",
    sg -> 'properties' ->> 'protocol' as "Protocol",
    sip as "Source Address Prefixes",
    dport as "Destination Port Range"
    from
        azure_network_security_group nsg,
        jsonb_array_elements(security_rules || default_security_rules) sg,
        jsonb_array_elements_text(sg -> 'properties' -> 'destinationPortRanges' || (sg -> 'properties' -> 'destinationPortRange') :: jsonb) dport,
        jsonb_array_elements_text(sg -> 'properties' -> 'sourceAddressPrefixes' || (sg -> 'properties' -> 'sourceAddressPrefix') :: jsonb) sip
      where
        sg -> 'properties' ->> 'direction' = 'Inbound'

provides following test output:

+--------+----------+-------------------------+------------------------+
| Access | Protocol | Source Address Prefixes | Destination Port Range |
+--------+----------+-------------------------+------------------------+
| Allow  | Tcp      | *                       | 434                    |
| Allow  | Tcp      | *                       | 443                    |
| Allow  | Tcp      | *                       |                        |
| Allow  | *        | VirtualNetwork          | *                      |
| Allow  | *        | AzureLoadBalancer       | *                      |
| Deny   | *        | *                       | *                      |
+--------+----------+-------------------------+------------------------+

Expected behavior Listing only configured NSG rules:

+--------+----------+-------------------------+------------------------+
| Access | Protocol | Source Address Prefixes | Destination Port Range |
+--------+----------+-------------------------+------------------------+
| Allow  | Tcp      | *                       | 434                    |
| Allow  | Tcp      | *                       | 443                    |
| Allow  | *        | VirtualNetwork          | *                      |
| Allow  | *        | AzureLoadBalancer       | *                      |
| Deny   | *        | *                       | *                      |
+--------+----------+-------------------------+------------------------+

Additional context Removing the concatenation should helps to remove the duplicate line, but also removes the default NSG rules:

jsonb_array_elements_text(sg -> 'properties' -> 'destinationPortRanges') dport,

resulting in

+--------+----------+-------------------------+------------------------+
| Access | Protocol | Source Address Prefixes | Destination Port Range |
+--------+----------+-------------------------+------------------------+
| Allow  | Tcp      | *                       | 434                    |
| Allow  | Tcp      | *                       | 443                    |
+--------+----------+-------------------------+------------------------+

[misraved]

Welcome to Steampipe @digitalbartimaeus and thanks for raising this issue including all the details 👍.

Great catch indeed 👍

Relevant slack thread - https://steampipe.slack.com/archives/C01UECB59A7/p1677062449090679

[rajlearner17]

@digitalbartimaeus Thanks for the reported issue; we have a fix available in this PR. Can you give it a try and share your feedback? You have to checkout the branch and run the dashboard. Thanks again!