Useful Log Queries

Log count from various sources

select source,
count(source) as log_count
from logs
group by source
order by log_count desc;

Count of all logs in system

select count(*) from logs;

Successful SSH Logins

json_extract(logs.msg, '$.MESSAGE') as message,
json_extract(logs.msg, '$.SYSLOG_TIMESTAMP') as ts,
json_extract(logs.msg, '$.SYSLOG_IDENTIFIER') as syslog_id
from logs
where json_valid(msg)
and syslog_id='sshd'
and message like '%Accepted publickey%'
order by timestamp desc;

When did I last update my Linux servers?

select id,
json_extract(logs.msg, '$.MESSAGE') as a_msg,
json_extract(logs.msg, '$._CMDLINE') as a_cmdline
from logs
where json_valid(msg)
and a_cmdline like '%sudo apt upgrade%'
order by timestamp desc
limit 100;

💌 Get notified on new features and updates

Only sent when a new version is released. Nothing else.