I have been using a simple macro for years that formats firewall logs for import into a database as delimited text. I recently upgraded the firewall (same company... new model) and they have made one annoying change to the log files. The added commas to separate port numbers in the comment field. I've been trying to reuse some of Mofi's great answers & samples but can' seem the get this part right.

I need to find and replace (with nothing) any commas that follow the phrase "TCP scanned port list". Example line:


06/06/2007 15:39:52.144 - Possible port scan dropped - 64.46.110.250, 80, WAN - 209.20.251.204, 5167, WAN - TCP scanned port list, 5085, 5143, 5143, 5143, 5143

would end up like this:

06/06/2007 15:39:52.144 - Possible port scan dropped - 64.46.110.250, 80, WAN - 209.20.251.204, 5167, WAN - TCP scanned port list 5085 5143 5143 5143 5143

So I need to lose the commas between the ports at the end but NOT after each IP number. From there I can reuse my existing macro code to format it the rest of the way.

It seems to me that the easiest way would be to search for "TCP scanned port list," and replace with "TCP scanned port list" ... or am I overlooking something else?

After thinking it over the easiest pattern to deal with is the IP and ports where you wish to keep commas. So replace the commas you want to keep with semicolon. Then replace/delete all unwanted commas and replace semicolon back to comma:

InsertMode
ColumnModeOff
HexOff
PerlReOn
Find RegExp "(\.\d{1,3}),( \d{1,4}),( WAN)"
Replace All "\1;\2;\3"
Find ","
Replace All ""
Find ";"
Replace All ","

and then continue with your original macro

This works perfectly on lines that contain scanned ports list but is zapping all the commas on lines that do not. Example:

06/02/2007 22:56:36.224 - UDP packet dropped - 66.129.65.54, 12069, WAN - 209.20.251.204, 33435, WAN - UDP Port: 33435

turns into:

06/02/2007 22:56:36.224 , UDP packet dropped , 66.129.65.54 12069 , 209.20.251.204 33435 , UDP Port: 33435

Note we lost the commas between IP & port number. I need to retain those.

Here is the whole macro so far:

InsertMode
ColumnModeOff
HexOff
PerlReOn
TabsToSpaces
Find RegExp "(\.\d{1,3}),( \d{1,4}),( WAN)"
Replace All "\1;\2;\3"
Find ","
Replace All ""
Find ";"
Replace All ","
Find "WAN"
Replace All ""
Find "LAN (admin)"
Replace All ""
Find "LAN"
Replace All ""
Find "'"
Replace All ""
Find "-"
Replace All ","
Find ", ,"
Replace All ","
Find "[5 space chars]"
Replace All " "
Find "[4 space chars]"
Replace All " "
Find "[3 space chars]"
Replace All " "
Find "[2 space chars]"
Replace All " "
Find " , "
Replace All ","
Save

(added "[X space chars]" as the number of empty spaces does not show correctly in HTML)

Just increase the number of digits matched in front of WAN:

Find RegExp "(\.\d{1,3}),( \d{1,5}),( WAN)"
Replace All "\1;\2;\3"

Thank you for your help. I think I can tweak it from here. Now to tackle that mountain of log files that have accumulated.