Using LogParser to Scan Firewall Logs

Using LogParser to Scan Firewall Logs

I’ve got a request from a customer to scan or review the firewall logs because of an audit finding.  But there is no budget to purchase any management product to actively monitor the humongous logs coming in daily.  We’ll have to make do with what we’ve got.  Well, we do have a state-of-the-art PC, armed with Intel Core 2 Duo @ 2.33GHz and 2GB of RAM.  Cool!

A typical daily firewall log size is around 3.8GB (roughly 11.8 million lines of text entries).  At first, we tried using some freeware tools out in the web like “Splunk”.  But because of the size of the log files, the response is slow and also it doesn’t meet our requirements.  We wanted something like a “Top 3 Deny entries”.

Well… being MS centric guy, I know about this great tool (if not the greatest tool) ever released free by Microsoft.  I’ve used it before and the speed is simply FAST and the beauty of it all is it’s flexible enough to scan any type of log files.

This is what I’ve done:

1. Run logparser to extract the key fields like “Action, Source IP, Destination IP, Source Port, Destination Port” and output to a csv file

1. Run logparser again to churn out the Top 3 Deny entries from the output csv file from Step 1

And guess what?  For this 3.8GB (11.8 million lines) text file, it only took 3.5 minutes!  Pretty impressive yeah?

Observations

While running, I noticed via “Windows Task Manager” that logparser only uses maximum 50% of the CPU.  Most likely it’s due to the fact that logparser (version 2.2) has been around for quite some time and it’s not optimized to make full use of multi-threading capability to use up all the dual core CPU power.  I do hope someone in MS can release a newer version to harness the power and imagine that instead of 3.5 mins, it will be 1.75 mins!!! ;)

Code Examples

Here is the code that does the magic…

A typical Juniper FW log looks something like this:

Nov 5 23:58:11 192.168.1.3 Netscreen-FW1: NetScreen device_id=Netscreen-FW1 [Root]system-notification-00257(traffic): start_time="2008-11-05 23:56:32" duration=0 policy_id=125 service=syslog proto=17 src zone=Untrust dst zone=Trust action=Deny sent=0 rcvd=0 src=172.26.1.75 dst=166.2.3.50 src_port=514 dst_port=514 session_id=0

Use this command to extract the important parameters from the log:

 Private Function ScanLog(strFile)  
   Dim strSQL  
   strSQL = "SELECT EXTRACT_VALUE(Text,'action',' ') AS Action, " & _  
     "EXTRACT_VALUE(Text,'src',' ') AS Src, " & _  
     "EXTRACT_VALUE(Text,'dst',' ') AS Dst, " & _  
     "EXTRACT_VALUE(Text,'src_port',' ') AS Src_Port, " & _  
     "EXTRACT_VALUE(Text,'dst_port',' ') AS Dst_Port " & _  
     "from " & strFile & " to results.csv"  
   WshShell.Run LOGPARSER & " -i:TEXTLINE """ & strSQL & """", HIDE_WINDOW, WAIT_ON_RETURN  
 End Function  

The above will output to a csv textfile call “results.csv”.  Next, use this function to generate the Top 10 results.



 Private Function GenTopResult(strFile)  
   Dim strSQL  
   strSQL = "SELECT TOP 10 Action, Src, Dst,Src_Port, Dst_Port, COUNT(*) AS Hits FROM " & strFile & " to top.csv WHERE Action='Deny' GROUP BY Action, Src, Dst, Src_Port, Dst_Port ORDER BY Hits DESC"  
   WshShell.Run LOGPARSER & " -i:CSV """ & strSQL & """", HIDE_WINDOW, WAIT_ON_RETURN  
 End Function  

At the end of it, you will get a “top.csv” text file containing the Top 10 results sorted by highest hits.