DSStats - 1.1 Released (Data Source Statistics)

[TheWitness]

All,

Version 1.1 has been released incorporating changes from Howie that allow for a more expansive use of the data that the plugin provides via either additional plugins, or new Graph Templates.

Regards,

TheWitness
_________________________________________________

All,

Please find the following user contributed plugin called DSStats. This plugin is an infrastructure plugin. What this means, is that you see nothing but slowed polling times by installing it, or at least on the surface.

However, if you are currently creating aggregate graphs and your savvy with SQL, you can now write script queries, that perform database calls to build those graphs further enhancing what we can already do with Aggregate and RRDtool.

Oh yeah right you say. Well how about a few examples. Let's say you are tracking the temperature of all equipment in a rack of servers, and want to know the average and peak temperatures within that rack, and you have some "MetaData" that tells you that so and so servers are in that rack.

Another example, would be to total all network bandwidth utilization within a state, city, country, floor, building, yah da yah da yah. Now, you can do it with a simple SQL query that sum's all data.

How about the top 10 Gigabit interfaces out of 10,000 in your company, either in the last hour, day, week, month, year. Now you can build a plugin that does that in an instant.

Well, I would like your comments, and donations to the Cacti Group. If you can spare it, please consider donating 10-100 dollars to the Cacti Group each year. It pays for lot's of beer and cider and keeps us going. Seriously!

And of course, before you turn this plugin loose, you must keep in mind the following:

1) The plugin uses two memory tables, do make sure you have enough memory to handle them.
2) If your poller is borderline in terms of making all polling in a poller cycle, then don't enable this without being prepared to immediately disable it.

Finally, good luck. Enjoy Cacti.

Regards,

TheWitness

Download Here

[Hyperlord]

Hi there,

I guess this is kinda groundbreaking!
Cacti catches up with commercial solutions (I do often talk about Ipswitch Whatsup Gold) very fast and very professional, too.
I'll try this system ASAP.

May I donate beer?

brgds

[TheWitness]

How about sponsorship so that we can visit where the beers made

TheWitness

[gsaray101]

how do we donate, can we buy something from Amazon?

[TheWitness]

We prefer PayPal.

http://www.cacti.net/donate.php

TheWitness

[Howie]

Could the last calculated value also be put into data_source_stats_hourly_last?

To get a list of the most recent values for a particular subset of results, the query needs to have some time component that it wouldn't otherwise need.

Code: Select all

select dss.*,dtd.name_cache,dtd.data_source_path from data_source_stats_hourly_cache dss, data_template_data dtd where value > 0 and (rrd_name='errors_in' or rrd_name='errors_out') and dtd.local_data_id = dss.local_data_id and dss.time > '2009-08-24 13:50' order by name_cache;

needs my script to figure out where I am relative to a poller cycle, and then what the '>' time should be. It could be a trivial (and static) query otherwise.

Or at least store the last start time somewhere?

[TheWitness]

Makes sense. Thought about that after I sent my PM. It makes for a time consuming query. Do you want to make the patch?

TheWitness

[browniebraun]

removed

[Howie]

To save someone else the research, I needed to alter my my.cnf:

Code: Select all

# 64M heap table size
max_heap_table_size = 67108864
tmp_table_size = 67108864

even with a smallish system and one hour of history.

[Howie]

Makes sense. Thought about that after I sent my PM. It makes for a time consuming query. Do you want to make the patch?

TheWitness

This seems to be working for me.

Code: Select all

*** setup.php-orig      2009-08-24 15:17:21.000000000 +0100
--- setup.php   2009-08-24 16:00:16.000000000 +0100
***************
*** 187,190 ****
--- 187,191 ----
                        `rrd_name` varchar(19) NOT NULL,
                        `value` varchar(30) NOT NULL,
+                       `calculated` DOUBLE NOT NULL,
                        PRIMARY KEY  (`local_data_id`,`rrd_name`)
                        ) ENGINE=MEMORY DEFAULT CHARSET=UTF8;"
***************
*** 192,195 ****
--- 193,200 ----
        }

+       if (!sizeof(db_fetch_row("SHOW COLUMNS from data_source_stats_hourly_last where Field='calculated'"))) {
+               db_execute("ALTER TABLE data_source_stats_hourly_last ADD calculated double not null after value");
+       };
+
        if (!sizeof(db_fetch_row("SHOW TABLES LIKE 'data_source_stats_monthly'"))) {
                db_execute("CREATE TABLE `data_source_stats_monthly` (
***************
*** 315,319 ****
                        $outbuf           = "";
                        $sql_cache_prefix = "INSERT INTO data_source_stats_hourly_cache (local_data_id, rrd_name, time, `value`) VALUES";
!                       $sql_last_prefix  = "INSERT INTO data_source_stats_hourly_last (local_data_id, rrd_name, `value`) VALUES";
                        $sql_suffix       = " ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)";
                        $overhead         = strlen($sql_prefix) + strlen($sql_suffix);
--- 320,324 ----
                        $outbuf           = "";
                        $sql_cache_prefix = "INSERT INTO data_source_stats_hourly_cache (local_data_id, rrd_name, time, `value`) VALUES";
!                       $sql_last_prefix  = "INSERT INTO data_source_stats_hourly_last (local_data_id, rrd_name, `value`, calculated) VALUES";
                        $sql_suffix       = " ON DUPLICATE KEY UPDATE `value`=VALUES(`value`)";
                        $overhead         = strlen($sql_prefix) + strlen($sql_suffix);
***************
*** 339,342 ****
--- 344,348 ----
                        $last_i       = 1;
                        $out_length   = 0;
+                       $last_length   = 0;
                        $lastbuf      = "";
                        $cachebuf     = "";
***************
*** 449,458 ****
                                                                $result["local_data_id"] . "','" .
                                                                $result["rrd_name"] . "','" .
!                                                               $lastval . "')";
                                                        $last_i++;
                                                }

                                                /* if we exceed out output buffer, it's time to write */
!                                               if (($out_length + $overhead) > $max_packet) {
                                                        db_execute($sql_cache_prefix . $cachebuf . $sql_suffix);

--- 455,466 ----
                                                                $result["local_data_id"] . "','" .
                                                                $result["rrd_name"] . "','" .
!                                                               $lastval . "','" .
!                                                               ($currentval != "U" ? $currentval:"-90909090909") . "')";
                                                        $last_i++;
+                                                       $last_length += strlen($lastbuf);
                                                }

                                                /* if we exceed out output buffer, it's time to write */
!                                               if ( ( ($out_length + $overhead) > $max_packet ) || ( ($last_length + $overhead) > $max_packet ) ) {
                                                        db_execute($sql_cache_prefix . $cachebuf . $sql_suffix);

***************
*** 464,467 ****
--- 472,476 ----
                                                        $lastbuf      = "";
                                                        $out_length   = 0;
+                                                       $last_length  = 0;
                                                        $cache_i      = 1;
                                                        $last_i       = 1;

[TheWitness]

Thanks Howie!

Larry

[Howie]

Thanks Howie!

Larry

Not so fast... it seems to be working for most data, but not at all for what I was looking for (errors_in and errors_out) - they are all marked as NaNs, even though the _cache table has real data for them.

[Howie]

So it wasn't working for *any* data after the first inserts...

NOW I'm getting updates OK. Patch from 1.0, not from my previous.

[Howie]

and to go with that, here's a first cut of a report page for Superlinks that shows you all interfaces with non-zero error counters (assuming you added the error counter DS in the first place). Drop it into the content/ folder of Superlinks and add it in the usual way. Rename it to something.php first.

You need to have my patch applied to DSStats, because the query looks for the field that that patch adds.

I could do with some help on the query so that it picks up a more-correct graph for the link. I can't figure out how to get the error graph instead of just the first graph for each interface - on my install I sometimes get the errors graph and sometimes get the traffic graph, and sometimes a completely wrong graph.

Anyway, might be a useful start for other folks wanting to make top 10 type reports - no need to make a whole plugin unless you really need to

[TheWitness]

Make it easy

Larry