Query: appservice_function_app_restrict_public_acces

with public_function_app as (
  select
    id
  from
    azure_app_service_function_app,
    jsonb_array_elements(configuration -> 'properties' -> 'ipSecurityRestrictions') as r
  where
    r ->> 'ipAddress' = 'Any'
    and r ->> 'action' = 'Allow'
)
select
  fa.id as resource,
  case
    when p.id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when p.id is null then name || ' not publicly accessible.'
    else name || ' publicly accessible.'
  end as reason
  
  , fa.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_app_service_function_app fa
  left join public_function_app as p on p.id = fa.id,
  azure_subscription sub
where
  sub.subscription_id = fa.subscription_id;