Find and Replace Only Specific Commas

Find and Replace Only Specific Commas

3
NewbieNewbie
3

    Jun 16, 2007#1

    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.

    61
    Advanced UserAdvanced User
    61

      Jun 16, 2007#2

      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?

        Jun 16, 2007#3

        Hmmm ... NOW I see what I missed! (For some reason, I can edit my own posts, but I can't delete them ... hmmm ...)

        Let me get back to you on this. You will definitely need a regular expression or two, that's for sure!

        262
        MasterMaster
        262

          Jun 17, 2007#4

          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

          3
          NewbieNewbie
          3

            Jun 17, 2007#5

            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)

            262
            MasterMaster
            262

              Jun 17, 2007#6

              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"

              3
              NewbieNewbie
              3

                Jun 18, 2007#7

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