Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
[Script] Analyse iptables logs as spreadsheet - Log viewer
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Thu Nov 10, 2005 10:35 pm    Post subject: [Script] Analyse iptables logs as spreadsheet - Log viewer Reply with quote

This sed script creates a comma-separated-values (.csv) file from your iptables logs. You can open this file in
OpenOffice.org Calc, and do some sorting/filtering/editing. Read usage below; grep your log lines and pipe through
sed. Now you have a powerful log viewer! 8)

firewall.sed
Code:
# Copyright 1999-2005 Gentoo Foundation
# Distributed under the terms of the GNU General Public License v2
# $Header: firewall.sed,v 1.0 2005/12/04 17:38:00 lost+found Exp $
#
# Analyse iptables logs as spreadsheet.
# 0.1 First version was posted here: http://forums.gentoo.org/viewtopic-p-2853826.html#2853826
# 0.2 Syntax and small changes, separated LEN/WINDOW columns, improved interfaces
# 0.3 Syntax and small changes, improved separators
# 0.4 Setting for max. number of rows
# 0.5 Syntax change, improved tcp flags, file naming/splitting
# 0.6 Improved ICMP labels/columns
# 0.7 Syntax and small changes, improved labels/columns, fragmentation, MTU, USAGE text
# 0.8 Syntax and big changes, improved labels/columns, more ICMP, INCOMPLETE, anything
# 0.9 Syntax and big changes, improved labels/columns, SEQ/ACK, PARAMETER, GATEWAY, AH/ESP
# 1.0 Syntax and small changes, UID, OPT, removed bad file splitting, excludes and optional things
#
#  HOMEPAGE: http://forums.gentoo.org/viewtopic-p-2868355.html#2868355
#
#  USAGE:
#  Save this script as "firewall.sed", adjust leading commas to fit your own system logger (see below).
#  If needed, uncomment the lines to be used for IPsec and/or the iptables logging options --log-ip-options,
#  --log-tcp-sequence, --log-tcp-options and --log-uid. Then type something like:
#  # grep -h FIREWALL /var/log/kernel/* | sed -f firewall.sed > firewall.csv


# Limits the number of rows. OOCalc can handle 65535 at max. Comment out for unlimited.
65535,$d

# Creates labels on row 1. Adjust first commas if your system logger starts lines differently. Example:
# Nov  8 21:50:23 [kernel] FIREWALL DROP UNKNOWN:  ---> this needs 7 columns, so 7 leading commas.
1i\
,,,,,,,"IN","OUT","MAC","SRC","DST","LEN","TOS","PREC","TTL","ID",,"OPT","PROTO","SPI","SPT","DPT","LEN","SEQ","ACK","WINDOW","RES",,"URGP","OPT","TYPE","CODE","ID","SEQ","PARAMETER","GATEWAY","INCOMPLETE","UID","[SRC","DST","LEN","TOS","PREC","TTL","ID",,"OPT","PROTO","SPI","SPT","DPT","LEN","SEQ","ACK","WINDOW","RES",,"URGP","OPT","TYPE","CODE","ID","SEQ","PARAMETER","GATEWAY","INCOMPLETE","] MTU"
# These are the original labels, but can be changed into anything you like.

# Here you can exclude log lines that might be less interesting. Some examples:
#
# Your network machines.
#/eth[^0]/d
#
# An IP address...
#/192\.168\.1\.77/d
#
# ...or all but not this IP address.
#/192\.168\.1\.77/!d
#
# Port probes from Micro$oft.
#/DPT=13[789].*SYN/d
#/DPT=445.*SYN/d

# Substitutes existing quotes and commas.
s/[",]/_/g

# Adds separators and delimiters; removes spaces, tabs and IN=.
s/^/"/
s/[[:space:]]*$/"/
s/[[:blank:]]*IN=/","/
s/[[:blank:]][[:blank:]]*/","/g

### Corrects column alignment; removes labels from data.
#
# MAC column.
s/OUT=\(.*\)","MAC=\(.*\)","SRC=/\1","\2","/
s/OUT=\(.*\)","SRC=/\1",,"/

s/DST=//g
s/LEN=//g
s/TOS=//g
s/PREC=//g
s/TTL=//g

# Uncomment next line if you use iptables --log-uid.
#s/bytes]","UID=/bytes]","/

# INCOMPLETE.
s/PROTO=\([^=]*\)","INCOMPLETE","\[\([[:digit:]]*\)","bytes]/PROTO=\1",,,,,,,,,,,,,,,,,,"\2 bytes/

# Uncomment next 3 lines if you use iptables --log-ip-options.
#s/ID=\([^[]*\)","\([^[]*\)","\([^[]*\)","OPT","(\([^[]*\))","PROTO=/\1","\2 \3","\4","/g
#s/ID=\([^[]*\)","\([^[]*\)","OPT","(\([^[]*\))","PROTO=/\1","\2","\3","/g
#s/ID=\([^[]*\)","OPT","(\([^[]*\))","PROTO=/\1",,"\2","/g

# Groups up to 2 IP flags.
s/ID=\([^[]*\)","\([^[]*\)","\([^[]*\)","PROTO=/\1","\2 \3",,"/g
s/ID=\([^[]*\)","\([^[]*\)","PROTO=/\1","\2",,"/g
s/ID=\([^[]*\)","PROTO=/\1",,,"/g

# Protocol specific.
/"ICMP"/ {
   # INCOMPLETE.
   s/CODE=\([[:digit:]]*\)","INCOMPLETE","\[\([[:digit:]]*\)","bytes]/\1",,,,,"\2 bytes/
   
   # Uncomment 4 next lines if you use iptables --log-uid.
   #s/SEQ=\(.*\)","UID=/SEQ=\1",,,,"/
   #s/PARAMETER=\(.*\)","UID=/\1",,,"/
   #s/TYPE=5","CODE=\([[:digit:]]\)","GATEWAY=\(.*\)","UID=\(.*\)","\[/TYPE=5","\1",,,,"\2",,"\3","\[/
   #s/CODE=\(.*\)","UID=/\1",,,,,,"/
   
   # Some ICMP flavours.
   s/TYPE=[34]","CODE=[[:digit:]]*"/&,,,,,,/g
   s/TYPE=5","CODE=\([[:digit:]]\)","GATEWAY=\(.*\)","\[/TYPE=5","\1",,,,"\2",,,"\[/
   s/TYPE=11","CODE=[[:digit:]]"/&,,,,,,/
   s/TYPE=12","CODE=[[:digit:]]"/&,,/
   
   s/"ICMP","TYPE=/"ICMP",,,,,,,,,,,,"/g
   s/CODE=//g
   s/ID=\(.*\)","SEQ=/\1","/
   s/PARAMETER=//
   s/\[SRC=//
   s/,"]"//
   s/"MTU=/,,,,,,,,,,,,,,,,,,"/
}
/"TCP"/ {
   s/"SPT=\(.*\)","DPT=/,"\1","/
   
   # Uncomment next line if you use iptables --log-tcp-sequence.
   #s/"SEQ=\(.*\)","ACK=\(.*\)","WINDOW=/,"\1","\2","/
   
   s/"WINDOW=/,,,"/
   # Groups up to 6 TCP flags.
   s/RES=\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","URGP=/\1","\2 \3 \4 \5 \6 \7","/
   s/RES=\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","URGP=/\1","\2 \3 \4 \5 \6","/
   s/RES=\(.*\)","\(.*\)","\(.*\)","\(.*\)","\(.*\)","URGP=/\1","\2 \3 \4 \5","/
   s/RES=\(.*\)","\(.*\)","\(.*\)","\(.*\)","URGP=/\1","\2 \3 \4","/
   s/RES=\(.*\)","\(.*\)","\(.*\)","URGP=/\1","\2 \3","/
   s/RES=\(.*\)","\(.*\)","URGP=/\1","\2","/
   s/RES=\(.*\)","URGP=/\1",,"/
   
   # Uncomment next 3 lines if you use iptables --log-tcp-options and/or --log-uid.
   #s/OPT","(\(.*\))","UID=/\1",,,,,,,,"/
   #s/OPT","(\(.*\))/\1/
   #s/"UID=/,,,,,,,,"/
   
   b cleanup
}
/"UDP"/ {
   s/"SPT=\(.*\)","DPT=/,"\1","/
   
   # Uncomment next line if you use iptables --log-uid.
   #s/"UID=/,,,,,,,,,,,,,,"/
   
   b cleanup
}

# Uncomment next section(s) if you use IPsec.
#/"AH"/ {
#   s/SPI=//
#   
#   # Uncomment next line if you use iptables --log-uid.
#   #s/"UID=/,,,,,,,,,,,,,,,,,"/
#   
#   b cleanup
#}
#/"ESP"/ {
#   s/SPI=//
#   
#   # Uncomment next line if you use iptables --log-uid.
#   #s/"UID=/,,,,,,,,,,,,,,,,,"/
#}

#
###

:cleanup
   # Removes empty delimiters.
   s/""//g


USAGE:
Save the script as "firewall.sed", adjust leading commas in the script, to fit your own system logger. If needed,
uncomment the lines to be used for IPsec and/or the iptables logging options --log-ip-options, --log-tcp-sequence,
--log-tcp-options and --log-uid. They are commented out to speed up the script for the non-users. The use of the
iptables --log-prefix option may be practical, but is not necessary though: IN= occurs in all log lines. If you use
more than one --log-prefix, it's a good idea to start them all with the same rather unique word (FIREWALL for
example), and let the number of words be the same. Only this way everything will fit into the columns of one
spreadsheet. Then type (as root usually) something like:
Code:
# grep -h FIREWALL /var/log/kernel/* | sed -f firewall.sed > firewall.csv
Change the grep command for your situation. The location of your logs may not be /var/log/kernel/, and instead
of * you can specify a file name. If you specify more than one file, put them in chronological order. FIREWALL is only
a word in *my* iptables --log-prefix (IN= should work too). You can even make it a regular expression maching a
combination of date and log prefix, like 'Nov 19.*FIREWALL' for the logs of November 19, or 'Nov 19 17.*FIREWALL'
for the logs of November 19 from 17:00-17:59. Just check how your log lines start, and perhaps read the grep
manual for a short regexp explanation.

Firewall logs can be huge, and OOCalc can handle max. 65535 rows (even that is too much on slow systems!).
Next shell script offers file splitting. The use of this script is optional, and includes the command described above.
I think this script is handy, even if you don't need file splitting.

firewall.sh
Code:
#!/bin/sh
# Copyright 1999-2005 Gentoo Foundation
# Distributed under the terms of the GNU General Public License v2
# $Header: firewall.sh,v 0.4 2005/11/29 22:15:00 lost+found Exp $
#
# Analyse iptables logs as spreadsheet (file splitter).
# 0.1 First version was posted here: http://forums.gentoo.org/viewtopic-p-2868355.html#2868355
# 0.2 Small changes, USAGE text, more variables, backups
# 0.3 Small change: $OUTPUTDIR/
# 0.4 Optional logs of today/this hour, error message
#
#  USAGE:
#  Save this script as "firewall.sh", adjust the variables below, make executable,
#  then execute. This script needs "firewall.sed".


# Change next variables to your taste.
LOGPREFIX="FIREWALL"
LOG="/var/log/kernel/*"
LOGLINES="65534"
SEDSCRIPT="/root/scripts/firewall.sed"
ENDUSER="you"
OUTPUTDIR="/home/$ENDUSER"
OUTPUTNAME="firewall_"

MONTH=`date +%b`
DAY=`date +%d | sed s/^0//`
HOUR=`date +%H`
# Uncomment next line for the logs of today...
#LOGPREFIX="$MONTH[[:space:]]*$DAY.*$LOGPREFIX"
# ...or uncomment next line for the logs of this hour.
#LOGPREFIX="$MONTH[[:space:]]*$DAY[[:space:]]$HOUR.*$LOGPREFIX"

TEMPDIR=`mktemp -d /tmp/firewall.XXXXXX` || exit 1
grep -h $LOGPREFIX $LOG | split -a 3 -d -l $LOGLINES - $TEMPDIR/$OUTPUTNAME
if [ -e $TEMPDIR/*000 ]; then
   for i in $TEMPDIR/*; do
      sed -f $SEDSCRIPT $i > $i.csv
      rm $i
      chown $ENDUSER:users $i.csv
      mv -b $i.csv $OUTPUTDIR/
   done
else
   echo "There are no logs right now."
fi
rm -r $TEMPDIR

You can repair the scripts after copy/paste from a browser window, by typing:
Code:
# sed -i~ -e 's/   /\t/g;s/\t /\t/;s/^ //;s/ $//' firewall.sed firewall.sh
This removes leading and/or trailing space characters, and adds some nicer looking tabs.


TIPS:
  • Make firewall.csv owned by you:
    Code:
    # chown you:users firewall.csv
    OOCalc will refuse to do some things if it's not.
  • Select in the Text Import popup Column type "Text" for 6 columns containing ip addresses (SRC, DST, ...
    GATEWAY, [SRC, DST, ... GATEWAY), because OOCalc may remove the dots as being thousands separators. Press
    Ctrl while clicking the columns, then select "Text", OK.
  • Lock the labels before scrolling. In Calc: select row 2 (i.e. one click on "2"), go to Window --> Freeze. Then unselect
    by clicking a cell somewhere.
  • To adjust column width, go to Edit --> Select All (just clicking in the little square above "1" and left to "A" does the
    same), then double click somewhere between two columns (the mouse pointer should be <=> shaped).
  • To sort: go to Edit --> Select All, go to Data --> Sort, and use "Range contains column labels" in the Options tab,
    and then go to the Sort Criteria tab.
  • To filter: go to Edit --> Select All, go to Data --> Filter --> AutoFilter, when asked for "...the first line to be used as
    column header?", press Yes. Now the first row has buttons; click and make a choice.
  • Save to the OOCalc .sxc format, because that's better than .csv from the stone age.


Special thanks to the poster(s) in this thread, for their contributions to iptables log viewing!

Cheers


Last edited by lost+found on Thu Oct 19, 2006 5:35 pm; edited 60 times in total
Back to top
View user's profile Send private message
Centinul
Apprentice
Apprentice


Joined: 28 Jul 2005
Posts: 232

PostPosted: Fri Nov 11, 2005 1:06 am    Post subject: Reply with quote

I just want to say that I like your script. It works extremely well. I've thought of a couple of more features that could be implemented if you want to. When I ran your script I happen to have more than 65535 entries, so I got an error from OOCalc saying that it was discarding the rest of my entries. I was wondering if some sort of check could be put in to see if it hits this maximum and then opens another file. Also, another thing I thought of is to eliminate the number of duplicates (i.e. same IP, same Destination Port) or something along those lines. Just my two cents!

Awesome script!
Back to top
View user's profile Send private message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Fri Nov 11, 2005 10:19 am    Post subject: Reply with quote

Thanks! And for your input too. I'm only able to test the script on a standalone pc, with some simple traffic... I couldn't find a script like this on the net, but I'm possibly reinventing the wheel. :)

Centinul wrote:
... When I ran your script I happen to have more than 65535 entries, so I got an error from OOCalc saying that it was discarding the rest of my entries. I was wondering if some sort of check could be put in to see if it hits this maximum and then opens another file. ...
There are some possibilities here, I guess.
- insert a sed address in the script like "1489, ... d", so sed will drop every line past this number. I think, if OOCalc means 65535 "cells", this is 65535/44=1489 lines max... I will put something in the script today, but I need some coffee first right now... :-)
- let grep do something like this (?, I'm no grep guru unfortunately...)
- log rotation, and don't grep "*", but grep log files just one by one. Metalog is doing rotation by default, but the app "logrotate" can be emerged too. The file size can be limited this way.
- use of a file splitter.

Centinul wrote:
... Also, another thing I thought of is to eliminate the number of duplicates (i.e. same IP, same Destination Port) or something along those lines. ...
Because sed is only line based, I think it can't do something like this. But there are more options, like:
- let grep filter these lines out (?)
- limit log output by the iptables limit module (Taken from this script: https://forums.gentoo.org/viewtopic-p-1463505.html#1463505)
Code:
# Do not complain if chain already exists (so restart is clean)
$IPT -N DROP1   2> /dev/null
$IPT -A DROP1 -m limit --limit 3/minute --limit-burst 10 -j LOG --log-prefix 'FIREWALL DROP BLOCKED:'
$IPT -A DROP1   -j DROP


(Some todo's: more than 3 tcp flags, ICMP other than type 3 and 8, and anything else not encountered yet.)
Back to top
View user's profile Send private message
Centinul
Apprentice
Apprentice


Joined: 28 Jul 2005
Posts: 232

PostPosted: Fri Nov 11, 2005 12:20 pm    Post subject: Reply with quote

I just want to apologize. I said 65535 cells when I meant to say ROWs.
Back to top
View user's profile Send private message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Sat Nov 12, 2005 12:02 pm    Post subject: Reply with quote

... Tried something that looks like file splitting. Somebody with a better idea, please post.

:?


Also log lines that go wrong are welcome. Just scramble MAC and ip addresses, like:

Nov 12 07:37:52 [kernel] FIREWALL DROP UNKNOWN:IN=ppp0 OUT= MAC= SRC=XXX.XXX.XXX.XXX DST=XXX.XXX.XXX.XXX LEN=64 TOS=0x00 PREC=0x00 TTL=53 ID=63454 PROTO=TCP SPT=80 DPT=55802 WINDOW=1716 RES=0x00 ACK URGP=0
Back to top
View user's profile Send private message
Centinul
Apprentice
Apprentice


Joined: 28 Jul 2005
Posts: 232

PostPosted: Mon Nov 14, 2005 1:23 am    Post subject: Reply with quote

lost+found. I haven't had a chance to look at the additions to your script yet, but I have an idea. I've always wanted to develop a script/app for a firewall that would parse logs and do some data analysis on them. Maybe you and I together can go into development on this and maybe make a nice firewall product. Let me know!
Back to top
View user's profile Send private message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Mon Nov 14, 2005 9:55 am    Post subject: Reply with quote

Centinul wrote:

Hi, Centinul

I like your idea. Something can be build around this script for some automation for instance, or a bigger app can just borrow something from the script.

I think the use of sed is a fast way to extract the data, don't know how accurate it can get though. TCP/UDP seems to work correctly now, but there are so much types of ICMP... I only saw types 3/code 1,3,4 and type 8 lately. Some types are maybe rare, but would show up in the wrong columns currently. When iptables changes, the script will be messed up. Maybe there are diffs in the way of logging in current versions of iptables too...?

For the app/script to develop, perhaps the data should go into a database or binary file for some speed. The sed script can be changed for that. Or maybe iptables could be patched to do it in real time.

I'm afraid my input wouldn't be that much of a help. I come from no computer background/study: zero knowledge of coding, firewall small small piece... :-) But I can help to try keep a script like this up to date, and improve it step by step. That's just like solving a puzzle.
Back to top
View user's profile Send private message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Sun Dec 04, 2005 4:49 pm    Post subject: Reply with quote

*bump*

:)


Any problems so far?
Back to top
View user's profile Send private message
Matteo Azzali
Retired Dev
Retired Dev


Joined: 23 Sep 2004
Posts: 1133

PostPosted: Tue Dec 13, 2005 5:15 pm    Post subject: Reply with quote

lost+found wrote:
... Tried something that looks like file splitting. Somebody with a better idea, please post.

:?


Well, I'm using logrotate to automatically split files if they bigger than 500k, this way they work fine.
_________________
Every day a new distro comes to birth. Every day a distro "eats" another.
If you're born distro, no matter what, start to run.
---- http://www.linuxprinting.org/ ---- http://tuxmobil.org/
Back to top
View user's profile Send private message
lost+found
Guru
Guru


Joined: 15 Nov 2004
Posts: 509
Location: North~Sea~Coa~s~~t~~~

PostPosted: Sat Sep 16, 2006 7:54 pm    Post subject: Reply with quote

Still workz, heh heh...
:)
Back to top
View user's profile Send private message
Centinul
Apprentice
Apprentice


Joined: 28 Jul 2005
Posts: 232

PostPosted: Wed Sep 20, 2006 10:43 am    Post subject: Reply with quote

lost+found:

I've spent some time lately developing a C daemon that parses log entries using Perl Compatible Regular Expressions. I don't pull all the information out like you do, only the pertinent information like: Month, Day, Time, Src IP, Dest IP, Src Port, Dest Port and Protocol (as well as ICMP Types). This daemon follows the log in REAL TIME and then (haven't implemented it yet) dump the data into a MySQL database. Just thought I'd let you know what I was up to :)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum