We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Eric
Eric asked
on
Medium Priority
1,317 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
====================================
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2007

Commented:
This is vbs - similar to vb, but 'not' vb...
Can you post a sample log file?
CERTIFIED EXPERT
Top Expert 2007

Commented:
Additionally, if ver 1 worked, can you post version 1?  Sounds like an easier fix...
CERTIFIED EXPERT
Top Expert 2007

Commented:
Actually, if you can post the log file and your DSN info, we can probably clean this up a bit...
EricIT Manager

Author

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
===========================
CERTIFIED EXPERT
Top Expert 2007

Commented:
Can you post a log file as well?
EricIT Manager

Author

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.
CERTIFIED EXPERT
Top Expert 2007

Commented:
on a meeting as well...
EricIT Manager

Author

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.  
EricIT Manager

Author

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.

CERTIFIED EXPERT
Top Expert 2007

Commented:
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?
EricIT Manager

Author

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
CERTIFIED EXPERT
Top Expert 2007

Commented:
Okay, but we can script creating tables too... ;^)
EricIT Manager

Author

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?
CERTIFIED EXPERT
Top Expert 2007

Commented:
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.  
CERTIFIED EXPERT
Top Expert 2007

Commented:
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...
CERTIFIED EXPERT
Top Expert 2007

Commented:
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"
EricIT Manager

Author

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

EricIT Manager

Author

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.
CERTIFIED EXPERT
Top Expert 2007

Commented:
Ok - working on it...give me a few minutes...
EricIT Manager

Author

Commented:
I confirmed.. my access db has zero entries with a blank "disp"

EricIT Manager

Author

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

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

CERTIFIED EXPERT
Top Expert 2007

Commented:
Yep - last question here...

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

Author

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
EricIT Manager

Author

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)
CERTIFIED EXPERT
Top Expert 2007

Commented:
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....
EricIT Manager

Author

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
CERTIFIED EXPERT
Top Expert 2007

Commented:
Strip out this line...

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

and it's corresponding

End If

(which appears just after
    End With


CERTIFIED EXPERT
Top Expert 2007
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
EricIT Manager

Author

Commented:
dam!  does that mean everything between the two  ''''''''''''''''''''''''''   does nothing? or just that line specifically?

and thanks!
CERTIFIED EXPERT
Top Expert 2007

Commented:
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?
EricIT Manager

Author

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
CERTIFIED EXPERT
Top Expert 2007

Commented:
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. :^)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.