PDA

View Full Version : Pick specific portions and make new lines



nightdreamer
10-06-2014, 06:41 AM
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
10-06-2014, 08:00 AM
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
10-06-2014, 09:46 AM
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
10-06-2014, 02:49 PM
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
10-07-2014, 11:53 AM
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
10-07-2014, 12:17 PM
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
10-08-2014, 12:15 AM
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
10-08-2014, 12:46 AM
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
10-08-2014, 02:19 AM
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
10-08-2014, 02:54 AM
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
10-08-2014, 04:52 AM
Unfortunately it does not work.
I have uploaded a sample.

I really appreciate your help! Both of you!

snb
10-08-2014, 06:10 AM
see the attachment

data will be written in sheet2

Bob Phillips
10-09-2014, 12:10 AM
see the attachment

data will be written in sheet2

And note that line 5 doesn't get converted.

snb
10-09-2014, 02:03 AM
Because it is identical to line 1.

Bob Phillips
10-09-2014, 04:36 AM
The output maybe, but the input isn't. And did he say he doesn't want identical lines?

nightdreamer
10-09-2014, 05:34 AM
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
10-09-2014, 05:56 AM
@xld

You might have a reread of http://www.vbaexpress.com/forum/showthread.php?50919-Pick-specific-portions-and-make-new-lines&p=316205&viewfull=1#post316205 point 1)

snb
10-09-2014, 05:57 AM
@nightdreamer

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

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

nightdreamer
10-09-2014, 06:25 AM
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
10-09-2014, 07:36 AM
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

nightdreamer
10-13-2014, 09:08 AM
I get it, thank you very much!
One last thing since i am sure it will be very easy for you.
Could you please make another (separate) macro where it will take the data from sheet2 (the one generated from the 1st macro) and write in sheet3 in this manner:
3 columns:
Source IP address
Destination IP address
Destination Port

Please use the attachment you uploaded! Thanks!