Query: compute_instance_with_public_network_interface

with network_if as (
  select
      *,
    unnest(from_json(request -> 'networkInterfaces', '["json"]')) as netif
  from
    gcp_audit_log
  where
    (
    method_name ilike '%.compute.instances.insert'
    or method_name ilike '%.compute.instances.update'
    )
  ),
  access_cfg as (
    select
      *,
      unnest(from_json(netif -> 'accessConfigs', '["json"]')) as ac
    from network_if
  )
  select
    tp_timestamp as timestamp,
method_name as operation,
resource_name as resource,
authentication_info.principal_email as actor,
tp_source_ip as source_ip,
tp_index as project,
tp_id as source_id,
-- Create new aliases to preserve original row data
operation as operation_src,
resource as resource_src,
*
exclude operation, resource

    ac
  from
    access_cfg
  where
    (
      (ac ->> 'name') ilike '%nat%'
      or (ac ->> 'name') ilike '%external%'
    )
    and severity != 'Error'

  order by
    timestamp desc;