Reading from external sqlite data

[cw1nte]

Hi,
I've been working on recording all shell command via a command prompt function. This allows me to both filter out some common commands I do not even want to log, then store the command start time, end time, user, tty, cwd and command plus any non zero failure code. I can also trim from the log any non-significant commands (think cat, head, tail, less, more etc) quickly and search/access this via a flexible command line tool. All good for me.

In lnav I currently read the full history logs, but this generates 2 display lines per command and has no filtering /trimming. However it IS useful to see the interleaved commands within the full range of other logging I wish to see.

The ideal would be (for me anyway) that lnav can read my command history sqlite file, or have some pass/insert to lnav tables though that might raise issues when I delete trimmable data from my command log. I guess I could open both databases and update from one to the other manually but that is not very fluid as a logging idea.

Obviously my particular file may not be of general value but perhaps there is a more general solution for reading "foreign" sqlite data that could be useful? A sort of table map/translate definition, which I assume is a wheel already invented...

As always, thanks for what we already have and best wishes!!!

[tstack]

It sounds like you're interested in displaying log entries from a SQLite table in the normal log view, is that right? That's not supported at the moment, but it's not too dissimilar from the existing support for JSON or CSV logs. I could imagine there being a SELECT statement that produced the columns and then a format string to specify how they should be rendered. Can you share what your table looks like at the moment and some sample data? Also, are you expecting the log view to be updated as new rows are inserted into the table?

[sakhnik]

I too would be interested in this feature. There's a proprietary logging solution backed by multiple sqlite files. I'd like to ingest them into lnav for exploration and analysis. Just like you suggest, it's a list of files to get the data in. The SELECT statement together with the fields description could be part of the custom format file.
Are there any considerations for implementing this feature?

[tstack]

@sakhnik Are these SQLite files being updated on-the-fly and are you expecting lnav to be tailing them? If they're not being updated, processing the tables to produce some formatted output can be done by an lnav script right now.

[sakhnik]

No, just good old archaeology is what I need. Is there an example of how to take the data? (Sorry, just learning)
UPD: No worries, it seems I've found what I needed!

[sakhnik]

Well, I ended up extracting data from the database into JSON-formatted files, describing the format in a config file. Although this requires an additional step and leaves duplicated information around, it's still better than just attaching a database and examining queries in the SQL view. At least I haven't found a way to interleave the selected data from the database with the rest of the logs.
P.S.: lnav is definitely the discovery of the year for me! Especially when half of my time is spent on investigations. Thanks a lot!

[cw1nte]

CREATE TABLE history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
salt INTEGER,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
user VARCHAR(20),
tty VARCHAR(10),
wdID INTEGER,
cmd TEXT,
UNIQUE(user,timestamp,cmd)
);
CREATE TABLE knownDirs (
idDir INTEGER PRIMARY KEY AUTOINCREMENT,
path TEXT UNIQUE
);
CREATE TABLE trimList (
removableCmdPattern TEXT UNIQUE);

When my command hooks fire and pass through my filters I basically write some shell data away into sql.
Pwd=working dir,
cmd is a formatted line from history -options depending on shell in use.
Use a normalised table for (possibly long text of) paths since they are relatively stable data sets and this keeps main table smaller.
INSERT INTO knownDirs(path) SELECT ("${__pwd}") WHERE NOT EXISTS (SELECT idDir FROM knownDirs WHERE path ="${__pwd}");
INSERT INTO history( cmd, wdID, tty, user, salt)
VALUES( "${__command}", (SELECT idDir FROM knownDirs WHERE path = "${__pwd}"), "${MYTTY}"," ${USER}", ${__histLink_salt});

Ideally the log view updates reflect live in lnav. Almost the whole assembly could run in lnav although I do want tables backed to file to preserve long term - but I quite like the idea that the logging is done by passing messages to a memory resident logger rather than load/unload of system though I guess it caches.
Don't want to go re-inventing wheels though (Apple new logging etc).
The other way to go might be simply to append my selected data to a plain ex log file that lnav reads though this replicates data and io in 2 systems (file and sqlite). This gives me flexible filter & format options in my tool and a coordinated log analysis tool in lnav but requires log rotation and trimming work...