Query: network_firewall_unrestricted_inbound_rules

with inbound_fw as (
  select
    id
  from
    digitalocean_firewall,
    jsonb_array_elements(inbound_rules) as i
  where
    i -> 'sources' -> 'addresses' = '["0.0.0.0/0","::/0"]'
    and i ->> 'protocol' <> 'icmp'
  group by
    id
)
select
  'Inbound (Excludes ICMP)' as label,
  case
    when i.id is null then 'Restricted'
    else 'Unrestricted'
  end as value,
  case
    when i.id is null then 'ok'
    else 'alert'
  end as type
from
  digitalocean_firewall as d
  left join inbound_fw as i on d.id = i.id
where
  d.urn = $1;