Control: Ensure that Network Security Group Flow logs are captured and sent to Log Analytics

powerpipe control run azure_compliance.control.nsg_network_watcher_flow_log_send_to_log_analytics

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

with nsg_network_watcher_flow_log as (
  select
    subscription_id,
    count(*) as nsg_flow_log_count
  from
    azure_network_watcher_flow_log
  where
    traffic_analytics -> 'workspaceId' is not null
    and target_resource_id like '%/Microsoft.Network/networkSecurityGroups/%'
  group by
    subscription_id
)
select
  sub.id resource,
  case
    when nsg_flow_log_count > 0  then 'ok'
    else 'alarm'
  end as status,
  case
    when nsg_flow_log_count > 0 then sub.display_name || ' has ' || nsg_flow_log_count || ' NSG flow log(s) captured and sent to log analytics.'
    else sub.display_name || ' has no NSG flow log captured and sent to log analytics.'
  end as reason
  , display_name as subscription
from
  azure_subscription as sub
  left join nsg_network_watcher_flow_log as nsg_flow_log on nsg_flow_log.subscription_id = sub.subscription_id;