βΉοΈ Skipped - page is already crawled
| Filter | Status | Condition | Details |
|---|---|---|---|
| HTTP status | PASS | download_http_code = 200 | HTTP 200 |
| Age cutoff | PASS | download_stamp > now() - 6 MONTH | 0.3 months ago |
| History drop | PASS | isNull(history_drop_reason) | No drop reason |
| Spam/ban | PASS | fh_dont_index != 1 AND ml_spam_score = 0 | ml_spam_score=0 |
| Canonical | PASS | meta_canonical IS NULL OR = '' OR = src_unparsed | Not set |
| Property | Value |
|---|---|
| URL | https://betterstack.com/docs/logs/dashboards/sql-queries/ |
| Last Crawled | 2026-04-03 20:56:17 (8 days ago) |
| First Indexed | 2023-09-28 12:20:21 (2 years ago) |
| HTTP Status Code | 200 |
| Meta Title | Writing SQL queries | Better Stack Documentation |
| Meta Description | Take your observability to the next level by visualizing exactly whatβs vital for you. Create tailored charts by writing your own custom SQL queries. |
| Meta Canonical | null |
| Boilerpipe Text | Create tailored charts by writing your own custom SQL queries.
Want a dashboard that works out of the box?
Use one of our ready-to-use templates to
create a dashboard
Getting started
Create your first chart using a custom SQL query. Start by creating a dashboard in
Dashboards
β
Create dashboard
.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied!
Getting
Missing columns
error?
Try selecting a different source or
add metric for the column
to your source.
Dashboard queries use
ClickHouse SQL
, which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the
countMerge
ClickHouse function
.
Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about
ClickHouse aggregate combinators
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
.
Queries access a unified row-based metrics schema. This schema stores all data in a unified format with metrics stored as named values with labels in a
tags
map. This allows for fast and efficient querying. Read more about
querying the metrics
below.
Not familiar with SQL?
Follow our examples below or start with
SQL tutorial from W3Schools
Curious about ClickHouse SQL?
Read more about
ClickHouse SQL syntax
and
compatibility with ANSI SQL
How to query the metrics?
We stores all data in a unified format, making querying consistent.
Accessing Custom Fields:
All custom or dynamic columns (labels/tags) use the
label()
function. For example,
response_status
becomes
label('response_status')
. Labels are always strings, so
toString(label(...))
is redundant.
Standard Columns:
Columns like
time
,
dt
,
name
,
_row_type
,
series_id
, and
tags
are part of the schema and should
not
use the
label()
wrapper.
Row Type Filtering:
Each ingested event creates multiple rows. Use
_row_type = 'log'
or
_row_type = 'span'
to get counts of the original events. Do
not
use
_row_type
when querying named metrics (where
name = 'metric_name'
).
Type Handling for Labels:
Since
label()
returns a string, explicit type conversions (
toUInt16OrNull()
,
toFloat64OrNull()
) are required for numeric comparisons or operations.
Event Counting:
Use
countMerge(events_count)
for aggregated metrics queries.
No Nested Aggregate Functions:
ClickHouse does not allow nesting aggregate functions (e.g.,
sum(countMerge(...))
). Merge functions (
countMerge
,
sumMerge
,
avgMerge
, etc.) already combine internal states.
Time Filtering:
Use
time
in
WHERE
clauses for accurate filtering across bucket boundaries, and
{{time}}
in
SELECT
/
GROUP BY
for bucketing.
Want to count the number of lines?
Use
countMerge(events_count)
. Avoid using
count(*)
as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
For metrics with aggregations, use
avgMerge(value_avg)
to get the average value of a metric.
Similarly, use
minMerge(value_min)
and
maxMerge(value_max)
to get a metric's minimum and maximum values.
Make sure to always match the -Merge function with the metric suffix correctly:
β
Correct: Using
sum
Merge with
_sum
suffix:
sumMerge(bucket_sum)
β Incorrect: Mixing
sum
and
count
:
sumMerge(bucket_count)
Want to use percentiles?
Check out the
complete example on percentiles
below.
Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in
Sources
β
your source
β
Configure
β
Logs to metrics
tab.
See
Query with
to know how to query the metric.
Use either
ClickHouse aggregations
or name of the metric depending on the configured aggregations.
You can
add more metrics
to use in your SQL queries.
Query examples
See the examples below for full queries using
Metrics
.
Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied!
Use
countMerge(events_count)
to count the number of log lines. Avoid using
count(*)
.
Filter error log lines using the
label('level')
metric. The metric has to be present in
Logs to metrics
configuration with
No aggregation
selected.
Average request duration
Chart the average duration of request:
Query for average request duration
Copied!
Use
avgMerge(value_avg)
to calculate an average for the
request_duration
metric. The metric has to be added in
Logs to metrics
β
Metrics
with
avg
aggregation.
Column names
Request duration
will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in
Chart setting
β
Axes
.
Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied!
Here we use the
histogramQuantile(0.95)
helper function to directly calculate the 95th percentile for the
request_duration_seconds
metric. This assumes
request_duration_seconds
is a histogram metric and
histogramQuantile
is defined in the database (which it is). If querying multiple metrics, use
quantilePrometheusHistogramArrayMergeIf
as described in the histogram documentation.
Want to learn how to use the aggregation functions?
Check out the official guide on
ClickHouse Aggregate Combinators
Prometheus-like metrics
Want to access metric labels?
Use
label('tag_name')
to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying.
We process your metrics and store them as
ClickHouse -State combinators
.
Everything about
querying the metrics
written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the
Logs to metrics
configuration. We automatically configure
Metrics
for your Prometheus-like metrics.
Let's take a look at the
Logs to metrics
configuration for metric sources.
You can find it in
Sources
β
your source
β
Configure
β
Logs to metrics
tab:
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.β¨
What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about
metric types
Tags
contain all metric tags, commonly called labels. Use
label('tag_name')
to get value for any tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Name
is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
Series_id
is a unique value for each metric series. Use
GROUP BY series_id
when aggregating your metrics.
What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with
hostname
tag will have one series for each hostname.
Examples with metrics
See complete SQL query examples using
ClickHouse aggregator combinators
and Prometheus-like metrics.
View Prometheus metrics
List your metrics with their tags and values as a
Table
chart type:
Query selecting raw metrics
Copied!
Select metric
name
,
tags
, and
time
. Use
anyLastMerge(value_last)
to get an unaggregated value for the metric.
Visualize the data using a
Table
chart type. In the right sidebar, choose
Chart settings
β
Chart
β
Table
.
CPU load
Chart CPU load using
cpu_load
gauge metric:β¨
Query calculating CPU load
Copied!
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 calculates a maximum for each metric series. Using
GROUP BY series_id
ensures that you get a correct maximum for each series.
In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting
label('host')
as
series
.
Number of requests
Chart the average number of served requests using
requests_total
counter metric:
Query for number of requests
Copied!
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.
As 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 the 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:
Query counting number of unique hosts
Copied!
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 label('hostname'))
.
Need help?
Please let us know at
hello@betterstack.com
.
We're happy to help! π
Querying legacy sources created before 2026
Create your first chart using a custom SQL query. Start by creating a dashboard in
Dashboards
β
Create dashboard
.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied!
Getting
Missing columns
error?
Try selecting a different source or
add metric for the column
to your source.
Dashboard queries use
ClickHouse SQL
, which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the
countMerge
ClickHouse function
.
Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about
ClickHouse aggregate combinators
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
.
Queries don't access your logs directly. To make querying fast and efficient we process your logs into metrics and store them as
ClickHouse -State combinators
. Dashboard queries use
ClickHouse -Merge functions
to get the values of the metrics. Read more about
querying the metrics
below.
Not familiar with SQL?
Follow our examples below or start with
SQL tutorial from W3Schools
Curious about ClickHouse SQL?
Read more about
ClickHouse SQL syntax
and
compatibility with ANSI SQL
How to query the metrics? (before 2026)
Want to count the number of lines?
Use
countMerge(events_count)
. Avoid using
count()
as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
Query metrics with no aggregations by their name just like regular database columns.
For metrics with aggregations, use
avgMerge(metric_name_avg)
to get the average value of a metric.
Similarly, use
minMerge(metric_name_min)
and
maxMerge(metric_name_max)
to get a metric's minimum and maximum values.
Make sure to always match the -Merge function with the metric suffix correctly:
β
Correct: Using
sum
Merge with
_sum
suffix:
sumMerge(metric_name_sum)
β Incorrect: Mixing
sum
and
count
:
sumMerge(metric_name_count)
Want to use percentiles?
Check out the
complete example on percentiles
below.
Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in
Sources
β
your source
β
Configure
β
Logs to metrics
tab.
See
Query with
to know how to query the metric.
Use either
ClickHouse aggregations
or name of the metric depending on the configured aggregations.
You can
add more metrics
to use in your SQL queries.
Query examples (before 2026)
See the examples below for full queries using
Metrics
.
Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied!
Use
countMerge(events_count)
to count the number of log lines. Avoid using
count(*)
.
Filter error log lines using the
level
metric. The metric has to be present in
Logs to metrics
configuration with
No aggregation
selected.
Average request duration
Chart the average duration of request:
Query for average request duration
Copied!
Use
avgMerge(request_duration_avg)
to calculate an average for the
request_duration
metric. The metric has to be added in
Logs to metrics
β
Metrics
with
avg
aggregation.
Column names
Request duration
will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in
Chart setting
β
Axes
.
Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied!
Here we have the
request_duration
metric with
p50
,
p90
,
p95
, and
p99
percentiles set up in Logs to metrics. Calculate the 95th percentile request duration using
quantilesMerge
. The
quantilesMerge
function gives us all four percentiles. We select the third percentile using
[3]
.
Want to learn how to use the aggregation functions?
Check out the official guide on
ClickHouse Aggregate Combinators
Prometheus-like metrics (before 2026)
Want to access metric labels?
Use
metricTag('tag_name')
to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying.
We process your metrics and store them as
ClickHouse -State combinators
.
Everything about
querying the metrics
written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the
Logs to metrics
configuration. We automatically configure
Metrics
for your Prometheus-like metrics.
Let's take a look at the
Logs to metrics
configuration for metric sources.
You can find it in
Sources
β
your source
β
Configure
β
Logs to metrics
tab:
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.β¨
What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about
metric types
Tags
contain 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.
Name
is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
Series_id
is a unique value for each metric series. Use
GROUP BY series_id
when aggregating your metrics.
What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with
hostname
tag will have one series for each hostname.
Examples with metrics (before 2026)
See complete SQL query examples using
ClickHouse aggregator combinators
and Prometheus-like metrics.
View Prometheus metrics
List your metrics with their tags and values as a
Table
chart type:
Query selecting raw metrics
Copied!
Select metric
name
,
tags
, and
time
. Use
anyLastMerge(value_last)
to get an unaggregated value for the metric.
Visualize the data using a
Table
chart type. In the right sidebar, choose
Chart settings
β
Chart
β
Table
.
CPU load
Chart CPU load using
cpu_load
gauge metric:β¨
Query calculating CPU load
Copied!
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 calculates a maximum for each metric series. Using
GROUP BY series_id
ensures that you get a correct maximum for each series.
In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting
metricTag('host')
as
series
.
Number of requests
Chart the average number of served requests using
requests_total
counter metric:
Query for number of requests
Copied!
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.
As 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 the 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:
Query counting number of unique hosts
Copied!
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'))
. |
| Markdown | Platform
[AI SRE Agentic root cause analysis](https://betterstack.com/ai-sre)
[Incident management & on-call Move fast when things break](https://betterstack.com/incident-management)
[Uptime monitoring The most reliable uptime monitoring](https://betterstack.com/uptime)
[Status page Communicate downtime & build trust](https://betterstack.com/status-page)
[Tracing eBPF-based OpenTelemetry-native tracing](https://betterstack.com/tracing)
[Log management Collect insights across your stack](https://betterstack.com/log-management)
[Infrastructure monitoring OpenTelemetry-native infrastructure monitoring](https://betterstack.com/infrastructure-monitoring)
[Real user monitoring Session replay, web vitals & product analytics](https://betterstack.com/real-user-monitoring)
[Error tracking AIβnative error tracking built on Better Stack](https://betterstack.com/error-tracking)
[Data warehouse Time series data warehouse as an API](https://betterstack.com/warehouse)
[Documentation](https://betterstack.com/docs/) [Pricing](https://betterstack.com/pricing)
Company
[Work at Better Stack](https://betterstack.com/careers)
[Engineering](https://betterstack.com/careers/engineering)
[Security](https://betterstack.com/security)
[Blog](https://betterstack.com/community/blog)
[Changelog](https://betterstack.com/tag/changelog)
[Enterprise](https://betterstack.com/enterprise)
[Sign in](https://betterstack.com/users/sign-in) [Sign up](https://betterstack.com/users/sign-up)
Platform
[Documentation](https://betterstack.com/docs/) [Pricing](https://betterstack.com/pricing)
Community
Company
[Enterprise](https://betterstack.com/enterprise)
Back
[AI SRE Agentic root cause analysis](https://betterstack.com/ai-sre)
[Incident management & on-call Move fast when things break](https://betterstack.com/incident-management)
[Uptime monitoring The most reliable uptime monitoring](https://betterstack.com/uptime)
[Status page Communicate downtime & build trust](https://betterstack.com/status-page)
[Tracing eBPF-based OpenTelemetry-native tracing](https://betterstack.com/tracing)
[Log management Collect insights across your stack](https://betterstack.com/log-management)
[Infrastructure monitoring OpenTelemetry-native infrastructure monitoring](https://betterstack.com/infrastructure-monitoring)
[Real user monitoring Session replay, web vitals & product analytics](https://betterstack.com/real-user-monitoring)
[Error tracking AIβnative error tracking built on Better Stack](https://betterstack.com/error-tracking)
[Data warehouse Time series data warehouse as an API](https://betterstack.com/warehouse)
Back
[Community home](https://betterstack.com/community)
[Guides](https://betterstack.com/community/guides)
[Questions](https://betterstack.com/community/questions)
[Comparisons](https://betterstack.com/community/comparisons)
Back
[Work at Better Stack](https://betterstack.com/careers)
[Engineering](https://betterstack.com/careers/engineering)
[Security](https://betterstack.com/security)
[Blog](https://betterstack.com/community/blog)
[Changelog](https://betterstack.com/tag/changelog)
Telemetry
[Getting started](https://betterstack.com/docs/getting-started/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) [Demos](https://betterstack.com/demos/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) [Uptime](https://betterstack.com/docs/uptime/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) [Telemetry](https://betterstack.com/docs/logs/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) [Errors](https://betterstack.com/docs/errors/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) [Warehouse](https://betterstack.com/docs/warehouse/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Getting started](https://betterstack.com/docs/getting-started/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Demos](https://betterstack.com/demos/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Uptime](https://betterstack.com/docs/uptime/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Telemetry](https://betterstack.com/docs/logs/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Errors](https://betterstack.com/docs/errors/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
- [Warehouse](https://betterstack.com/docs/warehouse/?utm_content&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
###### Explore documentation
# Writing SQL queries
Create tailored charts by writing your own custom SQL queries.
#### Want a dashboard that works out of the box?
Use one of our ready-to-use templates to [create a dashboard](https://telemetry.betterstack.com/team/0/dashboards?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
## Getting started
Create your first chart using a custom SQL query. Start by creating a dashboard in [Dashboards](https://telemetry.betterstack.com/team/0/dashboards) β **Create dashboard**.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied\!
```
SELECT {{time}} AS time,
countMerge(events_count) AS value,
label('level') AS series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
AND _row_type = 'log'
GROUP BY time, series
```
![Chart preview]()
#### Getting `Missing columns` error?
Try selecting a different source or [add metric for the column](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) to your source.
Dashboard queries use [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the `countMerge` [ClickHouse function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge).
#### Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about [ClickHouse aggregate combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
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](https://betterstack.com/docs/logs/dashboards/variables/).
Queries access a unified row-based metrics schema. This schema stores all data in a unified format with metrics stored as named values with labels in a `tags` map. This allows for fast and efficient querying. Read more about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) below.
#### Not familiar with SQL?
Follow our examples below or start with [SQL tutorial from W3Schools](https://www.w3schools.com/sql/sql_syntax.asp)
#### Curious about ClickHouse SQL?
Read more about [ClickHouse SQL syntax](https://clickhouse.com/docs/en/sql-reference/syntax) and [compatibility with ANSI SQL](https://clickhouse.com/docs/en/sql-reference/ansi)
## How to query the metrics?
We stores all data in a unified format, making querying consistent.
- **Accessing Custom Fields:** All custom or dynamic columns (labels/tags) use the `label()` function. For example, `response_status` becomes `label('response_status')`. Labels are always strings, so `toString(label(...))` is redundant.
- **Standard Columns:** Columns like `time`, `dt`, `name`, `_row_type`, `series_id`, and `tags` are part of the schema and should **not** use the `label()` wrapper.
- **Row Type Filtering:** Each ingested event creates multiple rows. Use `_row_type = 'log'` or `_row_type = 'span'` to get counts of the original events. Do **not** use `_row_type` when querying named metrics (where `name = 'metric_name'`).
- **Type Handling for Labels:** Since `label()` returns a string, explicit type conversions (`toUInt16OrNull()`, `toFloat64OrNull()`) are required for numeric comparisons or operations.
- **Event Counting:** Use `countMerge(events_count)` for aggregated metrics queries.
- **No Nested Aggregate Functions:** ClickHouse does not allow nesting aggregate functions (e.g., `sum(countMerge(...))`). Merge functions (`countMerge`, `sumMerge`, `avgMerge`, etc.) already combine internal states.
- **Time Filtering:** Use `time` in `WHERE` clauses for accurate filtering across bucket boundaries, and `{{time}}` in `SELECT`/`GROUP BY` for bucketing.
#### Want to count the number of lines?
Use `countMerge(events_count)`. Avoid using `count(*)` as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
For metrics with aggregations, use `avgMerge(value_avg)` to get the average value of a metric.
Similarly, use `minMerge(value_min)` and `maxMerge(value_max)` to get a metric's minimum and maximum values. Make sure to always match the -Merge function with the metric suffix correctly:
- β
Correct: Using **sum**Merge with **\_sum** suffix: `sumMerge(bucket_sum)`
- β Incorrect: Mixing **sum** and **count**: `sumMerge(bucket_count)`
**Want to use percentiles?**
Check out the [complete example on percentiles](https://betterstack.com/docs/logs/dashboards/sql-queries/#percentile-request-duration) below.
### Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab.
See **Query with** to know how to query the metric.
Use either [ClickHouse aggregations](https://clickhouse.com/docs/en/sql-reference/aggregate-functions) or name of the metric depending on the configured aggregations.
You can [add more metrics](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/) to use in your SQL queries.
![Screenshot of Logs to metrics tables]()
## Query examples
See the examples below for full queries using **Metrics**.
### Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied\!
```
SELECT {{time}} AS time,
countMerge(events_count) AS value,
label('level') AS series
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
AND _row_type = 'log'
AND series = 'error'
GROUP BY time, series
```
Use `countMerge(events_count)` to count the number of log lines. Avoid using `count(*)`.
Filter error log lines using the `label('level')` metric. The metric has to be present in **Logs to metrics** configuration with **No aggregation** selected.
### Average request duration
Chart the average duration of request:
Query for average request duration
Copied\!
```
SELECT {{time}} AS time,
avgMerge(value_avg) AS "Request duration"
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
AND name = 'request_duration'
GROUP BY time
```
Use `avgMerge(value_avg)` to calculate an average for the `request_duration` metric. The metric has to be added in **Logs to metrics** β **Metrics** with `avg` aggregation.
Column names `Request duration` will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in **Chart setting** β **Axes**.
### Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied\!
```
SELECT {{time}} AS time,
histogramQuantile(0.95) AS "95th percentile duration"
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
AND name = 'request_duration_seconds'
GROUP BY time, series
```
Here we use the `histogramQuantile(0.95)` helper function to directly calculate the 95th percentile for the `request_duration_seconds` metric. This assumes `request_duration_seconds` is a histogram metric and `histogramQuantile` is defined in the database (which it is). If querying multiple metrics, use `quantilePrometheusHistogramArrayMergeIf` as described in the histogram documentation.
#### Want to learn how to use the aggregation functions?
Check out the official guide on [ClickHouse Aggregate Combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
## Prometheus-like metrics
#### Want to access metric labels?
Use `label('tag_name')` to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying. We process your metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state).
Everything about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the **Logs to metrics** configuration. We automatically configure **Metrics** for your Prometheus-like metrics.
Let's take a look at the **Logs to metrics** configuration for metric sources.
You can find it in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab:
![Screenshot of Logs to metrics for Prometheus-like metrics]()
- **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.
#### What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about [metric types](https://prometheus.io/docs/concepts/metric_types/)
- **Tags** contain all metric tags, commonly called labels. Use `label('tag_name')` to get value for any tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
- **Name** is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
- **Series\_id** is a unique value for each metric series. Use `GROUP BY series_id` when aggregating your metrics.
#### What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with `hostname` tag will have one series for each hostname.
## Examples with metrics
See complete SQL query examples using [ClickHouse aggregator combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) and Prometheus-like metrics.
### View Prometheus metrics
List your metrics with their tags and values as a **Table** chart type:
Query selecting raw metrics
Copied\!
```
SELECT {{time}} AS time, tags, name, anyLastMerge(value_last)
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, tags, name
LIMIT 10
```
Select metric `name`, `tags`, and `time`. Use `anyLastMerge(value_last)` to get an unaggregated value for the metric.
Visualize the data using a **Table** chart type. In the right sidebar, choose **Chart settings** β **Chart** β **Table**.
### CPU load
Chart CPU load using `cpu_load` gauge metric:
Query calculating CPU load
Copied\!
```
-- sum values across series
SELECT time, series, SUM(series_maximum)/100.0 as value
FROM (
SELECT
{{time}} AS time,
label('host') AS series,
-- calculate average for each series
maxMerge(value_max) as series_maximum
FROM {{source}}
WHERE name = 'cpu_load' AND dt BETWEEN {{start_time}} AND {{end_time}}
GROUP BY series_id, time, series
)
GROUP BY time, series
```
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 calculates a maximum for each metric series. Using `GROUP BY series_id` ensures that you get a correct maximum for each series.
- In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting `label('host')` as `series`.
### Number of requests
Chart the average number of served requests using `requests_total` counter metric:
Query for number of requests
Copied\!
```
-- sum values across series to get total rate
SELECT time, 'Requests per second' AS series, SUM(avg_rate) as value
FROM (
SELECT
{{time}} AS time,
-- calculate average rate for each series
avgMerge(rate_avg) AS avg_rate
FROM {{source}}
WHERE name = 'requests_total' AND dt BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series_id
)
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.
As 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 the 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:
Query counting number of unique hosts
Copied\!
```
SELECT
{{time}} AS time,
COUNT(DISTINCT label('hostname')) AS value
FROM {{source}}
WHERE name = 'up'
AND dt 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 label('hostname'))`.
## Need help?
Please let us know at [hello@betterstack.com](mailto:hello@betterstack.com).
We're happy to help! π
***
## Querying legacy sources created before 2026
Create your first chart using a custom SQL query. Start by creating a dashboard in [Dashboards](https://telemetry.betterstack.com/team/0/dashboards) β **Create dashboard**.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied\!
```
SELECT {{time}} AS time,
countMerge(events_count) AS value,
level AS series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series
```
![Chart preview]()
#### Getting `Missing columns` error?
Try selecting a different source or [add metric for the column](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) to your source.
Dashboard queries use [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the `countMerge` [ClickHouse function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge).
#### Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about [ClickHouse aggregate combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
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](https://betterstack.com/docs/logs/dashboards/variables/).
Queries don't access your logs directly. To make querying fast and efficient we process your logs into metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state). Dashboard queries use [ClickHouse -Merge functions](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge) to get the values of the metrics. Read more about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) below.
#### Not familiar with SQL?
Follow our examples below or start with [SQL tutorial from W3Schools](https://www.w3schools.com/sql/sql_syntax.asp)
#### Curious about ClickHouse SQL?
Read more about [ClickHouse SQL syntax](https://clickhouse.com/docs/en/sql-reference/syntax) and [compatibility with ANSI SQL](https://clickhouse.com/docs/en/sql-reference/ansi)
## How to query the metrics? (before 2026)
#### Want to count the number of lines?
Use `countMerge(events_count)`. Avoid using `count()` as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
Query metrics with no aggregations by their name just like regular database columns.
For metrics with aggregations, use `avgMerge(metric_name_avg)` to get the average value of a metric.
Similarly, use `minMerge(metric_name_min)` and `maxMerge(metric_name_max)` to get a metric's minimum and maximum values. Make sure to always match the -Merge function with the metric suffix correctly:
- β
Correct: Using **sum**Merge with **\_sum** suffix: `sumMerge(metric_name_sum)`
- β Incorrect: Mixing **sum** and **count**: `sumMerge(metric_name_count)`
**Want to use percentiles?**
Check out the [complete example on percentiles](https://betterstack.com/docs/logs/dashboards/sql-queries/#percentile-request-duration) below.
### Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab.
See **Query with** to know how to query the metric.
Use either [ClickHouse aggregations](https://clickhouse.com/docs/en/sql-reference/aggregate-functions) or name of the metric depending on the configured aggregations.
You can [add more metrics](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/) to use in your SQL queries.
![Screenshot of Logs to metrics tables]()
## Query examples (before 2026)
See the examples below for full queries using **Metrics**.
### Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied\!
```
SELECT {{time}} AS time, countMerge(events_count) AS value, level as series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
AND level = 'error'
GROUP BY time, series
```
Use `countMerge(events_count)` to count the number of log lines. Avoid using `count(*)`.
Filter error log lines using the `level` metric. The metric has to be present in **Logs to metrics** configuration with **No aggregation** selected.
### Average request duration
Chart the average duration of request:
Query for average request duration
Copied\!
```
SELECT {{time}} AS time,
avgMerge(request_duration_avg) AS "Request duration"
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time
```
Use `avgMerge(request_duration_avg)` to calculate an average for the `request_duration` metric. The metric has to be added in **Logs to metrics** β **Metrics** with `avg` aggregation.
Column names `Request duration` will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in **Chart setting** β **Axes**.
### Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied\!
```
SELECT {{time}} AS time,
quantilesMerge(0.5, 0.9, 0.95, 0.99)(request_duration_quantiles)[3]
AS "95th percentile duration"
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series
```
Here we have the `request_duration` metric with `p50`, `p90`, `p95`, and `p99` percentiles set up in Logs to metrics. Calculate the 95th percentile request duration using `quantilesMerge`. The `quantilesMerge` function gives us all four percentiles. We select the third percentile using `[3]`.
#### Want to learn how to use the aggregation functions?
Check out the official guide on [ClickHouse Aggregate Combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
## Prometheus-like metrics (before 2026)
#### Want to access metric labels?
Use `metricTag('tag_name')` to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying. We process your metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state).
Everything about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the **Logs to metrics** configuration. We automatically configure **Metrics** for your Prometheus-like metrics.
Let's take a look at the **Logs to metrics** configuration for metric sources.
You can find it in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab:
![Screenshot of Logs to metrics for Prometheus-like metrics]()
- **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.
#### What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about [metric types](https://prometheus.io/docs/concepts/metric_types/)
- **Tags** contain 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.
- **Name** is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
- **Series\_id** is a unique value for each metric series. Use `GROUP BY series_id` when aggregating your metrics.
#### What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with `hostname` tag will have one series for each hostname.
## Examples with metrics (before 2026)
See complete SQL query examples using [ClickHouse aggregator combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) and Prometheus-like metrics.
### View Prometheus metrics
List your metrics with their tags and values as a **Table** chart type:
Query selecting raw metrics
Copied\!
```
SELECT {{time}} AS time, tags, name, anyLastMerge(value_last)
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, tags, name
LIMIT 10
```
Select metric `name`, `tags`, and `time`. Use `anyLastMerge(value_last)` to get an unaggregated value for the metric.
Visualize the data using a **Table** chart type. In the right sidebar, choose **Chart settings** β **Chart** β **Table**.
### CPU load
Chart CPU load using `cpu_load` gauge metric:
Query calculating CPU load
Copied\!
```
-- sum values across series
SELECT time, series, SUM(series_maximum)/100.0 as value
FROM (
SELECT
{{time}} AS time,
metricTag('host') AS series,
-- calculate average for each series
maxMerge(value_max) as series_maximum
FROM {{source}}
WHERE name = 'cpu_load' AND time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series, series_id
)
GROUP BY time, series
```
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 calculates a maximum for each metric series. Using `GROUP BY series_id` ensures that you get a correct maximum for each series.
- In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting `metricTag('host')` as `series`.
### Number of requests
Chart the average number of served requests using `requests_total` counter metric:
Query for number of requests
Copied\!
```
-- sum values across series to get total rate
SELECT time, 'Requests per second' AS series, SUM(avg_rate) as value
FROM (
SELECT
{{time}} AS time,
-- calculate average rate for each series
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
)
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.
As 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 the 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:
Query counting number of unique hosts
Copied\!
```
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'))`.
***
[Previous article Get started with Dashboards](https://betterstack.com/docs/logs/dashboards/getting-started/)
[Next article Variables in SQL queries](https://betterstack.com/docs/logs/dashboards/variables/)
###### On this page
- [Getting started](https://betterstack.com/docs/logs/dashboards/sql-queries/#getting-started)
- [How to query the metrics?](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics)
- [Query examples](https://betterstack.com/docs/logs/dashboards/sql-queries/#query-examples)
- [Prometheus-like metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#prometheus-like-metrics)
- [Examples with metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#examples-with-metrics)
- [Need help?](https://betterstack.com/docs/logs/dashboards/sql-queries/#need-help)
- [Querying legacy sources created before 2026](https://betterstack.com/docs/logs/dashboards/sql-queries/#querying-legacy-sources-created-before-2026)
- [How to query the metrics? (before 2026)](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics-before-2026)
- [Query examples (before 2026)](https://betterstack.com/docs/logs/dashboards/sql-queries/#query-examples-before-2026)
- [Prometheus-like metrics (before 2026)](https://betterstack.com/docs/logs/dashboards/sql-queries/#prometheus-like-metrics-before-2026)
- [Examples with metrics (before 2026)](https://betterstack.com/docs/logs/dashboards/sql-queries/#examples-with-metrics-before-2026)
Solutions
[AI SRE](https://betterstack.com/ai-sre)
[OpenTelemetry tracing](https://betterstack.com/tracing)
[Log management](https://betterstack.com/telemetry)
[Infrastructure monitoring](https://betterstack.com/infrastructure-monitoring)
[Error tracking](https://betterstack.com/error-tracking)
[Real user monitoring](https://betterstack.com/real-user-monitoring)
[Incident management](https://betterstack.com/incident-management)
[Uptime monitoring](https://betterstack.com/uptime)
[Status page](https://betterstack.com/status-page)
[Data warehouse](https://betterstack.com/warehouse)
Company
[Work at Better Stack](https://betterstack.com/careers)
[Engineering](https://betterstack.com/careers/engineering)
[Security](https://betterstack.com/security)
Resources
[Help & Support](https://betterstack.com/help)
[Documentation](https://betterstack.com/docs/)
[Enterprise](https://betterstack.com/enterprise)
[Integrations](https://betterstack.com/integrations)
[Dashboards](https://betterstack.com/dashboards)
Company
[Work at Better Stack](https://betterstack.com/careers)
[Engineering](https://betterstack.com/careers/engineering)
[Security](https://betterstack.com/security)
Resources
[Help & Support](https://betterstack.com/help)
[Documentation](https://betterstack.com/docs/)
[Enterprise](https://betterstack.com/enterprise)
[Integrations](https://betterstack.com/integrations)
[Dashboards](https://betterstack.com/dashboards)
Community
[What Is Incident Management? Beginnerβs Guide](https://betterstack.com/community/guides/incident-management/what-is-incident-management/) [Best Datadog Alternatives to Consider in 2026](https://betterstack.com/community/comparisons/datadog-log-management-alternatives/) [8 Best Free & Open Source Status Page Tools in 2026](https://betterstack.com/community/comparisons/free-status-page-tools/) [13 Best Sentry Alternatives in 2026](https://betterstack.com/community/comparisons/sentry-alternatives/) [15 Best Grafana Alternatives in 2026](https://betterstack.com/community/comparisons/grafana-alternatives/) [The 10 Best Incident.io Alternatives in 2026](https://betterstack.com/community/comparisons/incident-io-alternative/) [5 Most Used Incident Management Tools](https://betterstack.com/community/comparisons/incident-management-tools/)
30x cheaper than Datadog. Predictable pricing. Exceptional customer support.
[\+1 (628) 900-3830](<tel:+1 (628) 900-3830>) [hello@betterstack.com](mailto:hello@betterstack.com)
[Terms of Use](https://betterstack.com/terms) [Privacy Policy](https://betterstack.com/privacy) [GDPR](https://betterstack.com/dpa)
[System status](https://status.betterstack.com/)
Β© 2026 Better Stack, Inc.
### Please accept cookies
We use cookies to authenticate users, improve the product user experience, and for [personalized ads](https://business.safety.google/privacy/). [Learn more.](https://betterstack.com/privacy)
Deny
Accept required
Accept all |
| Readable Markdown | Create tailored charts by writing your own custom SQL queries.
#### Want a dashboard that works out of the box?
Use one of our ready-to-use templates to [create a dashboard](https://telemetry.betterstack.com/team/0/dashboards?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries)
## Getting started
Create your first chart using a custom SQL query. Start by creating a dashboard in [Dashboards](https://telemetry.betterstack.com/team/0/dashboards) β **Create dashboard**.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied\!
#### Getting `Missing columns` error?
Try selecting a different source or [add metric for the column](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) to your source.
Dashboard queries use [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the `countMerge` [ClickHouse function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge).
#### Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about [ClickHouse aggregate combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
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](https://betterstack.com/docs/logs/dashboards/variables/).
Queries access a unified row-based metrics schema. This schema stores all data in a unified format with metrics stored as named values with labels in a `tags` map. This allows for fast and efficient querying. Read more about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) below.
#### Not familiar with SQL?
Follow our examples below or start with [SQL tutorial from W3Schools](https://www.w3schools.com/sql/sql_syntax.asp)
#### Curious about ClickHouse SQL?
Read more about [ClickHouse SQL syntax](https://clickhouse.com/docs/en/sql-reference/syntax) and [compatibility with ANSI SQL](https://clickhouse.com/docs/en/sql-reference/ansi)
## How to query the metrics?
We stores all data in a unified format, making querying consistent.
- **Accessing Custom Fields:** All custom or dynamic columns (labels/tags) use the `label()` function. For example, `response_status` becomes `label('response_status')`. Labels are always strings, so `toString(label(...))` is redundant.
- **Standard Columns:** Columns like `time`, `dt`, `name`, `_row_type`, `series_id`, and `tags` are part of the schema and should **not** use the `label()` wrapper.
- **Row Type Filtering:** Each ingested event creates multiple rows. Use `_row_type = 'log'` or `_row_type = 'span'` to get counts of the original events. Do **not** use `_row_type` when querying named metrics (where `name = 'metric_name'`).
- **Type Handling for Labels:** Since `label()` returns a string, explicit type conversions (`toUInt16OrNull()`, `toFloat64OrNull()`) are required for numeric comparisons or operations.
- **Event Counting:** Use `countMerge(events_count)` for aggregated metrics queries.
- **No Nested Aggregate Functions:** ClickHouse does not allow nesting aggregate functions (e.g., `sum(countMerge(...))`). Merge functions (`countMerge`, `sumMerge`, `avgMerge`, etc.) already combine internal states.
- **Time Filtering:** Use `time` in `WHERE` clauses for accurate filtering across bucket boundaries, and `{{time}}` in `SELECT`/`GROUP BY` for bucketing.
#### Want to count the number of lines?
Use `countMerge(events_count)`. Avoid using `count(*)` as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
For metrics with aggregations, use `avgMerge(value_avg)` to get the average value of a metric.
Similarly, use `minMerge(value_min)` and `maxMerge(value_max)` to get a metric's minimum and maximum values. Make sure to always match the -Merge function with the metric suffix correctly:
- β
Correct: Using **sum**Merge with **\_sum** suffix: `sumMerge(bucket_sum)`
- β Incorrect: Mixing **sum** and **count**: `sumMerge(bucket_count)`
**Want to use percentiles?**
Check out the [complete example on percentiles](https://betterstack.com/docs/logs/dashboards/sql-queries/#percentile-request-duration) below.
### Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab.
See **Query with** to know how to query the metric.
Use either [ClickHouse aggregations](https://clickhouse.com/docs/en/sql-reference/aggregate-functions) or name of the metric depending on the configured aggregations.
You can [add more metrics](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/) to use in your SQL queries.
## Query examples
See the examples below for full queries using **Metrics**.
### Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied\!
Use `countMerge(events_count)` to count the number of log lines. Avoid using `count(*)`.
Filter error log lines using the `label('level')` metric. The metric has to be present in **Logs to metrics** configuration with **No aggregation** selected.
### Average request duration
Chart the average duration of request:
Query for average request duration
Copied\!
Use `avgMerge(value_avg)` to calculate an average for the `request_duration` metric. The metric has to be added in **Logs to metrics** β **Metrics** with `avg` aggregation.
Column names `Request duration` will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in **Chart setting** β **Axes**.
### Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied\!
Here we use the `histogramQuantile(0.95)` helper function to directly calculate the 95th percentile for the `request_duration_seconds` metric. This assumes `request_duration_seconds` is a histogram metric and `histogramQuantile` is defined in the database (which it is). If querying multiple metrics, use `quantilePrometheusHistogramArrayMergeIf` as described in the histogram documentation.
#### Want to learn how to use the aggregation functions?
Check out the official guide on [ClickHouse Aggregate Combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
## Prometheus-like metrics
#### Want to access metric labels?
Use `label('tag_name')` to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying. We process your metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state).
Everything about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the **Logs to metrics** configuration. We automatically configure **Metrics** for your Prometheus-like metrics.
Let's take a look at the **Logs to metrics** configuration for metric sources.
You can find it in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab:
- **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.
#### What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about [metric types](https://prometheus.io/docs/concepts/metric_types/)
- **Tags** contain all metric tags, commonly called labels. Use `label('tag_name')` to get value for any tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
- **Name** is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
- **Series\_id** is a unique value for each metric series. Use `GROUP BY series_id` when aggregating your metrics.
#### What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with `hostname` tag will have one series for each hostname.
## Examples with metrics
See complete SQL query examples using [ClickHouse aggregator combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) and Prometheus-like metrics.
### View Prometheus metrics
List your metrics with their tags and values as a **Table** chart type:
Query selecting raw metrics
Copied\!
Select metric `name`, `tags`, and `time`. Use `anyLastMerge(value_last)` to get an unaggregated value for the metric.
Visualize the data using a **Table** chart type. In the right sidebar, choose **Chart settings** β **Chart** β **Table**.
### CPU load
Chart CPU load using `cpu_load` gauge metric:
Query calculating CPU load
Copied\!
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 calculates a maximum for each metric series. Using `GROUP BY series_id` ensures that you get a correct maximum for each series.
- In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting `label('host')` as `series`.
### Number of requests
Chart the average number of served requests using `requests_total` counter metric:
Query for number of requests
Copied\!
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.
As 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 the 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:
Query counting number of unique hosts
Copied\!
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 label('hostname'))`.
## Need help?
Please let us know at [hello@betterstack.com](mailto:hello@betterstack.com).
We're happy to help! π
***
## Querying legacy sources created before 2026
Create your first chart using a custom SQL query. Start by creating a dashboard in [Dashboards](https://telemetry.betterstack.com/team/0/dashboards) β **Create dashboard**.
Create a new chart on your dashboard and use the query below:
Query counting the number of log lines by level
Copied\!
#### Getting `Missing columns` error?
Try selecting a different source or [add metric for the column](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/?utm_content=callout&utm_medium=docs-logs&utm_source=community&utm_term=docs-logs-using-logtail-dashboards-sql-queries) to your source.
Dashboard queries use [ClickHouse SQL](https://clickhouse.com/docs/en/sql-reference), which is largely similar to ANSI SQL youβre likely familiar with. In the SQL query above, we leverage the `countMerge` [ClickHouse function](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge).
#### Want an in-depth guide on ClickHouse aggregations?
Check out the official blog post about [ClickHouse aggregate combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
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](https://betterstack.com/docs/logs/dashboards/variables/).
Queries don't access your logs directly. To make querying fast and efficient we process your logs into metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state). Dashboard queries use [ClickHouse -Merge functions](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-merge) to get the values of the metrics. Read more about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) below.
#### Not familiar with SQL?
Follow our examples below or start with [SQL tutorial from W3Schools](https://www.w3schools.com/sql/sql_syntax.asp)
#### Curious about ClickHouse SQL?
Read more about [ClickHouse SQL syntax](https://clickhouse.com/docs/en/sql-reference/syntax) and [compatibility with ANSI SQL](https://clickhouse.com/docs/en/sql-reference/ansi)
## How to query the metrics? (before 2026)
#### Want to count the number of lines?
Use `countMerge(events_count)`. Avoid using `count()` as it returns incorrect results. Metric tables don't have the same number of rows as the original log tables.
Query metrics with no aggregations by their name just like regular database columns.
For metrics with aggregations, use `avgMerge(metric_name_avg)` to get the average value of a metric.
Similarly, use `minMerge(metric_name_min)` and `maxMerge(metric_name_max)` to get a metric's minimum and maximum values. Make sure to always match the -Merge function with the metric suffix correctly:
- β
Correct: Using **sum**Merge with **\_sum** suffix: `sumMerge(metric_name_sum)`
- β Incorrect: Mixing **sum** and **count**: `sumMerge(metric_name_count)`
**Want to use percentiles?**
Check out the [complete example on percentiles](https://betterstack.com/docs/logs/dashboards/sql-queries/#percentile-request-duration) below.
### Which metrics can you query?
We automatically create metrics for your logs based on the platform of your source.
Find the complete list of available metrics for your logs in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab.
See **Query with** to know how to query the metric.
Use either [ClickHouse aggregations](https://clickhouse.com/docs/en/sql-reference/aggregate-functions) or name of the metric depending on the configured aggregations.
You can [add more metrics](https://betterstack.com/docs/logs/dashboards/logs-to-metrics/) to use in your SQL queries.
## Query examples (before 2026)
See the examples below for full queries using **Metrics**.
### Number of errors
Chart the number of logs with the error log level:
Query counting number of errors
Copied\!
Use `countMerge(events_count)` to count the number of log lines. Avoid using `count(*)`.
Filter error log lines using the `level` metric. The metric has to be present in **Logs to metrics** configuration with **No aggregation** selected.
### Average request duration
Chart the average duration of request:
Query for average request duration
Copied\!
Use `avgMerge(request_duration_avg)` to calculate an average for the `request_duration` metric. The metric has to be added in **Logs to metrics** β **Metrics** with `avg` aggregation.
Column names `Request duration` will be used in chart tooltips and the legend. Select which column should be charted on the Y-axis on the right side in **Chart setting** β **Axes**.
### Percentile request duration
Chart the 95th percentile request duration:
Query for percentile request duration
Copied\!
Here we have the `request_duration` metric with `p50`, `p90`, `p95`, and `p99` percentiles set up in Logs to metrics. Calculate the 95th percentile request duration using `quantilesMerge`. The `quantilesMerge` function gives us all four percentiles. We select the third percentile using `[3]`.
#### Want to learn how to use the aggregation functions?
Check out the official guide on [ClickHouse Aggregate Combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states)
## Prometheus-like metrics (before 2026)
#### Want to access metric labels?
Use `metricTag('tag_name')` to get value for any metric tag. Use the resulting tag value to select, group by, or anywhere else in your SQL queries.
Metrics you send to Better Stack are processed like logs for fast and efficient querying. We process your metrics and store them as [ClickHouse -State combinators](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators#-state).
Everything about [querying the metrics](https://betterstack.com/docs/logs/dashboards/sql-queries/#how-to-query-the-metrics) written above also applies to writing queries for Prometheus-like metrics.
The most significant difference when writing queries for Prometheus-like metrics is the **Logs to metrics** configuration. We automatically configure **Metrics** for your Prometheus-like metrics.
Let's take a look at the **Logs to metrics** configuration for metric sources.
You can find it in [Sources](https://telemetry.betterstack.com/team/0/sources) β **your source** β **Configure** β **Logs to metrics** tab:
- **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.
#### What are gauges and counter metrics?
Gauge is a metric that can go up and down, used for, e.g., CPU or memory usage.
Counter is a metric that can only increase, commonly used for number of requests.
Read more about [metric types](https://prometheus.io/docs/concepts/metric_types/)
- **Tags** contain 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.
- **Name** is the name of your metric. Use it to select, filter, or anywhere else in your SQL queries.
- **Series\_id** is a unique value for each metric series. Use `GROUP BY series_id` when aggregating your metrics.
#### What is a metric series?
Metric series represent individual series of values that make up your metric. For example, a metric with `hostname` tag will have one series for each hostname.
## Examples with metrics (before 2026)
See complete SQL query examples using [ClickHouse aggregator combinators](https://clickhouse.com/blog/aggregate-functions-combinators-in-clickhouse-for-arrays-maps-and-states) and Prometheus-like metrics.
### View Prometheus metrics
List your metrics with their tags and values as a **Table** chart type:
Query selecting raw metrics
Copied\!
Select metric `name`, `tags`, and `time`. Use `anyLastMerge(value_last)` to get an unaggregated value for the metric.
Visualize the data using a **Table** chart type. In the right sidebar, choose **Chart settings** β **Chart** β **Table**.
### CPU load
Chart CPU load using `cpu_load` gauge metric:
Query calculating CPU load
Copied\!
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 calculates a maximum for each metric series. Using `GROUP BY series_id` ensures that you get a correct maximum for each series.
- In the outer query, sum all metrics series together to get the final result.
Chart CPU for multiple servers by setting `metricTag('host')` as `series`.
### Number of requests
Chart the average number of served requests using `requests_total` counter metric:
Query for number of requests
Copied\!
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.
As 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 the 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:
Query counting number of unique hosts
Copied\!
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'))`. |
| Shard | 1 (laksa) |
| Root Hash | 1143718778839325401 |
| Unparsed URL | com,betterstack!/docs/logs/dashboards/sql-queries/ s443 |