SQLite3 for L1 DFIR Log Review

Open & First Pass (Structure)

sqlite3 evidence.db

.tables
.schema
.schema table_name
-- list every table's definition at once, no schema-diving needed
SELECT sql FROM sqlite_master WHERE type='table';

Readable Output

.headers on
.mode column         -- scanning many rows
.mode line            -- reading ONE wide/suspicious row closely

Common Log Review Queries

-- Basic recent entries
SELECT * FROM logs ORDER BY timestamp DESC LIMIT 50;

-- Time window (adjust column name)
SELECT * FROM logs
WHERE timestamp BETWEEN 1723000000 AND 1723100000
ORDER BY timestamp;

-- Count by day / source / status
SELECT date(timestamp, 'unixepoch'), COUNT(*)
FROM logs
GROUP BY 1
ORDER BY 1;

-- Search for specific indicators
SELECT * FROM logs
WHERE message LIKE '%error%'
   OR message LIKE '%failed%'
   OR message LIKE '%admin%';

Timestamp Conversion

-- seconds epoch
SELECT datetime(timestamp, 'unixepoch') FROM logs;

-- milliseconds epoch (browser history, some EDR logs)
SELECT datetime(timestamp/1000, 'unixepoch') FROM logs;

-- Chrome/WebKit epoch (microseconds since 1601-01-01)
SELECT datetime(timestamp/1000000-11644473600, 'unixepoch') FROM logs;

Multi-DB Correlation

ATTACH DATABASE 'other.db' AS other;
SELECT * FROM logs
JOIN other.downloads ON logs.session_id = other.downloads.session_id;

Export for Reporting

.mode csv
.output findings.csv
SELECT * FROM logs WHERE message LIKE '%admin%';
.output stdout

Useful Extras

PRAGMA table_info(table_name);   -- clean column list
SELECT COUNT(*) FROM table_name;
.quit