Cookbook

This chapter contains recipes for common tasks that can be done in lnav. These recipes can be used as a starting point for your own needs after some adaptation.

Log Formats

TBD

Defining a New Format

TBD

Annotating Logs

Log messages can be annotated in a couple of different ways in lnav to help you get organized.

Create partitions for Linux boots

When digging through logs that can be broken up into multiple sections, lnav’s partitioning feature can be used to keep track of which section you are in. For example, if a collection of Linux logs covered multiple boots, the following script could be used to create partitions for each boot. After the partition name is set for the log messages, the current name will show up in the top status bar next to the current time.

partition-by-boot.lnav
 1#
 2# DO NOT EDIT THIS FILE, IT WILL BE OVERWRITTEN!
 3#
 4# @synopsis: partition-by-boot
 5# @description: Partition the log view based on boot messages from the Linux kernel.
 6#
 7
 8;UPDATE syslog_log
 9     SET log_part = 'Boot: ' || log_time
10     WHERE log_text LIKE '%kernel:%Linux version%';
11
12;SELECT 'Created ' || changes() || ' partitions(s)';

Tagging SSH log messages

Log messages can be tagged interactively with the :tag command or programmatically using the SQLite Interface. This example uses a script to search for interesting SSH messages and automatically adds an appropriate tag.

tag-ssh-msgs.lnav
 1#
 2# @synopsis: tag-ssh-msgs
 3# @description: Tag interesting SSH log messages
 4#
 5
 6;UPDATE all_logs
 7     SET log_tags = json_concat(log_tags, '#ssh.invalid-user')
 8     WHERE log_text LIKE '%Invalid user from%'
 9
10;SELECT 'Tagged ' || changes() || ' messages';

Log Analysis

Most log analysis within lnav is done through the SQLite Interface. The following examples should give you some ideas to start leveraging this functionality. One thing to keep in mind is that if a query gets to be too large or multiple statements need to be executed, you can create a .lnav script that contains the statements and execute it using the | command prompt.

Count client IPs in web access logs

To count the occurrences of an IP in web access logs and order the results from highest to lowest:

;SELECT c_ip, count(*) as hits FROM access_log GROUP BY c_ip ORDER BY hits DESC

Show only lines where a numeric field is in a range

The :filter-expr command can be used to filter web access logs to only show lines where the number of bytes transferred to the client is between 10,000 and 40,000 bytes like so:

:filter-expr :sc_bytes BETWEEN 10000 AND 40000

Generating a Report

Reports can be generated by writing an lnav script that uses SQL queries and commands to format a document. A basic script can simply execute a SQL query that is shown in the DB view. More sophisticated scripts can use the following commands to generate customized output for a report:

report-demo.lnav
 1#
 2# @synopsis: report-demo [<output-path>]
 3# @description: Generate a report for requests in access_log files
 4#
 5
 6# Figure out the file path where the report should be written to, default is
 7# stdout
 8;SELECT CASE
 9          WHEN $1 IS NULL THEN '-'
10          ELSE $1
11        END AS out_path
12
13# Redirect output from commands to $out_path
14:redirect-to $out_path
15
16# Print an introductory message
17;SELECT printf('\n%d total requests', count(1)) AS msg FROM access_log
18:echo $msg
19
20;WITH top_paths AS MATERIALIZED (
21        SELECT
22            cs_uri_stem,
23            count(1) AS total_hits,
24            sum(sc_bytes) as bytes,
25            count(distinct c_ip) as visitors
26          FROM access_log
27          WHERE sc_status BETWEEN 200 AND 300
28          GROUP BY cs_uri_stem
29          ORDER BY total_hits DESC
30          LIMIT 50),
31      weekly_hits_with_gaps AS MATERIALIZED (
32        SELECT timeslice(log_time_msecs, '1w') AS week,
33               cs_uri_stem,
34               count(1) AS weekly_hits
35          FROM access_log
36          WHERE cs_uri_stem IN (SELECT cs_uri_stem FROM top_paths) AND
37                sc_status BETWEEN 200 AND 300
38          GROUP BY week, cs_uri_stem),
39      all_weeks AS (
40        SELECT week
41          FROM weekly_hits_with_gaps
42          GROUP BY week
43          ORDER BY week ASC),
44      weekly_hits AS (
45        SELECT all_weeks.week,
46               top_paths.cs_uri_stem,
47               ifnull(weekly_hits, 0) AS hits
48          FROM all_weeks
49          CROSS JOIN top_paths
50          LEFT JOIN weekly_hits_with_gaps
51            ON all_weeks.week = weekly_hits_with_gaps.week AND
52               top_paths.cs_uri_stem = weekly_hits_with_gaps.cs_uri_stem)
53  SELECT weekly_hits.cs_uri_stem AS Path,
54         printf('%,9d', total_hits) AS Hits,
55         printf('%,9d', visitors) AS Visitors,
56         printf('%9s', humanize_file_size(bytes)) as Amount,
57         sparkline(hits) AS Weeks
58    FROM weekly_hits
59    LEFT JOIN top_paths ON top_paths.cs_uri_stem = weekly_hits.cs_uri_stem
60    GROUP BY weekly_hits.cs_uri_stem
61    ORDER BY Hits DESC
62    LIMIT 10
63
64:write-table-to -
65
66:echo
67:echo Failed Requests
68:echo
69
70;SELECT printf('%,9d', count(1)) AS Hits,
71        printf('%,9d', count(distinct c_ip)) AS Visitors,
72        sc_status AS Status,
73        cs_method AS Method,
74        group_concat(distinct cs_version) AS Versions,
75        cs_uri_stem AS Path,
76        replicate('|', (cast(count(1) AS REAL) / $total_requests) * 100.0) AS "% of Requests"
77    FROM access_log
78    WHERE sc_status >= 400
79    GROUP BY cs_method, cs_uri_stem
80    ORDER BY Hits DESC
81    LIMIT 10
82
83:write-table-to -