grep cannot tell you how many errors each service threw last Tuesday.
It can find the lines. Counting them, grouping them by service, and comparing this hour against the same hour yesterday is where the pipeline of grep | awk | sort | uniq -c starts to hurt. You end up writing a small program every time you have a question.
Central Logging stores logs in SQLite. Your logs are rows in a table, so the question above is one GROUP BY.
This guide covers the table you are querying, the JSON functions that make a log line into columns, and the one mistake that quietly gives you a wrong answer.
Every log source gets its own SQLite database with one table that matters:
CREATE TABLE logs (
id INTEGER PRIMARY KEY,
msg TEXT NOT NULL,
timestamp INTEGER DEFAULT 0,
source TEXT NOT NULL DEFAULT "unknown"
);
Four columns, and only two you will use often.
msg holds the log line exactly as it arrived. JSON, plain text, a syslog line — whatever the sender sent.
timestamp is Unix seconds. It is the time the server received the line, not the time your application wrote it. Those differ when an agent buffers through a network outage and delivers an hour late. If you need event time, read it out of your own JSON.
source holds the source token, not the friendly name you gave it in the UI.
Both timestamp and source are indexed. Filter on timestamp in anything you run against a large source.
The search page starts you here:
select timestamp, * from logs order by timestamp desc limit 50;
Unix seconds are hard to read. Convert them:
SELECT datetime("timestamp", 'unixepoch') AS at, msg
FROM logs
ORDER BY "timestamp" DESC
LIMIT 50;
datetime(..., 'unixepoch') gives UTC. Add 'localtime' for your server’s zone:
SELECT datetime("timestamp", 'unixepoch', 'localtime') AS at, msg FROM logs LIMIT 5;
Quote "timestamp". It is a SQLite keyword in some contexts and the quotes cost nothing.
Log in JSON and every field becomes a column you can select, filter and group.
SELECT datetime("timestamp", 'unixepoch') AS at,
json_extract(msg, '$.level') AS level,
json_extract(msg, '$.service') AS service,
json_extract(msg, '$.msg') AS message
FROM logs
WHERE json_valid(msg)
ORDER BY "timestamp" DESC
LIMIT 50;
json_extract(msg, '$.field') reads one field. $.a.b reaches into nested objects. $.items[0] indexes an array.
Note the WHERE json_valid(msg). It is not decoration. Read the next section before you drop it.
json_extract raises an error the moment it meets a line that is not JSON.
One plain-text line in the source is enough. A startup banner, a stack trace someone shipped raw, a health check writing OK — any of them, and this query breaks:
-- wrong: fails on the first non-JSON line
SELECT json_extract(msg, '$.level') AS level, count(*) AS n
FROM logs
GROUP BY level;
What you see depends on where the bad line sits. Sometimes you get malformed JSON. Sometimes SQLite has already returned the rows it found before the bad one, and you get a short result set that looks like a complete answer. The second case is the dangerous one. Nothing on screen says the count is wrong.
Guard every query that touches JSON:
SELECT json_extract(msg, '$.level') AS level, count(*) AS n
FROM logs
WHERE json_valid(msg)
GROUP BY level
ORDER BY n DESC;
In a WHERE clause, json_valid(msg) also protects the conditions after it:
SELECT json_extract(msg, '$.service') AS service,
json_extract(msg, '$.duration_ms') AS ms
FROM logs
WHERE json_valid(msg)
AND json_extract(msg, '$.duration_ms') > 500
ORDER BY ms DESC;
Order matters. Swap those two conditions and the query breaks again — SQLite stops at the first false term, so the guard has to come first.
That short circuit only applies in WHERE. Write the same AND as a value, inside SELECT or inside an aggregate, and both sides get evaluated:
-- wrong: json_extract still runs on non-JSON rows
SELECT sum(json_valid(msg) AND json_extract(msg, '$.level') = 'error') AS errors FROM logs;
Use CASE when you need the test as a value:
SELECT sum(CASE WHEN json_valid(msg)
THEN json_extract(msg, '$.level') = 'error'
ELSE 0 END) AS errors
FROM logs;
CASE never evaluates the branch it did not take, so it is safe everywhere.
Want to see the lines you are excluding? They are usually worth a look:
SELECT datetime("timestamp", 'unixepoch') AS at, msg
FROM logs
WHERE NOT json_valid(msg)
ORDER BY "timestamp" DESC
LIMIT 50;
How many errors, by service, in the last 24 hours?
SELECT json_extract(msg, '$.service') AS service, count(*) AS errors
FROM logs
WHERE json_valid(msg)
AND json_extract(msg, '$.level') = 'error'
AND "timestamp" > strftime('%s', 'now', '-1 day')
GROUP BY service
ORDER BY errors DESC;
strftime('%s', 'now', '-1 day') is how you write “24 hours ago” as Unix seconds. '-1 hour', '-7 day' and 'start of day' all work the same way.
Is the error rate rising? Bucket by hour:
SELECT strftime('%Y-%m-%d %H:00', "timestamp", 'unixepoch') AS hour,
count(*) AS total,
sum(CASE WHEN json_valid(msg) AND json_extract(msg, '$.level') = 'error'
THEN 1 ELSE 0 END) AS errors
FROM logs
WHERE "timestamp" > strftime('%s', 'now', '-2 day')
GROUP BY hour
ORDER BY hour DESC;
This one counts every line, JSON or not, and counts the errors among them. That is why the guard is a CASE inside the sum rather than a condition in the WHERE — moving it to the WHERE would drop the non-JSON lines from total too.
Which endpoints got slower?
SELECT json_extract(msg, '$.path') AS path,
count(*) AS n,
round(avg(json_extract(msg, '$.duration_ms')), 1) AS avg_ms,
max(json_extract(msg, '$.duration_ms')) AS max_ms
FROM logs
WHERE json_valid(msg)
AND json_extract(msg, '$.duration_ms') IS NOT NULL
GROUP BY path
HAVING n > 10
ORDER BY avg_ms DESC
LIMIT 20;
HAVING n > 10 drops the paths hit twice. One slow request is not a trend.
What did this user do?
SELECT datetime("timestamp", 'unixepoch') AS at,
json_extract(msg, '$.service') AS service,
json_extract(msg, '$.msg') AS message
FROM logs
WHERE json_valid(msg)
AND json_extract(msg, '$.user_id') = 'u_991'
ORDER BY "timestamp";
That query is the reason to put a request ID or user ID on every log line. Without one you are reading timestamps and guessing.
Two shapes come up often, and both need a different expression.
The clagent agent ships journald records. Your application’s JSON ends up inside the MESSAGE field, as a string:
{"MESSAGE":"{\"level\":\"warn\",\"msg\":\"slow query\"}","_SYSTEMD_UNIT":"api.service","_HOSTNAME":"web01"}
json_extract(msg, '$.MESSAGE.level') returns nothing. MESSAGE is a string that happens to contain JSON, not an object. Extract twice:
SELECT json_extract(msg, '$._HOSTNAME') AS host,
json_extract(json_extract(msg, '$.MESSAGE'), '$.level') AS level,
json_extract(json_extract(msg, '$.MESSAGE'), '$.msg') AS message
FROM logs
WHERE json_valid(msg)
AND json_valid(json_extract(msg, '$.MESSAGE'))
ORDER BY "timestamp" DESC;
Two guards, in that order. The first keeps the outer record safe, the second skips journald records whose MESSAGE is ordinary text.
<134>Aug 7 21:00:00 web01 api[1234]: {"level":"error","msg":"upstream timeout"}
Not valid JSON, because of everything before the brace. Cut to the first {:
SELECT json_extract(substr(msg, instr(msg, '{')), '$.level') AS level,
json_extract(substr(msg, instr(msg, '{')), '$.msg') AS message
FROM logs
WHERE instr(msg, '{') > 0
AND json_valid(substr(msg, instr(msg, '{')))
ORDER BY "timestamp" DESC;
instr returns 0 when there is no brace, and substr(msg, 0) returns the whole string, so keep the instr(msg,'{') > 0 guard.
Each source also has an FTS5 index over msg, maintained by triggers as rows arrive:
SELECT datetime(l."timestamp", 'unixepoch') AS at, l.msg
FROM logs_fts f
JOIN logs l ON l.id = f.rowid
WHERE logs_fts MATCH 'timeout OR refused'
ORDER BY l."timestamp" DESC
LIMIT 50;
logs_fts is an external-content table, so it stores no copy of your logs. Join back to logs on rowid = id to get the columns.
FTS is the right tool when you know a word and not a field. Use json_extract when you know the field.
The search box runs SQL or full-text search, and they behave differently.
SQL mode queries one source at a time. Select several and it uses the first, and tells you which. Correlating two services in SQL means two queries.
Full-text mode searches every selected source at once and labels each result with its source. Use it for “where did this request ID go”.
Full-text mode also takes field filters without any SQL:
| Query | Meaning |
|---|---|
level:error |
the level field equals error |
level:!debug |
anything but debug |
duration_ms:>500 |
numeric comparison |
path:/api/* |
wildcard |
"connection refused" |
exact phrase |
error timeout |
both terms |
error OR timeout |
either term |
A dotted field reaches nested payloads. MESSAGE.level:warn finds the journald records from earlier; plain level:warn does not, because the field is one level down.
One more thing about full-text mode: it defaults to the last 48 hours. Widen the time range before concluding your logs are missing.
The SQL box runs what you type. It is not restricted to SELECT. DELETE FROM logs deletes your logs. Only the signed-in admin can reach the page, so this is a warning rather than a hole — but a saved query is a loaded gun if you saved the wrong one.
timestamp is receipt time. Ordering by it orders by arrival. When an agent catches up after an outage, a batch of old events all carry roughly the same recent timestamp.
Save the queries you rerun. Name a query on the search page and it comes back with its source, time range and mode intact. The count-errors-by-service query is one you will want on a bad morning, not one you want to be writing on a bad morning.
Log in JSON if you can choose. Every query on this page gets shorter, and the ones you have not thought of yet become possible.
💌 Get notified on new features and updates