Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Setting character colour in a macro
I use a spreadsheet to analyse the firewall logs for the month to which is added
100-120 data entries per day. I add the data for the current day and then run a macro which sorts the data based on IP address. I read through the spreadsheet looking for multiple entries coming from an IP address and colour the characters where this occurs red for spam, magenta for port probes etc and blue for pings. Once that has been completed I run a second macro, which sorts the data back into date order, and then save the spreadsheet. I occurred to me that I could save myself quite a bit of checking time if the second macro could change the characters to grey in all entries where they have not already been made red, magenta or blue. The process would check the current colour and change it to grey if it was black. Since the characters for all new entries added that day would be black this would reduce my checking down to around 100-120 each day instead of up to 3,500 or so towards the end of the month. As the month progresses some of those entries previously made grey may need to be manually changed to red, magenta or blue if another entry or entries from that IP address occurs in the new entries added that day. To complicate matters I have 4,000 rows of helper columns H to N to enable the initial sort based on IP address. The data added each day only occupies columns A to G and only characters in these columns should be made grey. So the range selected should only be those rows which contain data in columns A to G. It is too fiddly to do this manually each day but it would be great if a macro could automate the process. Is this possible? Any ideas on how to do this? -- Cheers . . . JC |
#2
|
|||
|
|||
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. -- HTH RP (remove nothere from the email address if mailing direct) "JC" wrote in message ... I use a spreadsheet to analyse the firewall logs for the month to which is added 100-120 data entries per day. I add the data for the current day and then run a macro which sorts the data based on IP address. I read through the spreadsheet looking for multiple entries coming from an IP address and colour the characters where this occurs red for spam, magenta for port probes etc and blue for pings. Once that has been completed I run a second macro, which sorts the data back into date order, and then save the spreadsheet. I occurred to me that I could save myself quite a bit of checking time if the second macro could change the characters to grey in all entries where they have not already been made red, magenta or blue. The process would check the current colour and change it to grey if it was black. Since the characters for all new entries added that day would be black this would reduce my checking down to around 100-120 each day instead of up to 3,500 or so towards the end of the month. As the month progresses some of those entries previously made grey may need to be manually changed to red, magenta or blue if another entry or entries from that IP address occurs in the new entries added that day. To complicate matters I have 4,000 rows of helper columns H to N to enable the initial sort based on IP address. The data added each day only occupies columns A to G and only characters in these columns should be made grey. So the range selected should only be those rows which contain data in columns A to G. It is too fiddly to do this manually each day but it would be great if a macro could automate the process. Is this possible? Any ideas on how to do this? -- Cheers . . . JC |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
On Mon, 21 Feb 2005 22:37:16 -0000, "Bob Phillips"
wrote: 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 Hi Bob, Many thanks for your help. I have left in the helper columns at this stage and have added the ColourData macro lines into my DateSort macro and this works beautifully. I played around in a test spreadsheet before committing to the one I use each day and the new day's data stands out like the proverbial. This should cut the checking time by a large factor. I don't understand the logic in the Sub SortData. I will be near a bookstore tomorrow morning and will look for a book on Excel macros and try to figure out what you are doing. In the mean time, I will make do with the ColourData macro code. I have coded in Fortran, Basic and QuickBasic before so it shouldn't be too steep a learning curve. Once again, thanks for your help. -- Cheers . . . JC |
#6
|
|||
|
|||
"JC" wrote in message ... On Mon, 21 Feb 2005 22:37:16 -0000, "Bob Phillips" wrote: 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 snip Hi Bob, Many thanks for your help. I have left in the helper columns at this stage and have added the ColourData macro lines into my DateSort macro and this works beautifully. I played around in a test spreadsheet before committing to the one I use each day and the new day's data stands out like the proverbial. Aah, the benefit of age, wiseness and experience. Sensible move. This should cut the checking time by a large factor. Which is what it is all about. I don't understand the logic in the Sub SortData. I will be near a bookstore tomorrow morning and will look for a book on Excel macros and try to figure out what you are doing. The trick in this one is not really in the macro. All the macro does is create 2 helper columns where you created 8, to get an IP address that can be sorted. In your spreadsheet, if say the IP address was 1.2.3.4, you ended up with 1002 in H, and 3004 in I, and presumably you sort on those 2 columns. I end up with 1002003004 in one column and sort on that. So all the macro does is insert a formula in H (That isoltaes the IP address from column C) and in I (which makes a number from the IP address), and copy down to all rows, then sorts on column I. The macro is very simple, it sets the formulae and sorts the data. In the mean time, I will make do with the ColourData macro code. I have coded in Fortran, Basic and QuickBasic before so it shouldn't be too steep a learning curve. As I said, I think the code is so simple you should be able to understand it now. The trick is in the formula that I create, which, with all respect, unless you are into complex worksheet formulae, you are not going to understand. Just try on a copy workbook and see if it works, if it does, lie back and enjoy :-) I would still like to know the rules for colouring the data, to automate that. :-) Bob |
#7
|
|||
|
|||
On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips"
wrote: "JC" wrote in message ... I don't understand the logic in the Sub SortData. I will be near a bookstore tomorrow morning and will look for a book on Excel macros and try to figure out what you are doing. The trick in this one is not really in the macro. All the macro does is create 2 helper columns where you created 8, to get an IP address that can be sorted. In your spreadsheet, if say the IP address was 1.2.3.4, you ended up with 1002 in H, and 3004 in I, and presumably you sort on those 2 columns. I end up with 1002003004 in one column and sort on that. So all the macro does is insert a formula in H (That isoltaes the IP address from column C) and in I (which makes a number from the IP address), and copy down to all rows, then sorts on column I. The macro is very simple, it sets the formulae and sorts the data. I haven't had time to test this code out yet. I've grabbed a couple of books on Excel VBA coding and will settle down with them for a while to get an understanding on macros. In the mean time, I will make do with the ColourData macro code. I have coded in Fortran, Basic and QuickBasic before so it shouldn't be too steep a learning curve. As I said, I think the code is so simple you should be able to understand it now. The trick is in the formula that I create, which, with all respect, unless you are into complex worksheet formulae, you are not going to understand. Just try on a copy workbook and see if it works, if it does, lie back and enjoy :-) I would still like to know the rules for colouring the data, to automate that. :-) Bob The process is simple and difficult at the same time. The simple part is to look for multiple entries with the same source IP address. What makes it more difficult is that the end result I want is really based on ISP address ranges. ISPs have large address ranges. For example ISP X may have an IP address range from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have multiple IP address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255. It would be easy to program a macro to colour multiple entries from a single IP address but not so easy to cater for ISP address ranges. It requires knowledge of the entire range of IP address allocations probably in some form of lookup table and an updating process to keep it current. I check with APNIC etc when the number of entries exceeds 3 to get the ISP details including address range. This I put into column G and may result in me uncovering more entries coming from that ISP's address range. I have listed the most common offending ISPs in a workbook but this is only a tiny proportion of the entire list. I send out emails to the ISP if more than 3 entries occur in the month. If more entries appear on following days I send out additional emails listing all entries to date this month to reinforce my original request that the spam etc being sent be stopped. Detecting these additional entries would be more difficult if the macro automated the colouring process. This process results in about 20% of the entries being coloured with the rest remaining grey. I realise that the system I am using is inefficient and does not detect all cases of multiple entries coming from an ISPs address range. I hope this helps you understand the process. If you would like to see a copy of the current spreadsheet I could email it to you. -- Cheers . . . JC |
#8
|
|||
|
|||
"JC" wrote in message ... On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips" wrote: The process is simple and difficult at the same time. The simple part is to look for multiple entries with the same source IP address. What makes it more difficult is that the end result I want is really based on ISP address ranges. ISPs have large address ranges. For example ISP X may have an IP address range from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have multiple IP address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255. That partly explains the 2 groups of ISP components, I guess it makes that checking easier. It would be easy to program a macro to colour multiple entries from a single IP address but not so easy to cater for ISP address ranges. It requires knowledge of the entire range of IP address allocations probably in some form of lookup table and an updating process to keep it current. I check with APNIC etc when the number of entries exceeds 3 to get the ISP details including address range. This I put into column G and may result in me uncovering more entries coming from that ISP's address range. I have listed the most common offending ISPs in a workbook but this is only a tiny proportion of the entire list. But it would be easier would it not to maintain a list on a separate spreadsheet and write code that checks that database rather than do it manually. Maintaining that list is a simple typing exercise. I send out emails to the ISP if more than 3 entries occur in the month. If more entries appear on following days I send out additional emails listing all entries to date this month to reinforce my original request that the spam etc being sent be stopped. Detecting these additional entries would be more difficult if the macro automated the colouring process. This process results in about 20% of the entries being coloured with the rest remaining grey. I realise that the system I am using is inefficient and does not detect all cases of multiple entries coming from an ISPs address range. It seems incredibly tedious to me. I understand it might be necessary, but I am glad it is not me doing it. I hope this helps you understand the process. If you would like to see a copy of the current spreadsheet I could email it to you. If you think there is further help I could offer, I would be happy to. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
show macro security setting in a cell | Excel Worksheet Functions | |||
how can excel colour a cell when it has a specified character | Excel Discussion (Misc queries) | |||
Setting macro security level to "low" permanently? | Excel Discussion (Misc queries) | |||
Macro button colour change??? | Excel Worksheet Functions | |||
Macro to sort by cell colour | Excel Worksheet Functions |