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

select
json_extract(logs.msg, '$.MESSAGE') as message,
timestamp,
source,
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,
timestamp,
source,
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;