turbot/kubernetes_insights

Query: service_tree

Usage

powerpipe query kubernetes_insights.query.service_tree

SQL

with pods as (
select
uid as pod_uid,
title as pod_title
from
kubernetes_pod
where
selector_search in (select selector_query from kubernetes_service where uid = $1)
),
services as (
select
uid,
title,
pod_uid,
pod_title,
cluster_ip,
external_ips,
selector_query,
l as lb,
p ->> 'protocol' as protocol_number,
concat(p ->> 'port','/', p ->> 'protocol') as port,
concat(p ->> 'targetPort','/', p ->> 'protocol') as targetPort
from
pods,
kubernetes_service,
jsonb_array_elements(ports) as p,
jsonb_array_elements(load_balancer_ingress) as l
where
uid = $1
union
select
uid,
title,
pod_uid,
pod_title,
cluster_ip,
external_ips,
selector_query,
load_balancer_ingress as lb,
p ->> 'protocol' as protocol_number,
concat(p ->> 'port','/', p ->> 'protocol') as port,
concat(p ->> 'targetPort','/', p ->> 'protocol') as targetPort
from
pods,
kubernetes_service,
jsonb_array_elements(ports) as p
where
uid = $1
and load_balancer_ingress is null
)
-- LB
select
lb::text as id,
lb ->> 'ip' as title,
'lb' as category,
null as from_id,
null as to_id
from
services
-- EIP
union all
select
eip as id,
eip as title,
'external_ip' as category,
null as from_id,
null as to_id
from
services,
jsonb_array_elements_text(external_ips) as eip
-- ClusterIP
union all
select
cluster_ip as id,
cluster_ip as title,
'cluster_ip' as category,
null as from_id,
null as to_id
from
services
-- Ports
union all
select
port as id,
port as title,
'port' as category,
null as from_id,
null as to_id
from
services
-- service
union all
select
distinct title as id,
title as title,
'service' as category,
null as from_id,
null as to_id
from
services
-- targetPorts
union all
select
concat(targetPort,' (Target Port)') as id,
targetPort as title,
'targetPort' as category,
null as from_id,
null as to_id
from
services
-- pods
union all
select
distinct pod_title as id,
pod_title as title,
'pod' as category,
null as from_id,
null as to_id
from
services
-- externalIP -> port
union select
null as id,
null as title,
'external_ip' as category,
eip as from_id,
port as to_id
from services,
jsonb_array_elements_text(external_ips) as eip
-- clusterIP -> port
union select
null as id,
null as title,
'cluster_ip' as category,
cluster_ip as from_id,
port as to_id
from services
-- lb -> port
union select
null as id,
null as title,
'lb' as category,
lb::text as from_id,
port as to_id
from services
-- port -> service
union select
null as id,
null as title,
'port' as category,
port as from_id,
title as to_id
from services
-- service -> target
union select
null as id,
null as title,
'service' as category,
title as from_id,
concat(targetPort,' (Target Port)') as to_id
from services
-- target -> pod
union select
null as id,
null as title,
'targetPort' as category,
concat(targetPort,' (Target Port)') as from_id,
pod_title as to_id
from services

Params

ArgsNameDefaultDescriptionVariable
$1uid

    Dashboards

    The query is used in the dashboards: