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


Rsyslog Template for NetScreen Traffic


$template Netscreen, "insert into Netscreen (ReceivedAt,DeviceReportedTime,Facility,Severity,Priority,FromHost,InfoUnitID,SyslogTag,Device_ID,Start_Time,Duration ,Policy_ID ,Service,Proto,Src_Zone,Dst_Zone,Action,Sent ,Rcvd,Src_IP ,Src_Port,Dst_IP ,Dst_Port,Src_Xlated_IP ,Port,Session_ID) values ('%timegenerated:::date-mysql%','%timereported:::date-mysql%',%syslogfacility%,%syslogseverity%,%syslogpriority%,'%fromhost-ip%',%iut%,'%syslogtag%','%msg:R,ERE,1,BLANK:device_id=([a-zA-Z0-9]+)--end%','%msg:R,ERE,1,BLANK:start_time="(.+)"--end%',%msg:R,ERE,1,ZERO:duration=([0-9]+)--end%,%msg:R,ERE,1,ZERO:policy_id=([0-9]+)--end%,'%msg:R,ERE,1,BLANK:service=([a-zA-Z0-9_]+)--end%',%msg:R,ERE,1,ZERO:proto=([0-9]+)--end%,'%msg:R,ERE,1,BLANK:src zone=([a-zA-Z0-9_]+)--end%','%msg:R,ERE,1,BLANK:dst zone=([a-zA-Z0-9_]+)--end%','%msg:R,ERE,1,BLANK:action=([a-zA-Z0-9_]+)--end%',%msg:R,ERE,1,ZERO:sent=([0-9]+)--end%,%msg:R,ERE,1,ZERO:rcvd=([0-9]+)--end%,'%msg:R,ERE,1,BLANK:src=([0-9]+.[0-9]+.[0-9]+.[0-9]+)--end%',%msg:R,ERE,1,ZERO:src_port=([0-9]+)--end%,'%msg:R,ERE,1,BLANK:dst=([0-9]+.[0-9]+.[0-9]+.[0-9]+)--end%',%msg:R,ERE,1,ZERO:dst_port=([0-9]+)--end%,'%msg:R,ERE,1,BLANK:src-xlated ip=([0-9]+.[0-9]+.[0-9]+.[0-9]+)--end%',%msg:R,ERE,1,ZERO:[^_]port=([0-9]+)--end%,%msg:R,ERE,1,ZERO:session_id=([0-9]+)--end%)", SQL


Schema for NetScreen Traffic


CREATE TABLE `Netscreen` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ReceivedAt` datetime DEFAULT NULL,
`DeviceReportedTime` datetime DEFAULT NULL,
`Facility` tinyint(3) unsigned DEFAULT NULL,
`Severity` int(10) unsigned DEFAULT NULL,
`Priority` tinyint(3) unsigned DEFAULT NULL,
`FromHost` varchar(60) DEFAULT NULL,
`InfoUnitID` int(10) unsigned DEFAULT NULL,
`SysLogTag` varchar(60) DEFAULT NULL,
`Device_ID` varchar(60) DEFAULT NULL,
`Start_Time` datetime DEFAULT NULL,
`Duration` int(10) unsigned DEFAULT NULL,
`Policy_ID` tinyint(3) unsigned DEFAULT NULL,
`Service` varchar(60) DEFAULT NULL,
`Proto` tinyint(3) unsigned DEFAULT NULL,
`Src_Zone` varchar(60) DEFAULT NULL,
`Dst_Zone` varchar(60) DEFAULT NULL,
`Action` varchar(30) DEFAULT NULL,
`Sent` int(10) unsigned DEFAULT NULL,
`Rcvd` int(10) unsigned DEFAULT NULL,
`Src_IP` varchar(15) DEFAULT NULL,
`Dst_IP` varchar(15) DEFAULT NULL,
`Src_Port` smallint(5) unsigned DEFAULT NULL,
`Dst_Port` smallint(5) unsigned DEFAULT NULL,
`Src_Xlated_IP` varchar(15) DEFAULT NULL,
`Port` smallint(5) unsigned DEFAULT NULL,
`Session_ID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;