DrayTek UK Users' Community Forum

Help, Advice and Solutions from DrayTek Users

2820 - Syslog logging...

  • steveharding
  • Topic Author
  • Offline
  • New Member
  • New Member
More
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.

More
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
  • New Member
More
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
  • New Member
More
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: Sami