Trigger: Detect & correct Subscriptions without activity log alert for create and update security solution

with alert_rule as (
  select
    alert.id as alert_id,
    alert.name as alert_name,
    alert.enabled,
    alert.location,
    alert.subscription_id,
    alert.resource_group,
    alert._ctx ->> 'connection_name' as conn,
    jsonb_array_length(alert.condition -> 'allOf')
  from
    azure_log_alert as alert,
    jsonb_array_elements_text(scopes) as sc
  where
    alert.location = 'Global'
    and alert.enabled
    and sc = '/subscriptions/' || alert.subscription_id
    and (
      (
        alert.condition -> 'allOf' @> '[{"equals":"Security","field":"category"}]'
        and alert.condition -> 'allOf' @> '[{"field": "operationName", "equals": "Microsoft.Security/securitySolutions/write"}]'
      )
      or (
        alert.condition -> 'allOf' @> '[{"equals":"Security","field":"category"}]'
        and alert.condition -> 'allOf' @> '[{"field": "resourceType", "equals": "microsoft.security/securitysolutions"}]'
        and jsonb_array_length(alert.condition -> 'allOf') = 2
      )
    )
  limit
    1
), resource_group as (
  select
    distinct on (s.subscription_id) r.name,
    r.subscription_id
  from
    azure_subscription as s
    left join azure_resource_group as r on r.subscription_id = s.subscription_id
  order by
    s.subscription_id,
    r.name
)
select
  sub.subscription_id as title,
  sub._ctx ->> 'connection_name' as conn
from
  azure_subscription sub
  left join alert_rule a on sub.subscription_id = a.subscription_id
  left join resource_group as r on r.subscription_id = sub.subscription_id
group by
  sub.subscription_id,
  sub.display_name,
  sub._ctx,
  a.alert_id,
  a.alert_name,
  a.resource_group,
  a.subscription_id,
  a.conn,
  r.name
having
  not(count(a.subscription_id) > 0);