Welcome to logger
 
Here is where you find the database I have created to query FW1 logs.  If you have had problems in the past, I've updated the database, so it should be more stable now.  Besides, it be free.    If you have any recommendations, email me at lspitzner@enteract.com
 

What is it?

Logger is an MS Access 97 database that will query you FW1 logs.  You can easily create and add your own queries once you get the hang of Access.  Its easy to share the "SQL code", so we can share among each other queries we make.  So far I have successfully created only 3 queries.

This is how it works

Step one - Convert your fw.log file to an ascii file
You first have to convert the Firewall 1 log file into ascii, so you can import it into the database.  You can do this at the command prompt

fw logexport -i fw.log -o fwlog.txt -n

Note, with the -n parameter you do not resolve IP address, which is MUCH FASTER.  After you logexport the binary log file to ascii, this is what the first line of the ascii file should look like.

num;date;time;orig;type;action;alert;i/f_name;i/f_dir;proto;src;dst;service;s_port;len;rule;icmp-type;icmp-code;user;reason;sys_msgs

If the first line does not look like this, DO NOT PANIC.  Consistency is not a Checkpoint FW1 quality :)   Different people tend to get the fields in a different order.  All you need to do is change the order of the fields in the database table.  Don't worry if you do not know how, you will understand after reading this page..  NOTE:  If you are using NAT, you will have four extra fields in your database, xlatsrc, xlatdest, xlatdst, xlatsport, xlatdport.  You will have to add these four fields to the database table, in the order that they are listed in your ascii file.
 
 
Step two - Massaging the data
Okay, now we have to tweak that data so Access can import it.  Regardless if you are using Unix or NT, you will want to blow away the very first line of the log file.  This is nothing but headers.

Now, just for you Unix users, MS programs use the ^M character as the return character. (If you don't know what I am talking about, you can see the control characters in vi with ":set list").   Thus, Access will not be able to import your Ascii file.  You will need to add the ^M character to the end of every line.  There are several ways to do this.

  1. For you hardcore 'vi' types, open the file in 'vi', and do a global search and replace, adding the ^M character at the end.
  2. Most flavors of unix (including Solaris) come with a 'unix2dos(1)' utility that does it for you.
  3. Or for those who prefer the Windows GUI, try the following
Step three - Import the ascii log file to MS Access
Great, now that you have exported the log file into ascii and "tweaked" the data, we can import it into Access.  Fire up Access, bringing up the logger database.  You should get something looking like this (see image below).  You then want to go into File / Get External Data / Import.  Then select your fwlog.txt file to be imported.
 
.
 
 
You will be asked a variety of questions as you import the fwlog.txt file into Access database.  Continue to go with defaults untill it asks you which table you want to import it into.  Select logger (see image below).  Then continue with the defaults untill you finish.  That's it, your data is now successfully imported!!  Note, you might get an error at the end saying it wasn't able to import all the records, don't worry, there is always some random packet the database has a problem with.
 
 
 
 
Step four - Configure the queries
Once you have successfully imported the ascii log file, you then configure the queries for your network.  This is very simple.  You first go into queries (the tab next to tables, see image above), then just change the IP address to reflect your company's IP schema (see image below). This is the heart of the program. This is where you define what data is searched.  As stated before, there are 3 queries.  To configure each query for you network, select the query you want to modify, then select Design.  You will get the window you see below.
 
 
3 Queries Explained Step five - get your data!
Once you have successfully completed the configuration, you go to the Reports tab and click on the data you want.  Reports (tab next to query) are what display the data you want. To display the data, you just click on the respective report.  Each report is automatically linked to each query. This when you click on the form, it execute the query. Your done! Access also does a great job of converting these Reports into html!
 
Click here to get logger.  The file is 14K. logger.zip
 
 
Return to Whitepapers