Mailing List Archive

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")
}