my inserts are giving me all blanks
attached is my rules file when I run my lognormalizer it looks like it works
correctly
I have tried in my rules file rule=: (space)
{ "dstserver": "0", "dstmac": "00:11:bc:5f:1c:1a", "masterdstmac":
"00:11:bc:5f:1c:1a", "dstdevtype": "Router\/NAT Device", "appcat": "unscanned",
"rcvdpkt": "1", "sentpkt": "1", "rcvdbyte": "84", "sentbyte": "84", "duration":
"60", "transport": "0", "transip": "74.115.158.236", "trandisp": "snat",
"srccountry": "Reserved", "dstcountry": "United States", "service": "PING",
"policytype": "policy", "policyid": "236", "action": "accept", "proto": "1",
"sessionid": "2959412196", "poluuid": "edd53f90-c83a-51ea-7fb9-4d2448507263",
"disintfrole": "lan", "dstinf": "VLAN2596", "dstip": "13.226.194.172",
"srcintfrole": "undefined", "srcintf": "rootprivate0", "srcip": "10.82.12.16",
"eventtime": "1612281701", "vd": "root", "level": "notice", "subtype":
"forward", "type": "traffic", "logid": "0000000013", "devid":
"FG3H0E5818903304", "devname": "CSPCFW01-M", "fwtime": "09:01:41", "date1":
"2021-02-02", "host": "cspcfw01_nas", "date": "Feb 2 20:57:31" }
{ "dstserver": "0", "dstmac": "00:11:bc:5f:1c:1a", "masterdstmac":
"00:11:bc:5f:1c:1a", "dstdevtype": "Router\/NAT Device", "appcat": "unscanned",
"rcvdpkt": "1", "sentpkt": "1", "rcvdbyte": "84", "sentbyte": "84", "duration":
"60", "transport": "0", "transip": "74.115.158.236", "trandisp": "snat",
"srccountry": "Reserved", "dstcountry": "United States", "service": "PING",
"policytype": "policy", "policyid": "236", "action": "accept", "proto": "1",
"sessionid": "2959412462", "poluuid": "edd53f90-c83a-51ea-7fb9-4d2448507263",
"disintfrole": "lan", "dstinf": "VLAN2596", "dstip": "99.84.203.154",
"srcintfrole": "undefined", "srcintf": "rootprivate0", "srcip": "10.82.12.1",
"eventtime": "1612281702", "vd": "root", "level": "notice", "subtype":
"forward", "type": "traffic", "logid": "0000000013", "devid":
"FG3H0E5818903304", "devname": "CSPCFW01-M", "fwtime": "09:01:42", "date1":
"2021-02-02", "host": "cspcfw01_nas", "date": "Feb 2 20:57:32" }
{ "dstserver": "0", "dstmac": "00:11:bc:5f:1c:1a", "masterdstmac":
"00:11:bc:5f:1c:1a", "dstdevtype": "Router\/NAT Device", "appcat": "unscanned",
"rcvdpkt": "1", "sentpkt": "1", "rcvdbyte": "84", "sentbyte": "84", "duration":
"60", "transport": "0", "transip": "74.115.158.236", "trandisp": "snat",
"srccountry": "Reserved", "dstcountry": "United States", "service": "PING",
"policytype": "policy", "policyid": "236", "action": "accept", "proto": "1",
"sessionid": "2959412772", "poluuid": "edd53f90-c83a-51ea-7fb9-4d2448507263",
"disintfrole": "lan", "dstinf": "VLAN2596", "dstip": "13.226.249.170",
"srcintfrole": "undefined", "srcintf": "rootprivate0", "srcip": "10.82.12.2",
"eventtime": "1612281703", "vd": "root", "level": "notice", "subtype":
"forward", "type": "traffic", "logid": "0000000013", "devid":
"FG3H0E5818903304", "devname": "CSPCFW01-M", "fwtime": "09:01:43", "date1":
"2021-02-02", "host": "cspcfw01_nas", "date": "Feb 2 20:57:33" }
template (name="database" type="string" option.sql="on" string="insert into
cspfirewall (date, time, devname, devid, logid, type, srcip, dstip, sessionid,
action, policyid, service, dstcountry, srccountry, transip, duration, sentbyte,
rcvdbyte, sentpkt, rcvdpkt) values ('%$!date%', '%$!time%', '%$!devname%',
'%$!devid%', '%$!logid%', '%$!type%', '%$!srcip%', '%$!dstip%',
'%$!sessionid%', '%$!action%', '%$!policyid%', '%$!service%', '%$!dstcountry%',
'%$!srccountry%', '%$!transip%', '%$!duration%', '%$!sentbyte%',
'%$!rcvdbytes%', '%$!sentpkt%', '%$!rcvdpkt%')")
if ($msg contains "policyid=236")
then {
action(type="mmnormalize" rulebase="/opt/rsyslog/newrule.rb")
action(type="ommysql" server="127.0.0.1" serverport="3306"
db="fortigatefw" uid="rsyslog" pwd="crazylog2018" template="database")
#action(type="omfile" File="/var/log/policy236.log")
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcintf=%srcintf:quoted-string%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstintf=%dstinf:quoted-string% dstintfrole=%disintfrole:quoted-string%
poluuid=%poluuid:quoted-string% sessionid=%sessionid:number%
proto=%proto:number% action=%action:quoted-string% policyid=%policyid:number%
policytype=%policytype:quoted-string% service=%service:quoted-string%
dstcountry=%dstcountry:quoted-string% srccountry=%srccountry:quoted-string%
trandisp=%trandisp:quoted-string% transip=%transip:ipv4%
transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
rcvdpkt=%rcvdpkt:number% appcat=%appcat:quoted-string%
dstdevtype=%dstdevtype:quoted-string% masterdstmac=%masterdstmac:quoted-string%
dstmac=%dstmac:quoted-string% dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
rcvdpkt=%rcvdpkt:number% appcat=%appcat:quoted-string%
sentdelta=%sentdelta:number% rcvddelta=%rcvddelta:number%
dstdevtype=%dstdevtype:quoted-string% masterdstmac=%masterdstmac:quoted-string%
dstmac=%dstmac:quoted-string% dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
rcvdpkt=%rcvdpkt:number% appcat=%appcat:quoted-string%
dstdevtype=%dstdevtype:quoted-string% masterdstmac=%masterdstmac:quoted-string%
dstmac=%dstmac:quoted-string% dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
appcat=%appcat:quoted-string% sentdelta=%sentdelta:number%
rcvddelta=%rcvddelta:number% dstdevtype=%dstdevtype:quoted-string%
masterdstmac=%masterdstmac:quoted-string% dstmac=%dstmac:quoted-string%
dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
appcat=%appcat:quoted-string% dstdevtype=%dstdevtype:quoted-string%
masterdstmac=%masterdstmac:quoted-string% dstmac=%dstmac:quoted-string%
dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
appcat=%appcat:quoted-string% sentdelta=%sentdelta:number%
rcvddelta=%rcvddelta:number% dstdevtype=%dstdevtype:quoted-string%
masterdstmac=%masterdstmac:quoted-string% dstmac=%dstmac:quoted-string%
dstserver=%dstserver:number%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcport=%srcport:number% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstport=%dstport:number% dstintf=%dstinf:quoted-string%
dstintfrole=%disintfrole:quoted-string% poluuid=%poluuid:quoted-string%
sessionid=%sessionid:number% proto=%proto:number% action=%action:quoted-string%
policyid=%policyid:number% policytype=%policytype:quoted-string%
service=%service:quoted-string% dstcountry=%dstcountry:quoted-string%
srccountry=%srccountry:quoted-string% trandisp=%trandisp:quoted-string%
transip=%transip:ipv4% transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
appcat=%appcat:rest%
rule=:%date:date-rfc3164% %host:word% date=%date1:date-iso%
time=%fwtime:time-24hr% devname=%devname:quoted-string%
devid=%devid:quoted-string% logid=%logid:quoted-string%
type=%type:quoted-string% subtype=%subtype:quoted-string%
level=%level:quoted-string% vd=%vd:quoted-string% eventtime=%eventtime:number%
srcip=%srcip:ipv4% srcintf=%srcintf:word%
srcintfrole=%srcintfrole:quoted-string% dstip=%dstip:ipv4%
dstintf=%dstinf:quoted-string% dstintfrole=%disintfrole:quoted-string%
poluuid=%poluuid:quoted-string% sessionid=%sessionid:number%
proto=%proto:number% action=%action:quoted-string% policyid=%policyid:number%
policytype=%policytype:quoted-string% service=%service:quoted-string%
dstcountry=%dstcountry:quoted-string% srccountry=%srccountry:quoted-string%
trandisp=%trandisp:quoted-string% transip=%transip:ipv4%
transport=%transport:number% duration=%duration:number%
sentbyte=%sentbyte:number% rcvdbyte=%rcvdbyte:number% sentpkt=%sentpkt:number%
appcat=%appcat:rest%
_______________________________________________
rsyslog mailing list
https://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of
sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE
THAT.