Pick specific portions and make new lines

[nightdreamer]

Hello all.
I am a network engineer and have been assigned to look through thousands of lines of logging files and list active Communications. Unfortunately I know nothing from programming. I would be thankful if you could help me.

Here is a sample of the logs format:

access-list OUT permitted tcp OUT/172.19.49.18(58032) -> inside/172.18.47.38(10501) hit-cnt 1 first hit [0x16388a5d, 0x0]
access-list OUT permitted tcp OUT/172.25.33.61(58799) -> inside/172.18.31.8(88) hit-cnt 1 first hit [0x16388a5d, 0x0]
access-list OUT permitted tcp OUT/172.25.33.61(58800) -> inside/172.18.31.8(88) hit-cnt 1 first hit [0x16388a5d, 0x0]

Each Line is included in one cell.
What i would like to do is:


1) Find the Lines with the same source IPs and keep only one of them
e.g for the last two Lines the source IP is the same (172.25.33.61) but different source port is used (58799 and 58800). I want to keep only one of them.

And then:

2) transform each Line in the following manner:

e.g for the last Line:
host 172.25.33.61 host 172.18.31.8 eq 88



Thank you

[Bob Phillips]

Public Sub TidyData()
Dim rng As Range
Dim lastrow As Long
Dim i As Long
    
    Application.ScreenUpdating = False

    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B1").Resize(lastrow).Formula = "=MID(A1,FIND(""/"",A1)+1,FIND(""("",A1)-FIND(""/"",A1)-1)"
        .Range("C1").Resize(lastrow).Formula = "=COUNTIF(B1:B" & lastrow & ",B1)"
        .Rows(1).Insert
        .Range("C1").Value = "tmp"
        .Columns(3).AutoFilter field:=1, Criteria1:=">1"
        On Error Resume Next
        Set rng = .Range("C2").Resize(lastrow - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then rng.EntireRow.Delete
        .Columns("B:C").Delete
        .Rows(1).Delete
        
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B1").Resize(lastrow).Formula = "=SUBSTITUTE(SUBSTITUTE(A1,""access-list OUT permitted tcp OUT/"",""""),"" -> inside"","""")"
        .Range("C1").Resize(lastrow).Formula = "=REPLACE(B1,FIND(""("",B1),FIND("")"",B1)-FIND(""("",B1)+1,"""")"
        With .Range("D1").Resize(lastrow)
        
            .Formula = "=""host ""&SUBSTITUTE(SUBSTITUTE(REPLACE(C1,FIND("")"",C1),LEN(C1)-FIND("")"",C1)+1,""""),""/"", "" host ""),""("","" eq "")"
            .Value = .Value
        End With
        .Columns("A:C").Delete
    End With
    
    Application.ScreenUpdating = True
End Sub

[nightdreamer]

Thank you so much!
There is one thing though which I did not make clear.

I want to find the same lines where the only variable is the source port and keep only one of them.
E.g

access-list OUT permitted tcp OUT/172.25.33.61(58799) -> inside/172.18.31.8(88) hit-cnt 1 first hit [0x16388a5d, 0x0]
access-list OUT permitted tcp OUT/172.25.33.61(58800) -> inside/172.18.31.8(88) hit-cnt 1 first hit [0x16388a5d, 0x0]
access-list OUT permitted tcp OUT/172.25.33.61(58800) -> inside/172.18.31.8(89) hit-cnt 1 first hit [0x16388a5d, 0x0]
access-list OUT permitted tcp OUT/172.25.33.61(5881) -> inside/172.18.31.8(89) hit-cnt 1 first hit [0x16388a5d, 0x0]

Should remain one of the first two AND one of the other two
and have in the end:

host 172.25.33.61 host 172.18.31.8 eq 88
host 172.25.33.61 host 172.18.31.8 eq 89

Thanks!!

[Bob Phillips]

Public Sub TidyData()
Dim rng As Range
Dim lastrow As Long
Dim i As Long
    
    Application.ScreenUpdating = False

    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("B1").Resize(lastrow).Formula = "=SUBSTITUTE(SUBSTITUTE(A1,""access-list OUT permitted tcp OUT/"",""""),"" -> inside"","""")"
        .Range("C1").Resize(lastrow).Formula = "=REPLACE(B1,FIND(""("",B1),FIND("")"",B1)-FIND(""("",B1)+1,"""")"
        With .Range("D1").Resize(lastrow)
        
            .Formula = "=""host ""&SUBSTITUTE(SUBSTITUTE(REPLACE(C1,FIND("")"",C1),LEN(C1)-FIND("")"",C1)+1,""""),""/"", "" host ""),""("","" eq "")"
            .Value = .Value
        End With
        .Range("E1").Resize(lastrow).Formula = "=COUNTIF(D1:D" & lastrow & ",D1)"
        .Rows(1).Insert
        .Range("E1").Value = "tmp"
        .Columns("E").AutoFilter field:=1, Criteria1:=">1"
        On Error Resume Next
        Set rng = .Range("E2").Resize(lastrow - 1).SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not rng Is Nothing Then rng.EntireRow.Delete
        .Rows(1).Delete
        .Columns("E").Delete
        .Columns("D").AutoFit
        .Columns("A:C").Delete
    End With
    
    Application.ScreenUpdating = True
End Sub

[nightdreamer]

Thank you very much. It works!
The problem is....i have many thousands of rows and when i run the macro, excel hangs and finally i get a "not enough memory" error.
This happens with a 3.5GB RAM (32bit) system.
I will try tomorrow with a 8GB one.

[snb]

or:
adapt: Sheet2. (if necessary) into sheets("sheetname"). or ActiveSheet.
results will be shown in column C

Sub M_snb()
    sn = Sheet2.Columns(1).SpecialCells(2)
 
   With CreateObject("scripting.dictionary")
        For j = 1 To UBound(sn)
           x0 = .Item("host " & Split(Mid(sn(j, 1), 35), "(")(0) & " host " & Replace(Split(Split(sn(j, 1), "inside/")(1), ")")(0), "(", " eq "))
        Next
        Sheet2.Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
    End With
End Sub

[nightdreamer]

I am sorry but I do not understand what you mean. Could you please post the entire code?
Is this maybe a Logic that requires less resources?

Thanks!

[snb]

This is the entire code: it suffices.

The only thing you might have to adapt is the sheetname (as I indicated in my post)
If you post a sample file I can add the code to it.
And I certainly know the code is fast & lean.

[nightdreamer]

ok i get it.
Could you please change the code to work for data of this form:

DCN/172.25.33.61(58799) -> inside/172.18.31.8(88) hit-cnt 1 first hit [0x16388a5d, 0x0]

The destination IP (172.18.31.8) could also be a name (e.g COMPUTER). So in this case we should have:


host 172.25.33.61 host COMPUTER eq 88


Thank you!!

[snb]

Please post a sample file that contains all possible strings.

Unless this suffices:

Sub M_snb()
    sn = Sheet2.Columns(1).SpecialCells(2)
     
    With CreateObject("scripting.dictionary")
        For j = 1 To UBound(sn)
            x0 = .Item("host " & Split(Mid(c00, InStr(c00, "/") + 1), "(")(0) & " host " & Replace(Split(Mid(c00, InStr(InStr(c00, "/") + 1, c00, "/") + 1), ")")(0), "(", " eq "))
        Next
        Sheet2.Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
    End With
End Sub

[nightdreamer]

Unfortunately it does not work.
I have uploaded a sample.

I really appreciate your help! Both of you!

[snb]

see the attachment

data will be written in sheet2

[Bob Phillips]

see the attachment

data will be written in sheet2

And note that line 5 doesn't get converted.

[snb]

Because it is identical to line 1.

[Bob Phillips]

The output maybe, but the input isn't. And did he say he doesn't want identical lines?

[nightdreamer]

Hello,I do not understand the code (btw I would be very happy to learn the Logic behind it) but the result for this sample seems OK.

Basically, the Lines that have different source port (and all else the same), I consider them the same, and I would like only one to remain. Thus, the final form I want does not contain it anyways.

[snb]

@xld

You might have a reread of http://www.vbaexpress.com/forum/show...l=1#post316205 point 1)

[snb]

@nightdreamer

If you are interested in Dictionaries have a look over here:

http://www.snb-vba.eu/VBA_Dictionary_en.html

[nightdreamer]

Thanks for the link. I am completely clueless regarding programming...so it would be nice if you would explain me in plain english what the code does. This is more than enough for me


Thanks both of you for your help! Both codes are working! The second one indeed is very fast and light even with a million rows.
It is really magnificent what coding can do and make our lifes easier! I wish i had time to learn some things...it would help me a lot in my job (network admin)

[snb]

Sub M_snb()
'    put all values in column A of sheet2 into an array; put that array into variable 'sn'
    sn = Sheet2.Columns(1).SpecialCells(2)

' create a new object (Dictionary) that only can contain unique keys.
' we will add  new keys based on the information in the variable sn, that contains an array with all values in column1 in sheet2.
' a key identical to an existing one will be ignored.
' the dictionary object stores a list of unique keys in the property .keys, that is a 1-dimensional array
' since you can't write a 1-dimensional array to a column in a worksheet, you will have to convert it to a 2-dimensiional one first; using the Excel function 'Transpose'.

    With CreateObject("scripting.dictionary")       '  create a new dictionary
        For j = 1 To UBound(sn)                         '  create indexnumbers to get access to every item in the array
            x0 = .Item("host " & Split(Mid(sn(j, 1), InStr(sn(j, 1), "/") + 1), "(")(0) & " host " & Replace(Split(Mid(sn(j, 1), InStr(InStr(sn(j, 1), "/") + 1, sn(j, 1), "/") + 1), ")")(0), "(", " eq "))
            
            ' the string in every item of aray sn: sn(1,1), sn(2,1), sn(3,1), sn(4,1)... etc. will be
            ' - truncated after the first occurrence of the forward slash "/":  Mid(sn(j, 1), InStr(sn(j, 1))
            ' - the remaining string at the right side is being split by the character "("  : split(....,"(")
            ' - the first portion of the splitting (0) is being taken: it is the first IP-address
            ' - before this IP_address we simply put the string "host "; and after it we do the same: & " host "
            
            ' - now we want to truncate the string at the second occurrence of the forward slash in the string.
            ' - we do that by indicating the forward slash has to be sought after where the first forwardslash has been found: InStr(InStr(sn(j, 1), "/") + 1, sn(j, 1), "/") + 1)
            ' - we truncate the string after the second occurrence of the forwardslash: Mid(sn(j,1), ...)
            ' - the remaining string at the right side is being split by the character ")": split( ... ,")")
            ' - the first portion of the splitting (0) is being taken: it is the second IP-address, containing an "(" to indicate the port and the portnumber
            ' - the character "(" will be replaced by the string " eq ": replace( ..., "("," eq ")
            ' - this result will be added to the first IP-address to constitute a key that can be added to the dictionary
            
            ' - we add a key to a dictionary using the method x0 (that is arbirary) = .item( "keystring" )
            ' - only unique keys will be accepted by the dictionary: so the list of keys always consists of unique keys
        Next
        
        Sheet2.Cells(1, 3).Resize(.Count) = Application.Transpose(.keys) 
            '  - take cell C1 in sheet2 and enlarge its range in column C so it can contain all keys (.count) in the Dictionary
            '  - fill that range after converting the 1-dimensional array .keys into a 2-dimensional array with 'transpose'.
    End With
End Sub

pfffff