DrayTek UK Users' Community Forum
Help, Advice and Solutions from DrayTek Users
2820 - Syslog logging...
- steveharding
- Topic Author
- Offline
- New Member
Less
More
- Posts: 8
- Thank you received: 0
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?
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
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
Less
More
- Posts: 59
- Thank you received: 0
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
Less
More
- Posts: 8
- Thank you received: 0
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!
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
Less
More
- Posts: 8
- Thank you received: 0
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
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
Copyright © 2024 DrayTek