alex
Thu, 05 Jun 2008 00:13:49 -0700
Hello Ahmed,
Ok, I should have it nailed by now thanks to all of you. I will list my current config, and the SQL query I use. Please let me know if you spot a mistake ;) Config: ====== interface: eth1 daemonize: true aggregate: src_host,dst_host promisc: true plugins: mysql sql_db: pmacct sql_table: acct_v2 sql_table_version: 2 sql_passwd: xxxxxxx sql_user: pmacct sql_refresh_time: 300 sql_history: 1h sql_history_roundoff: mh networks_file: /etc/pmacct/pmacct-networks I now understand this config would give me a time "resolution" of 1 hour (i.e. I cannot dig into what happended inside that one hour), this is okwith me.
Yes, you are correct.
I also understand the logging into the DB will keeping logging new lines indefinitely, till I clear some rows manually. Is that correct ?
Yes, it is your duty.
Here's the SQL query I use to get a nice listing of total data downloaded per month (June) by each IP:SELECT month( stamp_inserted), sum(bytes), ip_dst FROM acct_v2 where month( stamp_inserted) = 6 and ip_dst !='0.0.0.0' GROUP BY MONTH( stamp_inserted ),ip_dst order by sum(bytes);
You don't use any filters (as i mention in my answer, SEE Enrico EXAMPLE BELOW) and you will have any internal and external ip-addreses in ip_dst field. Therefore you must specify in select clause expression for your network. But better use pmacctd filters. Alex
Again thanks a lot for your help and patience ;) Regards 2008/6/4 Ahmed Kamal <[EMAIL PROTECTED]>:I think the numbers were not matching as the DB was only tracking the last 5 mins, which would make sense. I will change that to track longer periods and get back to you. Thanks a lot guys. Regards On Wed, Jun 4, 2008 at 2:55 PM, alex <[EMAIL PROTECTED]> wrote:> I have a very basic knowledge of SQL, but I am reading up on that as well. > Let me tell you what I think this information means with an example: > > 1- A client with IP 1.2.3.4 connects to Google > 2- Client sends request packets to google > 3- Google sends reply packets to client > > In step 2 (the request), the packets uploaded from the client to Google >are > logged with 1.2.3.4 in the ip_src > In step 3 (the reply), the packets downloaded from Google to the client >are > logged with 1.2.3.4 in the ip_dst > > Is this understanding of pmacct operation correct ? > > If that is so, that means that *every* connection will get 2 rows with > alternating src and dst IPs, which should mean that the 2 SQL queries > mentioned earlier should return the exact same number, which is not the > case?! Please correct me! What is the "same number"? IP address? For collecting appropriate information you must setup correct filter (aggregate_filter, see Enrico example below). I think you must send your config and database content (several records) that you think not correct. >> > Thanks a lot for the help. I did read the description, but somehow the >> > explanation was not too clear for me! Perhaps because I am a non-native. >> > >> > What I am asking about is perhaps "What is the meaning of the returned >> >rows >> > from these 2 queries" ? >> > select distinct ip_src from acct_v2; >> > select distinct ip_dst from acct_v2; >> >> By SQL syntax i can say that you see not sorted list of not repeated >> (distinct) ip addresses (source for first select, and destination for >> second). I think this is not very useful information. >> Enrico describe another select command. >> Do you know SQL language? >> >> >> > Thanks and best regards >> > >> > >> >> Hello Ahmed, >> >> Sorry but you NEED to read with attention description those >> parameters >> >> that you use. >> >> >> >> >> >> > Oh sweat, I was only recording the last 10minutes, and was wondering >> why >> >> > mysql was blazing fast :) >> >> > Changed line to be: >> >> > sql_history: 3M >> >> > >> >> > This records last 3 months, I will generally query for only last 30 >> days >> >> >> >> 'sql_history' not limited period for gathering information. "The >> >> supplied value defines the time slot width during which >> >>bytes/packets/flows >> >> counters for each entry are accumulated." Now all data fields will be >> >> summarized during a three month (for your config). And you can't select >> >> values for each day. Do you want that? >> >> I am usung following settings: >> >> >> >> sql_history: 1d >> >> sql_history_roundoff: h >> >> >> >> >> >> > Now, if I could understand why ip_src and ip_dst count are different >>I >> >> >would be happier >> >> >> >> Sorry but i don't understand what do you mean "ip_src and ip_dst >> >>count" >> >> and why it must be equal (see Enrico answer with attention)? >> >> >> >> Alex >> >> >> >> >> >> > Thanks and best regards >> >> > >> >> >> >> >> >> Um, sorry one more basic question. My config is below (straight from >> >> >> examples), and the v2 table I have has a "bytes" column. I am not so >> >> sure >> >> >>if >> >> >> this bytes column is for download or upload or both. In my sql query >> I >> >> >>need >> >> >> to get the download and upload bytes per host IP address in last 30 >> >> days, >> >> >>a >> >> >> la >> >> >> >> >> >> IP DOWN UP >> >> >> 1.2.3.4 600M 100M >> >> >> 5.6.7.8 800M 80M >> >> >> >> >> >> Is it possible to get this info, from this config: >> >> >> >> >> >> interface: eth1 >> >> >> daemonize: true >> >> >> aggregate: src_host,dst_host >> >> >> ! aggregate: src_net,dst_net >> >> >> ! plugins: pgsql >> >> >> plugins: mysql >> >> >> sql_db: pmacct >> >> >> sql_table: acct_v2 >> >> >> sql_table_version: 2 >> >> >> sql_passwd: xxxxx >> >> >> sql_user: pmacct >> >> >> sql_refresh_time: 90 >> >> >> ! sql_optimize_clauses: true >> >> >> sql_history: 10m >> >> >> sql_history_roundoff: mh >> >> >> ! sql_preprocess: qnum=1000, minp=5 >> >> >> ! >> >> >> networks_file: /etc/pmacct/pmacct-networks >> >> >> >> >> >> >> >> >> yes, >> >> >> >> >> >> query download: >> >> >> SELECT sum( bytes ) >> >> >> FROM `table` >> >> >> WHERE ip_dst = '1.2.3.4' >> >> >> >> >> >> query upload: >> >> >> SELECT sum( bytes ) >> >> >> FROM `table` >> >> >> WHERE ip_src = '1.2.3.4' >> >> >> >> >> >> another way: mahe this changes in the script >> >> >> !download >> >> >> aggregate[in]: src_host, dst_host >> >> >> aggregate_filter[in]: dst host 1.2.3.4 >> >> >> >> >> >> !upload >> >> >> aggregate[out]: src_host, dst_host >> >> >> aggregate_filter[out]: src host 1.2.3.4 >> >> >> >> >> >> !you need data per mounth >> >> >> sql_history: 30m >> >> >> sql_refresh_time: 300 >> >> >> >> >> >> then you can make a sum per column
------Так много можно сказать! Так мало нужно платить! Абоненты тарифных планов 'Свои люди' и 'Люблю поговорить' говорят внутри сети 'БеСТ' всего от 10 рублей за минуту разговора. Подробности на сайте http://www.best.by.
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists