DrayTek UK Users' Community Forum

Help, Advice and Solutions from DrayTek Users

2820 - Syslog logging...

steveharding
Topic Author
Offline
New Member

17 Aug 2010 13:43 #63384 by steveharding

2820 - Syslog logging... was created by steveharding

I am trying to look at a reliable way of logging access and firewall hits from the 2820. I intend to write a small app which will read the log file, store it in a SQL server in an atered way and present it in a more readable format and for historical logging.

There are 2 options that I know about - both of which seem to have their little niggles..

1. Draytek SysLog on a computer.
this has 2 options. 1 to log to a text file and the 2nd to log to a DB file..

The text file stops working if the server is rebooted - requires manual intervention to get working again

The database file - meh - I could use it - but would prefer a text file - it also seems a bit buggy - seems as though it does not like creating a new file for a new day. I suppose I could connect to the access DB, read the data and delete what ive read - but Access can be funny at times and may cause loss of data due to the DB been locked by either my script, or the Syslog app

2. Syslog to USB memory stick on the router
This seems to "better" way to do it - no need to rely on a machine to be up and running with the correct software.

However, it also has its bugs

The way i wanted to do this was to allow it to generate its daily files, rename the file then get my script to remove the file off the pen drive. This way no additional items will be writen to the log file and would be put into a new one.... AS soon as I renamed the file - syslog stops dumping to the USB mem stick....

Anybody else come across this, or have any other suggestions for logging the syslog output from the router?

Please Log in or Create an account to join the conversation.

thrain
Offline
Junior Member

18 Aug 2010 11:10 #63403 by thrain

Replied by thrain on topic 2820 - Syslog logging...

Use Splunk, it does exactly what you want.

www.splunk.com

Please Log in or Create an account to join the conversation.

steveharding
Topic Author
Offline
New Member

21 Aug 2010 15:06 #63451 by steveharding

Replied by steveharding on topic 2820 - Syslog logging...

Ta for the suggestion of Splunk - looks good but I managed to understand the database logging a bit better, it does seem to split the DB's per day - however it seems to do this at 6pm for some reason!!!

Anyway, I have wrote a script that will pull out the information in the DB and dump it into SQL in an easier to read/search format! Hope this helps somebody else.

Copy and paste into notepad, make changes to suite your needs and save as a VBS!

Notes - I am running this on a 2008R2 64x server - so there is a reference to a ACE connector. You will need to download the Office 2010 ACE connector for either 32 or 64bit to allow it to read the Access DB

It also does not check the existance of an item found against whats in the SQL DB - so if you run it twice - it WILL import the access DB into SQL twice if you have not cleaned it out... I may fix this in the future - but at the moment I am happy clearing out the DB and reimporting it all...

Workes with 4.2.0 and 4.2.1 Draytek Syslog's

Good practice to run any code on a test system. Although I have tested the code and am happy for it to run on my system... it may hose yours!



Code:

Const adOpenStatic = 3 
Const adLockOptimistic = 3 

DBServer="VPC-SERVER\WebSQL"
DBName="DraytekSysLog"
DBUser="user"
DBPass="pass"

on error resume next

Dim objDatabaseFileArray(11)
objDatabaseFileArray(0)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-20_17-59-33.mdb"
objDatabaseFileArray(1)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-21.mdb"
objDatabaseFileArray(2)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-22_14-49-13.mdb"
objDatabaseFileArray(3)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-23_14-49-33.mdb"
objDatabaseFileArray(4)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-24.mdb"
objDatabaseFileArray(5)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-24_14-49-52.mdb"
objDatabaseFileArray(6)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-25_14-56-12.mdb"
objDatabaseFileArray(7)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-26.mdb"
objDatabaseFileArray(8)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-26_14-56-32.mdb"
objDatabaseFileArray(9)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-27_15-05-40.mdb"
objDatabaseFileArray(10)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-28_15-06-00.mdb"
objDatabaseFileArray(11)= "\\server\data2\Draytek Log Files\SyslogDB\192.168.20.254\Vigor_v1.4_2010-08-29_15-06-19.mdb"

For Each objDatabaseFile in objDatabaseFileArray
    
'##################################################
'Get Access Logs
'##################################################
    Set objGetDBConnection = CreateObject("ADODB.Connection") 
    Set objGetDBRecordSet = CreateObject("ADODB.Recordset")     
    objGetDBConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objDatabaseFile & ";"
    objGetDBRecordSet.Open "SELECT * FROM LOG WHERE (MSG LIKE '%Local User%' OR MSG LIKE '%Open port%') AND MSG NOT LIKE '%0.0.0.0%' AND MSG NOT LIKE '%NAT Setup%'" , objGetDBConnection, adOpenStatic, adLockOptimistic 
        
    Set objMSG = objGetDBRecordSet("msg")

    Do Until objGetDBRecordSet.EOF
        objInsertSQL = 0

        objTime = cdate(objGetDBRecordSet("SysT"))
        
        If inSTR(lcase(objMSG), "local user (") =>1 AND inSTR(lcase(objMSG), "dns") = 0  AND instr(lcase(objmsg), "(icmp)") = 0 Then
            objDirection = "Out"
            objDNSRequest = "-"

            'Get Source info
            StartTextCount = inSTR(lcase(objMSG), "): ") + 3
            EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
            DiffTextCount = EndTextCount - StartTextCount
            objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
                
            StartTextCount = EndTextCount + 1
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ->")
            DiffTextCount = EndTextCount - StartTextCount
            objSourcePort = mid(objMSG, StartTextCount, DiffTextCount)
            
            'Get destination info
            StartTextCount = EndTextCount + 4
            EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
        
            StartTextCount = EndTextCount + 1
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationPort = mid(objMSG, StartTextCount, DiffTextCount)
                
            'type
            StartTextCount = EndTextCount + 2
            EndTextCount = instr(StartTextCount, lcase(objMSG), ")")
            DiffTextCount = EndTextCount - StartTextCount
            objType = mid(objMSG, StartTextCount, DiffTextCount)
            objInsertSQL = 1
            
        Elseif inSTR(lcase(objMSG), "local user (") =>1 AND instr(lcase(objmsg), "(icmp)") => 1 Then
            objDirection = "Ping"
            objDNSRequest = "-"

            'Get Source info
            StartTextCount = inSTR(lcase(objMSG), "): ") + 3
            EndTextCount = instr(StartTextCount, lcase(objMSG), " -> ")
            DiffTextCount = EndTextCount - StartTextCount
            objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
            
            objSourcePort = "0"        
            
            'Get destination info
            StartTextCount = EndTextCount + 4
            EndTextCount = instr(StartTextCount, lcase(objMSG), "(icmp)")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
            
            StartTextCount = EndTextCount + 1
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationPort = "0"
            objType = "ICMP"
            objInsertSQL = 1
            
        ElseIf inSTR(lcase(objMSG), "local user (") =>1 AND inSTR(lcase(objMSG), "dns") => 1 Then
            objSourcePort = "0"
            objDestinationPort = "0"
            objType = "DNS"        
            objDirection = "DNS"
            
            'Get Source info
            StartTextCount = inSTR(lcase(objMSG), "): ") + 3
            EndTextCount = instr(StartTextCount, lcase(objMSG), " dns -> ")
            DiffTextCount = EndTextCount - StartTextCount
            objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
            
            'Get destination info
            StartTextCount = EndTextCount + 8
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
        
            StartTextCount = instr(EndTextCount, lcase(objMSG), " inquire ") + 9
            EndTextCount = len(objMSG) + 1
            DiffTextCount = EndTextCount - StartTextCount
            objDNSRequest = mid(objMSG, StartTextCount, DiffTextCount)    
            
            objInsertSQL = 1
            
        Elseif inSTR(lcase(objMSG), "open port") =>1 Then
            objDirection = "In"
            objDNSRequest = "-"
            'Get Source info
            StartTextCount = inSTR(lcase(objMSG), "open port: ") + 11
            EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
            DiffTextCount = EndTextCount - StartTextCount
            objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
            
            StartTextCount = EndTextCount + 1
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ->") -3
            DiffTextCount = EndTextCount - StartTextCount
            objSourcePort = mid(objMSG, StartTextCount, DiffTextCount)
            
            'Get destination info
            StartTextCount = EndTextCount + 7
            EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
            
            StartTextCount = EndTextCount + 1
            EndTextCount = instr(StartTextCount, lcase(objMSG), " ")
            DiffTextCount = EndTextCount - StartTextCount
            objDestinationPort = mid(objMSG, StartTextCount, DiffTextCount)
            
            'type
            StartTextCount = EndTextCount + 2
            EndTextCount = instr(StartTextCount, lcase(objMSG), ")")
            DiffTextCount = EndTextCount - StartTextCount
            objType = mid(objMSG, StartTextCount, DiffTextCount)
            objInsertSQL = 1
            
        End If
        
        If objInsertSQL = 1 Then
            If Err.Number <> 0 Then
                wscript.echo "ERROR FOUND: LOCAL USER" & vbNewLine & _
                            "File: " & objDatabaseFile & vbNewLine & _
                            "Direction: " & objDirection & vbNewline & _
                            "Date: " & objTime & VbNewLine & _
                            "Message: " & objMSG
                On error goto 0
                on error resume next
            
            Else
                Set DatabaseConnection = CreateObject("ADODB.Connection")
                DatabaseConnection.Open "Driver={SQL Server};Server=" & DBServer & ";Database=" & DBName & ";Uid=" & DBUser & ";Pwd=" & DBPass & ";"
                Set SQLRecordSet = CreateObject("ADODB.Recordset")
                SQLRecordSet.CursorType = 2
                SQLRecordSet.LockType = 3
                
                SQLString = "INSERT INTO tbl_accesslog (Date_Time, Direction, SourceIP, SourcePort, DestinationIP, DestinationPort, DNS_Request, Type) VALUES ('" & objTime & "','" & objDirection & "','" & objSourceIP & "','" & objSourcePort & "','" & objDestinationIP & "','" & objDestinationPort & "','" & objDNSRequest & "','" & objType & "');"
                SQLRecordSet.Open SQLString, DatabaseConnection            

                DatabaseConnection.Close
                set DatabaseConnection = Nothing        

                If Err.Number <> 0 Then            
                    wscript.echo "ERROR FOUND: LOCAL USER SQL ERROR" & vbNewLine & _
                            "File: " & objDatabaseFile & vbNewLine & _
                            "Direction: " & objDirection & vbNewline & _
                            "Date: " & objTime & VbNewLine & _
                            "Message: " & objMSG & VbNewLine & _
                            "SQL: " & SQLRecordSet
                    On error goto 0
                    on error resume next
                End If            
            
            End If
        End If
        
        objGetDBRecordSet.movenext
    Loop
    objGetDBRecordSet.Close
    objGetDBConnection.Close


'##################################################
'Get Filter Logs
'Update Allowed, insert blocked
'##################################################


    objGetDBConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objDatabaseFile & ";"
    objGetDBRecordSet.Open "SELECT * FROM LOG WHERE MSG LIKE '%FILTER%' AND MSG NOT LIKE '%Local User%' AND MSG NOT LIKE '%Open port%' AND MSG NOT LIKE '%License%'" , objGetDBConnection, adOpenStatic, adLockOptimistic 
        
    Set objMSG = objGetDBRecordSet("msg")
        
    Do Until objGetDBRecordSet.EOF
        objInsertSQL = 0
        objDirection = "-"
        objTime = cdate(objGetDBRecordSet("SysT"))
        
        'Get Firewall Rule Number
        StartTextCount = inSTR(lcase(objMSG), "[@s:r=") + 6
        EndTextCount = instr(StartTextCount, lcase(objMSG), ",")
        DiffTextCount = EndTextCount - StartTextCount
        objFilterRule = mid(objMSG, StartTextCount, DiffTextCount)
        
        'Get Source info
        StartTextCount = EndTextCount + 2
        EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
        DiffTextCount = EndTextCount - StartTextCount
        objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
                
        StartTextCount = EndTextCount + 1
        EndTextCount = instr(StartTextCount, lcase(objMSG), "->")
        DiffTextCount = EndTextCount - StartTextCount
        objSourcePort = mid(objMSG, StartTextCount, DiffTextCount)
            
        'Get destination info
        StartTextCount = EndTextCount + 2
        EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
        DiffTextCount = EndTextCount - StartTextCount
        objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
        
        StartTextCount = EndTextCount + 1
        EndTextCount = instr(StartTextCount, lcase(objMSG), "]")
        DiffTextCount = EndTextCount - StartTextCount
        objDestinationPort = mid(objMSG, StartTextCount, DiffTextCount)
        
        If Err.Number <> 0 Then
            wscript.echo "ERROR FOUND: %FILTER%" & vbNewLine & _
                        "File: " & objDatabaseFile & vbNewLine & _
                        "Direction: " & objDirection & vbNewline & _
                        "Date: " & objTime & VbNewLine & _
                        "Message: " & objMSG
                On error goto 0
                on error resume next
        Else
        
            If instr(lcase(objmsg), "[pass]") => 1 Then
                SQLString = "UPDATE TBL_accessLog SET Filter='" & "Pass" & "', Firewall_Rule='" & objFilterRule & "' WHERE Date_Time = '" & objTime & "' AND SourceIP = '" & objSourceIP & "' AND SourcePort = '" & objSourcePort & "' AND DestinationIP = '" & objDestinationIP& "' AND DestinationPort = '" & objDestinationPort & "';"
            
            ElseIf instr(lcase(objmsg), "[block]") => 1 Then
                
                If instr(lcase(objmsg), "lan->wan") =>1 Then
                    objDirection = "In"
                Elseif instr(lcase(objmsg), "wan->lan") =>1 Then
                    objDirection = "Out"
                End If
                
                SQLString = "INSERT INTO tbl_accesslog (Date_Time, Direction, SourceIP, SourcePort, DestinationIP, DestinationPort, DNS_Request, Type, Filter, Firewall_Rule) VALUES ('" & objTime & "','" & objDirection & "','" & objSourceIP & "','" & objSourcePort & "','" & objDestinationIP & "','" & objDestinationPort & "','" & objDNSRequest & "','" & objType & "','Block','" & objFilterRule & "');"
            End If
        
            Set DatabaseConnection = CreateObject("ADODB.Connection")
            DatabaseConnection.Open "Driver={SQL Server};Server=" & DBServer & ";Database=" & DBName & ";Uid=" & DBUser & ";Pwd=" & DBPass & ";"
            Set SQLRecordSet = CreateObject("ADODB.Recordset")
            SQLRecordSet.CursorType = 2
            SQLRecordSet.LockType = 3
            
            SQLRecordSet.Open SQLString, DatabaseConnection            

            DatabaseConnection.Close
            set DatabaseConnection = Nothing    
            
            If Err.Number <> 0 Then
                wscript.echo "ERROR FOUND: %FILTER% SQL ERROR" & vbNewLine & _
                            "File: " & objDatabaseFile & vbNewLine & _
                            "Direction: " & objDirection & vbNewline & _
                            "Date: " & objTime & VbNewLine & _
                            "Message: " & objMSG & VbNewLine & _
                            "SQL: " & SQLRecordSet
                On error goto 0
                on error resume next
            End If
            
            objGetDBRecordSet.movenext
        End If
    Loop
    
    objGetDBRecordSet.Close
    objGetDBConnection.Close

'##################################################
'Get DOS Logs
'##################################################
    objGetDBConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & objDatabaseFile & ";"
    objGetDBRecordSet.Open "SELECT * FROM LOG WHERE MSG LIKE '%DOS%' AND MSG NOT LIKE '%Local User%' AND MSG NOT LIKE '%Open port%'" , objGetDBConnection, adOpenStatic, adLockOptimistic 
        
    Set objMSG = objGetDBRecordSet("msg")
        
    Do Until objGetDBRecordSet.EOF
        objDirection = "-"
        objType = "DOS"
        objTime = cdate(objGetDBRecordSet("SysT"))
        
        'Get Firewall Rule Number
        StartTextCount = inSTR(lcase(objMSG), "[DOS][Block][") + 14
        EndTextCount = instr(StartTextCount, lcase(objMSG), "]")
        DiffTextCount = EndTextCount - StartTextCount
        objFilterRule = mid(objMSG, StartTextCount, DiffTextCount)
        
        'Get Source info
        StartTextCount = EndTextCount + 2
        EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
        DiffTextCount = EndTextCount - StartTextCount
        objSourceIP = mid(objMSG, StartTextCount, DiffTextCount)
                
        StartTextCount = EndTextCount + 1
        EndTextCount = instr(StartTextCount, lcase(objMSG), "->")
        DiffTextCount = EndTextCount - StartTextCount
        objSourcePort = mid(objMSG, StartTextCount, DiffTextCount)
            
        'Get destination info
        StartTextCount = EndTextCount + 2
        EndTextCount = instr(StartTextCount, lcase(objMSG), ":")
        DiffTextCount = EndTextCount - StartTextCount
        objDestinationIP = mid(objMSG, StartTextCount, DiffTextCount)
        
        StartTextCount = EndTextCount + 1
        EndTextCount = instr(StartTextCount, lcase(objMSG), "]")
        DiffTextCount = EndTextCount - StartTextCount
        objDestinationPort = mid(objMSG, StartTextCount, DiffTextCount)
        
        If Err.Number <> 0 Then
            wscript.echo "ERROR FOUND: %DOS%" & vbNewLine & _
                        "File: " & objDatabaseFile & vbNewLine & _
                        "Direction: " & objDirection & vbNewline & _
                        "Date: " & objTime & VbNewLine & _
                        "Message: " & objMSG
            On error goto 0
            on error resume next
        Else
        
            SQLString = "INSERT INTO tbl_accesslog (Date_Time, Direction, SourceIP, SourcePort, DestinationIP, DestinationPort, DNS_Request, Type, Filter, Firewall_Rule) VALUES ('" & objTime & "','" & objDirection & "','" & objSourceIP & "','" & objSourcePort & "','" & objDestinationIP & "','" & objDestinationPort & "','" & objDNSRequest & "','" & objType & "','DOS','" & objFilterRule & "');"
            
            Set DatabaseConnection = CreateObject("ADODB.Connection")
            DatabaseConnection.Open "Driver={SQL Server};Server=" & DBServer & ";Database=" & DBName & ";Uid=" & DBUser & ";Pwd=" & DBPass & ";"
            Set SQLRecordSet = CreateObject("ADODB.Recordset")
            SQLRecordSet.CursorType = 2
            SQLRecordSet.LockType = 3
            
            SQLRecordSet.Open SQLString, DatabaseConnection            
            
            DatabaseConnection.Close
            set DatabaseConnection = Nothing    
            
            If Err.Number <> 0 Then
                wscript.echo "ERROR FOUND: %DOS% SQL ERROR" & vbNewLine & _
                            "File: " & objDatabaseFile & vbNewLine & _
                            "Direction: " & objDirection & vbNewline & _
                            "Date: " & objTime & VbNewLine & _
                            "Message: " & objMSG & VbNewLine & _
                            "SQL: " & SQLRecordSet
                On error goto 0
                on error resume next
            End If
            
            objGetDBRecordSet.movenext
        End If
    
    Loop
    
    objGetDBRecordSet.Close
    objGetDBConnection.Close
    Set objGetDBConnection = Nothing
    Set objGetDBRecordSet = Nothing
Next

wscript.echo "done"

Please Log in or Create an account to join the conversation.

steveharding
Topic Author
Offline
New Member

30 Aug 2010 13:43 #63597 by steveharding

Replied by steveharding on topic 2820 - Syslog logging...

I have found a few small bugs in the code which I have ironed out...
The script was crashing out during one of the importers getting fed strings it was not expecting.

I added some additional error checking and found that the Draytek sometimes puts in additional likes that reports:
[LICENCE][WebFilter Service not active ]

This is been picked up by the '%filter%' SQL query and causing the script to exit. Adjusted the SQL query.

Another one that it has picked up on is:
[WEB]Nat Setup>Open Ports Setup>Edit Open Ports Setup

This was under the "local user" bit - again a modification to the SQL query solved the issue!

I have also added some error checking on the SQL update or inserts to catch any errors during SQL operations

Please Log in or Create an account to join the conversation.

Moderators: Chris, Sami