Hi Anil, In principle what you are doing looks correct: perhaps for a cleaner result i would use filters to separate the traffic, two plugins, two SQL tables - one for incoming the other for outgoing traffic. Anyway, the result of your SQL query is to account for traffic between your servers and the rest of the world. It leaves out traffic internal to your subnets.
In saying that your counters don't match with those of your provider, can you be more precise? Some exaples (byte and packet counters, you vs your provider) and more information would help to shed some light; things that pop to my mind: * Which method does your provider use to account data, ie. SNMP, a SPAN port or rather either sFlow or NetFlow. * How you are comparing things. For example, your SQL query didn't specify any timeframe (ie. all yesterday traffic from midnight to midnight). Do you compare daily traffic levels? Cheers, Paolo On Wed, Apr 29, 2009 at 08:03:32AM -0700, Anil wrote: > I've been capturing the bandwidth usage on my server for the past few months > now, and when I correlate it with my network provider's bandwidth graphs, > they don't correlate. I am sure their graphs are right. > > Here is a snapshot of some of the data in the database: > > mysql> select * from acct limit 5; > +---------+---------+---------------+---------------+----------+----------+----------+---------+-----------+---------------------+---------------------+ > | mac_src | mac_dst | ip_src | ip_dst | src_port | dst_port | > ip_proto | packets | bytes | stamp_inserted | stamp_updated | > +---------+---------+---------------+---------------+----------+----------+----------+---------+-----------+---------------------+---------------------+ > | | | 199.89.XX.XX | 0.0.0.0 | 0 | 0 | > ip | 138004 | 178895357 | 2009-04-01 09:05:00 | 2009-04-01 09:10:01 | > > | | | 0.0.0.0 | 199.89.XX.XX | 0 | 0 | > ip | 2145 | 303722 | 2009-04-01 09:05:00 | 2009-04-01 09:10:01 | > > | | | 199.89.XX.XX | 0.0.0.0 | 0 | 0 | > ip | 1950 | 179998 | 2009-04-01 09:05:00 | 2009-04-01 09:10:01 | > > | | | 0.0.0.0 | 199.89.XX.XX | 0 | 0 | > ip | 85468 | 7506758 | 2009-04-01 09:05:00 | 2009-04-01 09:10:01 | > > | | | 0.0.0.0 | 199.89.XX.XX | 0 | 0 | > ip | 3638 | 845965 | 2009-04-01 09:05:00 | 2009-04-01 09:10:01 | > > +---------+---------+---------------+---------------+----------+----------+----------+---------+-----------+---------------------+---------------------+ > 5 rows in set (0.00 sec) > > > r...@vps2:/usr/local/pmacct/etc# cat networks.def > ! > ! local networks > ! > 208.64.XX.YY/26 > 208.64.XX.XX/27 > > There is data in the table for the 208.64 subnets, so ignore that it doesn't > show in the SELECT above. > > r...@vps2:/usr/local/pmacct/etc# cat pmacctd.conf > interface: e1000g0 > daemonize: true > !aggregate: sum_host > aggregate: src_host,dst_host > plugins: mysql > !plugins: memory > ! > sql_host: > sql_db: > sql_table: acct > sql_user: > sql_passwd: > sql_table_version: 1 > ! > sql_refresh_time: 300 > sql_history: 5m > sql_history_roundoff: m > networks_file: /usr/local/pmacct/etc/networks.def > > I do something like this when calculating total bandwidth: > > SELECT sum(bytes) FROM acct WHERE ip_src = '%s' AND ip_dst = '0.0.0.0'; > SELECT sum(bytes) FROM acct WHERE ip_src = '0.0.0.0' AND ip_dst = '%s'; > > Then, I add both the in+out to capture usage for a specific IP address. > > Is this the right approach? I am wondering if I am doing something wrong > here. > > Thanks, > Anil _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
