Writing metrics SQL queries

Gain insights into your infrastructure and applications by visualizing your metrics. Create tailored charts by writing your own custom SQL queries.

Getting started

Chart free memory for host web-01 using metric memory_free_bytes:

SELECT {{time}} AS time,
  avgMerge(value_avg) AS value,
  'Free RAM' AS series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
  AND name = 'memory_free_bytes'
  AND metricTag('hostname') = 'web-01'
GROUP BY time

• Dashboard queries use ClickHouse SQL, which is largely similar to ANSI SQL you’re likely familiar with. In SQL query above, we use avgMerge ClickHouse function.

• On top of SQL, dashboard queries feature variables like {{source}} or {{time}}. Variables conveniently insert selected source, current time range, and other selected values into your queries. Read more about query variables.

• Chart queries don't query pure metrics directly. Metric values are aggregated into efficient metrics. You can use name and metricTag() to access metric name and tags in your queries.

Which columns can be used in metrics queries?


We automatically make relevant columns available for metrics.

You can find available columns in Sources → your source → Configure → Advanced settings → Logs to metrics section:

Metrics

• Rows is the number of underlying metric data points.

• Value metric represents the immediate value of the metric. Use it with gauge metrics or anytime you want to chart the metric value directly.

• Rate represents how much the metric value has increased per second. Common use for rates is to calculate the number of requests per second. Use rate with counter metrics.


Rows, value and rate are aggregated metrics. Check out the examples below to see how to use the aggregated metrics.

Group by

• Name column is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.

• Tags column contains all metric tags, commonly called labels. Use metricTag(‘tag_name’) to get value for any tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.

• Series_id column is a unique value for each metric series. Use GROUP BY series_id when aggregating your metrics.

Metrics query examples

CPU load

Chart CPU load using cpu_load gauge metric:


WITH grouped_values AS
-- get maximum value for each unique series within the time interval
(
  SELECT
    {{time}} AS time, 
    maxMerge(value_max) as max_value
  FROM {{source}}
  WHERE name = 'cpu_load'
    AND time BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY series_id, time 
)
-- SUM all series to get the total value
SELECT time, SUM(max_value) as value
FROM grouped_values
GROUP BY time

Select cpu_load metric by its name using WHERE name = 'cpu_load'.

Use maxMerge(value_max) to get the maximal CPU load in each time interval. Calculating maximum gives you an accurate visualization of CPU load peaks. Using avgMerge(value_avg) is a good alternative if you care about average values more than peaks.

Write a subquery to aggregate and use a second query to get the final result.

• Subquery grouped_values calculates a maximum for each metric series. Using GROUP BY series_id ensures that you get a correct maximum for each series.
• In the second query, sum all metrics series together to get the final result.

Number of requests

Chart the average number of served requests using requests_total counter metric:

WITH 
-- get average rate for each unique series within the time interval
grouped_rate AS (
  SELECT
    {{time}} AS time, 
    series_id,
    avgMerge(rate_avg) AS avg_rate
  FROM {{source}}
  WHERE name = 'requests_total' 
     AND time BETWEEN {{start_time}} AND {{end_time}}
  GROUP BY time, series_id
)
-- SUM all series to get total rate
SELECT 'Requests per second' AS series, SUM(avg_rate) as value, time
FROM grouped_rate
GROUP BY time

Select requests_total metric using WHERE name = 'requests_total'.

Calculate the average number of served requests using avgMerge(rate_avg). The metric requests_total is a counter metric. It represents the total number of requests. Use rate_avg to get an average increase in the metric. Usually, you should avoid using value_* with counter metrics.

Just like in the previous example, you need to aggregate first to get the average for each metric series. Then, SUM() the averages in the second query to get the final average number of served requests.

Use SELECT 'Requests per second' as series to set the name of series. It will be used in tooltips and the chart legend.



Number of running servers

Get the number of running servers by counting the number of values for hostname tag:

SELECT 
  {{time}} AS time, 
  COUNT(DISTINCT metricTag('hostname')) AS value
FROM {{source}}
WHERE name = 'up'
  AND time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time

Select the up metric using WHERE name = 'up' to only get hostname values for running servers. You should always select metrics by name to prevent other metrics tags and values from causing inaccuracies in your chart. Selecting only the metrics you need will also make your queries faster.

Count the total number of unique values for the hostname tag using COUNT(DISTINCT metricTag('hostname')).

Need help?

Please let us know at hello@betterstack.com.
We're happy to help! 🙏