turbot/azure_compliance

Query: compute_vm_tcp_udp_access_restricted_internet

Usage

powerpipe query azure_compliance.query.compute_vm_tcp_udp_access_restricted_internet

SQL

with network_sg as (
select
distinct id as sg_id,
subscription_id,
network_interfaces
from
azure_network_security_group as nsg,
jsonb_array_elements(security_rules) as sg,
jsonb_array_elements_text(
sg -> 'properties' -> 'destinationPortRanges' || (sg -> 'properties' -> 'destinationPortRange') :: jsonb
) as dport,
jsonb_array_elements_text(
sg -> 'properties' -> 'sourceAddressPrefixes' || (sg -> 'properties' -> 'sourceAddressPrefix') :: jsonb
) as sip
where
sg -> 'properties' ->> 'access' = 'Allow'
and sg -> 'properties' ->> 'direction' = 'Inbound'
and sg -> 'properties' ->> 'protocol' in ('TCP', 'UDP')
and sip in (
'*',
'0.0.0.0',
'0.0.0.0/0',
'Internet',
'any',
'<nw>/0',
'/0'
)
and (
dport in ('22', '3389', '*')
or (
dport like '%-%'
and (
(
53 between split_part(dport, '-', 1) :: integer
and split_part(dport, '-', 2) :: integer
or 123 between split_part(dport, '-', 1) :: integer
and split_part(dport, '-', 2) :: integer
or 161 between split_part(dport, '-', 1) :: integer
and split_part(dport, '-', 2) :: integer
or 389 between split_part(dport, '-', 1) :: integer
and split_part(dport, '-', 2) :: integer
or 1900 between split_part(dport, '-', 1) :: integer
and split_part(dport, '-', 2) :: integer
)
or (
split_part(dport, '-', 1) :: integer <= 3389
and split_part(dport, '-', 2) :: integer >= 3389
)
or (
split_part(dport, '-', 1) :: integer <= 22
and split_part(dport, '-', 2) :: integer >= 22
)
)
)
)
), network_security_group_subnets as (
select
nsg.id as nsg_id,
sub ->> 'id' as subnet_id
from
azure_network_security_group as nsg,
jsonb_array_elements(nsg.subnets) as sub
where
nsg.id in (select sg_id from network_sg )
),
virtual_machines_with_access as (
select
nic.virtual_machine_id as virtual_machine_id
from
azure_network_interface as nic,
jsonb_array_elements(nic.ip_configurations) as config
left join network_security_group_subnets as sub on config -> 'properties' -> 'subnet' ->> 'id' = sub.subnet_id
where
nic.virtual_machine_id is not null
and sub.nsg_id is not null
union
select
n.virtual_machine_id as virtual_machine_id
from
network_sg as nsg,
jsonb_array_elements(network_interfaces) as vm_nic
left join azure_network_interface as n on n.id = vm_nic ->> 'id'
)
select
vm.id as resource,
case
when m.virtual_machine_id is not null then 'alarm'
else 'ok'
end as status,
case
when m.virtual_machine_id is not null then vm.title || ' restricts remote access from internet.'
else vm.title || ' allows remote access from internet.'
end as reason,
vm.resource_group as resource_group,
sub.display_name as subscription
, vm.resource_group as resource_group
, sub.display_name as subscription
from
azure_compute_virtual_machine as vm
left join virtual_machines_with_access as m on lower(m.virtual_machine_id) = lower(vm.id)
join azure_subscription as sub on sub.subscription_id = vm.subscription_id;

Controls

The query is being used by the following controls: