WFilter NGF的数据库结构

1 上网记录（wfilter.db）

1.1 网页浏览websurflog

create table IF NOT EXISTS websurflog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
host            varchar(200),
url             varchar(512),
webtitle        varchar(512),
tls		char(1),
useragent	varchar(512),
remark		varchar(100),
targetip	varchar(50)
)

1.2 网页粘贴webpostlog

create table IF NOT EXISTS webpostlog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
host            varchar(200),
webtitle        varchar(512),
postsize        varchar(20),
posturl         varchar(512),
fileid          varchar(50),
refer		varchar(512),
useragent	varchar(512),
tls		char(1),
remark		varchar(100),
targetip	varchar(50)
)

1.3 邮件收发maillog

create table IF NOT EXISTS maillog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
direction       char(1) NOT NULL,
fromid          varchar(100),
toid            varchar(500),
subject         varchar(500),
messageid       varchar(200),
fileid          varchar(50),
proto		varchar(20),
remark		varchar(100),
targetip	varchar(50)
)

1.4 ipmac历史ipmaclog

create table IF NOT EXISTS ipmaclog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
hostname	varchar(50)
)

1.5 文件传输ftplog

create table IF NOT EXISTS ftplog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
direction       char(1) NOT NULL,
type		varchar(10) NOT NULL,
target		varchar(200),
filesize	varchar(20),
refer		varchar(512),
filename	varchar(512),
title		varchar(512),
useragent	varchar(512),
fileid		varchar(50),
remark		varchar(100),
targetip	varchar(50)
)

1.6 ftp/telnet历史sessionslog

create table IF NOT EXISTS sessionslog
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
mac		varchar(20),
happentime      varchar(20),
direction       char(1) NOT NULL,
proto		varchar(20),
target		varchar(200),
cmd		varchar(512),
remark		varchar(100)
)

2 统计报表（report.db）

2.1 协议统计protocolreport

共有两个表格：protocolreport（历史协议统计）和protocolreport_today（当日协议统计）

create table IF NOT EXISTS protocolreport
(
ip              varchar(50),
group1		varchar(200),
acct		varchar(200),
happendate      varchar(10),
catagory        varchar(10),
protocol	varchar(20),
uptraffic       varchar(20),
downtraffic     varchar(20),
during          varchar(20)
)

2.2 网站统计webreport

有两个表格：webreport（历史网站统计）和webreport_today（当日网站统计）

create table IF NOT EXISTS webreport
(
ip		varchar(50),
group1		varchar(200),
acct		varchar(200),
happendate	varchar(10),
host            varchar(100),
category1	varchar(10),
category2	varchar(10),
visitcnt	varchar(20),
uptraffic       varchar(20),
downtraffic     varchar(20),
during          varchar(20)
)

2.3 小时统计hourreport

create table IF NOT EXISTS hourreport
(
happendate      varchar(10),
hour		char(2),
uptraffic       varchar(20),
downtraffic     varchar(20)
)

3 运营管理（ISP.db）

3.1 账号统计acctreport

create table IF NOT EXISTS acctreport
(
happendate      varchar(10),
acct		varchar(200),
uptraffic       varchar(20),
downtraffic     varchar(20)
)

3.2 IP统计ipreport

create table IF NOT EXISTS ipreport
(
happendate      varchar(10),
ip		varchar(50),
uptraffic       varchar(20),
downtraffic     varchar(20)
)