I'm working on centralizing my logs to a syslog server and will have a fortigate and Windows servers (2016) sending syslogs to a single linux server running rsyslog and store them in mysql. I configured the fortigate to send syslogs, but the rows in mysql from the fortigate do not have the same field names, so the data is not recorded in all of the columns in mysql.
For example, a syslog from the linux server contains the following:
Nov 2 11:15:25 fps-linux-01 kernel: [3716995.353386] audit: type=1400 audit(1635869725.570:17): apparmor="STATUS" operation="profile_load" profile="unconfined" name="/usr/sbin/tcpdump" pid=25552 comm="apparmor_parser"
Which results in the database fields "FromHost" to have a value of "fps-linux-01". But, an entry from the fortigate says:
Nov 3 09:04:46 . date=2021-11-03 time=09:03:56 devname="Frederick-200F" devid="FG200FT920907269" eventtime=1635948236739611958 tz="-0500" logid="0000000013" type="traffic" subtype="forward" level="notice" vd="root" srcip=xxxxxxx srcport=59142 srcintf="lan" srcintfrole="lan" dstip=172.98.64.173 dstport=443 dstintf="port16" dstintfrole="wan" srccountry="Reserved" dstcountry="United States" sessionid=408405616 proto=6 action="start" policyid=8 policytype="policy" poluuid="2f7b990a-a844-51eb-73cf-8a7dcbe631da" policyname="Filter - Staff" user="xxxxxx" group="xxxxxxxx" authserver="xxxx" service="HTTPS" trandisp="snat" transip=xxxxxxxxx transport=59142 duration=0 sentbyte=0 rcvdbyte=0 sentpkt=0 rcvdpkt=0 appcat="unscanned"
From this, FromHost is blank in the DB because (I assume) the fortigate entry has a period in the hostname field.
Here's the relative configs:
$template RemoteLogs,"/var/log/%HOSTNAME%/%PROGRAMNAME%.log"
. ?RemoteLogs & ~
module (load="ommysql")
*.* action(type="ommysql" [Connection info])
I've seen that I can get the properties of the message and format it as needed, but the execution is beyond me, at the moment (meaning, I don't know how to implement it).
Here's my best guess at the moment, please correct anything that's wrong or missing:
template(name="RSYSLOG_StdFmt" type="list") {
property(name="devname")
constant(value=" ")
property(name="type")
constant(value=": ")
*** Place Rest of message here ****
}
Is it possible to have the rest of the data after the "type" field simply regurgitated to the output from that point on? If so, how do I do that?
That should put the entry into the log file similar to syslog native. Will rsyslog-mysql pick that up and put it in the database correctly, or do I need to make a specific template for mysql, too? If so, how do I apply my template to only the logs coming from fortigate and not to local entries or ones from the Windows servers?
