Query: sql_server_audting_retention_period_90

with sql_server as (
  select
    *
  from
    terraform_resource
  where
    type = 'azurerm_mssql_server'
), server_audit_policy as (
  select
    *
  from
    terraform_resource
  where
    type = 'azurerm_mssql_server_extended_auditing_policy'
    and (attributes_std ->> 'retention_in_days')::int > 90
)
select
  address as resource,
  case
    when (s.attributes_std ->> 'server_id') is not null then 'ok'
    else 'alarm'
  end as status,
  split_part(a.address, '.', 2) || case
    when (s.attributes_std ->> 'server_name') is not null then ' audit retention greater than 90 days'
    else ' audit retention less than 90 days'
  end || '.' reason
  
  , a.path || ':' || a.start_line
from
  sql_server as a
  left join server_audit_policy as s on a.name = ( split_part((s.attributes_std ->> 'server_id'), '.', 2));