Mailing List Archive

mysql template and rule issues
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")
Re: mysql template and rule issues [ In reply to ]
output from rsyslog -dn

6006.670358749:main Q:Reg/w0 : ruleset.c: processBATCH: next msg 0: <189>date=2021-02-03 time=12:24:09 devname="CSPCFW01-M" devid="FG3H0E5818903304" logid="0000000020" type="traffic" subtype="forw
6006.670363987:main Q:Reg/w0 : rainerscript.c: IF
6006.670371444:main Q:Reg/w0 : rainerscript.c: var 'msg'
6006.670381727:main Q:Reg/w0 : rainerscript.c: CONTAINS
6006.670390139:main Q:Reg/w0 : rainerscript.c: string 'policyid=236'
6006.670403747:main Q:Reg/w0 : rainerscript.c: eval expr 0x55af717e8b10, type 'CMP_CONTAINS'
6006.670407982:main Q:Reg/w0 : rainerscript.c: eval expr 0x55af717e8d40, type 'V'
6006.670413764:main Q:Reg/w0 : rainerscript.c: rainerscript: (string) var 1: ' time=12:24:09 devname="CSPCFW01-M" devid="FG3H0E5818903304" logid="0000000020" type="traffic" subtype="forward" level="notice" vd="root" eventtime=1612380249 srcip=10.81.137.123 srcport=32688 srcintf="rootprivate0" srcintfrole="undefined" dstip=52.73.23.137 dstport=3199 dstintf="VLAN2596" dstintfrole="lan" poluuid="0348a7dc-978f-51e9-d448-7e7317e42667" sessionid=2784514197 proto=6 action="accept" policyid=172 policytype="policy" service="DigiRM" dstcountry="United States" srccountry="Reserved" trandisp="snat" transip=74.115.158.222 transport=32688 duration=921807 sentbyte=13176902 rcvdbyte=7027271 sentpkt=62059 rcvdpkt=61392 appcat="unscanned" sentdelta=2065 rcvddelta=916 dstdevtype="Router/NAT Device" masterdstmac="00:11:bc:5f:1c:1a" dstmac="00:11:bc:5f:1c:1a" dstserver=0'

________________________________
From: rsyslog <rsyslog-bounces@lists.adiscon.com> on behalf of Jason Prouty via rsyslog <rsyslog@lists.adiscon.com>
Sent: Tuesday, February 2, 2021 2:52:25 PM
To: rsyslog@lists.adiscon.com <rsyslog@lists.adiscon.com>
Cc: Jason Prouty <jprouty@cctus.com>
Subject: [rsyslog] mysql template and rule issues

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")
_______________________________________________
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.
Re: mysql template and rule issues [ In reply to ]
I also see this is my debug output

: parser.c: msg parser: flags 70, from '~NOTRESOLVED~', msg '<189>date=2021-02-03 time=13:10:23 devname="CSPCFW01-M" devi'
________________________________
From: Jason Prouty <jprouty@cctus.com>
Sent: Wednesday, February 3, 2021 2:26 PM
To: rsyslog-users <rsyslog@lists.adiscon.com>
Cc: Jason Prouty <jprouty@cctus.com>
Subject: RE: mysql template and rule issues


output from rsyslog -dn


6006.670358749:main Q:Reg/w0 : ruleset.c: processBATCH: next msg 0: <189>date=2021-02-03 time=12:24:09 devname="CSPCFW01-M" devid="FG3H0E5818903304" logid="0000000020" type="traffic" subtype="forw

6006.670363987:main Q:Reg/w0 : rainerscript.c: IF
6006.670371444:main Q:Reg/w0 : rainerscript.c: var 'msg'
6006.670381727:main Q:Reg/w0 : rainerscript.c: CONTAINS
6006.670390139:main Q:Reg/w0 : rainerscript.c: string 'policyid=236'
6006.670403747:main Q:Reg/w0 : rainerscript.c: eval expr 0x55af717e8b10, type 'CMP_CONTAINS'
6006.670407982:main Q:Reg/w0 : rainerscript.c: eval expr 0x55af717e8d40, type 'V'
6006.670413764:main Q:Reg/w0 : rainerscript.c: rainerscript: (string) var 1: ' time=12:24:09 devname="CSPCFW01-M" devid="FG3H0E5818903304" logid="0000000020" type="traffic" subtype="forward" level="notice" vd="root" eventtime=1612380249 srcip=10.81.137.123 srcport=32688 srcintf="rootprivate0" srcintfrole="undefined" dstip=52.73.23.137 dstport=3199 dstintf="VLAN2596" dstintfrole="lan" poluuid="0348a7dc-978f-51e9-d448-7e7317e42667" sessionid=2784514197 proto=6 action="accept" policyid=172 policytype="policy" service="DigiRM" dstcountry="United States" srccountry="Reserved" trandisp="snat" transip=74.115.158.222 transport=32688 duration=921807 sentbyte=13176902 rcvdbyte=7027271 sentpkt=62059 rcvdpkt=61392 appcat="unscanned" sentdelta=2065 rcvddelta=916 dstdevtype="Router/NAT Device" masterdstmac="00:11:bc:5f:1c:1a" dstmac="00:11:bc:5f:1c:1a" dstserver=0'



________________________________
From: rsyslog <rsyslog-bounces@lists.adiscon.com> on behalf of Jason Prouty via rsyslog <rsyslog@lists.adiscon.com>
Sent: Tuesday, February 2, 2021 2:52:25 PM
To: rsyslog@lists.adiscon.com <rsyslog@lists.adiscon.com>
Cc: Jason Prouty <jprouty@cctus.com>
Subject: [rsyslog] mysql template and rule issues

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")
_______________________________________________
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.