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 ;


MonitorWare Schema


CREATE TABLE SystemEvents
(
ID int unsigned not null auto_increment primary key,
CustomerID bigint,
ReceivedAt datetime NULL,
DeviceReportedTime datetime NULL,
Facility smallint NULL,
Priority smallint NULL,
FromHost varchar(60) NULL,
Message text,
NTSeverity int NULL,
Importance int NULL,
EventSource varchar(60),
EventUser varchar(60) NULL,
EventCategory int NULL,
EventID int NULL,
EventBinaryData text NULL,
MaxAvailable int NULL,
CurrUsage int NULL,
MinUsage int NULL,
MaxUsage int NULL,
InfoUnitID int NULL ,
SysLogTag varchar(60),
EventLogType varchar(60),
GenericFileName VarChar(60),
SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
ID int unsigned not null auto_increment primary key,
SystemEventID int NULL ,
ParamName varchar(255) NULL ,
ParamValue text NULL
);


參考文章：
rsyslogの設定@CentOS5.3