- Overview.
- Terms and definitions.
- Tables description.
- Database structure.
- Building traffic counters.
- Notes.
1. Overview
Flowc package begin with version 1.4.3 contains integrated traffic billing system oriented for Internet Service Providers. For it activation you need to specify --with-web=... option in configure script. Billing system contains:
- PHP interface for administrator (directory admin/).
- PHP interface for customers (directory customers/).
- Report generator report.pl. It generates reports about customers traffic, send their via email and storers in archive (MySQL database).
- flowd and loader programs. They use at atart time information from billing related MySQL tables.
Traffic billing based on traffic counters. But for flexibility, customers traffic counters build from MySQL database.
2. Terms and definitions
Traffic zone - Certain traffic type. For example foreing, peering, local,
summary, etc.
Tariff model - Set of traffic zones with additional parameters. For example:
Model N1: monthly fee $50
1Gb foreing traffic, overlimit $0.05/Gb
5Gb peering traffic, overlimit $0.01/Gb
local traffic free of charge
Model N2: monthly fee $100
3Gb foreing traffic, overlimit $0.04/Gb
5Gb peering traffic, overlimit $0.01/Gb
local traffic free of charge
3. Tables description
routers - routers table.
router_id - router id (primary key)
hostname - router name (may be arbitrary, flowd use
address field for access to router)
last_file_offset - pointer to last loaded in to database record
from dump file
last_time_access - time of load last record from dump file
dump_file - routers dump file name
address - routers IP address used as src address in
netflow packets.
By default it is nearest to netflow collector
interface address, or address of interface
specified in
"ip flow-export source <interface>" command
community - community string for SNMP access to router
flow_cache_size - size (in records) of the routers flow cahe
allocated by flowd.
rotate_level - dump file rotate level (dump files max number
file.o, file.1, file.2 ... after
flowd -k rotate)
auto_rebuild - reserved for future versions
source - If source set to 'File', all described above
routers parameters got from flowd.conf during
flowd start. There is no sence to modify their
because it will be reset to flowd.conf values
after next flowd restart.
If source field set to 'SQL', then this router
was added to database via web interface (or
manually), and you can change described above
parameters.
interfaces - routers interfaces table.
if_id - interface id (primary key)
router_id - link to router from routers table
description - interface name (Serial0, FastEthernet0/1,...)
if_alias - description (description command on interface)
type - interface type ('Internal' or 'External').
It used for minimization stored netflow
information. All packets switched between pair
of Internal interfaces are ignored by flowd.
All packets switched between Internal-External
or External-External interfaces processed by
flowd. If corresponding to this interface
router configured from flowd.conf
(routers.router_id='File') then interface type
set automatically during flowd startup,
according to routers configuration in
flowd.conf. If routers.router_id='SQL' then
interface type reads from 'interfaces' table.
It can be set previously via web interface or
manually.
flows - default flows table.
If flowd.conf global option 'load_default'
set to 'on', then all traffic between
interfaces Internal-External or
External-External will be loaded to database
in default flows table.
flows_ - traffic counters flows tables.
If flowd.conf traffic ounter has option 'load'
then loader program creates
flows_<traf_counter_name>
table and load to it traffic satisfying
traffic counter filter list. If flowd.conf
global option 'load_tc' set to 'on', then
mentioned above traffic will be loaded in
default flows table 'flows' too.
If traffic counter configured from MySQL
(customers traffic counter) then option 'load' assumed by default.
flows-* - flows tables after rotate (flowd -k rotate_db).
All flows tables have same structure:
router_id - router id from 'routers' table
in_if_id - input interface interface id from 'interfaces'
table.
out_if_id - output interface interface id from
'interfaces' table.
timestamp - flows start time
src_addr - source IP address
dst_addr - destination IP address
d_pkts - packet number in flow
d_octets - bytes number in flow
protocol - protocol (ICMP, TCP, UDP, GRE, ...)
src_port - source port for TCP/UDP flows
dst_port - destination port for TCP/UDP flows
src_as - source AS number
dst_as - destination AS number
bill_direction - Defined only for customers flows tables. In
default 'flows' table it absent. This field
defines billing traffic direction. see
traf_zones table description, split_traf field
for additional information about billing
traffic direction.
Depend of routers netflow configureation
"ip flow-export version 5 origin-as" or
"ip flow-export version 5 peer-as" router use source/destination
traffic AS numbers or your peers AS numbers (for exaplme your ISP AS
number) respectively. If router is not BGP enabled or source/destination
address is absent in BGP table, or source/destination address belong
to your AS (and presented in BGP table with empty AS-path:
"show ip bgp reg ^$") then zero value will be used in src_as/dst_as.
customers - customers table.
customer_id - customers id (primary key)
name - customers name. This field used for customers traffic
counters building, and must contains only characters
ÓÉÍ×ÏÌÙ [a-zA-Z0-9] and symbol '-'
email - Email address (or addresses divided by comma) which
used for sending reports to customer
tm_id - customer tariff model id (see table tafiff_models)
password - password for reports review via web
valid - active (generate reports and traffic counters) or
inactive customer.
detail_report - if it turn to 'Y', the customer report will be
contain detail part in addition to summary part.
Detail part detalize traffic flows and include
src/dst addresses, src/dst ports, protocol and
bytes/packets counters.
juridical - additional information parameters
comments - ...
address - ...
phone - ...
customer_params - customer parameters
id - parameter id (primary key)
customer_id - customer id from 'customers' table
if_id - interface id customers interface from 'interfaces'
table. If if_id equal zero this means that customer
interface is not defined.
addr - customers address range, start address.
end_addr - customers address range, last adress. If it empty
(0.0.0.0) then customer has only one address in
address range.
asnum - customers AS. By default value (65536) means any AS
number (like * in flowd.conf AS numbers)
Parameters if_id, addr-end_addr, asnum use for building customers
traffic counters. Those parameters substitute in traffic patterns
generated from tables traf_zones, traf_patterns, tm_tz_lnk and
tariff_models (see description later). At the least one parameter
must be defined unambiguously
(if_id != 0 || addr != 0.0.0.0 || asnum != 65536).
traf_zones - traffic zones.
id - zone id (primary key)
name - zone name
comment - comment
split_traf - If it turn to 'on', the customers detail report for
this traffic zone will be splited in to two
subreports, for ech billing direction, incoming and
outgoing. Billing traffic direction (don't confuse
with real interface traffic direction) calculates by
the loader program on the assumption of interface
traffic direction (In, Out) and interface type
(Client, External, Internal).
traf_patterns - traffic patterns used for customers traffic counters
generation. In other words it is traffic zones configuration.
id - pattern id (primary key)
zone_id - zone id from traf_zones table
if_id - interface id from interfaces table
direction - traffic direction
addr - address range. Start address. Depends on traffic
direction and if_type this fiend will be used as
src_addr or as dst_addr during customers traffic
counters building. (see p5 for details.)
end_addr - last address in range.
asnum - AS number. Value 65536 means any AS number.
asset_id - id AS_set. Value 0 means that AS_Set is not defined
and asnum field used instead. asnum and asset_id
fields can not be defined simultaneously
(asnum != 65536 && asset_id != 0)
src_port - source port range. start port number.
end_src_port - source port range. last port number.
dst_port - destination port range. start port number.
end_dst_port - destination port range. last port number.
If end_src_port/end_dst_port equal zero then
only src_port/dst_port used respectively.
Zero value in src_port or dst_port means any source
or destination port numbers.
protocol - protocol
match_action - 'STOP' value means ignorring this traffic or in other
words interrupt flow processing by traffic counter
without incrementing bytes and packets counters in
case if flow parameters hit to traffic counter filter
condition.
'PASS' value (by default) means account traffic in
traffic counter
if_type - interface type. 'External' means interface to provider
or external peer, 'Internal' means interface to
your internal network, 'Client' means customers
interface.
seq_num - sequence number. String from this table for traffic
counters building will be used in sequence number
order.
tariff_models - tariff models table.
tm_id - model id (primary key)
name - model name.
fee - monthly fee.
comments - comment.
tm_tz_lnk - link table between tariff_models and traf_zones tables.
id - record id (primary key).
tm_id - tariff model id from tariff_models table.
zone_id - zone id from traf_zones table.
costGb - 1 Gb traffic cost for traffic zone in case if traffic
overlimit limitGb value.
limitGb - number of Gb traffic included in monthly fee
as_sets - AS groups table (AS_Sets).
asset_id - AS set id (primary key)
name - AS_Set name
as_lists - AS numbers lists table
asset_id - AS_Set id from as_sets table
as_num - AS number belongs to specified asset_id
reports - customers reports archive
id - report id (primary key).
customer_id - customer id from customers table.
date - report generation date.
type - report type (daily, weekly, monthly, other)
report - report text.
Customer reports generates by report.pl script, and in case of using
'-i' option in addition to send via Email to address from customers
table, reports stores in database archive. Customer has access
to own report archive via web interface.
4. Database structure
+-------------+
| reports |
+=============+
| customer_id |-----+
| ... | |
+-------------+ |
|
+-----------+ +------------+ +-----------------+ |
| routers | | interfaces | | customer_params | +-------------+ |
+===========+ +============+ +=================+ | customers | |
| router_id |<-- | if_id |<-+----| if_id | +=============+ |
| ... | |-<-| router_id | | | customer_id |<---| customer_id |-<---+
+-----------+ | | ... | | | ... | | ... |
| +------------+ | +-----------------+ + tm_id |->---+
| | +-------------+ |
^ ^ |
+-----------+ | | +---------------+ +------------+ |
| flows* | | | | traf_patterns | | traf_zones | |
+===========+ | | +===============+ +============+ |
| router_id |--+ | | zone_id |----->| id |<---+ |
| in_if_id |--\ | | asset_id |<-+ | .... | | |
| ... | >--->--------------+-<--| if_id | | +------------+ | |
| out_if_id |--/ | ... | | | |
+-----------+ +---------------+ ^ | |
| ^ |
+---------->------------+ | |
| | |
+----------+ +----------+ | +---------------+ +-----------+ | |
| as_lists | | as_sets | | | tariff_models | | tm_tz_lnk | | |
+==========+ +==========+ | +===============+ +===========+ | |
| asset_id |-->--| asset_id |--->--+ +--->| tm_id |----->| tm_id | | |
| as_num | | .... | | | ... | | zone_id |->-+ |
+----------+ +----------+ | +---------------+ | ... | |
^ +-----------+ |
| |
+----------------------<----------------------+
5. Building traffic counters.
Customers traffic counters builds from MySQL database in the following way:
- as counter name use <customer-name>_<traffic-zone>
- customers traffic will be stored in database by loader program in separate table with name: flows_<customer-name>_<traffic-zone>
- traffic filter list (filter rules) generates from some tables. Fist of all tables traf_patterns, traf_zones, tm_tz_lnk, tariff_models, customers uses for building traffic pattern. This pattern may has undefined address part, interface or AS. Undefined parameters takes from customer_params table and used in pattern for building traffic counter not diffrent from flowd.conf traffic counter. Depends on interface type (External - your ISP or external peer, Internal - link inside your network or Client - customers interface) and traffic direction (In, Out) in table traf_patterns, customers parameters from customer_params table will be used in src part of traffic counter (src_addr, src_if_id, src_as, src_asset, etc) or dst part. The second part of traffic counter will be always undefined (any address, any any AS, ...). Notice, after traffic patterns and customers parameters superposition, finally the interface, address and AS part traffic counters part must be defined obvious. For example, if in traffic pattern interface is not defined (if_id=0), then customers parameters must define customer interface obvious. Or if pattern has undefined address part, then customer parameters must contains customers address range. The main SQL query for building traffic patterns (see src/mysql.c) listed bellow:
SELECT c.customer_id, c.name, tp.zone_id, tz.name,
tp.if_id, tp.if_type, tp.direction, tp.addr, tp.end_addr,
tp.asnum, tp.asset_id, as_sets.name, tp.src_port,
tp.end_src_port, tp.dst_port, tp.end_dst_port, tp.protocol,
tp.match_action, tp.seq_num
FROM customers c, traf_zones tz, tariff_models tm,
traf_patterns tp, tm_tz_lnk lnk
LEFT JOIN as_sets on tp.asset_id=as_sets.asset_id
WHERE c.valid='Y'
AND c.tm_id = tm.tm_id
AND tm.tm_id = lnk.tm_id
AND lnk.zone_id = tz.id
AND tz.id = tp.zone_id
ORDER BY 1,3,19
6. Notes:
- Don't use in flowd.conf traffic counters with names identical generated from MySQL database.
- Don't modify manually netflow database, it may has unpredictable consequences because logical integrity may be lost. Use administrators web interface for billing configuration.
- All database changes comes in to effect after flowd restart. (flowd -k shutdown; flowd)
- Before flowd restart, make sure in generated from MySQL database traffic counters correctness. Run "counter -d" command for view current customers counters configuration.
- For customers reports generation use cron. The example of /etc/crontab file listed bellow:
# /etc/crontab - root's crontab for FreeBSD
#
# $FreeBSD: src/etc/crontab,v 1.21.2.1 2000/09/20 02:32:51 jkh Exp $
#
SHELL=/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/cisco/bin
HOME=/var/log
*/5 * * * * root loader
00 06 * * * root analyser.pl d
00 05 * * 6 root analyser.pl w
00 03 1 * * root analyser.pl m
39 01 * * * root report.pl -m -a /etc/advert -s now -d 1 -i
#
55 23 31 1 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 28 2 * root report.pl -m -a /etc/advert -s now -d 28 -i; flowd -k rotate_db; flowd -k rotate;
55 23 29 2 * root report.pl -m -a /etc/advert -s now -d 29 -i; flowd -k rotate_db; flowd -k rotate;
55 23 31 3 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 30 4 * root report.pl -m -a /etc/advert -s now -d 30 -i; flowd -k rotate_db; flowd -k rotate;
55 23 31 5 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 30 6 * root report.pl -m -a /etc/advert -s now -d 30 -i; flowd -k rotate_db; flowd -k rotate;
55 23 31 7 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 31 8 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 30 9 * root report.pl -m -a /etc/advert -s now -d 30 -i; flowd -k rotate_db; flowd -k rotate;
55 23 31 10 * root report.pl -m -a /etc/advert -s now -d 31 -i; flowd -k rotate_db; flowd -k rotate;
55 23 30 11 * root report.pl -m -a /etc/advert -s now -d 30 -i; flowd -k rotate_db; flowd -k rotate;
SUCCESSFUL AND HAEMORROIDLESS BILLING!!!