tail_n_mail

Last updated: 2021-02-23

tail_n_mail (sometimes abbreviated TNM or tnm) is a Perl script for automatically detecting interesting items that appear in log files and mailing them out to interested parties. It is primarily aimed at Postgres log files but can be used for any files. It was originally developed at End Point Corporation by Greg Sabino Mullane.

Download

The latest version, 3.3.0, can be downloaded here:

Basic Usage

To use this script, simply run and provide it with the name of a config file as the first argument. A sample config file can be generated by running:

tail tail_n_mail > tnm.config.txt

Edit this file so that it points to your particular logfile and change the email address, then test it out by running:

perl tail_n_mail tnm.config.txt

Once it is working properly, have it run periodically by adding an entry to your crontab.

If running against a Postgres log file (the default), you should add your log_line_prefix value to a file named .tailnmailrc in your home directory. The file will look something like this:

log_line_prefix='%t [%p] %u@%d '

Most options below can be put in this file as well. If you are using syslog for your Postgres logs, add this to your .tailnmailrc file:

pgmode: syslog

Sample Output

This is an example of what an email from the tail_n_mail script might look like.

Date: Fri Mar 19 09:52:30 2010
Host: hyperion.example.com
Unique items: 4
Total matches: 134
Matches from [A]  /var/log/apps/2010/hyperion/03/19/05/pgsql-warn.log: 60
Matches from [B]  /var/log/apps/2010/hyperion/03/19/06/pgsql-warn.log: 8
Matches from [C]  /var/log/apps/2010/hyperion/03/19/07/pgsql-warn.log: 16
Matches from [D]  /var/log/apps/2010/hyperion/03/19/08/pgsql-warn.log: 31
Matches from [E]  /var/log/apps/2010/hyperion/03/19/09/pgsql-warn.log: 19

[1] From files B to E Count: 102
First: [B] 2010-03-19T06:18:58-06:00 hyperion postgres[27317]
Last:  [E] 2010-03-19T09:22:50-06:00 hyperion postgres[12493]
ERROR: syntax error at end of input at character 47 STATEMENT: select count(*) from sometable where = 34

[2] From files A to E Count: 30
First: [A] 2010-03-19T05:10:01-06:00 hyperion postgres[9917]
Last:  [E] 2010-03-19T09:50:01-06:00 hyperion postgres[30464]
ERROR: insert or update on table "reindeer" violates foreign key constraint "reindeer_ref_antlers"
DETAIL: Key (antlers)=(green) is not present in table "antlers". STATEMENT: INSERT INTO reindeer
(age, gender, antler, notes) VALUES ($1,$2,$3,$4)

[3] From file B
2010-03-19T06:10:50-06:00 hyperion postgres[12868]: [31-1] ERROR: invalid input syntax for type
timestamp: " 00:00:00" STATEMENT: SELECT DISTINCT color FROM antler WHERE creation_date
BETWEEN ' 00:00:00' AND ' 23:59:59'

[4] From file D
2010-03-19T06:10:50-06:00 hyperion postgres[12868]: [33-1] FATAL:  role "nosuchuser" does not exist

How It Works

When tail_n_mail is run, it first finds which log file or log files to look at. Then it travels to the point in the file (if any) that it left off of last time, generates a list of all files that were created from the last time it was run until the latest file, and extracts all the lines of interest by applying inclusion and exclusion rules. Finally, it generates an email message which is then sent to the addresses in the EMAIL line of the config file. The config file is rewritten so that it knows where to start looking the next time it is run.

Requirements

TNM is meant to have minimum requirements. Perl must be version 5.8.3 or better. If you are sending mail in sendmail mode, you must have a sendmail executable available that can accept the -f option. If you are using SMTP mode, you must have the Net::SMTP::SSL module available. On Linux and similar systems, you can install the modules for SMTP support by installing these packages (via yum or other method):

  • perl-Net-SMTP-SSL
  • perl-Authen-SASL

On Windows, you will need to install the following items via ppm install:

  • Net_SSLeay.ppd
  • IO-Socket-SSL.ppd
  • Authen-SASL.ppd
  • Net-SMTP-SSL.ppd

Command Line Options

There is only one mandatory command line option, and that is the name of a config file. All other options start with a “double dash”.

Basic options:

  • --version
    • Returns the version number and exits
  • --verbose
    • Increases the level of verbosity
  • --quiet
    • Turns off minor warnings, e.g. the log file not existing
  • --help
    • Shows a sample usage line and exits
  • --reset
    • Resets the file size stored in the config file to the current file size. This has the effect of marking the log file as “up to date” so that the next run will only contain entries that occurred after running tail_n_mail with the --reset option. Note that this option rewrites the config file even if --dryrun is used.
  • --duration=XXX
    • If running in duration mode, specified the minimum number of milliseconds a query has run before it is reported. It is usually better to put this into the config file with the DURATION: option.
  • --duration_limit=XXX
    • If running in duration mode, limits the number of matching entries that will be displayed.
  • --tempfile_limit=XXX
    • If running in tempfile mode, limits the number of matching entries that will be displayed.
  • --flatten
    • Attempt to normalize similar queries by replacing things such as “VALUES (123, 'abc')” with “VALUES (?,?)”. This is on by default, so use --noflatten to turn it off
  • --sqlstate
    • Off by default. If enabled, this will strip out the SQLSTATE codes that appear after the ERROR and FATAL lines for purposes of regex matching. Handy when you are toggling log_error_verbosity.
  • --pgmode
    • On by default, this indicates that the log files are coming from Postgres. In other words, if you are not analyzing Postgres log files, it’s best to use --pgmode=0
  • --sortby
    • How the final output will be sorted. Must be one of ‘count’ or ‘date’. Count is the default (the most commonly occurring errors appear at the top of the report)
  • --mailmode=XXX
    • How to send the email: can be either sendmail or authenticated smtp. Defaults to sendmail.
  • --mailcom=XXX
    • When using the sendmail mode, the command to use. Defaults to /usr/sbin/sendmail.
  • --mailserver=XXX
    • The mail server to connect to when using SMTP mode.
  • --mailuser=XXX
    • The mail username when using SMTP mode.
  • --mailpass=XXX
    • The mail password when using SMTP mode.
  • --mailport=###
    • The port to use when using SMTP mode. Defaults to 465.
  • --mailzero
    • If set, then mail is sent even if no items were found.
  • --mailsig=file
    • Add the contents of a file to the end of messages that are mailed out. Can have more than one file.
  • --maxemailsize
    • The size in bytes before email is broken into separate messages. Defaults to 10 million.
  • --tsep
    • Sets the thousands separator for formatting long numbers. This is usually determined automatically based on your locale, so setting this should not be necessary. You can turn off the formatting of long numbers by setting this to 0
  • --tsepnosub
    • Prevents formatting of long numbers inside the subject line. This is a workaround for a Mailman bug.
  • --skip_non_parsed
    • If true, prevents unrecognizable log lines from being added to the report. Defaults to false.

Debugging options:

  • --dryrun
    • Does everything except send email and rewrite the config file. The contents of the mail message are sent to stdout.
  • --nomail
    • Prevents the sending of email, and outputs the command it would have used.
  • --debug
    • Greatly increases the level of verbosity
  • -- yesfile and --nofile
    • Filter out which files to use by regex
  • --file=XXX
    • Use the specified file rather than determining the log file from the config file.
  • --offset=XXX
    • Specifies the exact byte position to start at in the log file. Used for debugging.
  • --rewind=XXX
    • Seeks backwards in the log file by the given number of bytes before processing it. Used for debugging.
  • --timewarp=XXX
    • Adjust the time by a number of seconds. Used for debugging time-based logfiles with POSIX escapes.
  • --showonly=XXX
    • Only output the top XXX items.
  • --hideflatten
    • By default, we only show the ‘flattened’ version of a statement if there is more than one found. You can change this to always show the flattened and actual for every statement by using --nohideflatten. Mostly useful for debugging.

Alternate modes

Normally, tail_n_mail simply reads in and reports on all matching lines. However, two special modes are available: duration and tempfile. Both can be enabled by adding the string ‘duration’ or ‘tempfile’ to your config file. For example, a config file named ‘tnm.config.tempfile.txt’ would automatically switch tnm to tempfile mode. Both modes have a limit option (--duration_limit=N and --tempfile_limit=N) to show only the top N matches.

Duration mode

In duration mode, queries are sorted from longest to shortest duration, or time to run. So, using a --duration_limit=10 option would show you the top ten slowest queries.

Tempfile mode

When Postgres needs more memory than what is available, it creates temporary files on disk. If the log_temp_files is on (set to something other than -1), the size of each file created on disk is logged. In tempfile mode, queries that create on-disk temporary files are sorted from largest to smallest. In addition, the arithmetic mean for each query, as well as the total memory used, is computed and reported as well.

Config File Format

Note: the config file is rewritten by tail_n_mail each time it is run, so items inside of it may get reordered and reformatted. However, the basic information will stay the same. Blank lines and lines starting with a hash (#) are ignored. All other items are of the format NAME: value where NAME is always a single uppercase word. The items are:

  • EMAIL:
    • (mandatory) This item consists of one or more email addresses, comma-separated
  • FILE:
    • (mandatory) This is the name of the file(s) to be scanned. More than one FILE can be added. In addition to an absolute file name, the following formats are supported:
      • POSIX escape syntax, for example: FILE: /var/log/pg_log/postgresql-%Y-%m-%d.log
      • The special word LATEST. which will cause the program to use the file in that directory that was most recently changed. For example: FILE: /var/lib/postgresql/8.4/main/pg_log/LATEST
  • INHERIT:
    • A file that has configuration parameters inside of it. This allows you to use a single file containing a centralized list of strings to include and exclude, for example, and have many configuration files point to it. The inherit file can contain most of the parameters that a config file can, with the exception of FILE, OFFSET, and LASTFILE.
  • INCLUDE:
    • A regular expression indicating which lines match for possible mailing out. More than one INCLUDE item line can be used.
  • INCLUDE_VIA_FILE:
    • Similar to INCLUDE, but for specific files. Format is string_regex ~ file_regex
  • EXCLUDE:
    • A regular expression indicating which lines to exclude. This is applied after the INCLUDE rules above. More than one EXCLUDE item line can be used.
  • EXCLUDE_VIA_FILE:
    • Similar to EXCLUDE, but for specific files. Format is string_regex ~ file_regex
  • EXCLUDE_PREFIX:
    • A regular expression which indicates which lines to exclude by looking at the prefix of the line, rather than the content. The prefix typically contains things such as the timestamp and PID.
  • EXCLUDE_NON_PARSED:
    • A regular expression indicating which non-parsed lines to be skipped (non-parsed indicating those that do not match the log_line_prefix, such as notices directly from the OS - one example is rsync errors inside of an archive_command call)
  • MAILSUBJECT:
    • The subject line for the email to be sent. Special uppercase words can be used that will be replaced each time:
      • HOST - replaced with the current hostname
      • FILE - replaced with the current log file name
      • NUMBER - the total number of matches
      • UNIQUE - number of unique matches
  • FIND_LINE_NUMBER:
    • Indicates whether an effort is made to report the line number that each line of interest appears at. Valid values are 1 and 0, and this item is on by default. Counting line numbers can be expensive for very large log files, so this item should be turned off if tail_n_mail is running very often (e.g. once a minute) and the line number is not important.
  • OFFSET:
    • How many bytes to skip in the current log file before beginning to scan. This is set automatically by the program and should not be changed manually.
  • LASTFILE:
    • The name of the last file to be checked. Used in case the file has rotated to another one since the last time tail_n_mail was run. Set automatically, and should not be changed manually.
  • MAXSIZE:
    • The maximum number of bytes in the file to check. This is set to 80 million bytes by default.
  • TYPE:
    • What type of file this is. Currently, the only two choices are:
      • normal: the default, no special processing
      • duration: the duration times are extracted from the log file, allowing the DURATION item of --duration command line flag to be used. This mode is automatically chosen if the name of the config file contains the string ‘duration’.
  • DURATION:
    • The minimum number of milliseconds a query is run before it will be reported. Only works when the mode is set to ‘duration’.
  • PGFORMAT:
    • Indicates the format of the log file, so that the date can be extracted from each line. The value should be a single number, and defaults to choice “1” below. Each number corresponds to a regular expression as follows:
      • 1: (.+?[\d+]): [\d+-\d+]
      • 2: (.+?\d\d:\d\d:\d\d \w\w\w)
      • 3: (.+?\d\d:\d\d:\d\d \w\w\w)
  • MAILZERO:
    • Indicated whether to send mail even if no matches were found. As it is off by default, the only sensible option at the moment is MAILZERO=1
  • MAILSIG:
    • A file whose contents will be appended to the end of mail messages. More than one MAILSIG can be given: each file will be appended in order.
  • TSEP:
    • The thousands separator: see discussion in the basic options section.
  • LOG_LINE_PREFIX:
    • The current log_line_prefix setting
  • LOG_LINE_PREFIX_REGEX:
    • Can be used multiple times to set different log_level_prefix values to file names matching a regex. Format is regex = ‘myprefix’. For example: LOG_LINE_PREFIX_REGEX: warehouse = ‘%t %u@%d [%p] ‘

Config File Example

Here is a complete sample config file that grabs all “fatal” errors from today’s Postgres log, while excluding any errors caused by trying to connect to a non-existent database:

## Config file for the tail_n_mail program`
## This file is automatically updated`
EMAIL: greg@endpoint.com`
MAILSUBJECT: HOST Postgres fatal errors (FILE)`

FILE: /var/log/postgresql-%Y-%m-%d.log`
INCLUDE: FATAL:  `
EXCLUDE: database ".+" does not exist`

Note that there are two spaces after the final colon in the INCLUDE line. This regex might pick up some false positives, but it’s good enough for everyday use. If the name of the file above was “tnm.config.fatals.txt”, it could be run every five minutes by adding the following line to your crontab:

*/5 * * * * perl tail_n_mail tnm.config.fatals.txt

Temporarily Disabling

If you need to temporarily disable tail_n_mail from running (for example, during a Postgres upgrade), you an add the following line to the tailnmailrc file:

disable=1

Mailing Lists

There are two mailing lists related to tail_n_mail:

  • TNM - General information and discussion
  • TNM-announce - Low volume announcement list

Bugs and Feature Requests

Bugs should be reported through the GitHub issue page. Feature requests are welcome there as well, or send us an email.

Development

Everyone is encouraged to look over and make improvements to the code. The latest development version can be obtained from GitHub by running:

git clone https://github.com/bucardo/tail_n_mail.git

Bucardo.org is hosted and supported by End Point Dev

End Point Dev logo

Need assistance with tail_n_mail?

End Point Dev offers professional support for tail_n_mail, as well as specializing in developing, designing, and marketing effective websites. Since 1995, our diverse team of developers has shown that End Point can handle your organization’s greatest web and database challenges.