Urgent: Need script Guru to answer some questions I have on provided script

I am grabbing syslog messages from my firewall.
Kiwi syslog support created it for me.  It is not working quite yet.
this is version 2.  Version 1 worked but only if every fields existed.
If it did not , no values were inserted into the database.
IE all files have a "msg" field.  sometimes thats the only fields.
If his is the case no results get posted to DB.  I want it to post
whatever it has from teh listed fields.  
Thats question1.
question2, my software does not allow me to autocreate more than
16 custom fields names.  So I did not add mAlarm_name to my table.
Would that completly break this script?  Because my table is blank.
If i add a autoincrement field.. it does increment.. but tha tis the only
field with any values.
PS: I do not know VB at all.  I am not even 100% sure this is vb :o
Thanks
========================================
Function Main()

' This script will split the space delimited WatchGuard message text into
' separate custom fields which can then be logged to a database

' Note: This script requires Read access to "Other fields" variables.
' Ensure that the Fields read/write permissions are set as below...
'
'                Read | Write
' Common fields    X  |
' Other fields     X  |
' Custom fields    X  |  X

' Dim the variables
Dim SplitData
Dim MyDSN, MyTable, MyFields, DBTimeout, SQLcmd

' The script assumes that a table called "Syslogd" has already been
' created and contains all the required fields as listed below in MyFields
'
' It also assumes that the DNS that you are using is called syslog
'
' NOTE: If you installed Kiwi Syslog Daemon as a service you will need to
' insure that the DSN is a System DSN not a User DSN


MyDSN = "DSN=syslog;"
MyTable = "Syslogd"
DBTimeout = 30 ' Database timeout

MyFields = "mDate,mTime,mDisposition,mPolicy,mSrcIP,mSrcPort," & _
           "mDstIP,mDstPort,mProtocol,mSrcInf,mDstInf,mSrcUser,mMsg," & _
         "mProxyAct,mRuleName,mHeader,mAlarmName"

' Split the data into an array based on the " " (space) delimiter
'
' We need to do this to get the date and time from the message text field

SplitData = Split(Fields.VarCleanMessageText, " ")

If InStr(SplitData(3), "disp=") > 0 Then
   
    With Fields
   
        ' Construct the insert statement
        SQLcmd = "INSERT INTO " & MyTable & " (" & MyFields & ") VALUES (" & _
        Quote(SplitData(0)) & "," & Quote(SplitData(1)) & "," & Quote(ValueGet(.VarCleanMessageText, "disp=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "policy=")) & "," & Quote(ValueGet(.VarCleanMessageText, "src_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "dst_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "pr=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_intf=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_intf=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_ip_nat=")) & "," & Quote(ValueGet(.VarCleanMessageText, "msg="))  & "," & _
        Quote(ValueGet(.VarCleanMessageText, "proxy_act="))  & "," & _
        Quote(ValueGet(.VarCleanMessageText, "rule_name=")) & "," & Quote(ValueGet(.VarCleanMessageText, "header="))  & "," & _
        Quote(ValueGet(.VarCleanMessageText, "alram_name=")) & ")"
   
        ' Log the data to database using DSN, Table, SQLcmd and DBTimeout
        .VarGlobal01 = .ActionLogToODBC(MyDSN, MyTable, SQLcmd, DBTimeout)
        ' VarGlobal01 now holds the return value from the function.

    End With
End If

' Set the return value to indicate that the script ran correctly
Main = "OK"

End Function


Function Quote(Data)
    ' Replace all occurrences of ' with '' to escape existing quotes
    ' Wrap data with single quotes
    Quote = "'" & Replace(Data, "'", "''") & "'"
End Function

Function ValueGet(Message, Tag)

    ' This function will look for an occurrance of the Tag within the message.  
    ' If one is found it will return the associated Tag value

    Dim SPos
    Dim EPos
   
    ValueGet = ""
    'Is the tag found within the message? Case insensitive compare
    SPos = InStr(1, Message, Tag, vbTextCompare)
    If SPos > 0 Then
        'Move pointer past the tag
        SPos = SPos + Len(Tag)
        'Check for a quoted value
        If Mid(Message, SPos, 1) = """" Then
            'adjust pointer past quote
            SPos = SPos + 1
            'find end quote
            EPos = InStr(SPos, Message, """")
        Else
            'If unquoted, look for space separator
            EPos = InStr(SPos, Message, " ")
            'We might be at the last field so set pointer to end of message
            If EPos < 1 Then EPos = Len(Message) + 1
        End If
        If EPos > 0 Then
            'Return the value
            ValueGet = Mid(Message, SPos, EPos - SPos)
        End If
    End If

End Function
====================================
LVL 11
EricIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sirbountyCommented:
This is vbs - similar to vb, but 'not' vb...
Can you post a sample log file?
0
sirbountyCommented:
Additionally, if ver 1 worked, can you post version 1?  Sounds like an easier fix...
0
sirbountyCommented:
Actually, if you can post the log file and your DSN info, we can probably clean this up a bit...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

EricIT ManagerAuthor Commented:
Version1

========================
Function Main()

' This script will split the space delimited WatchGuard message text into
' separate custom fields which can then be logged to a database

' Note: This script requires Read access to "Other fields" variables.
' Ensure that the Fields read/write permissions are set as below...
'
'                Read | Write
' Common fields    X  |
' Other fields     X  |
' Custom fields    X  |  X

' Dim the variables
Dim SplitData
Dim MyDSN, MyTable, MyFields, DBTimeout, SQLcmd

' The script assumes that a table called "Syslogd" has already been
' created and contains all the required fields as listed below in MyFields
'
' It also assumes that the DNS that you are using is called syslog
'
' NOTE: If you installed Kiwi Syslog Daemon as a service you will need to
' insure that the DSN is a System DSN not a User DSN


MyDSN = "DSN=syslog;"
MyTable = "Syslogd"
DBTimeout = 30 ' Database timeout

MyFields = "mDate,mTime,mDisposition,mPolicy,mSrcIP,mSrcPort," & _
           "mDstIP,mDstPort,mProtocol,mSrcInf,mDstInf,mSrcUser,mMsg"

' Split the data into an array based on the " " (space) delimiter
'
' We need to do this to get the date and time from the message text field

SplitData = Split(Fields.VarCleanMessageText, " ")

If InStr(SplitData(3), "disp=") > 0 Then
   
    With Fields
   
        ' Construct the insert statement
        SQLcmd = "INSERT INTO " & MyTable & " (" & MyFields & ") VALUES (" & _
        Quote(SplitData(0)) & "," & Quote(SplitData(1)) & "," & Quote(ValueGet(.VarCleanMessageText, "disp=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "policy=")) & "," & Quote(ValueGet(.VarCleanMessageText, "src_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "dst_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "pr=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_intf=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_intf=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_ip_nat=")) & "," & Quote(ValueGet(.VarCleanMessageText, "msg="))  & ")"
   
        ' Log the data to database using DSN, Table, SQLcmd and DBTimeout
        .VarGlobal01 = .ActionLogToODBC(MyDSN, MyTable, SQLcmd, DBTimeout)
        ' VarGlobal01 now holds the return value from the function.

    End With
End If

' Set the return value to indicate that the script ran correctly
Main = "OK"

End Function


Function Quote(Data)
    ' Replace all occurrences of ' with '' to escape existing quotes
    ' Wrap data with single quotes
    Quote = "'" & Replace(Data, "'", "''") & "'"
End Function

Function ValueGet(Message, Tag)

    ' This function will look for an occurrance of the Tag within the message.  
    ' If one is found it will return the associated Tag value

    Dim SPos
    Dim EPos
   
    ValueGet = ""
    'Is the tag found within the message? Case insensitive compare
    SPos = InStr(1, Message, Tag, vbTextCompare)
    If SPos > 0 Then
        'Move pointer past the tag
        SPos = SPos + Len(Tag)
        'Check for a quoted value
        If Mid(Message, SPos, 1) = """" Then
            'adjust pointer past quote
            SPos = SPos + 1
            'find end quote
            EPos = InStr(SPos, Message, """")
        Else
            'If unquoted, look for space separator
            EPos = InStr(SPos, Message, " ")
            'We might be at the last field so set pointer to end of message
            If EPos < 1 Then EPos = Len(Message) + 1
        End If
        If EPos > 0 Then
            'Return the value
            ValueGet = Mid(Message, SPos, EPos - SPos)
        End If
    End If

End Function
===========================
0
sirbountyCommented:
Can you post a log file as well?
0
EricIT ManagerAuthor Commented:
i have a meeting.  will do when i get back.
syslog has diffeernt levels.. some contain different strings in the msg field.   I will give a few examples after my meeting.
0
sirbountyCommented:
on a meeting as well...
0
EricIT ManagerAuthor Commented:
Example 1:  (extracted just msg part of syslog)
2006-03-23 16:57:51 firewall   ma: msg_id="0401-1000" pri="1" alarm_name="spoofing_dos" alarm_id="2012" time="Thu Mar 23 16:57:51 2006 (EST)" msg="spoofing_dos detected,  IP source spoofing, src_intf=1, src_ip=192.168.10.58 (11095.domain.com) " hostname="firewall.mydomain.com"

however thats a rare log.  most logs look like this:

Example 2: (this is a full log.  i only parse info from the 5th field "message")
[date,  time, priority, hostname, message]
04-03-2006      15:10:29      Local1.Debug      hq.mydomain.com      2006-04-03 15:10:21 firewall disp="Deny"   pri="1" policy="internal policy" src_ip="192.168.10.115 (user.mydomain.com) " dst_ip="64.12.169.185 (aimexpress-vm03.evip.aol.com) " pr="http/tcp" src_port="4384" dst_port="80" src_intf="1-LAN" dst_intf="unknown"  rc="104" msg="TCP RST packet without an associated connection, firewall drop" pckt_len="40" ttl="128"


now notice "msg" was in both.  i want that info inserted to the datbase no matter what. I dont care if all the fields are present .. because based on the priority level of the syslog.. the variables are not all the same.
That was the fault of my first version.  version 2 added some fields to cover all priorities, and was supposed to log even if not all criteria is matched.  
0
EricIT ManagerAuthor Commented:
for the record.  I removed alram_name=  (which was spelled wrong btw)  
my datbase table tool only lets me input 16 custom fields.  i guess it was pissed that the script had more fields names than the table.
now i get results.  any comments appreciated.
i am waiting for kiwi support to let me know how to add more custom fields.

0
sirbountyCommented:
Couple of questions for you before I can proceed:

What is the full report you want from both examples?
Are those tab seperated examples?
This extra field you're wanting - is that a limitation of the software or the database?
0
EricIT ManagerAuthor Commented:
I just want it logged to a database so i can run reports/querys

space delimited

I think its a limitation of the software that creates teh table for me.  May have to create a table in access manuallY?
waiting ot hear back from support.. so you may want to hold.

Thanks
0
sirbountyCommented:
Okay, but we can script creating tables too... ;^)
0
EricIT ManagerAuthor Commented:
My software Kiwi syslog daemon ( u familar with it?) has rule sets.
in a rule i have it filter no packets, then send them to a display AND send them to the script for processing.  The script then makes entries into the access db.

however some things are STILL being logged to the display and not the database.  which means something in that script keeps certain log entries from being sent to the database.  IE: I have authentication statements as below:

04-04-2006      11:09:56      Local2.Error      hq.mydomain.com      2006-04-04 11:09:47 firewall   admd: pri="3" msg_id="1100-1012" msg="ADM auth Firewall user [me@Active Directory] Accepted"

this never makes it to my db.  WHY?   what in that script allows or denies things?
0
sirbountyCommented:
No, not familiar with it, but it should be simple to accomplish this.
I'm looking at it now - will post something on it shortly (unless I get stuck and have to ask further questions).

It shouldn't be a problem to get what you're after with the current setup.  
0
sirbountyCommented:
Based upon your two examples, I don't see this ever being true:

If InStr(SplitData(3), "disp=") > 0 Then
(which is where all the processing begins)

Element 3, if we split the data on spaces, is equal to "ma:" in the first example
and equal to "hq.mydomain.com"

in the second.

I think I'll try rewriting this a bit so that it will pass the data regardless of that conditioning...
0
sirbountyCommented:
Are you saying you only want data reported in this format?

[date,  time, priority, hostname, message]??

cause the db appears to be setup to receive:
"mDate,mTime,mDisposition,mPolicy,mSrcIP,mSrcPort,mDstIP,mDstPort,mProtocol,mSrcInf,mDstInf,mSrcUser,mMsg"
0
EricIT ManagerAuthor Commented:
so that is saying if the 3rd space sperated portion = disp (disposition)  process...
otherwise ignore?

0
EricIT ManagerAuthor Commented:
i want it to receive it all.. and I dont want all the fields to be in place for it to do so.
the most recent example never gets there.. and by what you just said im guessing it is because it has no disposition value.
0
sirbountyCommented:
Ok - working on it...give me a few minutes...
0
EricIT ManagerAuthor Commented:
I confirmed.. my access db has zero entries with a blank "disp"

0
EricIT ManagerAuthor Commented:
i think every entry has a "msg" tablename "mMsg"

also date and time.  so we can key off one of them maybe?

0
sirbountyCommented:
Yep - last question here...

mSrcUser...do you know what this relates to?  In your two examples, could you point it out for me?
0
EricIT ManagerAuthor Commented:
it displays this:  (only somtimes)  0.0.0.0  (firewall.mydomain.com)

where 0.0.0.0 is my IP of the firewalls external interface


actually i may remove that one if its all it ever returns.. need to do more monitoring before i decide that.

then i could add the one value i am missing
0
EricIT ManagerAuthor Commented:
found an instance of it that i need i think.

04-04-2006      11:09:57      Local1.Debug      hq.mydomain.com      2006-04-04 11:09:47 firewall disp="Allow"   pri="1" policy="WatchGuard Authentication-00" src_ip="192.168.10.50 (me.mydomain.com) " dst_ip="192.168.10.254 (hq.mydomain.com) " pr="WG-Authentication/tcp" src_port="2146" dst_port="4100" src_intf="1-LAN" dst_intf="Firebox"  src_user="me@Active Directory" rc="100" msg="allowed, mss not exceeding 1460, idle timeout=43205 sec" pckt_len="48" ttl="128"

it appears to show the authenticated user info for a filter.  (sometimes)
0
sirbountyCommented:
Looking at this again, it may be easier to cleanup what you've got.
Personally, I'd rewrite it for my use, but basically you just need to ensure there is a value assigned if none exists in the data stream.  Unfortunately, the coder decided to 'blank' out the value if none were found - this errors out the INSERT statement.

So, in the ValueGet Function, change it to read like this:

Function ValueGet(Message, Tag)

    ' This function will look for an occurrance of the Tag within the message.  
    ' If one is found it will return the associated Tag value

    Dim SPos
    Dim EPos
   
    ValueGet = ""   <==========change this line to be the following:
                            ValueGet = "Not Found"  '(or whatever suits your fancy)

Now, you'll at least be writing a 'bogus' value if nothing's there....
0
EricIT ManagerAuthor Commented:
ok.  now its puttin NA in spots with no data.
but it was puttinb blanks in before.

how do i get it to put data in even if disp=  is not there
0
sirbountyCommented:
Strip out this line...

If InStr(SplitData(3), "disp=") > 0 Then
   

and it's corresponding

End If

(which appears just after
    End With


0
sirbountyCommented:
Or you can comment them out - here's that block of code with the problem line(s) removed...

''''''''''''''''''''''''If InStr(SplitData(3), "disp=") > 0 Then
    With Fields
   
        ' Construct the insert statement
        SQLcmd = "INSERT INTO " & MyTable & " (" & MyFields & ") VALUES (" & _
        Quote(SplitData(0)) & "," & Quote(SplitData(1)) & "," & Quote(ValueGet(.VarCleanMessageText, "disp=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "policy=")) & "," & Quote(ValueGet(.VarCleanMessageText, "src_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_ip=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "dst_port=")) & "," & Quote(ValueGet(.VarCleanMessageText, "pr=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_intf=")) & "," & Quote(ValueGet(.VarCleanMessageText, "dst_intf=")) & "," & _
        Quote(ValueGet(.VarCleanMessageText, "src_ip_nat=")) & "," & Quote(ValueGet(.VarCleanMessageText, "msg="))  & ")"
   
        ' Log the data to database using DSN, Table, SQLcmd and DBTimeout
        .VarGlobal01 = .ActionLogToODBC(MyDSN, MyTable, SQLcmd, DBTimeout)
        ' VarGlobal01 now holds the return value from the function.

    End With
''''''''''''''''''''''''End If
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EricIT ManagerAuthor Commented:
dam!  does that mean everything between the two  ''''''''''''''''''''''''''   does nothing? or just that line specifically?

and thanks!
0
sirbountyCommented:
an apostrophe (') comments out that line so that it's simply ignored...
I just put several there (''''''''''''''') for emphasis.
Those lines will not run, but the ones between them will.
Why?  Is it not working?
0
EricIT ManagerAuthor Commented:
no it is.. i just wonded if that section with

Quote(ValueGet(.VarCleanMessageText, "policy=")) & "," & Quote(ValueGet(.VarCleanMessageText, "src_ip=")) & "," & _


was still doing script stuff :D

its working.. good good its working
0
sirbountyCommented:
The Quote function, imho, is a bit needless.  It's just an external function calling an internal one...waste of time..
But, glad it's working. :^)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.