?
Solved

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

Posted on 2006-04-03
32
Medium Priority
?
1,298 Views
Last Modified: 2008-01-09
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
====================================
0
Comment
Question by:Eric
  • 17
  • 15
32 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 16361239
This is vbs - similar to vb, but 'not' vb...
Can you post a sample log file?
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 16361243
Additionally, if ver 1 worked, can you post version 1?  Sounds like an easier fix...
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 16361522
Actually, if you can post the log file and your DSN info, we can probably clean this up a bit...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 11

Author Comment

by:Eric
ID: 16361557
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16361611
Can you post a log file as well?
0
 
LVL 11

Author Comment

by:Eric
ID: 16361631
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16361786
on a meeting as well...
0
 
LVL 11

Author Comment

by:Eric
ID: 16364272
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
 
LVL 11

Author Comment

by:Eric
ID: 16365443
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16365988
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
 
LVL 11

Author Comment

by:Eric
ID: 16366013
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16366551
Okay, but we can script creating tables too... ;^)
0
 
LVL 11

Author Comment

by:Eric
ID: 16372303
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372471
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372545
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372574
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
 
LVL 11

Author Comment

by:Eric
ID: 16372585
so that is saying if the 3rd space sperated portion = disp (disposition)  process...
otherwise ignore?

0
 
LVL 11

Author Comment

by:Eric
ID: 16372602
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372619
Ok - working on it...give me a few minutes...
0
 
LVL 11

Author Comment

by:Eric
ID: 16372620
I confirmed.. my access db has zero entries with a blank "disp"

0
 
LVL 11

Author Comment

by:Eric
ID: 16372647
i think every entry has a "msg" tablename "mMsg"

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

0
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372660
Yep - last question here...

mSrcUser...do you know what this relates to?  In your two examples, could you point it out for me?
0
 
LVL 11

Author Comment

by:Eric
ID: 16372723
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
 
LVL 11

Author Comment

by:Eric
ID: 16372864
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16372994
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
 
LVL 11

Author Comment

by:Eric
ID: 16373290
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16373395
Strip out this line...

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

and it's corresponding

End If

(which appears just after
    End With


0
 
LVL 67

Accepted Solution

by:
sirbounty earned 2000 total points
ID: 16373403
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
 
LVL 11

Author Comment

by:Eric
ID: 16373437
dam!  does that mean everything between the two  ''''''''''''''''''''''''''   does nothing? or just that line specifically?

and thanks!
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 16373478
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
 
LVL 11

Author Comment

by:Eric
ID: 16373498
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
 
LVL 67

Expert Comment

by:sirbounty
ID: 16373662
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question