Control: Long-term geo-redundant backup should be enabled for Azure SQL Databases

powerpipe control run azure_compliance.control.sql_database_long_term_geo_redundant_backup_enabled

powerpipe login
powerpipe control run azure_compliance.control.sql_database_long_term_geo_redundant_backup_enabled --share

select
  s.database_id resource,
  case
    when retention_policy_property ->> 'monthlyRetention' <> 'PT0S'
      or retention_policy_property ->> 'weeklyRetention' <> 'PT0S'
      or retention_policy_property ->> 'yearlyRetention' <> 'PT0S'
      then 'ok'
      else 'alarm'
  end as status,
  case
    when retention_policy_property ->> 'monthlyRetention' <> 'PT0S'
      or retention_policy_property ->> 'weeklyRetention' <> 'PT0S'
      or retention_policy_property ->> 'yearlyRetention' <> 'PT0S'
      then s.title || ' long-term geo-redundant backup enabled.'
      else s.title || ' long-term geo-redundant backup disabled.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_database s
  left join azure_subscription sub on sub.subscription_id = s.subscription_id
where
  s.name <> 'master';