On Mon, 21 Feb 2005 09:33:59 -0000, "Bob Phillips"
wrote:
JC,
It is difficult to be precise without the data, what is in A-G, and H-N
exactly, but you could do it all by macro, that is set the various colours
for violations as well as the grey for non-violation. You could even sort on
this later to reduce the amount of time that the macro takes each day.
Furthermore, you could do away with the helper columns as you could build
these dynamically, and then remove them.
What I don't fully get, is why do you only colour multiple entries, and how
you determine the spam etc.
Hi Bob
The columns and sample data are as follows:-
Col A Date & time 2005/01/14 21:24:53.480 -
Col B Action taken UDP packet dropped -
Col C Source IP address, port Source:w.x.y.z, port, WAN -
Col D Destination IP address, port Destination:w.x.y.z, port, WAN -
where w, x, y and z are 1, 2 or 3 digit numbers. There are another 2
columns E and F which are as follows:-
Col E may contain Ping, SSH, or the port number
Col F firewall rule invoked
The data in columns A to F come from the firewall log.
Column G I add details of ISP and IP range when I get multiple entries from the
one IP address or address range.
Col J = IF($A1="","",FIND(":",$C1))
Col K = IF($A1="","",FIND(".",$C1))
Col L = IF($A1="","",FIND(".",$C1,$K1+1))
Col M = IF($A1="","",FIND(".",$C1,$L1+1))
Col N = IF($A1="","",FIND(",",$C1))
From these helper columns H and I are calculated as follows:
Col H =IF($A1="","",1000*MID($C1,$J1+1, $K1-$J1-1) +MID($C1,$K1+1, $L1-$K1-1))
Col I =IF($A1="","",1000*MID($C1,$L1+1, $M1-$L1-1) +MID($C1,$M1+1, $N1-$M1-1))
Columns H, I and A are used in the original sort based on IP address. I have
inserted Columns H to N out to row 4,000 to ensure that they exist for the
current expected maximum number of entries in the month.
The firewall sends me email alert messages if it detects thing like port scans
or similar - these I manually colour magenta.
I check for multiple entries from a single IP address or ISP IP address range
and manually colour the characters blue if Ping is in Col E and red for all
other types. The firewall is configured to drop all packets coming from the
WAN that were not initiated by an action on the LAN. I treat anything that I
have not initiated as hostile.
--
Cheers . . . JC
|