Trigger: Detect & correct Subscriptions without network bastion host

with bastion_hosts as (
  select
    subscription_id,
    _ctx,
    region,
    resource_group,
    count(*) as no_bastion_host
  from
    azure_bastion_host
  group by
    subscription_id,
    _ctx,
    resource_group,
    region
)
select
  sub.subscription_id as title,
  sub._ctx ->> 'connection_name' as conn
from
  azure_subscription as sub
  left join bastion_hosts as i on i.subscription_id = sub.subscription_id
where
  i.subscription_id is null;