JC,
It seems that the colour setting is all done maunally, so it reduces the
automation
This is the sort of thing I had in mind. Get rid of your columns H-N, and
run this macro after adding the data
Sub SortData()
Dim sFormula As String
Dim sFormula1 As String
Dim cLastRow As Long
Dim i As Long
sFormula =
"=IF(A1="""","""",MID(C1,FIND("":"",C1)+1,FIND("", "",C1)-1-FIND("":"",C1)))"
sFormula1 = "=SUMPRODUCT(MID(H1,FIND(""~""," & _
"SUBSTITUTE("".""&H1&""."",""."",""~""," & _
"ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1))))," & _
"FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~"", " & _
"ROW(INDIRECT(""2:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+2))))-" & _
"FIND(""~"",SUBSTITUTE("".""&H1&""."",""."",""~"", " & _
"ROW(INDIRECT(""1:""&LEN(H1)-" & _
"LEN(SUBSTITUTE(H1,""."",""""))+1))))-1)*(1000^(LEN(H1)-" &
_
"LEN(SUBSTITUTE(H1,""."",""""))+1-" & _
"ROW(INDIRECT(""1:""&LEN(H1)-LEN(SUBSTITUTE(H1,""."",""""))+1)))))"
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(1, "H").Formula = sFormula
Cells(1, "I").Formula = sFormula1
Range("H1:I1").AutoFill Range("H1").Resize(cLastRow, 2)
Rows("1:" & cLastRow).Sort Key1:=Range("I1"), _
Order1:=xlAscending, _
Header:=xlNo
End Sub
Then run this macro after you have coloured the data
Sub ColourData()
Dim cLastRow As Long
Dim i As Long
cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
If Cells(i, "A").Font.ColorIndex = xlColorIndexAutomatic Then
Cells(i, "A").EntireRow.Font.ColorIndex = 48
End If
Next i
End Sub
--
HTH
RP
(remove nothere from the email address if mailing direct)
"JC" wrote in message
...
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
|