False positive in query sql_server_audting_retention_period_90 if audit log retention day equals 0

[sfunkernw]

Describe the bug

The query sql_server_audting_retention_period_90 has a false positive alarm if the retention days are configured with 0.

The Azure portal shows for the Audit retention the following help text "The value in days of the retention period (0 is an indication for unlimited retention)."

This means, the Steampipe Mod Azure Compliance CIS benchmark would raise a false positive here.

Steampipe version v0.14.1 / latest

Plugin version v0.12 / latest

To reproduce

Set audit log retention of an Azure SQL server to 0 under https://portal.azure.com/#@<TENANT>/resource/subscriptions/<SUBSCRIPTION>/resourceGroups/<RES_GROUP>/providers/Microsoft.Sql/servers/<SQL_SERVER>/serverAuditing and a storage account to log the audit logs to and run the Steampipe Azure CIS benchmark Mod. It will raise it as alarm claiming the retention days are smaller than 90 days.

Expected behavior

Show check status should be OK.

Additional context

The following code should be changed to include a check for value 0:

https://github.com/turbot/steampipe-mod-azure-compliance/blob/v0.11/query/sql/sql_server_audting_retention_period_90.sql#L5

[cbruno10]

@sfunkernw Thanks for raising this report! Interesting to see that Azure SQL uses 0 to indicate unlimited retention time, we'll do some testing from our side and update the query state/reason based on the 0 value.

[rajlearner17]

@sfunkernw Thanks for checking this out; I tested with small validation in the query to call out 0 retention as unlimited days, the query can be arranged to include zero (0) or >= 90 as ok, but I kept it as below

select
  -- Required Columns
  s.id as resource,
  case
    when (audit -> 'properties' ->> 'retentionDays')::integer = 0 then 'ok'
    when (audit -> 'properties' ->> 'retentionDays')::integer >= 90 then 'ok'
    else 'alarm'
  end as status,
  case
    when (audit -> 'properties' ->> 'retentionDays')::integer = 0 then name || ' audit retention set to unlimited days.'
    when (audit -> 'properties' ->> 'retentionDays')::integer >= 90 then name || ' audit retention greater than 90 days.'
    else  name || ' audit retention less than 90 days.'
  end as reason,
  -- Additional Dimensions
  resource_group,
  sub.display_name as subscription
from
  azure_sql_server s,
  jsonb_array_elements(server_audit_policy) audit,
  azure_subscription sub
where
  sub.subscription_id = s.subscription_id;

We are still checking other queries to see if we have this condition ignored. Thank you again