Query: network_security_group_udp_access_restricted

with nsg_udp_access as (
  select
   distinct address as name
  from
    terraform_resource,
    jsonb_array_elements(
      case jsonb_typeof(attributes_std -> 'security_rule')
      when 'array' then (attributes_std -> 'security_rule')
      else null end
     ) as s
  where
    type = 'azurerm_network_security_group'
    and lower(s ->> 'protocol') = 'udp'
    and lower(s ->> 'direction') = 'inbound'
    and lower(s ->> 'access') = 'allow'
    and lower(s ->> 'source_address_prefix') in ('*', '0.0.0.0', '<nw>/0', '/0', 'internet', 'any')
)
select
  r.address as resource,
  case
    when (attributes_std -> 'security_rule') is null then 'alarm'
    when s.name is not null then 'alarm'
    when lower(attributes_std -> 'security_rule' ->> 'protocol') = 'udp'
      and lower(attributes_std -> 'security_rule' ->> 'direction') = 'inbound'
      and lower(attributes_std -> 'security_rule' ->> 'access') = 'allow'
      and lower(attributes_std -> 'security_rule' ->> 'source_address_prefix') in ('*', '0.0.0.0', '<nw>/0', '/0', 'internet', 'any') then 'alarm'
    else 'ok'
  end as status,
  split_part(r.address, '.', 2) || case
    when (attributes_std -> 'security_rule') is  null then ' security rule not defined'
    when s.name is not null then ' allows UDP services from internet'
    when lower(attributes_std -> 'security_rule' ->> 'protocol') = 'udp'
      and lower(attributes_std -> 'security_rule' ->> 'direction') = 'inbound'
      and lower(attributes_std -> 'security_rule' ->> 'access') = 'allow'
      and lower(attributes_std -> 'security_rule' ->> 'source_address_prefix') in ('*', '0.0.0.0', '<nw>/0', '/0', 'internet', 'any') then ' allows UDP services from internet'
    else ' restricts UDP services from internet'
  end || '.' reason
  
  , path || ':' || start_line
from
  terraform_resource as r
  left join nsg_udp_access as s on s.name = r.name
where
  type = 'azurerm_network_security_group';