Help with strings

using:-

Code: 
tb1!swSourceName = Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3))
and getting a "type mismatch" error - just trying to work out why, suspect that the array result is not a string whereas the db value is set to nvarchar
 
New2VB said:
using:-

Code: 
tb1!swSourceName = Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3))
and getting a "type mismatch" error - just trying to work out why, suspect that the array result is not a string whereas the db value is set to nvarchar
Click to expand...

To make sure it returns a string you could try -

Code: 
tb1!swSourceName = Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3)) & ""

We'll get there. :o
 
nanscombe said:
We'll get there. :o
Click to expand...

Oh I hope so, losing the will to live....:)

No, still "type mismatch" - reading about referring to an array index though
 
Not sure that that's the problem. Reading some stuff about array variants, variants of arrays & variant arrays of variants. Brain hurts....

Oh for C#'s Convert.ToString....
 
I think it will be beneficial if you upload a db with some sample data and the form, queries and tables in concern (with some sample data) so that nanscombe can see what you are doing and be able to better troubleshoot.
 
@vbaInet - bit of a problem doing that, the backend is a substantial SQL db containing confidential information.
My code however:-
Code: 
Dim Fields, varArrayEntry, cma, cmaArray As Variant
Dim strMsg, strswSource As String
    Fields = Split(strMessage, " - ")
    cma = Split(Fields(4), " ")
    Dim db1 As Database, tb1 As DAO.Recordset
    Set db1 = CurrentDb
    Set tb1 = db1.OpenRecordset("dbo_stuff", 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))
        tb1!swReason = Mid(Trim$(Fields(3)), 2)
        strswSource = Mid(Left(Trim$(Fields(4)), InStr(1, (Fields(4)), ",", vbTextCompare) - 1), 2)
        tb1!swSourceIP = strswSource
            For Each cmaArray In cma
            tb1!swSourceName = (Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3)))
            Next cmaArray
        tb1!swDestination = Mid(Trim$(Fields(5)), 2)
        tb1!swOptions = Mid(Trim$(Fields(6)), 2)
        tb1.Update
        tb1.Close
        db1.Close
    Next varArrayEntry
 
I think there was a bit of unnecessary Array code in there.

Code: 
Dim Fields, varArrayEntry, cma, cmaArray As Variant
Dim strMsg, strswSource As String
    Fields = Split(strMessage, " - ")
    Dim db1 As Database, tb1 As DAO.Recordset
    Set db1 = CurrentDb
    Set tb1 = db1.OpenRecordset("dbo_stuff", 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))
        tb1!swReason = Mid(Trim$(Fields(3)), 2)
        strswSource = Mid(Left(Trim$(Fields(4)), InStr(1, (Fields(4)), ",", vbTextCompare) - 1), 2)
        tb1!swSourceIP = strswSource

' Assuming it is that sort of line -> 192.168.x.y, aa, bb, cc 
        [B]cmaArray = Split(Fields(4), ",")[/B]
        [B]tb1!swSourceName = (Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3)))[/B]

        tb1!swDestination = Mid(Trim$(Fields(5)), 2)
        tb1!swOptions = Mid(Trim$(Fields(6)), 2)
        tb1.Update
        tb1.Close
        db1.Close
    Next varArrayEntry
 
Last edited:
Example strings:
12:14:56 - bob - logon - bobspc - 192.168.0.1 - 192.168.0.2 - thisserver - 1,2,3,4,5
12:15:11 - harry - logoff - harryspc - 1.12.13.15 - 192.168.0.5 - theotherserver - 1,8,11,13,100
so, the only thing that the strings have in common is that the substrings are "delimited" by hyphens (-).
Click to expand...

your statement is not correct - the strings have a common format, time, user, action etc etc

it should be quite straightforward to process a log file like this
 
@Nigel - looks better but still not working
@gemma-the-husky, the source is a text email. Some examples:-

11/20/2011 23:24:53.224 - Alert - Intrusion Prevention - Probable port scan detected - aa.aa.aa.aa, bb, cc - mm.mm.mm.mm,nnnn,pp name.domain.extension - TCP scanned port list, 4352, 4334, 4346, 4320, 4316, 4328, 4338, 4312, 4326, 4368


11/21/2011 00:50:55.400 - Alert - Intrusion Prevention - Probable port scan detected - aa.aa.aa.aa, bb, cc,dd - mm.mm.mm.mm,nnnn,pp - TCP scanned port list, 1185, 1160, 1164, 1187, 1177, 1183, 1191, 1197, 1199, 1195

11/21/2011 02:56:47.384 - Alert - Intrusion Prevention - Possible FIN Flood on IF zz - src: aa.aa.aa.aa:bb dst: mm.mm.mm.mm:nnnnn - -

so yes, there are "commonalities" but they are not consistent enough (imo)
 
well if you do not have a standard message structure, you cannot automate a process for it, can you?
 
New2VB said:
@vbaInet - bit of a problem doing that, the backend is a substantial SQL db containing confidential information.
Click to expand...
I don't need your confidential data. Create a sample db and add some bogus data to it (ensuring that it is representative of what you currently have). Make sure to include the function and form from which you are running the code.
 
src: aa.aa.aa.aa:bb dst: mm.mm.mm.mm:nnnnn is not so different from aa.aa.aa.aa, bb.bb.bb.bb

A couple of Replace() statements should sort it:
Code: 
src: aa.aa.aa.aa:bb dst: mm.mm.mm.mm:nnnnn
Replace(Fields(4), "src: ", "") ->
Code: 
aa.aa.aa.aa:bb dst: mm.mm.mm.mm:nnnnn
Replace(Fields(4), " dst: ", ", ") ->
Code: 
aa.aa.aa.aa:bb , mm.mm.mm.mm:nnnnn

Code: 
Dim Fields, varArrayEntry, cma, cmaArray As Variant
Dim strMsg, strswSource As String
    Fields = Split(strMessage, " - ")
    Dim db1 As Database, tb1 As DAO.Recordset
    Set db1 = CurrentDb
    Set tb1 = db1.OpenRecordset("dbo_stuff", 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))
        tb1!swReason = Mid(Trim$(Fields(3)), 2)
        strswSource = Mid(Left(Trim$(Fields(4)), InStr(1, (Fields(4)), ",", vbTextCompare) - 1), 2)
        tb1!swSourceIP = strswSource

' If Fields(4) contains src: aa.aa.aa.aa:bb dst: mm.mm.mm.mm:nnnnn
' then next two lines will convert it back to aa.aa.aa.aa:bb, mm.mm.mm.mm:nnnn
' which can be dealt with.

       [b]Fields(4) = Replace(Fields(4), "src: ", "")[/b]
       [b]Fields(4) = Replace(Fields(4), " dst: ", ", ")[/b]

' Assuming it is that sort of line -> 192.168.x.y, aa, bb, cc 
        cmaArray = Split(Fields(4), ",")
        tb1!swSourceName = (Trim$(cmaArray(Abs(UBound(cmaArray) >= 3) * 3)))

        tb1!swDestination = Mid(Trim$(Fields(5)), 2)
        tb1!swOptions = Mid(Trim$(Fields(6)), 2)
        tb1.Update
        tb1.Close
        db1.Close
    Next varArrayEntry
 
Last edited:
@vbaInet - the code I am trying to get working is used to put the data into the database. The method is:-
text email comes in as per the previous examples
the code in my previous post "breaks up" the email and inserts it into the fields

i.e. I am not using the code to extract the data

I am aware that "best practise" is to put the raw data in to the database and use a front-end to present it to a user but in this instance and after a lot of trial and error it has worked out that this is the best way to do it.

The only other way of doing it is to pass the whole email (which contains data I don't need) into a SQL nvarchar(MAX) record and then use a timer-based form - which has no user interaction - to constantly update the table's contents and split it up.

I would welcome your thoughts though.

@Nigel - I am not sure the method you suggested would apply to all types of example. ie. example 3 has no 'dd' field. I need the dd field (which relates to cma(3) where it exists) to be inserted into tb1!swSourceName, the 'aa.aa.aa.aa' fields go into tb1!swSourceIP ('bb' & 'cc' can be discarded)
 
Code: 
aa.aa.aa.aa, bb.bb.bb.bb, cc.cc.cc.cc, dd.dd.dd.dd

I thought you were just grabbing dd.dd.dd.dd, if it existed, or aa.aa.aa.aa if it didn't?

aa.aa.aa.aa -- aa.aa.aa.aa
aa.aa.aa.aa, bb.bb.bb.bb -- aa.aa.aa.aa
aa.aa.aa.aa, bb.bb.bb.bb cc.cc.cc.cc -- aa.aa.aa.aa
aa.aa.aa.aa, bb.bb.bb.bb, cc.cc.cc.cc, dd.dd.dd.dd -- dd.dd.dd.dd
 
It would be good it it was that simple (maybe I'm just being too complex?)

please see the attached example matrix file of how I am trying to break down the data.

BTW. Thank you very much for all you help so far...
 

Attachments

Right! You're lucky I like the challenge of string extraction. :cool:

Have a look at the attached database. You can paste in Messages and see the results.

In there is a function, called splitLogMessage, which takes 9 Parameters. :eek:

I hope I have named the parameters in an evident manner, according to your matrix document, you should be able to pass in the message and the table fields and it should populate them for you.

Code: 
Public Function splitLogMessage(ByVal theMessage As String, ByRef A_Received As String, ByRef B_Note As String, ByRef C_Category As String, ByRef D_Reason As String, ByRef E_SourceIP As String, ByRef F_SourceName As String, ByRef G_Destination As String, ByRef H_Options As String)
Dim lngFindString As Long, Fields As Variant, lngFields As Long
Dim subFields As Variant, lngSubFields As Long
Dim I_Result As String
' As at 22:41 22/11/2011

Fields = Split(theMessage, " - ")
lngFields = UBound(Fields)
A_Received = Fields(0)
B_Note = Fields(1)
C_Category = Fields(2)
D_Reason = Fields(3)

E_SourceIP = ""
F_SourceName = ""
G_Destination = ""
H_Options = ""

Fields(4) = Replace(Fields(4), " ", "")
lngFindString = InStr(Fields(4), "src:")
If lngFindString > 0 Then
    Fields(4) = Replace(Fields(4), "src:", "")
    Fields(4) = Replace(Fields(4), "dst:", "::")
    subFields = Split(Fields(4), ":")
Else
    subFields = Split(Fields(4), ",")
End If
lngSubFields = UBound(subFields)

E_SourceIP = subFields(0)
lngFindString = InStr(E_SourceIP, ",")
If lngFindString > 0 Then E_SourceIP = Left$(E_SourceIP, lngFindString - 1)

If lngSubFields = 3 Then F_SourceName = subFields(3)

If lngFields = 5 Then
    G_Destination = subFields(3)
End If

If lngFields = 6 Then
    G_Destination = Fields(5)
    lngFindString = InStr(G_Destination, ",")
    If lngFindString > 0 Then G_Destination = Left$(G_Destination, lngFindString - 1)

    H_Options = Fields(6)
    lngFindString = InStr(H_Options, ",")
    If lngFindString > 0 Then H_Options = Left$(H_Options, lngFindString - 1)
End If

I_Result = theMessage & vbCrLf
I_Result = I_Result & vbCrLf & "Received: " & A_Received
I_Result = I_Result & vbCrLf & "Note: " & B_Note
I_Result = I_Result & vbCrLf & "Category: " & C_Category
I_Result = I_Result & vbCrLf & "Reason: " & D_Reason
I_Result = I_Result & vbCrLf & "Source IP: " & E_SourceIP
I_Result = I_Result & vbCrLf & "Source Name: " & F_SourceName
I_Result = I_Result & vbCrLf & "Destination: " & G_Destination
I_Result = I_Result & vbCrLf & "Options: " & H_Options
splitLogMessage = I_Result
End Function


Fingers crossed ... and so to bed. zzzzzzzzz
 

Attachments

looks promising, I 'll try it in the a.m.

yup, bed...
 
I've only just been able to follow up this thread after reading through the posts. Your text file is not consistent and you haven't clearly explained some parts of the string.

Here are some examples

aa.aa.aa.aa, bb, cc
where does bb, cc go?

aa.aa.aa.aa:bb
why is there no colon in the previous record? and where does :bb go?

name.domain.extension
where does this go?

I suspect there could be more inconsistencies but if this was an extract from a program (and sent via e-mail), I would expect consistency between fields.
 
vbaInet:
It looks like some type of Intruder log so there should be a sort of consistency, just different types of records.

New2VB:
By all means have a play with the database I've uploaded but don't do anything live with it just yet.

What we'll have to do is identify the different types of records.

Today I will knock up a database which will enable you to harvest anonymized examples of the records.

I'll get back to you a little later.
 
Making good progress.

Should have something ready in the next couple of hours.
 
You must log in or register to reply here.

Similar threads

B
Insert Into Statement problem
Replies
4
Views
362
static
S
J
Access Crashes after sending E-Mail
Replies
5
Views
1,284
jco23
J
rio
subform filter
Replies
7
Views
1,323
Galaxiom
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom