turbot/gcp_compliance

Query: compute_instance_with_no_public_ip_addresses

Usage

powerpipe query gcp_compliance.query.compute_instance_with_no_public_ip_addresses

Steampipe Tables

SQL

with instance_without_access_config as (
select
name
from
gcp_compute_instance,
jsonb_array_elements(network_interfaces) nic
where
nic ->> 'accessConfigs' is null
),
instance_with_access_config as (
select
name
from
gcp_compute_instance,
jsonb_array_elements(network_interfaces) nic,
jsonb_array_elements(nic -> 'accessConfigs') d
where
d ->> 'natIP' is null
)
select
self_link resource,
case
when name like 'gke-%'
and labels ? 'goog-gke-node' then 'skip'
when name in (
select
name
from
instance_without_access_config
) then 'ok'
when name in (
select
name
from
instance_with_access_config
) then 'ok'
else 'alarm'
end as status,
case
when name like 'gke-%'
and labels ? 'goog-gke-node' then title || ' created by GKE.'
when name in (
select
name
from
instance_without_access_config
)
or name in (
select
name
from
instance_with_access_config
) then title || ' not associated with public IP addresses.'
else title || ' associated with public IP addresses.'
end as reason,
location as location,
project as project
from
gcp_compute_instance;

Controls

The query is being used by the following controls: