Explore logs with SQL

Gain insights about your logs by drilling down into any fields with custom SQL.

Getting started

Create your first logs query in Explore logs → Create query. Paste query below into the input:

SELECT {{time}} AS time, AVG(length(JSONExtractString(json, 'message'))) as value, JSONExtractString(json, 'level') as series
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time, series

Extracting fields

Use JSONExtract() function to explore your logs fields:

JSONExtract(json, 'heroku', 'details', 'load', Nullable(String))

Here we extract load nested field from heroku.details.load. Because of Nullable(String) parameter the resulting value will be null if the load field is missing.

Converting logs to metrics

Want to extract fields from your logs and use them in dashboards?

Use any SQL expression from Explore logs to define new column in Sources → your source → Advanced settings → Logs to metrics section.

Start with a SQL query extracting column you want to use in dashboards:

SELECT JSONExtract(json, 'level', Nullable(String)) as Level
FROM {{source}};

(table screenshot ?)

Not happy with the results in the table?
Refine the query until you are getting the results you want.

1. Copy the SQL expression from SELECT part of the query. Here we would use JSONExtract(json, 'level', Nullable(String)).

2. Go to Sources → your source.

3. Navigate to Advanced settings → Logs to metrics section.

4. Fill out Column name and paste in the SQL expression.

5. Choose Codec to match the data in results of your SQL expression.

6. Click Add button.

TODO: (screenshot table bottom)

Explore logs with examples

Match part of log message

Track specific errors in your logs using CASE and LIKE. The LIKE operator performs case-sensitive search:

TODO: rewrite using CASE to make this usable with Logs to metrics
E.g.: CASE message LIKE '%connection lost%' THEN true ELSE false

SELECT {{time}} AS time, 
       count(*) as value
FROM {{source}}
WHERE message ILIKE '%connection lost%' 
AND time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time

Want to visualize the matched error?

Add CASE message LIKE '%connection lost%' THEN true ELSE false SQL expression in Log to metrics to make the column available in any chart in dashboards.

TODO: more examples

• Error type

  • Query: case match multiple strings
  • Column name: error_type: “Connection error”, …

• Extract response times (?)

  • Query
  • Logs to metrics: Column name and SQL expression