Query: databricks_workspace_subnet_with_nsg_configured

with databricks_subnets as (
  select
    id,
    'private' as subnet_type,
    concat(parameters -> 'customVirtualNetworkId' ->> 'value', '/subnets/', parameters -> 'customPrivateSubnetName' ->> 'value') as subnet_id
  from
    azure_databricks_workspace
  where
    parameters -> 'customVirtualNetworkId' IS NOT NULL
    and parameters -> 'customPrivateSubnetName' ->> 'value' IS NOT NULL
  UNION ALL
  select
    id,
    'public' as subnet_type,
    concat(parameters -> 'customVirtualNetworkId' ->> 'value', '/subnets/', parameters -> 'customPublicSubnetName' ->> 'value') as subnet_id
  from
    azure_databricks_workspace
  where
    parameters -> 'customVirtualNetworkId' IS NOT NULL
    and parameters -> 'customPublicSubnetName' ->> 'value' IS NOT NULL
), databricks_subnets_without_nsg as (
  select
    distinct ds.id,
    network_security_group_id
  from
    azure_subnet as s
    right join databricks_subnets as ds on lower(s.id) = lower(ds.subnet_id)
  where
    s.network_security_group_id is null
)
select
  a.id as resource,
  case
    when parameters -> 'customVirtualNetworkId' is null then 'skip'
    when nsg.id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when parameters -> 'customVirtualNetworkId' is null then a.name || ' is not deployed in a customer-managed virtual network.'
    when nsg.id is null then a.name || ' subnets are configured with network security group.'
    else a.name || ' subnets are not configured with network security group.'
  end as reason
  
  , a.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_databricks_workspace as a
  left join databricks_subnets_without_nsg as nsg on nsg.id = a.id
  left join azure_subscription as sub on sub.subscription_id = a.subscription_id;