Rsyslog Template for Fortigate Traffic


CREATE TABLE IF NOT EXISTS `Fortigate` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`log_time` datetime NOT NULL,
`log_id` int(10) unsigned NOT NULL,
`type` varchar(20) NOT NULL,
`subtype` varchar(20) NOT NULL,
`pri` varchar(20) NOT NULL,
`fwver` varchar(20) NOT NULL,
`vd` varchar(20) NOT NULL,
`msg` varchar(100) NOT NULL,
`action` varchar(20) NOT NULL,
`rem_ip` varchar(15) NOT NULL,
`loc_ip` varchar(15) NOT NULL,
`rem_port` int(10) unsigned NOT NULL,
`loc_port` int(10) unsigned NOT NULL,
`out_intf` varchar(20) NOT NULL,
`cookies` varchar(40) NOT NULL,
`user` varchar(20) NOT NULL,
`group` varchar(20) NOT NULL,
`xauth_user` varchar(20) NOT NULL,
`xauth_group` varchar(20) NOT NULL,
`vpn_tunnel` varchar(20) NOT NULL,
`status` varchar(20) NOT NULL,
`init` varchar(20) NOT NULL,
`mode` varchar(20) NOT NULL,
`dir` varchar(20) NOT NULL,
`stage` int(10) unsigned NOT NULL,
`role` varchar(20) NOT NULL,
`result` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

$template fortigate, "insert into Fortigate (log_time,log_id,type,subtype,pri,fwver,vd,msg,action,rem_ip,loc_ip,rem_port,loc_port,out_intf,cookies,user,group,xauth_user,xauth_group,vpn_tunnel,status,init,mode,dir,stage,role,result) values ('%timegenerated:::date-mysql%','%msg:R,ERE,1,BLANK:log_id=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:type=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:subtype=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:pri=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:fwver=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:vd=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:msg=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:action=([a-zA-Z0-9]+)--end%',%msg:R,ERE,1,BLANK:rem_ip=([0-9]+.[0-9]+.[0-9]+.[0-9]+)--end%,'%msg:R,ERE,1,BLANK:loc_ip=([0-9]+.[0-9]+.[0-9]+.[0-9]+)--end%',%msg:R,ERE,1,ZERO:rem_port=([0-9]+)--end%,%msg:R,ERE,1,ZERO:loc_port=([0-9]+)--end%,
'%msg:R,ERE,1,BLANK:out_intf=([a-zA-Z0-9_]+)--end%',%msg:R,ERE,1,BLANK:cookies=([a-zA-Z0-9]+)--end%,'%msg:R,ERE,1,BLANK:user=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:group=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:xauth_user=([a-zA-Z0-9]+)--end%',%msg:R,ERE,1,BLANK:xauth_group=([a-zA-Z0-9]+)--end%,
%msg:R,ERE,1,BLANK:vpn_tunnel=([a-zA-Z0-9]+)--end%,'%msg:R,ERE,1,BLANK:status=([a-zA-Z0-9]+)--end%',%msg:R,ERE,1,BLANK:init=([a-zA-Z0-9]+)--end%,'%msg:R,ERE,1,BLANK:mode=([a-zA-Z0-9]+)--end%',%msg:R,ERE,1,BLANK:dir=([a-zA-Z0-9]+)--end%,'%msg:R,ERE,1,BLANK:stage=([a-zA-Z0-9]+)--end%',%msg:R,ERE,1,BLANK:role=([a-zA-Z0-9]+)--end%,%msg:R,ERE,1,BLANK:result=([a-zA-Z0-9]+)--end%)", SQL



CREATE TABLE IF NOT EXISTS `Fortigate2` (
`ID` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`log_time` DATETIME NOT NULL ,
`log_id` INT( 10 ) UNSIGNED NOT NULL ,
`type` VARCHAR( 20 ) NOT NULL ,
`subtype` VARCHAR( 20 ) NOT NULL ,
`pri` VARCHAR( 20 ) NOT NULL ,
`vd` VARCHAR( 20 ) NOT NULL ,
`msg` VARCHAR( 100 ) NOT NULL ,
`action` VARCHAR( 20 ) NOT NULL ,
`user` VARCHAR( 20 ) NOT NULL ,
`status` VARCHAR( 20 ) NOT NULL ,
`reason` VARCHAR( 20 ) NOT NULL ,
`ui` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `ID` ))
ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =1;

$template fortigate2, "insert into Fortigate2 (log_time,log_id,type,subtype,pri,vd,msg,action,user,status,reason) values ('%timegenerated:::date-mysql%','%msg:R,ERE,1,BLANK:log_id=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:type=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:subtype=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:pri=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:vd=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:msg=([a-zA-Z0-9() .]+)--end%','%msg:R,ERE,1,BLANK:action=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:user="([a-zA-Z0-9]"+)--end%','%msg:R,ERE,1,BLANK:status=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:reason=([a-zA-Z0-9"_]+)--end%')", SQL