Pulling Bandwidth from SQL

[hornet]

Hello,

I'm looking at pulling BW from a DB where the data is grouped in hours and there is both a rx and tx.

What would be the best way for me to do this?

From reading around, I can not find any examples of this being done or close to it. I found one post that said to create the rrd files externally. Is this the best thing to do? If so, do I need to "check-in" the rrd file to cacti, or will dropping them in the rra dir suffice in the correct naming format?

I've lightly looked at using Data Queries. It looks like the right way of doing it. The thing that eludes me is how to set the date and times. Cacti seems to want to collect the data using the last time it was updated, to the current update in intervals of 5 minutes. This would not work for me since the data resolution is in hours in the DB.

Also the boost plug-in might the the holy grail for me, since it supports on demand pulls.

Here is the quick perl script I made,

Code: Select all

my $stmt="select Date, rx,tx from Bandwidth
        left join Sites on Sites.pkey = Bandwidth.sitepkey
        where BmsSiteId = $site";

my $data=$dbh->prepare($stmt);
$data->execute;

my %data;
while (my @got=$data->fetchrow_array) {
        my $date=UnixDate($got[0],"%s"); #time in epoch
        $data{$date}{RX}=$got[1];
        $data{$date}{TX}=$got[2];
}

for my $key (sort {$a <=> $b} keys %data) {
        print "date:$key, rx:$data{$key}{RX}, tx:$data{$key}{TX}\n";
}

Thanks for any advise you can throw my way.

[BSOD2600]

this script already pulls mysql traffic data -- you don't want this?

If you want to go ahead and use externally updated rrd files, thats fine. Read this http://docs.cacti.net/node/358 on how to make it work with Cacti. Naming of the *.rrd file doesn't matter (assuming that file doesn't exist of course).

If you're data doesn't get updated every 5 minutes, then you have two options. 1) Go ahead and run your data collection script every 5 minutes, even though there is no new data. 2) Get Boost working.

Either way you to it, ideally there should be a script which Cacti invokes to collect the data (unless its really time/cpu intensive and should get its own external task). Read through the documentation site on how to write scripts, templates, etc to work with cacti.

[hornet]

BSOD2600,

Thanks for the link to the SQL script. Although it is for monitoring an SQL server, it serves as an example just as well.

I guess I'm still wondering if using cacti to feed rrdtool the data is the best way to go about this. With the 5 minute poll, wont the time between 2007-06-26 01:00:00 and 2007-06-26 02:00:00 just be zeros?
Or, does using cacti provide some other advantages?

Thanks again!