Parsing Common Log Formats With Papertrail

When setting up logging for your apps and services for the first time, it’s natural to want to focus on the content of the messages; you want to make sure you have all the information you need when you use your logs to troubleshoot issues. But picking your log format is just as important.

With so many log formats out there, each with its own set of pros and cons, it can be hard to know the best one for your application. Before you give up and start rolling your own format, you should try one of the options listed here. These formats are supported by tools and frameworks, so you can automatically benefit from the wide ecosystem of log parsers.

For example, SolarWinds^® Papertrail^™ supports the formats listed here and more, making it easy to aggregate your logs in the cloud for lightning quick filtering and analysis. But before we get into how to use Papertrail to dig into your log data, let’s take a look at some of the most common log formats.

JSON

JSON is a de facto log format standard for many apps requiring structured logging. It’s a machine- and human-readable format, which makes it an excellent choice because it’s easy to parse and understand when using it for debugging. Here’s an example of a JSON object describing a person with a name and age:

{ “name”:”John,” “age”:38 }

There are many tools out there for converting to and from JSON because it’s so ubiquitous. If you want to be certain you’re writing legal JSON syntax, there are even validators capable of telling you if your JSON data is properly formatted.

MySQL

Sending MySQL error logs to Papertrail is easy, and it automatically accepts the three log writers for MySQL 8.0: log_sink_internal, log_sink_json, and log_sink_syseventlog. You can also configure MySQL to provide more or less detail using the MySQL SET GLOBAL command:

SET GLOBAL log_error_services = ‘log_filter_internal; log_sink_internal;

By default, MySQL uses log_sink_internal, which sends error logs in this format:

timestamp thread_id [priority] [err_code] [subsystem] message

Consider this example:

2020-03-22T12:35:47.538083Z 0 [Note] [MY-012487] [InnoDB] InnoDB: DDL log recovery : begin

In this example, MySQL is reporting with priority “Note” that thread “0” in InnoDB subsystem “MY-012487” was beginning log recovery. Increasingly, MySQL admins are using the expanded log_sink_json log writer, providing more detail and easier analysis. To enable log_sink_json, use the following MySQL commands:

INSTALL COMPONENT ‘file://component_log_sink_json’;

SET GLOBAL log_error_services = ‘log_filter_internal; log_sink_json’;

Papertrail will then receive a JSON block of key-value pairs similar to the following:

{
   "prio":3,
   "err_code":10051,
   "subsystem":"Server",
   "source_file":"event_scheduler.cc",
   "function":"run",
   "msg":“Event Scheduler scheduler thread started with id 4”,
   "time":“2018-03-22T12:35:47.669397Z”,
   "thread":4,
   "err_symbol":"ER_SCHEDULER_STARTED",
   "SQL_state":“HY000”,
   "label":"Note"
}

Whichever log writer you choose, Papertrail will collect your MySQL event logs and make them easy to view, search, and filter. Simply enable Papertrail as a log destination in the remote_syslog2 daemon or by setting the log_syslog configuration variable.

Windows Event Log

The Windows Event Log records log messages from services such as SQL Server and IIS as well as the Windows operating system itself. Like JSON, Windows event logs use a plain-text structured format. This makes them easy to parse with Windows Event Viewer, an app preinstalled on Windows machines. Windows event log files contain a single file header and one or more event records, which contain information such as the type of event, a time stamp, the ID of the user who generated the event, and the name of the computer.

Besides Windows Event Viewer, many other tools are available for parsing Windows Event Logs. Papertrail is one of these tools. Papertrail accepts log files shipped from Windows machines using either the remote_syslog2 daemon or an app-level library like NXLog.

Common Log Format (Apache)

The Common Log Format, otherwise known as the NCSA Common log format, is a plain-text, single-line file format used extensively for the server log files of web servers like Apache. These log files are frequently used to record which sections of a website users are accessing along with any errors they encounter along the way.

The Common Log Format includes the following parts:

host ident authuser date request status bytes

Here’s a typical example from an Apache access.log file that recorded a user successfully (HTTP status code 200) accessing the home page (/) of a web server running on localhost (127.0.0.1):

127.0.0.1 - - [23/Jan/2020:16:41:01 +0000] “GET / HTTP/1.1” 200 11229

Because the Common Log Format is standardized, it’s used by many web servers (not just Apache). Many tools offer support for parsing these log files, including Papertrail. For example, you can route Apache log files from your machine to Papertrail using the remote_syslog2 daemon.

W3C Extended Log Format

Another log file format used by web servers such as IIS is the W3C extended log format. This is a customizable server-side logging format allowing you to pick and choose from a list of fields to include in your log messages. The fields you can choose from include the date, time, and IP address of the server handling the request. In fact, the W3C format was originally created to allow for a wide range of data to be captured in logs.

Here’s an example log file entry showing a user accessed the path /images/picture.jpg:

#Software: Microsoft HTTP Server API 2.0
#Version: 1.0 // the log file version as it’s described by “https://www.w3.org/TR/WD-logfile.”
#Date: 2002-05-02 17:42:15 // when the first log file entry was recorded, which is when the entire log file was created.
#Fields: date time c-ip cs-username s-ip s-port cs-method cs-uri-stem cs-uri-query sc-status cs(User-Agent)
2002-05-02 17:42:15 172.22.255.255 - 172.30.255.255 80 GET /images/picture.jpg - 200 Mozilla/4.0+(compatible;MSIE+5.5;+Windows+2000+Server)

With the W3C extended log format, a header is included. This header tells you the field names used by each log entry.

Filtering Log Results

No matter which log format you choose, Papertrail can help you quickly search and filter your log data using a simple query syntax. This syntax is similar to Google search and supports standard Boolean operators such as AND and OR. You can also exclude search terms by using the negative operator (–).

Let’s look at how to search through your logs for messages from an individual machine. Suppose you need to find all error messages from a machine named web01. The following query will return any matching log entries:

web01 error

If you need to uncover error or critical log messages from web01, you can use a query like this:

web01 AND (error OR critical)

Papertrail also supports searching with attributes. Sometimes, it’s easier to narrow down search results using one of these five attributes: sender, program, message, severity, and facility. You can use these attributes in the format key:value-style. Continuing with the previous example, if you want to find all the log messages from web01 containing error or critical in the log message and generated by the Apache web server, you can use the following query:

sender:web01 AND program:httpd AND (error OR critical)

Unlike the previous query, which may include some noise, this one offers much tighter control and will only include the messages you intended.

Conclusion

Logging is a critical part of running apps and services. If you design your message content well and select a supported log format, you’re setting yourself up to be able to quickly troubleshoot problems in your application. With wide support across all common log formats, Papertrail can parse and ingest your log files. Once your logs are neatly tucked away in the cloud, you can quickly search through them using the simple search syntax in Papertrail.