A Universal Firewall Reporting Tool

Bob McCormick, Greg Hartung, and Zachary Conger

One of the difficult problems in systems and network administration is the management of log files. Most administrators realize the importance of monitoring log files, but often systems and devices generate such a large volume of logs that manual monitoring is impossible. Log watching programs such as Swatch can help automate the process of looking for errors, but they do nothing for reporting or trending, nor do they allow you to query for specific information. Fortunately, with a minimal knowledge of Perl, SQL, and CGI programming, it is not difficult to create homegrown front ends to summarize and report on your logs.

Recently, we were faced with the problem of summarizing and reporting on the logs from several high-traffic firewall systems. Using some relatively simple Perl scripts, a PostgreSQL database, and a network sniffing utility, we developed a system to report on the Internet-bound user HTTP traffic through these firewalls. This system logs user traffic through PIX firewalls and Squid proxy servers to the PostgreSQL database. A CGI front-end then allows administrators and managers to query and generate reports regarding this traffic.

Project Goals

The first step was defining project goals and requirements. The requirements identified were:

• Log user HTTP requests. We decided that, at least initially, we would only concern ourselves with outbound user HTTP traffic. We allow only limited outbound access on ports other than 80 and 443, so logging and reporting on non-HTTP traffic was not as critical. However, some firewalls, such as the PIX, only log IP addresses from HTTP requests rather than fully qualified domain names. We would need a way to inject the domain information into our logs for such devices.
• Support diverse firewalls. Specifically, we needed to support Cisco PIX and Squid. We also wanted a solution that was flexible enough to accommodate any future firewall platforms.
• Support a large number of users and very high traffic levels. We estimated supporting approximately 7000 users between our firewalls. Logging HTTP traffic for this number of users will generate a lot of log information.
• Real-time or near real-time reporting. We wanted to find out what people were doing now, not just yesterday or last week.

Based on these requirements, we first searched for an existing solution (either commercial or open source) that would meet our requirements or could be readily modified to meet our requirements.

There are quite a few programs, both commercial and non-commercial, for analyzing PIX logs, but none of them do anything with user authentication messages or HTTP traffic messages. There are also quite a few programs to analyze Squid log files. All of the ones we found run as nightly batch processes and produce a group of static report files. This fails our requirement for near real-time reporting. Additionally, our Squid server handles so much traffic that the resulting static report files were often quite large.

Probably the most difficult requirement was finding one tool that could report on a wide variety of firewalls. We didn't just want a reporting tool for one firewall; we wanted a universal firewall reporting tool.

The Solution

Our solution was to write all of the log information to a PostgreSQL database. We would then write a CGI front-end to generate various canned and ad hoc reports by querying the database.

The Squid proxy server logs user authentication information and user traffic information locally to the access.log file. The PIX logs authentication and traffic information to syslog. We ran one small Perl process on the Squid proxy server, and one on the syslog server, that would parse the relevant log messages and insert them as records into the PostgreSQL database. Because of limitations in the way the PIX logs user traffic, we also had to log information from a sniffer placed in front of the PIX. A Perl CGI program on the Web server provided a reporting and querying front-end.

The Database

The database is central to the universal firewall reporting tool. By using a database server, we were able to handle very large amounts of log data much more manageably and more responsively than with flat files. Reports that might have run for hours to get them from our old flat log files could be generated in a matter of minutes. Using an SQL database server as the "middleware" also made generating new reports easier.

For the main table, called "traffic", we needed to record the users' login name, source IP address, where they went (the destination URL), and when they went there. We split the destination URL information into two fields -- one for the domain, and one for the URL path information. Because we have multiple firewalls, we also created a field to record which firewall the traffic passed through. Additionally, we created a status field to record whether the request was permitted or denied. That field is currently used only for data from the Squid proxy server. The table definition can be seen in Listing 1.

This information was easy to parse out of the Squid proxy server logs. However, the PIX, unfortunately, logs the destination IP address of the user's traffic instead of the domain name in the user's original request. For a variety of reasons, virtual hosting, etc., most Internet IP addresses are unlikely to reverse to what the user actually typed. For example, a user might type:

http://www.joeswebsite.com/index.html

someuser@192.1.1.1 Accessed URL 10.1.2.3:/index.html

www.hugehostingcompany.com

someuser@192.1.1.1 Accessed URL www.hugehostingcompany/index.html

We captured user traffic through the PIX firewalls with an inline sniffer strategically placed to capture all traffic into the inside interface of the PIX (connected to a span/mirror port on the switch). We selected urlsnarf (part of the dsniff package) as our sniffer application. Urlsnarf captures the actual URL from the HTTP header and records it in the standard Extended Common Log Format used by most Web server logs. This information is parsed with a short Perl script and then remotely inserted into the database table mentioned above.

The HTTP request header captured by urlsnarf only contains the user's source IP address, not their username. This left us without the username. Before we could insert the request data into our database, we needed to find the username. Our solution was to log the user authentication from the PIX in a separate database table called the "auth" table. We then linked these tables using triggers. A trigger is a stored function that "fires" before or after a row is inserted into the database. The table definition for the "auth" table can be seen in Listing 2.

We built the triggers out of PL/SQL code. The first trigger, "auth_prune", fires on the user authentication table before an insert. This trigger deletes any old records with the same source IP field as the new record. The new user authentication record is then inserted without further interruption. It's not a problem to delete old authentication records. Traffic information from dsniff is recorded in real-time, and we only need to know who is currently logged in.

The second trigger fires before an insert into the traffic table. This trigger, "traffic_username" performs an SQL query from the "auth" table to look up the username corresponding to the source IP address of the current traffic packet. We have thousands of users, but because all old records for that source address are removed from the "auth" table, only one record is returned and it comes back very quickly. The PL/SQL source for the trigger functions can be seen in Listing 3.

Collecting the Data

As mentioned previously, we collect data to be inserted into the universal log database from three locations: the Squid log files on the proxy server, the PIX logs on the syslog server, and the urlsnarf application running on the sniffer box. For each of these data sources, a Perl script parses the data and inserts it over the network into the database.

The Squid log file script is shown in Listing 4. The script uses the CPAN module File::Tail to read new log messages from the end of the Squid log file "access.log". The log file message is parsed and the necessary information is inserted into the database using the standard Perl DBI module. When run with the "-d" command-line option, the script will background itself and run as a daemon.

The PIX authentication log script shown in Listing 5 is very similar, except that instead of tailing the log file, we log to a named pipe. The script reads from the named pipe, parses the incoming log messages, and then inserts the results into the database.

The final data collection script runs on the sniffer box. This script, shown in Listing 6, is again very similar to the previous two. In this case, rather than reading from a log file or name pipe, the script executes the urlsnarf application and pipes the standard output of urlsnarf into the filehandle. This output is then parsed and inserted into the database.

The Web Query Front-End

Once the universal log database was constructed and populated, we had a good engine for querying the logs in very flexible ways. We agreed that a Web-based interface to the database would be ideal for providing reporting functionality for ourselves as well as non-technical users of the system. We decided that a set of CGI programs should be constructed to allow general queries, as well as canned reports such as "Top 10 Websites", "Top 10 Users", and the like.

We could have written the CGI programs in any of a number of languages, but we chose Perl for a number of reasons. Perl had been used for the other parts of the log database system, we have experience in it, and it works well for CGI.

To begin, we wrote a general database query tool called "querytool.pl" consisting of a query page and a result page. We reasoned that canned reports could simply be a set of specific queries fed into this more general subroutine. This query tool needed essentially three parts: the query page, the query translator, and the results page.

The query page has these parts:

• User picker -- A list of users pulled from the current database of users that have at some point authenticated to the firewalls or proxies.
• String search fields -- Fields that allow a pattern match against domain names or URLs.
• Date range picker -- A series of drop-down boxes allowing the user to pick a date range for the query.
• Report format options -- A series of options for the format of the report, such as which columns to show, and how many results to show per page.
• Sort options -- Columns on which to sort the report.

The query translator transforms the chosen parameters from the query page to an SQL SELECT statement that PostgreSQL will understand. Finally, the results page prints the results in the chosen format. Again, Perl modules made the job simpler. We used the CPAN module CGI.pm to simplify the construction of CGI variables, HTML output, form handling, and cookies. As in the log collection scripts, the DBI module provided hooks into the SQL database.

The Query Page

Since the query page needs to show a list of available users, we connect to the database right away and grab them as an array. The section of code to do this is Listing 7. Then we construct the actual query page using the CGI module as shown in Listing 8. The "start_page" and "end_page" subroutines refer to a subroutine that creates a standardized and theme-able HTML wrapper for the page. The resulting output page can be seen in Figure 1.

When users click the "Submit Query" button, they are directed to the same program, querytool.pl, but with a hidden CGI field "target", which is set to "query_results". The script sees this and knows to run the query_report subroutine, which translates the query, queries the database, and displays the results.

The Query Translator

The query translator separates all of the variables entered from the query form and constructs a rational SQL query from it. This was the most complex part of the query tool, because it involved taking all of the requested bits of information and cramming them into a concise and syntactically correct statement. The query translator can be seen in Listing 9. We end up with two queries. $querything is a SQL statement that selects the actual information we want, limited by the number of results per page (the LIMIT clause) and the offset according to what page we're on (the OFFSET clause). $querycount is an SQL statement that makes the same query, but just returns a count of results. We use the count of results to calculate which page we're on, and what other pages of results are available. In Listing 10, the actual queries are made and the results are returned for printing. A representative output page can be seen in Figure 2.

Conclusion

This project looked daunting when we drew up the initial requirements. It was a large undertaking, but dividing it up into smaller, manageable pieces was the key to making the project a success. Also, using PostgreSQL as a common middle layer allowed us to isolate and abstract out the differences between our data sources. By doing so, each of the data collection scripts had to be concerned only with the specifics of one data source. More importantly, the reporting front-end did not need any information about how the separate firewalls function or the differences in how they record user traffic.

Refinements and features should be simple to add given this foundation. We could write new collection scripts for additional firewalls and proxies, tweak the scripts for performance, prepare canned reports, or add charts and graphs to the mix. But even with the minimal effort so far, we have created a useful and effective universal firewall reporting tool.

Bob McCormick has been a UNIX and network administrator for nine years. He's worked with a variety of UNIX systems, including Solaris, AIX, HP-UX, Linux, and BSD. He currently administers routers, switches, and firewalls supporting tens of thousands of users across the continental United States.

Zachary Conger has been a systems and network engineer for nine years, working as a consultant for companies along the Front Range in Colorado, and specializing in security. He studied psychology and computer science at Iowa State University and the University of Iowa, and neuroscience at the University of Minnesota.

Greg Hartung has been a network engineer, sys admin, and DBA for more than 10 years. In his free time Greg also develops embedded microcontroller applications, is a private pilot, and has returned to school to study physics.