[pmacct-discussion] pmacct mysql setup
Hello everyone, this is my first post to this mailing list, and well any mailing list. I've installed pmacct on Debian and I'm working with nfacctd. I've verified nfacctd is able to gather data by using the command "nfacctd -l 2055 -P print -c src_host,dst_host,src_port,dst_port" and I see it captures flow data. However when I change it to use a config file and using the mysql plugin I'm not seeing any records added to the database. When I compiled, I used ./configure --enable-mysql, and then I ran the mysql scripts in /sql: mysql -u root -p < pmacct-create-db_v9.mysql mysql -u root -p < pmacct-grant-db.mysql My nfacctd.conf file looks like: ! daemonize: true plugins: mysql aggregate: src_host,dst_host,src_port,dst_port nfacctd_port: 2055 sql_refresh_time: 120 sql_history: 10m sql_history_roundoff: mh sql_table_version: 9 ! MySQL runs fine, I see the pmacct DB and the acct_v9 table, but it is empty. Other than that I'm not sure where to go next to get MySQL working. I'm not sure how I would configure credentials or even a remote MySQL server if I were to deploy it differently. Could anyone provide any insight or links to documentation? Thank you, Robert Juric ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] pmacct mysql setup
I turned on the debug command by running "nfacctd -d true -P mysql" and I saw this after capturing some flow records: ERROR ( default/mysql ): PRIMARY 'mysql' backend trouble. ERROR ( default/mysql ): The SQL server says: Table 'pmacct.acct' doesn't exist I looked in MySQL and found: mysql> show databases; ++ | Database | ++ | information_schema | | mysql | | performance_schema | | pmacct | ++ 4 rows in set (0.00 sec) mysql> use pmacct; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--+ | Tables_in_pmacct | +--+ | acct_v9 | +--+ 1 row in set (0.00 sec) I think this is because I only ran the v9 MySQL script. I was just a little confused, should I run all the scripts, just v1, or which? Robert On Wed, Mar 9, 2016 at 10:34 AM, fboehm wrote: > Am 09.03.2016 um 17:06 schrieb Robert Juric: > >> MySQL runs fine, I see the pmacct DB and the acct_v9 table, but it is >> empty. Other than that I'm not sure where to go next to get MySQL >> working. I'm not sure how I would configure credentials or even a remote >> MySQL server if I were to deploy it differently. Could anyone provide >> any insight or links to documentation? >> > I used the debug parameter and debugged my SQL configuration this way. It > helped me. But depending on your configuration you might have to wait a few > minutes until the first data is written from pmacct internal buffer into > mysql database. > > Franz > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] pmacct mysql setup
Thank you all for the suggestions. I found 2 issues. Running the debug from CLI I noticed it was defaulting to the v1 table, when I ran that script it resolved that. I then noticed it was discarding the NFv9 for unknown template. I set "aggregate: none" and now I'm having records put into the v1 table!! However, I'm confused as to the differences or pros/cons between the table versions? On Wed, Mar 9, 2016 at 11:30 PM, fboehm wrote: > Am 09.03.2016 um 20:39 schrieb Robert Juric: > >> I think this is because I only ran the v9 MySQL script. I was just a >> little confused, should I run all the scripts, just v1, or which? >> > Robert, please run the v4 SQL scripts and set "sql_table_version: 4" in > your configuration. > > Maybe you anyway don't need the additional fields that v9-tables provide. > In general I'm not sure why it doesn't like the v9 settings. > > > Franz > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] NFv9 Unknown Template
I correct the mysql configuration and when I went to change and I restarted the service to change the table version I'm now seeing NFv9 packets received and discarded for Unknown Template. I've not been able to find much information regarding this. I'm using a Juniper SRX router with inline-jflow. root@debian-netflow:/etc/pmacct# nfacctd -l 2100 -P print -c none -d true DEBUG: [cmdline] plugin name/type: 'default'/'core'. DEBUG: [cmdline] plugin name/type: 'default'/'print'. DEBUG: [cmdline] nfacctd_port:2100 DEBUG: [cmdline] aggregate:none DEBUG: [cmdline] debug:true INFO ( default/core ): Reading configuration from cmdline. INFO ( default/print ): plugin_pipe_size=4096000 bytes plugin_buffer_size=228 bytes INFO ( default/print ): ctrl channel: obtained=212992 bytes target=143712 bytes INFO ( default/core ): waiting for NetFlow data on 0.0.0.0:2100 INFO ( default/print ): cache entries=16411 base cache memory=44769208 bytes PACKETS BYTES DEBUG ( default/core ): Received NetFlow/IPFIX packet from [ 192.168.1.1:55602] version [9] seqno [45617] DEBUG ( default/core ): Discarded NetFlow v9/IPFIX packet (R: unknown template 257 [192.168.1.1:142]) DEBUG ( default/core ): Received NetFlow/IPFIX packet from [ 192.168.1.1:55602] version [9] seqno [45618] DEBUG ( default/core ): Discarded NetFlow v9/IPFIX packet (R: unknown template 257 [192.168.1.1:142]) Could anyone point me in the right direction? ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] NFv9 Unknown Template
Sure, I've noticed that it does this for a few minutes after starting nfacctd, before it will eventually recognize and process the flows: root@SRX100> show configuration forwarding-options sampling | display set set forwarding-options sampling input rate 1 set forwarding-options sampling input run-length 0 set forwarding-options sampling family inet output flow-server 192.168.1.71 port 2100 set forwarding-options sampling family inet output flow-server 192.168.1.71 version9 template IPV4-TEMPLATE set forwarding-options sampling family inet output inline-jflow source-address 192.168.1.1 root@SRX100> show configuration services | display set set services flow-monitoring version9 template IPV4-TEMPLATE ipv4-template Robert On Thu, Mar 10, 2016 at 12:21 PM, Adam Bogdan wrote: > Hi Robert > > > > Could You show Your netflow/jflow configuration on Your SRX ? > > > > Best > > Adam > > > > *Od: *Robert Juric > *Wysłano: *czwartek, 10 marca 2016 18:13 > *Do: *pmacct-discussion@pmacct.net > *Temat: *[pmacct-discussion] NFv9 Unknown Template > > > > I correct the mysql configuration and when I went to change and I > restarted the service to change the table version I'm now seeing NFv9 > packets received and discarded for Unknown Template. > > I've not been able to find much information regarding this. I'm using a > Juniper SRX router with inline-jflow. > > root@debian-netflow:/etc/pmacct# nfacctd -l 2100 -P print -c none -d true > DEBUG: [cmdline] plugin name/type: 'default'/'core'. > DEBUG: [cmdline] plugin name/type: 'default'/'print'. > DEBUG: [cmdline] nfacctd_port:2100 > DEBUG: [cmdline] aggregate:none > DEBUG: [cmdline] debug:true > INFO ( default/core ): Reading configuration from cmdline. > INFO ( default/print ): plugin_pipe_size=4096000 bytes > plugin_buffer_size=228 bytes > INFO ( default/print ): ctrl channel: obtained=212992 bytes target=143712 > bytes > INFO ( default/core ): waiting for NetFlow data on 0.0.0.0:2100 > INFO ( default/print ): cache entries=16411 base cache memory=44769208 > bytes > PACKETS BYTES > DEBUG ( default/core ): Received NetFlow/IPFIX packet from [ > 192.168.1.1:55602] version [9] seqno [45617] > DEBUG ( default/core ): Discarded NetFlow v9/IPFIX packet (R: unknown > template 257 [192.168.1.1:142]) > DEBUG ( default/core ): Received NetFlow/IPFIX packet from [ > 192.168.1.1:55602] version [9] seqno [45618] > DEBUG ( default/core ): Discarded NetFlow v9/IPFIX packet (R: unknown > template 257 [192.168.1.1:142]) > > Could anyone point me in the right direction? > > > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Getting nfacctd to NOT aggregate ?
I found you have to build the tables with timestamps and then when you aggregate with timestamp_start and timestamp_end you can get the individual flow records as opposed to aggregating the records. Robert Juric On Tue, Apr 19, 2016 at 9:00 AM, Dariush Marsh-Mossadeghi < dari...@gravitas.co.uk> wrote: > Hi List, > > Is there a way to get pmacctd/nfacctd to NOT do any aggregation of flow > records ? > > Specifically, I’ve got IPFIX coming off a router being handed by nfacctd > and it would be useful to temporarily have visibility of every flow record. > Tcpdump is not a suitable as part of what I’m trying to ascertain is > whether the IPFIX data is accurate. > > Any suggestions/recipes/config snippets/pointers to RTFM would be > gratefully received. > > Thanks > Dariush > > Dariush Marsh-Mossadeghi > E: dari...@gravitas.co.uk > M: +44 7973 259510 > W: https://uk.linkedin.com/in/dariushmm > > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Getting nfacctd to NOT aggregate ?
No problem! If you feel like RTFM :), check out the official examples http://wiki.pmacct.net/OfficialExamples under Section XVII - Using pmacct as traffic/event logger; they have some initial information that can be used. It took me a little trial and error to figure it out. Robert Juric On Tue, Apr 19, 2016 at 9:49 AM, Dariush Marsh-Mossadeghi < dari...@gravitas.co.uk> wrote: > A… aggregating on something which can’t be aggregated. Nice hack :-) > Thanks Robert > > On 19 Apr 2016, at 15:26, Robert Juric wrote: > > I found you have to build the tables with timestamps and then when you > aggregate with timestamp_start and timestamp_end you can get the individual > flow records as opposed to aggregating the records. > > Robert Juric > > > > On Tue, Apr 19, 2016 at 9:00 AM, Dariush Marsh-Mossadeghi < > dari...@gravitas.co.uk> wrote: > >> Hi List, >> >> Is there a way to get pmacctd/nfacctd to NOT do any aggregation of flow >> records ? >> >> Specifically, I’ve got IPFIX coming off a router being handed by nfacctd >> and it would be useful to temporarily have visibility of every flow record. >> Tcpdump is not a suitable as part of what I’m trying to ascertain is >> whether the IPFIX data is accurate. >> >> Any suggestions/recipes/config snippets/pointers to RTFM would be >> gratefully received. >> >> Thanks >> Dariush >> > > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Looking for a fresh pmacct UI
I had started to work with HighCharts to put a front-end together for my small deployment. I also wouldn't mind contributing to a project in any way I could. Robert Juric On Tue, Jul 26, 2016 at 9:58 AM, Davide Principi < davide.princ...@nethesis.it> wrote: > Thanks for the prompt reply, Harry! > > > > > You might be interested in: http://uowits.github.io/herbert-gui/index > > .html > > > It looks great, but if I understand correctly that UI requires MongoDB > and RabbitMQ messaging queue to collect data. Of course, I would not > run that infrastructure on a single router! > > Any other idea? > > > > > One thing you might notice is that due to the flexible nature of > > pmacct, creating an all encompassing front-end is quite a mammoth > > task. I think a lot of people tend to plug the aggregates into their > > existing infrastructure. > > This is an important point! It could also explain why a simple UI that > runs on the same host where data is collected is not so easy to > find... > > Nobody is interested on a similar project in the "datacenter era" :) ? > > However SME businesses, non-profit orgs with a LAN and their firewall > could appreciate it... What do you think? > > > > > That being said, I'd be happy to help contribute if you do decide to > > start a project. > > > > This is awesome, I'll keep you informed! > > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Looking for a fresh pmacct UI
I think my goals areinline with yours, possibly with the exception of counters but that could be handy. I was looking to see top XX lists (talkers, protocols, ports/applications) as well as time based traffic charts. My current use is very small as I'm just testing in my home lab, I would need some reliable front-end before recommending to customers without a dev team. The way I see it, the pmacct database already has all the data that is necessary in SQL (or whatever platform you're using). Which is why I started looking at tools like HighCharts to just put a front-end on it. On Tue, Jul 26, 2016 at 10:52 AM, Davide Principi < davide.princ...@nethesis.it> wrote: > Thanks for chiming in! > > On Tue, 2016-07-26 at 10:23 -0500, Robert Juric wrote: > > I had started to work with HighCharts to put a front-end together for > > my small deployment. I also wouldn't mind contributing to a project > > in any way I could. > > HighCharts is very attractive! > > Would you mind sharing the goals/requirements of your environment? Do > you think they match mine? > > -- > Davide > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Looking for a fresh pmacct UI
Well would anyone else be interested in developing a dedicated front-end utilizing the existingpmacct database? Or is it the general consensus that everyone exports the pmacct data to other systems for graphical representation? On Tue, Aug 2, 2016 at 8:40 AM, Davide Principi wrote: > On Tue, 2016-07-26 at 15:39 +0200, Davide Principi wrote: > > I'm looking for a bandwidthd replacement and I started experimenting > > with pmacct. > > Well thanks again guys for all your suggestions! > > Just for the record, I decided to enable the sqlite backend on > bandwidthd, by compiling it with an old patch starting from the Fedora > RPM. > > You know, my customers are happy with its interface and it's hard to > find a good substitute. > > Source code is available here: > https://github.com/NethServer/bandwidthd > > -- > Davide Principi > > #davidep | @davideprincipi | GPG 0x5651EA71 > > > > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] Nfacctd Unknown Templates
I had nfacctd up and running, recording everything to a MySQL database. I just tried to swap it over to using the Memory plug-in so I can pull the data out to an rrd based graphing solution. However now I'm getting a debug message that the Netflow packet was discarded due to unknown template. Can anyone refresh me as to how to fix the templates when using nfacctd+memory plugin? ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
[pmacct-discussion] Graphing Options
After fiddling around for a few days I'm still at a loss for finding a good graphing option. I've been working today trying to use the memory plugin and cacti to graph some data, but I realized that it won't be good for dynamic type graphs. I could easily graph total tcp/udp traffic since those don't alter too much. Ideally I'd like to aggregate the dst_ports but I'd like to graph the top 5. I understand Cacti may not be the best for this since you have to define the data sources manually. What other options are available or commonly used for graphing? Preferably something that can be aggregated on a per conversation basis? ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Graphing Options
What I'm not sure of is whether or not time-series is the correct way to store my data? I am currently aggregating nfacctd data based on flow timestamps for accounting purposes. For those using InfuxDB and Graphite/Graphana, what primitives are you aggregating on and what do you pull out of the data in the graphing solution? On Wed, Nov 9, 2016 at 4:21 PM, Rasto Rickardt wrote: > I would use InfluxDB as database & Grafana for graphing. > > As you already using memory plugin, you can use pmacct client and push > data to InfluxDB. It is webservice, so simple bash & curl will work. > > r. > > On 11/09/2016 11:01 PM, Robert Juric wrote: > > After fiddling around for a few days I'm still at a loss for finding a > > good graphing option. > > > > I've been working today trying to use the memory plugin and cacti to > > graph some data, but I realized that it won't be good for dynamic type > > graphs. I could easily graph total tcp/udp traffic since those don't > > alter too much. Ideally I'd like to aggregate the dst_ports but I'd like > > to graph the top 5. I understand Cacti may not be the best for this > > since you have to define the data sources manually. > > > > What other options are available or commonly used for graphing? > > Preferably something that can be aggregated on a per conversation basis? > > > > > > ___ > > pmacct-discussion mailing list > > http://www.pmacct.net/#mailinglists > > > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] Graphing Options
I wanted to check back in and say thanks to everyone. After some time
digging in I finally got my nfacctd data into InfluxDB and Grafana. Had to
learn a lot on the way but I can finally put nfacctd to good use now!
Robert
On Thu, Nov 10, 2016 at 5:07 AM, Rasto Rickardt wrote:
> Robert,
>
> Generally - if you want Top 5 talkers in some time range, you need to
> store it somewhere to be able to select them. Both works in SQL DB and
> NoSQL - time series DB. RRD based solution will not give you features
> you need.
>
> If you speak about conversation, i suppose aggregation on
> src_host/dst_host you are interested in for a time range.
>
> You have it from memory, or f.e AMQP plugin.
>
> You have output like
> src inB outB
> 1.2.3.4 100 200
>
> You can push it to InfluxDB like this:
>
> dbname,src=$src inputbytes=$inB,outputbytes=$outB
> timestamp is added automatically from the time of insert.
>
> Grafana query can look like this
>
> SELECT (last("inputbytes") - first("inputbytes")) + (last("outputbytes")
> - first("outputbytes")) as "data" FROM "dbname" WHERE $timeFilter and
> "src" =~ /$src$/ GROUP BY timestamp
>
> $timeFilter is time window selected in Grafana to show (15 minutes, 30
> days)
>
> It is possible that i do not fully understand your use case, but i hope
> this will help you to have an idea how to do this.
>
> r.
>
>
> On 11/09/2016 11:55 PM, Robert Juric wrote:
> > What I'm not sure of is whether or not time-series is the correct way to
> > store my data? I am currently aggregating nfacctd data based on flow
> > timestamps for accounting purposes. For those using InfuxDB and
> > Graphite/Graphana, what primitives are you aggregating on and what do
> > you pull out of the data in the graphing solution?
> >
> >
> >
> > On Wed, Nov 9, 2016 at 4:21 PM, Rasto Rickardt > <mailto:pho...@axfr.org>> wrote:
> >
> > I would use InfluxDB as database & Grafana for graphing.
> >
> > As you already using memory plugin, you can use pmacct client and
> push
> > data to InfluxDB. It is webservice, so simple bash & curl will work.
> >
> > r.
> >
> > On 11/09/2016 11:01 PM, Robert Juric wrote:
> > > After fiddling around for a few days I'm still at a loss for
> finding a
> > > good graphing option.
> > >
> > > I've been working today trying to use the memory plugin and cacti
> to
> > > graph some data, but I realized that it won't be good for dynamic
> type
> > > graphs. I could easily graph total tcp/udp traffic since those
> don't
> > > alter too much. Ideally I'd like to aggregate the dst_ports but
> > I'd like
> > > to graph the top 5. I understand Cacti may not be the best for this
> > > since you have to define the data sources manually.
> > >
> > > What other options are available or commonly used for graphing?
> > > Preferably something that can be aggregated on a per conversation
> > basis?
> > >
> > >
> > > ___
> > > pmacct-discussion mailing list
> > > http://www.pmacct.net/#mailinglists
> > <http://www.pmacct.net/#mailinglists>
> > >
> >
> > ___
> > pmacct-discussion mailing list
> > http://www.pmacct.net/#mailinglists
> > <http://www.pmacct.net/#mailinglists>
> >
> >
> >
> >
> > ___
> > pmacct-discussion mailing list
> > http://www.pmacct.net/#mailinglists
> >
>
> ___
> pmacct-discussion mailing list
> http://www.pmacct.net/#mailinglists
>
___
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
Re: [pmacct-discussion] How to get raw netflow data out of pmacct
So its my understanding that the use of pmacctd/nfacctd as an accounting tool came later in the original design. Though I'd love to hear the story behind that. I assume the timestamp primitives were added later to differentiate flow records, but he didn't want to change the aggregate config key. So to gather netflow data for accounting purposes if you specify the timestamp aggregates you're then forced to have unique records as opposed to the default temporal (time-based) aggregation. I use: aggregate: src_host, dst_host, timestamp_start, timestamp_end, src_port, dst_port, proto, tos, tcpflags timestamp_secs:true In the Official Examples (http://wiki.pmacct.net/OfficialExamples) section XVII talks about "Using pmacct as traffic/event logger" and the use of the timestamp primitives. Robert On Mon, Dec 5, 2016 at 8:52 AM, Julian Keppel wrote: > Hi, > > I don't understand the aggregate field in the configuration file. What I > want to get out of pmacct in the first step is the "most raw" data > possible, with no aggregations at all (for some experiments). > > In a next step, I maybe want to get some aggregates, as I use the data for > a machine learning process and some features could be derived directly in > pmacct... is that a common approach? > > How can I achive the first approach with raw netflow data (as "raw" as > possilbe) where I don't want any aggregation at all? And how does the > aggregation mechanism work? The only thing I found in the documentation > was: http://wiki.pmacct.net/OfficialConfigKeys > > But there are some fields missing like for example timestamp_start... so > where is a complete list of possible fields? And how can I distinguish > between aggregation directives and "normal" fields like timestamp? Maybe > the configuration field "aggregate" is misleading because you don't only > configure the aggregate fields, but also the "normal" fields to receive? > > Maybe I'm missing some piece of documentation... sorrry. And thank you in > advance for you help. > > Julian > > ___ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > ___ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists
