Help with strings

[nanscombe]

As promised.

This database functions in a similar manner to the previous one.

However, this version will store unique, anonymized, examples of any message types it comes across.

Once we have a bigger cross-section of the message types it will be easier to deal with them.

Have a play, watch the message types mount up and let me know how you do.

 

[nanscombe]

When it comes to writing a better extraction function I have finally (after many years of failing to pass in User Defined data types) realised how you pass a variant type into a function and return an array of data.

That should make life a bit tidier.

Public Sub testSplitMessage()
dim varResultArray as variant
splitMessage ("The Message", varResultArray)
End Sub

Public Function splitMessage(byval theMessage, byRef theResultArray as variant)
ReDim theResultArray(9) as string

...
...
End Function

Instead of -

Public Sub testSplitMessage()
dim F0 as string, F1 as string ...
splitMessage ("The Message", F0,F1,F2,F3,F4,F5,F6,F7,F8)
End Sub

Public Function splitMessage(byval theMessage, byRef F0 as string, byRef F1 as string ...)

...
...
End Function

A lot tidier than passing in 9 separate variables.

Ah well. Off to do a bit of shopping now.

 

[New2VB]

@Nigel - did you sleep last night? That's a lot of code. Thank you. I 'll work my way through it
@vbaInet - they are firewall/ids logs (good guess Nigel). I have attached a txt file containing 6 copy-and-paste examples taken directly from the emails and a description of what I am trying to do.

Hope this helps.

 

[nanscombe]

This has been a challenging little project.

Here is a new version with a streamlined message splitting function. It now takes a string parameter (to hold the message) and a variant (to pass back an array of resulting data). This is something I have tried and failed to do for years.

I have also defined a series of constants to allow names to be used to access the array data rather than meaningless numbers, ie 'mSpilt00_Received' rather than '0'.

I have also modified the form to build the result string from the individual array items, this also demonstrates how you would use the data to write it away into a table.

Have a look around and see what you think.

 

[nanscombe]

It sometimes pays to step away from a problem for a while.

The field which contains src: xxx.xxx.xxx.xxx dst: yyy.yyy.yyy.yyy has been complicating things and meaning I have write some complicated code to extract the destination.

However, after a nights sleep, I have a different solution. Replace "dst:" with " - dst:" in the initial string.

The destination is then pushed into the next array item so it's the same as all the others.

This should tidy things up quite a bit.

Streamlined version coming soon ....

 

[nanscombe]

You can ignore New2VB_1_00_004.zip as New2VB_1_00_005.zip is here.

A much streamlined version of the extraction code together with an example of writing the info into a table, well an Access one anyway.

Hope it looks good.

 

[New2VB]

Awesomeness surrounds you...

I think we may have overcomplicated the issue a bit, thus I have taken a step back and, thanks to an idea I got from your code, stripped the 'functions' down to the basics.

My version is nowhere near as sexy as yours but it has survived several rounds of testing and is holding up so far....

No doubt it needs improving and revising but for now...

Dim Fields, varArrayEntry, cma, cmaArray, count As Variant
Dim tempReason, tempIP, tempDest  As String
Dim cma0, cma1, cma2, cma3 As Integer

Fields = Split(strMessage, " - ")
cma = Split(Fields(4), ",")
cma0 = cma(0)
cma1 = cma(1)
cma2 = cma(2)
cma3 = cma(3)
 
Dim db1 As Database, tb1 As DAO.Recordset
Set db1 = CurrentDb
Set tb1 = db1.OpenRecordset("dbo_Alerts", dbOpenDynaset, dbSeeChanges)
For Each varArrayEntry In Fields
    tb1.AddNew
    tb1!swReceived = Trim$(Fields(0))
    tb1!swNote = Trim$(Fields(1))
    tb1!swSender = mymail.SenderName
    tb1!swCategory = Trim$(Fields(2))
        
    If InStr(1, Fields(4), "FIN") Then
    tempReason = Mid(Trim$(Fields(3)), 2) & " " & cma0
    tb1!swReason = Mid([tempReason], 1, Len([tempReason]) - 3)
    Else
    tb1!swReason = Mid(Trim$(Fields(3)), 2)
    End If
    
    If InStr(1, cma0, "src") Then
    tempIP = Mid(Replace(Left(cma0, 18), "src:", ""), 2)
    tb1!swSourceIP = tempIP
    Else
    tb1!swSourceIP = Mid(Left(Trim$(Fields(4)), InStr(1, (Fields(4)), ",", vbTextCompare) - 1), 2)
    End If
        
    If Len(cma3) = 0 Then cma3 = Mid(Trim$(cma0), 2)
    tb1!swSourceName = cma3
    
    If InStr(1, Fields(4), "has ceased") Then
    tb1!swSourceName = ""
    End If
    
    If InStr(1, cma0, "src") Then
    Dim swSrc As Variant
    Dim srcip0, srcip1, srcip2 As Integer
    src = Left(cma0, 22)
    swSrc = Split(src, ":")
    srcip0 = swSrc(0)
    srcip1 = swSrc(1)
    srcip2 = swSrc(2)
    tb1!swSourceName = srcip1
    Else
    
    End If
        
    If InStr(1, cma0, "dst") Then
    Dim dst As Variant
    Dim dstip0, dstip11, dstip2 As Integer
    src = Right(cma0, 22)
    dst = Split(src, ":")
    dstip0 = dst(0)
    dstip1 = dst(1)
    dstip2 = dst(2)
    tb1!swDestination = dstip0
    Else
    tb1!swDestination = Mid(Trim$(Fields(5)), 2)
    End If
        
    tb1!swOptions = Mid(Trim$(Fields(6)), 2)
    tb1.Update
    tb1.Close
    db1.Close
Next varArrayEntry
mymail.UnRead = False

 

[nanscombe]

That's all right. As long as it's been helpful.

I haven't done much in the way of serious VBA coding since the end of 2006, when I was made redundant.

I've come here looking to find some ideas to get me back in the swing of things, and give others a hand in the process.

.... And I've learnt a few new things as well.

Anyhow, good luck with the rest of it. If you get stuck, you know where we are.

 

[vbaInet]

... you know where we are.

I do... in Kent

 

[New2VB]

@Nigel
It has been ENORMOUSLY helpful (caps for emphasis, not for shouting). Thank you.

 

[nanscombe]

You're welcome.

It's been good to work with someone again, if only virtualy.