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 ok with me. I also understand the logging into the DB will keeping logging new lines indefinitely, till I clear some rows manually. Is that correct ? 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); 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 >> >> >> >> ------ >> Тарифный план 'ПРОНТО' от 'БеСТ': бесплатные внутрисетевые разговоры >> со второй минуты общения. Подключайся до 30 июня. >> Подробнее: http://www.best.by. ПРОНТО: Cвои �C не платят! Cвои �C общаются! >> >> _______________________________________________ >> pmacct-discussion mailing list >> http://www.pmacct.net/#mailinglists >> > >
_______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
