MonitorWare Knowledge Base

Hi all,

i want use rsyslog with mysql to store syslog events from a netasq firewall.
All work great if i use the default template.
I try to make one in order to extract data correctly but i have some problems.

this what i made :

rsyslog.conf :
#################
#### MODULES ####
#################

#$ModLoad imuxsock # provides support for local system logging
#$ModLoad imklog # provides kernel logging support (previously done by rklogd)
$ModLoad immark # provides --MARK-- message capability
$ModLoad ommysql.so #load the mysql output driver

# provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# provides TCP syslog reception
#$ModLoad imtcp
#$InputTCPServerRun 514


###########################
#### GLOBAL DIRECTIVES ####
###########################

#
$FileOwner root
$FileGroup adm
$FileCreateMode 0640

#
# Include all config files in /etc/rsyslog.d/
#
$IncludeConfig /etc/rsyslog.d/*.conf


###################
#### templates ####
###################
$AllowedSender UDP, 192.168.145.254
$template F50,"insert into SystemEvents (ReceivedAt, DeviceReportedTime, FromHost, srcip, srcport, dstip, dstport, proto, action) values ('%timereported:::date-mysql%', '%HOSTNAME%', '%msg:F,20:11%', '%msg:F,20:12%', '%msg:F,20:14%', '%msg:F,20:15%', '%msg:F,20:9%', '%msg:F,20:17%')",sql
*.* >localhost,Syslog,syslogfeeder,feeder;F50


and this the data receive from the firewall :

id=firewall time="2008-07-31 15:09:26" fw="F50-EE592000800808" tz=+0000 startime="2008-07-31 15:09:26" pri=1 srcif="Ethernet0" srcifname="In" ipproto=udp proto=netbios-dgm src=192.168.2.150 srcport=138 srcportname=netbios-dgm dst=192.168.2.255 dstport=138 dstportname=netbios-dgm action=block msg="IP address spoofing (type=1)" class=protocol classification=0 alarmid=1 logtype="alarm"





I search a solution in the forum but i don't find an explicit one.

Thank you for your help.

Regards

• Custom written rsyslog.conf?
• Maintenance Contract?
• Installation support?

Hi, what I see looks good, but you have not told us what your actual problem is Please elaborate what does not work as you expect it.

hi,

ok, i forget the most importatant,

the problem is that i have this error log : db error (1136): Column count doesn't match value count at row 1

and field in database are not populate.

regards

fredg wrote:the problem is that i have this error log : db error (1136): Column count doesn't match value count at row 1

I think the message is correct: I just counted, and there are 9 column names, but only 8 values provided

Rainer

just another info:

this the fields of my database :


ID

CustomerID

ReceivedAt

DeviceReportedTime

Facility

Priority

FromHost

Message

NTSeverity

Importance

EventSource

EventUser

EventCategory

EventID

EventBinaryData

MaxAvailable

CurrUsage

MinUsage

MaxUsage

InfoUnitID

SysLogTag

EventLogType

GenericFileName

SystemID

srcip

destip

action

srcport

destport

proto

you need to fix your sql statement by providing the (correct) 9th value...

OK if i understand correctly this is the correspondance :

ReceivedAt
DeviceReportedTime %timereported:::date-mysql%
FromHost %HOSTNAME%
srcip %msg:F,20:11%
srcport %msg:F,20:12%
destip %msg:F,20:14%
dstport %msg:F,20:15%
proto %msg:F,20:10%
action %msg:F,20:17%
Message %msg:F,20:18%

what is the corrct value for ReceiveAT ???

regards

First of all, you can simple leave the field out if you don't need it. If you would like to include it, using %timegenerated:::date-mysql% is probably a good choice

All properties can be found here:

http://www.rsyslog.com/doc-property_replacer.html

Rainer

Thk's,

i have now a new problem, when i restart rsyslog to take the good rsyslog.conf, i receive this error :
**FIELD NOT FOUND**

this value is put in all my fields.

Does it means that it can't parse the message because the separator are "space" ??

regards.

Ps : many thanks to Rainer for helping me

args, I didn't notice that before. The chracter code is decimal! Ascii SP is hex 20, but 32 in decimal. So you need to replace the 20 by 32.

Also, I noticed that some "fields" in the message contain spaces to separate a date and a timestamp. Note that this counts as TWO separate fields!

Let us know if that helped!

Thanks,
Rainer

OK this works fine but you're rught the nulber of field are now incorrect.

Does i can says to not count the space beetwen "" for exemple the space in the msg field??

Or does i need to use the "FromChar and toChar" variable.

Regards

let me provide a sample. This message:

Code: Select all

id=firewall time="2008-07-31 15:09:26" fw="F50-EE592000800808"

Field assignment (based on F,32) will be:
1: id=firewall
2: time="2008-07-31
3: 15:09:26"
4: fw="F50-EE592000800808"

The property replacer does not care about name value pairs. It cares about fields, being delimited by something, this something being SP in your case. As the format seems to be consistent, I think you can work around this "problem" quite easily.

Rainer

OK thk's for your help, i will do differents tests.

regards