Query: storage_account_use_virtual_service_endpoint

with storage_account_network_rules as (
  select
    name,
    address,
    type,
    path,
    start_line,
    split_part((attributes_std ->> 'storage_account_name'), '.',2) as storage_account_name
  from
    terraform_resource
  where
    type = 'azurerm_storage_account_network_rules'
    and (attributes_std ->> 'default_action') = 'Deny'
), storage_account_name as (
    select
      name,
      address,
      type,
      path,
      _ctx,
      start_line
    from
      terraform_resource
    where
      type = 'azurerm_storage_account'
)
select
  san.address as resource,
  case
    when sanr.address is null then 'alarm'
    else 'ok'
  end status,
  split_part(san.address, '.', 2) || case
    when sanr.address is null then ' does not use virtual service endpoint'
    else ' uses virtual service endpoint'
  end || '.' reason
  
  , san.path || ':' || san.start_line
from
  storage_account_name as san
  left join storage_account_network_rules as sanr on sanr.storage_account_name = san.name;