Palo Alto Log Analysis Using MySQL
The purpose of this project is to provide a versatile and affordable firewall log analysis platform for the network security analyst.
Why?
Because intelligent analysis of firewall logs is an integral piece of network security monitoring. Firewall log analysis can point to serious network or system configuration problems, help profile suspicious host behavior, and provide essential forensic data.
The core of the platform is the combination of custom Perl code and the open source database software MySQL. Those components are then integrated on the standard Debian Linux 64-bit distribution with modest hardware requirements.
I hope there is enough interest with and participation in this project to warrant more materials.
Please feel free to contribute queries, scripts, suggestions, or questions.
A work in progress, the journey to Palo Alto log analysis begins with import of URL logs.
Requires: Linux with Perl, Perl-DBI, and MySQL
Step 1: Study the MySQL table description and statements in PanLogURLtoMySQLschema.txt
Step 2: Use the MySQL statements to create a database called panlogs and the table combined_template.
Step 3: Obtain a url log export from a Palo Alto appliance. (see Note 1)
Step 4: Configure the Perl script (PanLogURLtoMySQL.pl) with the user name and password of the MySQL account with create, read, write permissions for the panlogs database and its tables. Closely inspect the other system specific variable assignments, such as MySQL database path, tmp file path, etc.
Step 5: Run PanLogURLtoMySQL.pl
Step 6: Enjoy URL log data analysis in MySQL
Note 1: Currently, the code uses the file name for table naming. Since a scheduled log export for “yesterday's logs” is always named with “today's date”, that means the table name with always be the day after the day of the url data. Example; DC-5060_url_2013_12_17_last_calendar_day.csv, or a format of DEVICE-NAME_url_YYYY_MM_DD_last_calendar_day.csv, is the log export for 12/16 URL log data. The current code parses on the underscore delimiter “_” to arrive at the table name, such as panlogs.20131217. If the DEVICE-NAME has an underscore in it, you will need to modify the code. At some point, I considered grabbing the date of the first record from the .csv file to name the table, but just haven't gotten around to that.
PanLogURLtoMySQLschema.txt Table description and MySQL statement to create the database and table template
PanLogURLtoMySQL.tgz Perl code to convert the Palo Alto URL log export (.csv) into MySQL data
Disclaimer: Use at your own risk. Absolutely no warranty is either expressed or implied as to the suitability or safety of any code or writings. Always test software of unknown quality and function in a disposable lab environment before trusting it in a work environment.