Control: Ensure that Network Security Group Flow Log retention period is 'greater than 90 days'

powerpipe control run azure_compliance.control.network_sg_flowlog_retention_period_greater_than_90

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

select
  sg.id resource,
  case
    when fl.id is null or not fl.enabled or fl.retention_policy_days < 90 then 'alarm'
    else 'ok'
  end as status,
  case
    when fl.id is null or not fl.enabled
      then sg.name || ' flowlog not enabled.'
    when fl.retention_policy_days < 90
      then sg.name || ' flowlog ' || fl.title || ' retention period is less than 90 days.'
    else sg.name || ' flowlog ' || fl.title || ' retention period is ' || fl.retention_policy_days || ' days.'
  end as reason
  
  , sg.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_network_security_group sg
  left join azure_network_watcher_flow_log fl on sg.id = fl.target_resource_id
  join azure_subscription sub on sub.subscription_id = sg.subscription_id;