Hi, can you please provide some assistance on converting PromQL to Clickhouse query? My Clickhouse queries are not returning any data. For start I would like to receive CPU usage for all hosts in a table:

100 * (1 - (avg by(host_name) (irate(system_cpu_time{state="idle"}[5m]))))

But this does not returning anything:

SELECT

host_name,

100 * (1 - avg(system_cpu_time_idle) / avg(system_cpu_time_total)) AS idle_cpu_percentage

FROM

signoz_metrics.time_serie_v2

WHERE

datetime >= now() - INTERVAL 5 MINUTE

GROUP BY

host_name

It's an invalid query. Is there a reason why you want to write a ClickHouse query instead of using the query builder?

Query builder not giving correct results and PromQL not sorting descending.

You should use

avg_rate

operator from the options.

avg_rate

not giving correct values

What is the correct value?

If I run

top -bn2 | grep '%Cpu' | tail -1 | grep -P '(....|...) id,'|awk '{print "CPU Usage: " 100-$8 "%"}'

on one of those host I'll get CPU Usage: 1.4% It will not be 0.17 (17%) as shown in previous screenshot I sent

You are looking at the raw data. Please try

avg_rate

from operator and use the formula expression

100 * (1-A)

to achieve result of promql query.

Do you mean trying

avg_rate

in Query builder ?

Yes

avg_rate

operator in Query builder does not generating correct values (actual CPU usage on the machines) However, I finally figured out how to achieve this with clickhouse:

WITH

-- Query to get the total CPU time for all hosts that exist in host_info

total_cpu_time AS (

SELECT

JSONExtractString(t.labels, 'host_name') AS host_name,

SUM(s.value) AS total_time

FROM

signoz_metrics.samples_v2 s

INNER JOIN

signoz_metrics.time_series_v2 t

ON

s.fingerprint = t.fingerprint

INNER JOIN

signoz_metrics.host_info hi ON JSONExtractString(t.labels, 'host_name') = hi.host_name

WHERE

s.metric_name = 'system_cpu_time'

AND JSONExtractString(t.labels, 'state') IN ('nice', 'user', 'steal', 'wait', 'system', 'softirq', 'idle')  -- Relevant CPU states

AND s.timestamp_ms >= (toUnixTimestamp(now() - INTERVAL 5 MINUTE) * 1000)  -- Last 5 minutes

GROUP BY

JSONExtractString(t.labels, 'host_name')

),

-- Query to get the idle CPU time for all hosts that exist in host_info

idle_cpu_time AS (

SELECT

JSONExtractString(t.labels, 'host_name') AS host_name,

SUM(s.value) AS idle_time

FROM

signoz_metrics.samples_v2 s

INNER JOIN

signoz_metrics.time_series_v2 t

ON

s.fingerprint = t.fingerprint

INNER JOIN

signoz_metrics.host_info hi ON JSONExtractString(t.labels, 'host_name') = hi.host_name

WHERE

s.metric_name = 'system_cpu_time'

AND JSONExtractString(t.labels, 'state') = 'idle'

AND s.timestamp_ms >= (toUnixTimestamp(now() - INTERVAL 5 MINUTE) * 1000)

GROUP BY

JSONExtractString(t.labels, 'host_name')

)

-- Calculate the CPU usage percentage for all hosts in host_info

SELECT

hi.host_name AS Host,

hi.ip_address AS IP,

ROUND(100 * (1 - (ic.idle_time / tc.total_time)), 2) AS cpu_usage

FROM

total_cpu_time tc

LEFT JOIN

idle_cpu_time ic ON tc.host_name = ic.host_name

LEFT JOIN

signoz_metrics.host_info hi ON tc.host_name = hi.host_name

WHERE

hi.host_name IS NOT NULL  -- Ensure the host_name exists in host_info

ORDER BY

cpu_usage DESC

Good but I didn't say

avg_rate

gives the result. You have to use the formula on top of it then it is the same as the promql and must give the correct result. If you prefer to use ClickHouse go ahead with it.