Query: network_policy_blocked_list_set

with applied_network_policy as (
  select
    name,
    coalesce(blocked_ip_list, '') as blocked_ip_list,
    -- blocked_ip_list is optional therefore can be null
    account
  from
    snowflake_network_policy
  where
    name = (
      select
        value
      from
        snowflake_account_parameter
      where
        key = 'NETWORK_POLICY'
    )
),
analysis as (
  select
    name,
    to_jsonb ($1 :: text [ ]) < @ array_to_json(string_to_array(blocked_ip_list, ',')) :: jsonb as has_blocked_ips,
    to_jsonb ($1) - string_to_array(blocked_ip_list, ',', '') as missing_ips,
    account
  from
    applied_network_policy
)
select
  coalesce(analysis.name, sap.account) as resource,
  case
    when (
      select
        count(*)
      from
        applied_network_policy
      group by
        name
    ) is null then 'alarm'
    when has_blocked_ips then 'ok'
    else 'alarm'
  end as status,
  case
    when (
      select
        count(*)
      from
        applied_network_policy
      group by
        name
    ) is null then 'No network policy activated in the account.'
    when has_blocked_ips then name || ' has all blocked IPs.'
    else name || ' is missing blocked IPs: ' || array_to_string(
      array (
        select
          jsonb_array_elements_text(missing_ips)
      ),
      ', '
    ) || '.'
  end as reason,
  sap.account
from
  snowflake_account_parameter as sap
  left join analysis on sap.account = analysis.account
where
  key = 'NETWORK_POLICY';