Trigger: Detect & correct NSGs allowing inbound to HTTPS port

select
  concat(
    nsg.id,
    ' [',
    nsg.subscription_id,
    '/',
    nsg.resource_group,
    '/',
    sg ->> 'name',
    ']'
  ) as title,
  sg ->> 'name' as rule_name,
  nsg.name as sg_name,
  sip as source_address,
  dport as destination_port,
  nsg.resource_group,
  nsg.subscription_id,
  nsg._ctx ->> 'connection_name' as conn
from
  azure_network_security_group nsg,
  jsonb_array_elements(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' ->> 'access' = 'Allow'
  and sg -> 'properties' ->> 'direction' = 'Inbound'
  and sg -> 'properties' ->> 'protocol' ilike 'TCP'
  and sip in (
    '*',
    '0.0.0.0',
    '0.0.0.0/0',
    'Internet',
    'any',
    '<nw>/0',
    '/0'
  )
  and (
    dport in ('80', '443', '*')
    or (
      dport like '%-%'
      and split_part(dport, '-', 1) :: integer <= 80
      and split_part(dport, '-', 2) :: integer >= 80
    )
    or (
      dport like '%-%'
      and split_part(dport, '-', 1) :: integer <= 443
      and split_part(dport, '-', 2) :: integer >= 443
    )
  )