Firewall Log Fun

This thread is ongoing, but let me start with the results I have from a year worth of dropped firewall connections.

  • 228376
    January 2016
  • 253698
    February 2016
  • 244374
    March 2016
  • 494842
    April 2016
  • 611021
    May 2016
  • 259013
    June 2016
  • 529243
    July 2016
  • 406937
    August 2016
  • 2096766
    September
  • 264421
    October

Let’s jump back a minute. I am importing firewall logs for dropped connections into a MS SQL Database. September as you can see is a fun month with 2,096,766 records.
Since my firewall is a Zyxel device, I gave a look at the .csv delimited log output. Easily enough you can use a Data Import Wizard to spin the logs into some tables. Rough table to log structure is as such:

CREATE TABLE zy_2016-09 (
  time VARCHAR(50) NULL,
  source VARCHAR(50) NULL,
  destination VARCHAR(50) NULL,
  priority VARCHAR(50) NULL,
  category VARCHAR(50) NULL,
  note VARCHAR(50) NULL,
  sour_interface VARCHAR(50) NULL,
  dest_interface VARCHAR(50) NULL,
  protocol VARCHAR(50) NULL,
  message VARCHAR(250) NULL,
  col00 VARCHAR(250) NULL,

I am having fun crawling some output. Typically it’s some sort of fancy OpSec to not say your type of network gear, but this is meant to be informative and hopefully helpful.
So let’s crawl some queries and output in the next post.