Kinda SQL "join" in Prometheus

I'm using Prometheus query language, PromQL, quite a bit these days. But all I do are very simple queries like sum(...) or rate(...[5m]) on a OpenShift cluster I work with.

For few weeks now, mine inner me was bothered with one slightly more complex stuff. To filter one metric by label from different metric - something like JOIN in SQL world. Specifically, I wanted to see number of pods running on each cluster node with "worker" role.

We have (I'm on OpenShift 4.13) kube_node_role{role="worker"} (AFAICT this is what we call "vector" in PromQL) that have these labels:

Name            container             endpoint    job                 namespace             node                     prometheus                role    service             Value
kube_node_role  kube-rbac-proxy-main  https-main  kube-state-metrics  openshift-monitoring  ip-1-2-3-4.ec2.internal  openshift-monitoring/k8s  worker  kube-state-metrics  1
kube_node_role  kube-rbac-proxy-main  https-main  kube-state-metrics  openshift-monitoring  ip-1-2-3-5.ec2.internal  openshift-monitoring/k8s  worker  kube-state-metrics  1
[...]

And we have kube_pod_info with these labels:

Name           container             created_by_kind  created_by_name  endpoint    host_ip        host_network  job                 namespace       node                     pod                                               pod_ip       priority_class           prometheus                service             uid                                   Value
kube_pod_info  kube-rbac-proxy-main  <none>           <none>           https-main  10.201.24.232  false         kube-state-metrics  openshift-etcd  ip-1-2-3-6.ec2.internal  etcd-guard-ip-10-201-24-232.ec2.internal          10.128.2.14  system-cluster-critical  openshift-monitoring/k8s  kube-state-metrics  a2eec7b0-9f29-42b4-853d-6919d963ffa1  1
kube_pod_info  kube-rbac-proxy-main  <none>           <none>           https-main  10.201.24.232  false         kube-state-metrics  openshift-etcd  ip-1-2-3-6.ec2.internal  revision-pruner-13-ip-10-201-24-232.ec2.internal  10.128.2.4   system-node-critical     openshift-monitoring/k8s  kube-state-metrics  df5cdd67-b0f5-4896-b0b0-85095a9f3122  1

We will use on(...) and group_left(...) PromQL operators. I had some issues understanding what these do, so here is mine interpretation:

* because values are always 1 in these vectors, it is safe to multiply these.

on(...) allows me to define common label(s) that should be used to match two different vectors.

group_left(...) ... thinking, thinking, nah. I forgot mine mental model here :-/

And this is the final query I used:

sum(
    kube_pod_info{} * on(node) group_left(role) kube_node_role{role="worker"}
) by(node)

These links helped me a lot:

• How to have labels for machine roles
• Prometheus Cheat Sheet - How to Join Multiple Metrics (Vector Matching)
• PromQL operators

My Prometheus@OpenShift cheat-sheet

Prometheus is monitoring solution in OpenShift and I'm reading some basic out of it after some performance tests. Here are the queries I'm using:

Get CPU consumption by pods xyz...:

sum(pod_name:container_cpu_usage:sum{pod_name=~'xyz.*',namespace='qa'})

Now for memory usage (these "POD" and "''" container names seems to be doubling the value):

sum(container_memory_usage_bytes{namespace='qa', pod_name=~'xyz.*', container_name!='POD', container_name!=''})

Also see these nice examples on how to construct query.

To Querying Prometheus via API, I have used range query and this Python code:

assert start is not None and end is not None, \
    "We need timerange to approach Prometheus"

# Get data from Prometheus
token = 'your `oc whoami -t`'
url = 'https://prometheus-k8s.openshift-monitoring.svc:9091/api/v1/query_range'   # I'm running this inside the cluster, so I can use internal hostname
headers = {
    'Authorization': f'Bearer {token}',
    'Content-Type': 'application/json',
}
params = {
    'query': monitoring_query,   # this will be some query from above
    'step': monitoring_step,   # using 60 seconds here
    'start': start.strftime('%s'),
    'end': end.strftime('%s'),
}
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)   # security is hard ;)
response = requests.get(url, headers=headers, params=params, verify=False)

# Check that what we got back seems OK
response.raise_for_status()
json_response = response.json()
assert json_response['status'] == 'success'
assert 'data' in json_response
assert 'result' in json_response['data']
assert len(json_response['data']['result']) == 1
assert 'values' in json_response['data']['result'][0]

data = [float(i[1]) for i in json_response['data']['result'][0]['values']]