Table: azure_network_security_group - Query Azure Network Security Groups using SQL
A Network Security Group in Azure is a security feature that acts as a virtual firewall for your network in Azure, using inbound and outbound rules to allow or deny network traffic to resources. It provides granular access control over network traffic by defining network security rules that allow or deny traffic based on traffic direction, protocol, source address and port, and destination address and port. This is a fundamental layer of security for virtual networks in Azure.
Table Usage Guide
The azure_network_security_group
table provides insights into Network Security Groups within Azure. As a security analyst or network administrator, you can explore the details of these groups through this table, including security rules, configurations, and associated metadata. Utilize this table to uncover information about the security posture of your network, such as the rules that are allowing or denying traffic, the protocols used, and the source and destination addresses and ports.
Examples
Subnets and network interfaces attached to the network security groups
Explore the relationships between network security groups, their attached network interfaces, and the subnets within the virtual networks. This can help in understanding the network topology and identifying potential security vulnerabilities.
select name, split_part(nic ->> 'id', '/', 9) network_interface, split_part(vn ->> 'id', '/', 9) virtual_network, split_part(vn ->> 'id', '/', 11) subnetsfrom azure_network_security_group cross join jsonb_array_elements(network_interfaces) as nic, jsonb_array_elements(subnets) as vn;
Error: SQLite does not support splitor string_to_array functions.
List the network security groups whose inbound is not restricted from the internet
Explore which network security groups are not restricting inbound access from the internet. This is useful in identifying potential security vulnerabilities within your network infrastructure.
select name, sg ->> 'name' as sg_name, sg -> 'properties' ->> 'access' as access, sg -> 'properties' ->> 'description' as description, sg -> 'properties' ->> 'destinationPortRange' as destination_port_range, sg -> 'properties' ->> 'direction' as direction, sg -> 'properties' ->> 'priority' as priority, sg -> 'properties' ->> 'sourcePortRange' as source_port_range, sg -> 'properties' ->> 'protocol' as protocolfrom azure_network_security_group cross join jsonb_array_elements(security_rules) as sgwhere ( sg -> 'properties' ->> 'sourcePortRange' = '*' and sg -> 'properties' ->> 'destinationPortRange' = '*' and sg -> 'properties' ->> 'access' = 'Allow' );
select name, json_extract(sg.value, '$.name') as sg_name, json_extract(sg.value, '$.properties.access') as access, json_extract(sg.value, '$.properties.description') as description, json_extract(sg.value, '$.properties.destinationPortRange') as destination_port_range, json_extract(sg.value, '$.properties.direction') as direction, json_extract(sg.value, '$.properties.priority') as priority, json_extract(sg.value, '$.properties.sourcePortRange') as source_port_range, json_extract(sg.value, '$.properties.protocol') as protocolfrom azure_network_security_group, json_each(security_rules) as sgwhere ( json_extract(sg.value, '$.properties.sourcePortRange') = '*' and json_extract(sg.value, '$.properties.destinationPortRange') = '*' and json_extract(sg.value, '$.properties.access') = 'Allow' );
Default security group rules info
Discover the details of default security group rules within your Azure network security group. This query can help you understand the access, direction, and protocol of each rule, which can be useful for auditing and optimizing your network security settings.
select name, sg -> 'name' as sg_name, sg -> 'properties' ->> 'access' as access, sg -> 'properties' ->> 'description' as description, sg -> 'properties' ->> 'destinationPortRange' as destination_port_range, sg -> 'properties' ->> 'direction' as direction, sg -> 'properties' ->> 'priority' as priority, sg -> 'properties' ->> 'sourcePortRange' as source_port_range, sg -> 'properties' ->> 'protocol' as protocolfrom azure_network_security_group cross join jsonb_array_elements(default_security_rules) as sg;
select name, json_extract(sg.value, '$.name') as sg_name, json_extract(sg.value, '$.properties.access') as access, json_extract(sg.value, '$.properties.description') as description, json_extract(sg.value, '$.properties.destinationPortRange') as destination_port_range, json_extract(sg.value, '$.properties.direction') as direction, json_extract(sg.value, '$.properties.priority') as priority, json_extract(sg.value, '$.properties.sourcePortRange') as source_port_range, json_extract(sg.value, '$.properties.protocol') as protocolfrom azure_network_security_group, json_each(default_security_rules) as sg;
Query examples
- azure_compute_virtual_machine_egress_access
- compute_virtual_machine_ingress_access
- compute_virtual_machine_unrestricted_remote_access_count
- compute_virtual_machines_for_network_security_group
- network_interface_attached_nsg
- network_network_interfaces_for_network_security_group
- network_security_group_assoc
- network_security_group_attached_enis_count
- network_security_group_attached_subnets_count
- network_security_group_by_provisioning_state
- network_security_group_by_region
- network_security_group_by_subscription
- network_security_group_count
- network_security_group_egress_rule_sankey
- network_security_group_egress_rules
- network_security_group_egress_rules_count
- network_security_group_flow_logs_disabled_count
- network_security_group_ingress_rule_sankey
- network_security_group_ingress_rules
- network_security_group_ingress_rules_count
- network_security_group_tags
- network_security_group_unassociated_count
- network_security_group_unrestricted_egress_count
- network_security_group_unrestricted_egress_remote_access
- network_security_group_unrestricted_ingress_count
- network_security_group_unrestricted_ingress_remote_access
- network_security_groups_for_compute_virtual_machine
- network_security_groups_for_compute_virtual_machine_scale_set
- network_security_groups_for_compute_virtual_machine_scale_set_vm
- network_security_groups_for_network_interface
- network_security_groups_for_network_subnet
- network_security_groups_for_network_virtual_network
- network_subnets_for_network_security_group
- network_virtual_networks_for_network_security_group
- security_group_flow_logs
- virtual_network_egress_rule_sankey
- virtual_network_ingress_rule_sankey
- virtual_network_nsg
Control examples
- All Controls > Network > Ensure that HTTP(S) access from the Internet is evaluated and restricted
- All Controls > Network > Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- All Controls > Network > Ensure that SSH access is restricted from the internet
- All Controls > Network > Ensure that UDP Services are restricted from the Internet
- All Controls > Network > Network security groups should restrict inbound ICMP port access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 135 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 1433 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 20 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 21 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 23 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 25 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 3306 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 4333 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 445 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 53 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 5432 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 5500 access from internet
- All Controls > Network > Network security groups should restrict inbound TCP port 5900 access from internet
- All Controls > Network > Network security groups should restrict inbound UDP port 137 access from internet
- All Controls > Network > Network security groups should restrict inbound UDP port 137 access from internet
- All Controls > Network > Network security groups should restrict inbound UDP port 1434 access from internet
- All Controls > Network > Network security groups should restrict inbound UDP port 445 access from internet
- All Controls > Network > Network security groups should restrict inbound UDP port 53 access from internet
- All Controls > Network > Network security groups should restrict outbound access from internet
- All network ports should be restricted on network security groups associated to your virtual machine
- CIS v1.3.0 > 6 Networking > 6.1 Ensure that RDP access is restricted from the internet
- CIS v1.3.0 > 6 Networking > 6.2 Ensure that SSH access is restricted from the internet
- CIS v1.3.0 > 6 Networking > 6.4 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- CIS v1.3.0 > 6 Networking > 6.6 Ensure that UDP Services are restricted from the Internet
- CIS v1.4.0 > 6 Networking > 6.1 Ensure that RDP access is restricted from the internet
- CIS v1.4.0 > 6 Networking > 6.2 Ensure that SSH access is restricted from the internet
- CIS v1.4.0 > 6 Networking > 6.4 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- CIS v1.4.0 > 6 Networking > 6.6 Ensure that UDP Services are restricted from the Internet
- CIS v1.5.0 > 6 Networking > 6.1 Ensure that RDP from the internet access is evaluated and restricted
- CIS v1.5.0 > 6 Networking > 6.2 Ensure that SSH access from the internet is evaluated and restricted
- CIS v1.5.0 > 6 Networking > 6.3 Ensure that UDP access from the Internet is evaluated and restricted
- CIS v1.5.0 > 6 Networking > 6.4 Ensure that HTTP(S) access from the Internet is evaluated and restricted
- CIS v1.5.0 > 6 Networking > 6.5 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- CIS v2.0.0 > 6 Networking > 6.1 Ensure that RDP access from the Internet is evaluated and restricted
- CIS v2.0.0 > 6 Networking > 6.2 Ensure that SSH access from the Internet is evaluated and restricted
- CIS v2.0.0 > 6 Networking > 6.3 Ensure that UDP access from the Internet is evaluated and restricted
- CIS v2.0.0 > 6 Networking > 6.4 Ensure that HTTP(S) access from the Internet is evaluated and restricted
- CIS v2.0.0 > 6 Networking > 6.5 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- CIS v2.1.0 > 6 Networking > 6.1 Ensure that RDP access from the Internet is evaluated and restricted
- CIS v2.1.0 > 6 Networking > 6.2 Ensure that SSH access from the Internet is evaluated and restricted
- CIS v2.1.0 > 6 Networking > 6.3 Ensure that UDP access from the Internet is evaluated and restricted
- CIS v2.1.0 > 6 Networking > 6.4 Ensure that HTTP(S) access from the Internet is evaluated and restricted
- CIS v2.1.0 > 6 Networking > 6.5 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- CIS v3.0.0 > 7 Networking > 7.1 Ensure that RDP access from the Internet is evaluated and restricted
- CIS v3.0.0 > 7 Networking > 7.2 Ensure that SSH access from the Internet is evaluated and restricted
- CIS v3.0.0 > 7 Networking > 7.3 Ensure that UDP access from the Internet is evaluated and restricted
- CIS v3.0.0 > 7 Networking > 7.4 Ensure that HTTP(S) access from the Internet is evaluated and restricted
- CIS v3.0.0 > 7 Networking > 7.5 Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'
- Deploy Diagnostic Settings for Network Security Groups
- Flow logs should be configured for every network security group
- Internet-facing virtual machines should be protected with network security groups
- Management ports should be closed on your virtual machines
- Subnets should be associated with a Network Security Group
- Windows machines should meet requirements for 'User Rights Assignment'
Schema for azure_network_security_group
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
akas | jsonb | Array of globally unique identifier strings (also known as) for the resource. | |
cloud_environment | text | The Azure Cloud Environment. | |
default_security_rules | jsonb | A list of default security rules of network security group. | |
diagnostic_settings | jsonb | A list of active diagnostic settings for the network security group. | |
etag | text | An unique read-only string that changes whenever the resource is updated. | |
flow_logs | jsonb | A collection of references to flow log resources. | |
id | text | Contains ID to identify a network security group uniquely. | |
name | text | = | The friendly name that identifies the network security group. |
network_interfaces | jsonb | A collection of references to network interfaces. | |
provisioning_state | text | The resource type of the network security group. | |
region | text | The Azure region/location in which the resource is located. | |
resource_group | text | = | The resource group which holds this resource. |
resource_guid | text | The resource GUID property of the network security group resource. | |
security_rules | jsonb | A list of security rules of network security group. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
subnets | jsonb | A collection of references to subnets. | |
subscription_id | text | =, !=, ~~, ~~*, !~~, !~~* | The Azure Subscription ID in which the resource is located. |
tags | jsonb | A map of tags for the resource. | |
title | text | Title of the resource. | |
type | text | The resource type of the network security group. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- azure
You can pass the configuration to the command with the --config
argument:
steampipe_export_azure --config '<your_config>' azure_network_security_group