[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Has any thought of a rrd ODBC driver? -Original Message- From: Marc Bilodeau [EMAIL PROTECTED] Sent: 2/17/04 7:24:20 AM To: 'Tobias Oetiker' [EMAIL PROTECTED], 'Cook, Garry' [EMAIL PROTECTED] Cc: 'Max Clark' [EMAIL PROTECTED], 'MRTG Mailinglist' [EMAIL PROTECTED] Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Good question tobi, at Somix our denika/webnm products write the polled data to both the RRD files and the MySQL back-end. We believe that users should have a quick reference for daily, weekly, monthly, and yearly data. However, if the user needs to create a report based on 5 minute samples between 8am and 5pm for Tuesday, Thursday, and Friday on the week of October 23 they can with the stored data. Some would argue by increasing the amount of data that can be stored in the rrd file would do the trick. However, many other tools out there can take advantage of the database via ODBC or a direct connection. Sincerely, Marc http://www.somix.com/support/mrtg_repository.php -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tobias Oetiker Sent: Tuesday, February 17, 2004 9:35 AM To: Cook, Garry Cc: Max Clark; MRTG Mailinglist Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Today Cook, Garry wrote: Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? I wondr though, why you would want to write the data into a log or rrd file first instead of punishing your database server with it right away ... ? tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://peoplÑ?e.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Binary/unsupported file stripped by Ecartis -- -- Err : No filename to use for decode, file stripped. -- Type: application/ms-tnef -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Hi, Max. On Mon, 16 Feb 2004 18:14:07 -0800, Max Clark wrote in [EMAIL PROTECTED]: I am pleased to announce the release of mrtgsql version 0.1 ... Feedback and suggestions are always welcome. Nice work! No offence, but don't forget Y2K problem ;) --- mrtgreport.orig Tue Feb 17 12:09:33 2004 +++ mrtgreport Tue Feb 17 12:15:36 2004 @@ -84,7 +84,9 @@ my @months = qw(31 28 31 30 31 30 31 31 30 31 30 31); -$months[1]++ if ($year + 1900) % 4 == 0; +$months[1]++ if $year + 1900) % 4 == 0) +(($year + 1900) % 100 != 0)) + || (($year + 1900) % 400 == 0)) my $localstart = timelocal(0,0,0,1,$mon,$year); my $localend = timelocal(0,0,0,$months[$mon],$mon,$year); Regards, Takumi Yamane [EMAIL PROTECTED] -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
[EMAIL PROTECTED] wrote: I am pleased to announce the release of mrtgsql version 0.1 - a MRTG to SQL logging and reporting tool. mrtgsql was written to help facilitate bandwidth billing based on MRTG data. mrtgsql is composed of two programs: mrtgsql which parses data from the MRTG .log files and inserts the MRTG data into a SQL database, and mrtgreport which reports usage on the interfaces in total GB transfered and the 95th percentile. You can download mrtgsql from a href=http://www.cthought.com;Creative Thought/a's web site. http://www.cthought.com/free_software/mrtgsql Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? Garry W. Cook, CCNA Network Infrastructure Manager MACTEC, Inc. - http://www.mactec.com/ 303.308.6228 (Office) - 720.220.1862 (Mobile) -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Today Cook, Garry wrote: Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? I wondr though, why you would want to write the data into a log or rrd file first instead of punishing your database server with it right away ... ? tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Has anyone worked on creating the ability to generate the graphs with a logarithmic scale? This would really help when there are large spikes in the data. Charles -Original Message- From: Tobias Oetiker [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 8:35 AM To: Cook, Garry Cc: Max Clark; MRTG Mailinglist Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Today Cook, Garry wrote: Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? I wondr though, why you would want to write the data into a log or rrd file first instead of punishing your database server with it right away ... ? tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Good question tobi, at Somix our denika/webnm products write the polled data to both the RRD files and the MySQL back-end. We believe that users should have a quick reference for daily, weekly, monthly, and yearly data. However, if the user needs to create a report based on 5 minute samples between 8am and 5pm for Tuesday, Thursday, and Friday on the week of October 23 they can with the stored data. Some would argue by increasing the amount of data that can be stored in the rrd file would do the trick. However, many other tools out there can take advantage of the database via ODBC or a direct connection. Sincerely, Marc http://www.somix.com/support/mrtg_repository.php -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tobias Oetiker Sent: Tuesday, February 17, 2004 9:35 AM To: Cook, Garry Cc: Max Clark; MRTG Mailinglist Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Today Cook, Garry wrote: Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? I wondr though, why you would want to write the data into a log or rrd file first instead of punishing your database server with it right away ... ? tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Today Marc Bilodeau wrote: Good question tobi, at Somix our denika/webnm products write the polled data to both the RRD files and the MySQL back-end. We believe that users should have a quick reference for daily, weekly, monthly, and yearly data. However, if the user needs to create a report based on 5 minute samples between 8am and 5pm for Tuesday, Thursday, and Friday on the week of October 23 they can with the stored data. Some would argue by increasing the amount of data that can be stored in the rrd file would do the trick. However, many other tools out there can take advantage of the database via ODBC or a direct connection. Hi Marc, Yep, that seems like a sensible thing I would assume though that in a setup with MANY datasources, that mysql may probably not be able to keep up or fill up quickly if no data is deleted ... have you done any tests in this direction ? mysql vs rrd performance? cheers tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
We have. RRDTool stores the data faster then MySQL, and RRDTool's footprint is smaller by far. Some of our large WebNM installs have gigs of MySQL data for every data point for several years. However, management wants the data in a format that they can use multiple tools to manipulate the data. Some of our customers use tools like crystal report with our product because crystal is where their expertise is. With the right settings to MySQL, it can keep up with 5,000 to 10,000 reports. Of course this depends on factors such as hard drives, and other hardware. We prefer to let RRDTool calculate the data point, then we store the data values to the MySQL database. Its been a couple of years and a few versions until we've gotten it right. Sincerely, Marc http://www.somix.com/support/mrtg_repository.php -Original Message- From: Tobias Oetiker [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 10:29 AM To: Marc Bilodeau Cc: 'MRTG Mailinglist' Subject: RE: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Today Marc Bilodeau wrote: Good question tobi, at Somix our denika/webnm products write the polled data to both the RRD files and the MySQL back-end. We believe that users should have a quick reference for daily, weekly, monthly, and yearly data. However, if the user needs to create a report based on 5 minute samples between 8am and 5pm for Tuesday, Thursday, and Friday on the week of October 23 they can with the stored data. Some would argue by increasing the amount of data that can be stored in the rrd file would do the trick. However, many other tools out there can take advantage of the database via ODBC or a direct connection. Hi Marc, Yep, that seems like a sensible thing I would assume though that in a setup with MANY datasources, that mysql may probably not be able to keep up or fill up quickly if no data is deleted ... have you done any tests in this direction ? mysql vs rrd performance? cheers tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
I am working on adding support for .rrd files in the next release. The environment that this was initially written for still uses .log hence the initial support. -Max -- Max Clark Creative Thought, Inc 877.256.8901 Toll Free 310.455.6735 International 310.943.1968 Fax [EMAIL PROTECTED] http://www.cthought.com This transmission may contain privileged and confidential information intended only for the recipient(s) named above. If you have, or believe you have, received this transmission in error, or are not [one of] the intended recipient(s) named above, you must notify the sender immediately and permanently destroy any and all copies of this transmission. -Original Message- From: Cook, Garry [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 17, 2004 6:30 AM To: Max Clark; [EMAIL PROTECTED] Subject: RE: [mrtg] mrtgsql - a MRTG to SQL logging and reporting tool [EMAIL PROTECTED] wrote: I am pleased to announce the release of mrtgsql version 0.1 - a MRTG to SQL logging and reporting tool. mrtgsql was written to help facilitate bandwidth billing based on MRTG data. mrtgsql is composed of two programs: mrtgsql which parses data from the MRTG .log files and inserts the MRTG data into a SQL database, and mrtgreport which reports usage on the interfaces in total GB transfered and the 95th percentile. You can download mrtgsql from a href=http://www.cthought.com;Creative Thought/a's web site. http://www.cthought.com/free_software/mrtgsql Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? Garry W. Cook, CCNA Network Infrastructure Manager MACTEC, Inc. - http://www.mactec.com/ 303.308.6228 (Office) - 720.220.1862 (Mobile) -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
The intention is not to replace the .log/.rrd backend for MRTG with SQL, but to create a granular archive for billing purposes. In this environment there are hundreds/thousands of interfaces being monitored by MRTG. Only 1/2 interfaces per switch are being used for billing, and only those interfaces are being logged to SQL. By default mrtgsql will not dump all of the data into SQL, you have to specify which interfaces. Cricket has copy to sql support built into the application now, I thought about using that for this purpose. I elected to have an offline process inserting data into SQL for a couple of reasons: 1) it doesn't require patching MRTG, 2) MRTG is very fast/efficient at polling and writing to its internal data stores (I didn't want to mess with that), 3) I did not want MRTG to be dependant on the SQL server. With the data collection in mrtgsql a second process offline from MRTG you could lower the frequency of the cron job and insert to SQL in bulk once per hour lets say. -Max -- Max Clark Creative Thought, Inc 877.256.8901 Toll Free 310.455.6735 International 310.943.1968 Fax [EMAIL PROTECTED] http://www.cthought.com This transmission may contain privileged and confidential information intended only for the recipient(s) named above. If you have, or believe you have, received this transmission in error, or are not [one of] the intended recipient(s) named above, you must notify the sender immediately and permanently destroy any and all copies of this transmission. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cook, Garry Sent: Tuesday, February 17, 2004 6:53 AM To: Tobias Oetiker Cc: Max Clark; MRTG Mailinglist Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Tobias Oetiker wrote: Today Cook, Garry wrote: Feedback and suggestions are always welcome. You mentioned .log files... Will this work for .rrd files as well? Any plans for such a beast? I wondr though, why you would want to write the data into a log or rrd file first instead of punishing your database server with it right away ... ? tobi Good question. I hadn't really considered that, only had half a cup of coffee this morning... If I had an sql server already setup, I might have thought about it more in depth. I don't know mysql or msql well enough to weigh the pros and cons, although I find rrdtool and .rrd files very clean and easy to manage. It sounds like mrtgsql would add a redundant layer to my setup. However, I've written many scripts that automate the tasks involved with my mrtg installation, it would probably be easier for me to add this extra layer than to rebuild my entire mrtg management system. Perhaps I should have asked, 'Will the second portion of the package, mrtgreport, hook directly to an .rrd?' Garry W. Cook, CCNA Network Infrastructure Manager MACTEC, Inc. - http://www.mactec.com/ 303.308.6228 (Office) - 720.220.1862 (Mobile) -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi
[mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool
Logging every datasource to mysql will definitely be slower and less efficient than logging to .rrd. People that are writing to mysql will have to monitor the size of their database and most likely create a data warehouse of some kind for old data. I am not advocating the unilateral use of mrtgsql for all of the datasources in the MRTG config, only the ones where detailed reporting (in my case for billing) is necessary. -Max -- Max Clark Creative Thought, Inc 877.256.8901 Toll Free 310.455.6735 International 310.943.1968 Fax [EMAIL PROTECTED] http://www.cthought.com This transmission may contain privileged and confidential information intended only for the recipient(s) named above. If you have, or believe you have, received this transmission in error, or are not [one of] the intended recipient(s) named above, you must notify the sender immediately and permanently destroy any and all copies of this transmission. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tobias Oetiker Sent: Tuesday, February 17, 2004 7:29 AM To: Marc Bilodeau Cc: 'MRTG Mailinglist' Subject: [mrtg] Re: mrtgsql - a MRTG to SQL logging and reporting tool Today Marc Bilodeau wrote: Good question tobi, at Somix our denika/webnm products write the polled data to both the RRD files and the MySQL back-end. We believe that users should have a quick reference for daily, weekly, monthly, and yearly data. However, if the user needs to create a report based on 5 minute samples between 8am and 5pm for Tuesday, Thursday, and Friday on the week of October 23 they can with the stored data. Some would argue by increasing the amount of data that can be stored in the rrd file would do the trick. However, many other tools out there can take advantage of the database via ODBC or a direct connection. Hi Marc, Yep, that seems like a sensible thing I would assume though that in a setup with MANY datasources, that mysql may probably not be able to keep up or fill up quickly if no data is deleted ... have you done any tests in this direction ? mysql vs rrd performance? cheers tobi -- ____ _ /_ __/_ / / (_) Oetiker @ ISG.EE, ETZ J97, ETH, CH-8092 Zurich / // _ \/ _ \/ / System Manager, Time Lord, Coder, Designer, Coach /_/ \.__/_.__/_/ http://people.ee.ethz.ch/~oetiker +41(0)1-632-5286 -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi -- Unsubscribe mailto:[EMAIL PROTECTED] Archive http://www.ee.ethz.ch/~slist/mrtg FAQ http://faq.mrtg.orgHomepage http://www.mrtg.org WebAdminhttp://www.ee.ethz.ch/~slist/lsg2.cgi