Missing information from SQL Command Results #1027

wcbrown86 · 2022-08-23T21:55:59Z

wcbrown86
Aug 23, 2022

I seem to be running into a problem with my formats. I have a defined format (see below) for a log file. However, the results are missing information when I run an SQL command. My goal is to be able to use lnav to gather the logs and use SQL command to grab the logs from a time frame to then parse in a separate program. I need the SQL results to have all the information from the body shown in log_msg_format. This is just one example; other lines are also missing information. Any help would be greatly appreciated.

lnav 0.10.1-dirty

What is printed: #: Load=# Idle=#
What I would like:[A]Current Usage Stats: Load=2.35 Idle=67.64

For example:
SQL result: - 87│ │2022-08-20 14:21:29.855│ 180│info │ 0│ │ │ │ttmenu_log│#: Load=# Idle=#

The full line from the log:
2022/08/20 14:21:29:855 [A]Current Usage Stats: Load=2.35 Idle=67.64

Log Format file(partial view, there are more samples in the original file):

{   
    "ttmenu_log" : {
        "title" : "TTMenu",
        "description" : "The logs generated by TTMenu",
        "url" : "",
        "multiline": true,
        "regex": {
            "single_ttmenu" : {
                "pattern" : "^(?<timestamp>\\d{4}\/\\d{2}\/\\d{2}\\s\\d{2}:\\d{2}:\\d{2}:\\d{3})\\s*(?<alert_level>\\[\\w\\])(?<body>.(?:.*|\\s\\w*\\s)*.)$"
            },
            "multi_ttmenu" :{
                "pattern" : "^(?<timestamp>\\d{4}\/\\d{2}\/\\d{2}\\s\\d{2}:\\d{2}:\\d{2}:\\d{3})\\s*(?<alert_level>\\[\\w\\])(?<body>.(?:.*|\\s\\w*\\s)*\\n.)$"
            }
        },
        "timestamp-format" : ["%Y/%m/%d %H:%M:%S:%L"],
        "value" : {
			"body"	: { "kind" : "string" }
		},
        "sample": [
            {
                "line" : "2022/07/13 14:17:27:198  [A]Current Usage Stats: Load=2.82  Idle=68.42"
            }
        ]
    }
}

tstack · 2022-08-23T23:41:25Z

tstack
Aug 23, 2022
Maintainer

It looks like you're doing a query on the all_logs table? The log_msg_format is lnav's guess about the shape of the log message and what it will extract. The actual extracted data is available in the dynamically generated logline table. If you have that line at the top of the view and run ;SELECT * FROM logline, you'll get something closer to what you want. But, it will only return the lines that have the same message format. The extraction can also be done manually using the extract() SQL function. So, SELECT *,extract(log_body) FROM all_logs will add a column with the data extracted into a JSON object and that will work for all log messages.

Another option would be to use search tables where you define a regex that extracts the data you're interested in. If you're concerned about how reliable the extract() and logline functionality is, this might be the better option.

It really kinda depends on what types of messages are in the logs. Would you be able to supply some more samples?

4 replies

wcbrown86 Aug 24, 2022
Author

Thanks, this is excellent information. When I run SELECT *,extract(log_body) FROM all_logs against the log files for this format. It is causing lnav to crash. Attached are the crash logs. Looks like it is something with the log format, but I'm not 100% sure what the issue is. Thanks for your time!

crash-2022-08-23-21-13-13.92238.log
crash-2022-08-23-21-14-00.92464.log
crash-2022-08-23-21-24-38.93340.log
crash-2022-08-23-21-30-02.93679.log
crash-2022-08-23-21-31-49.93938.log
crash-2022-08-23-21-32-05.94006.log
crash-2022-08-23-21-39-46.94588.log

tstack Aug 24, 2022
Maintainer

It looks like the automated parser is choking on some of the log messages. I'd need the log files to debug it fully. You could send them to me at [email protected] if you don't want to attach them to a github issue. Otherwise, it will be a bit difficult to track down.

Are you actually looking for lnav to extract this data from the log message or do you just need the timestamp/body?

wcbrown86 Aug 24, 2022
Author

For right now, if I could get lnav to give me the timestamp/body from a given timeframe, that would be great.

tstack Aug 26, 2022
Maintainer

The simplest way to filter by time might be the :hide-lines-before and :hide-lines-after commands. You can then use a query to get what you're interested in:

;SELECT log_time,log_body FROM all_logs

Note that the log_body is a HIDDEN field, so it is not automatically included in a SELECT * query. There are several other fields that are hidden by default as well.

Note that if want this data in another SQLite database, you can do an ATTACH to connect to the other DB. Then, you can do a CREATE TABLE AS to create a table in the other DB and populate it with the log data.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Missing information from SQL Command Results #1027

{{title}}

Replies: 1 comment 4 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Missing information from SQL Command Results #1027

wcbrown86 Aug 23, 2022

Replies: 1 comment · 4 replies

tstack Aug 23, 2022 Maintainer

wcbrown86 Aug 24, 2022 Author

tstack Aug 24, 2022 Maintainer

wcbrown86 Aug 24, 2022 Author

tstack Aug 26, 2022 Maintainer

wcbrown86
Aug 23, 2022

Replies: 1 comment 4 replies

tstack
Aug 23, 2022
Maintainer

wcbrown86 Aug 24, 2022
Author

tstack Aug 24, 2022
Maintainer

wcbrown86 Aug 24, 2022
Author

tstack Aug 26, 2022
Maintainer