Writing SQL queries

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.

Getting started

Create your first tailored 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:

SELECT {{time}} AS time, count(*) AS value, level AS series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series

• Dashboard queries use ClickHouse SQL, which is largely similar to ANSI SQL you’re likely familiar with.

• 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.

• You can use columns extracted from your logs and metrics columns in your queries. In SQL query above, we use level column.

Available columns

Which columns can be used in SQL queries?

You can query columns extracted from your logs and metrics.

• For Logs, we automatically extract common columns based on source type. You can add more columns to use in dashboard queries.

• Metrics always have name and tags columns. There are also gauge.value or counter.value columns depending on the metric type. Read more about writing SQL queries for metrics.

Find the complete list of columns extracted from your logs and metrics in
Sources → your source → Advanced settings → Logs to metrics section.

Use any columns in your queries using their Column name.

Adding more columns

Want to use more log fields in your queries?

Add any field to the table, and we’ll extract it for use in dashboard queries.
Fill out the inputs:

• Column name: Name your extracted column.
• SQL expression: Use JSONExtract() or any SQL expression from Explore logs with SQL.
  • For example, use JSONExtract(json, 'context', 'system', 'hostname', Nullable(String)) to extract context.system.hostname field from your logs.
  • Using Nullable(String) parses the value as string and prevents errors when the field is missing. See all ClickHouse data types.
• Codec: Choose type with compression codec based on data in your logs field. For example, use String (LowCardinality) for strings.
• Index: Leave index blank. Setting indexes for certain columns can improve query performance.

Querying with examples

TODO: - Log level - Setting custom Axe labels