turbot/oci_insights

Query: vcn_network_security_group_assoc

Usage

powerpipe query oci_insights.query.vcn_network_security_group_assoc

SQL

with nsg_vnic_attachment as (
select
jsonb_array_elements_text(nsg_ids) as n_id,
instance_id as instance_id
from
oci_core_vnic_attachment
) -- Compute Instance
select
i.title as "Title",
'oci_core_instance' as "Type",
i.id as "ID",
'/oci_insights.dashboard.compute_instance_detail?input.instance_id=' || i.id as link
from
nsg_vnic_attachment as g
left join oci_core_network_security_group as nsg on g.n_id = nsg.id
left join oci_core_instance as i on i.id = g.instance_id
where
nsg.id = $1 -- File Storage Mount Target
union all
select
title as "Title",
'oci_file_storage_mount_target' as "Type",
id as "ID",
null as link
from
oci_file_storage_mount_target,
jsonb_array_elements_text(nsg_ids) as sg
where
sg = $1 -- Load Balancer
union all
select
title as "Title",
'oci_core_load_balancer' as "Type",
id as "ID",
null as link
from
oci_core_load_balancer,
jsonb_array_elements_text(network_security_group_ids) as sg
where
sg = $1 -- Network Load Balancer
union all
select
title as "Title",
'oci_core_network_load_balancer' as "Type",
id as "ID",
null as link
from
oci_core_network_load_balancer,
jsonb_array_elements_text(network_security_group_ids) as sg
where
sg = $1

Dashboards

The query is used in the dashboards: