Perl SNMP polling script

[rcnavas]

After a night without sleeping... I finished this *very* dirty script in perl that substitutes cmd.php.
Prerequisites:
* Perl Modules: DBI, DBD::Mysql, Net::SNMP, Sys::Syslog, RRDs (rrdtool perl module)
* Make a backup of your RRA files first!! or test it on a clean cacti installation.
* (Recommended) Try to optimize Mysql tables with indexes... (view a previous post)

Installation:
* Copy & Paste the code on a file... then chmod 0755 <filename>
* Put it anywhere on the file system, and edit the configuration parameters at the top of the file.... Sorry... no code comments yet!
* Test it manually... it will write a log in /tmp/spine.log
* Put it to run on the crontab every 5 minutes as you do with cmd.php.

Limitations:
* a lot!
* it can handle only those data sources that use SNMP Network Data or SNMP Data.... any DS that needs an external program to gather data will be ignored.
* Be aware that I based the code on the version 0.6.7 of Cacti... I don't know if it will break anything on other versions.

Results so far:
* on my production CACTI Box with 520 data sources (mostly SNMP) it takes less than a minute to complete with no noticeable load on the machine.

What it does:
* Almost the same as cmd.php, except that doesn't touch all Data Sources that fork an external program... only those that use INTERNAL SNMP functions. Also it cannot handle Multi output DS yet... (I don't get them yet...)
* It uses the Net::SNMP capability to do non-blocking SNMP requests... so they execute "simultaneously" (sort of)

Please test it out... and if anyone thinks its worth it to complete/extend, send me an email. as always bug reports and comments are welcome.

The code follows:

Code: Select all

#!/usr/bin/perl -w
# Program:		spine.pl
# Description:	Subtituto del cmd.php incluido en el CACTI
#				quien es el que se encarga de colectar los
#				datos via SNMP para actualizar las graficas RRD.

use DBI;
use Net::SNMP;
use RRDs;
use Sys::Syslog;

$0			= 'spine';
$path_cacti = '/var/www/html/cacti';

%config = (	'debug'			=> 1,
			'MYSQL_HOST'	=> 'localhost',
			'MYSQL_DB'		=> 'cacti',
			'MYSQL_USER'	=> 'cacti',
			'MYSQL_PASS'	=> 'secret',
			'snmp_timeout'	=> 3,
			'path_cacti'	=> "$path_cacti",
			'path_images'	=> "$path_cacti/graphs",
			'path_rra'		=> "$path_cacti/rra-spine",
			'path_log'		=> "$path_cacti/log/rrd.log",
			'path_rrdtool'	=> "/usr/local/rrdtool/bin/rrdtool",
			'syslog_facility'=> "local4"
);

%oidOctets = (	'in'		=> ".1.3.6.1.2.1.2.2.1.10",
				'out'		=> ".1.3.6.1.2.1.2.2.1.16",
				'hcin'		=> ".1.3.6.1.2.1.2.2.1.10",
				'hcout'		=> ".1.3.6.1.2.1.2.2.1.16" );

printlog('debug', "INICIO spine.pl " . localtime()); 

$dbh	= DBI->connect(	'dbi:mysql:host=' . $config{'MYSQL_HOST'} . ';database=' . $config{'MYSQL_DB'},
			$config{'MYSQL_USER'},
			$config{'MYSQL_PASS'}	) or do {
											printlog('err', "ERROR: No se pudo conectar a Servidor MySQL! (" . $DBI::errstr . ")");
											exit 1;
											};

$sql	= "select d.id, d.name, d.srcid, s.formatstrin, s.formatstrout, s.id as sid, s.type
			from rrd_ds d left join src s on d.srcid=s.id
			where 
			d.active=\"on\" and 
			d.subdsid=0";

$sth1	= $dbh->prepare($sql);
$sth1->execute() or do {
						printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
						exit 1;
						};

while ($sql_id = $sth1->fetchrow_hashref()) {
	printlog('debug', "Datasource: " . $sql_id->{name});
	if ($sql_id->{formatstrout} =~ /<(\S+)>/i) {
		$out_field	= $1;
	} else {
		printlog('err', "ERROR: formatstrout = " . $sql_id->{formatstrout} . " is not valid!");
		next;
	}
	$sql	= "select d.fieldid, d.dsid, d.value, f.srcid, f.dataname
				from src_data d left join src_fields f on d.fieldid=f.id
				where
				d.dsid=" . $sql_id->{id} . " and 
				f.srcid=" . $sql_id->{srcid};

	$sth2	= $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	while ($sql_id_field = $sth2->fetchrow_hashref()) {
		printlog('debug', "\tField: " . $sql_id_field->{dataname} . " Value: " . $sql_id_field->{value});
		$sql_id_field_value{$sql_id_field->{dataname}} = $sql_id_field->{value};
	}
	if ($sql_id->{type} eq "") {
		# We have to fork a external program...
		# Not implemented Yet...
		next;
	} else {	
		# It's an internal SNMP function...
		if	($sql_id->{type} eq "snmp_net") {
			my ($snmp, $error) = Net::SNMP->session(
							-hostname	=> $sql_id_field_value{"ip"},
							-community	=> $sql_id_field_value{"community"},
							-port		=> 161,
							-timeout	=> $config{"snmp_timeout"},
							-nonblocking	=> 0x1 );
			if (!defined($snmp)) {
				printlog('err', "ERROR: SNMP connect error: $error");
				next;
			}
			my $oid	   = $oidOctets{$sql_id_field_value{"inout"}} . "." . $sql_id_field_value{"ifnum"};
			printlog('debug', "\tOID to get = $oid");
			my $result = $snmp->get_request(
							-varbindlist	=> [$oid],
							-callback	=> [ \&get_snmp_net, $oid, $sql_id, $out_field ] );

		} elsif	($sql_id->{type} eq "snmp") {
			my ($snmp, $error) = Net::SNMP->session(
							-hostname	=> $sql_id_field_value{"ip"},
							-community	=> $sql_id_field_value{"community"},
							-port		=> 161,
							-timeout	=> $config{"snmp_timeout"},
							-nonblocking	=> 0x1 );
			if (!defined($snmp)) {
				printlog('err', "ERROR: SNMP connect error: $error");
				next;
			}
			my $oid	   = $sql_id_field_value{"oid"};
			printlog('debug', "\tOID to get = $oid");
			my $result = $snmp->get_request(
							-varbindlist	=> [$oid],
							-callback	=> [ \&get_snmp_data, $oid, $sql_id, $out_field ] );

		
		} else {
			# Bad Type...!
		}
	}
}

printlog('debug', "SENDING ALL SNMP requests...");
Net::SNMP->snmp_dispatcher();
printlog('debug', "FIN spine.pl " . localtime());
exit 0;

#
# FUNCTIONS....
#

sub get_snmp_net {
	my ($session, $oid, $sql_id, $out_field) = @_;
	my $host = $session->hostname;
	my $out_data = '';
	if (!defined($session->var_bind_list)) {
		my $error  = $session->error;
		printlog('err', "ERROR: get_snmp_net($host) Error: $error");
	} else {
		my $result = $session->var_bind_list->{$oid};
		printlog('debug', "get_snmp_net($host) $oid = $result");
		$out_data  = "$result";
	}
	update_src_data($sql_id, $out_field, $out_data);
	rrdtool_function_create($sql_id->{id}, 0);
	if ($sql_id->{srcid} != 0) {
		rrdtool_function_update($sql_id->{id}, 0, 0);
	}
	return;
}

sub get_snmp_data {
	my ($session, $oid, $sql_id, $out_field) = @_;
	my $host = $session->hostname;
	my $out_data = "";
	if (!defined($session->var_bind_list)) {
		my $error  = $session->error;
		printlog('err', "ERROR: get_snmp_data($host) Error: $error");
	} else {
		my $result = $session->var_bind_list->{$oid};
		printlog('debug', "get_snmp_data($host) $oid = $result");
		$out_data = "$result";
	}
	update_src_data($sql_id, $out_field, $out_data);
	rrdtool_function_create($sql_id->{id}, 0);
	if ($sql_id->{srcid} != 0) {
		rrdtool_function_update($sql_id->{id}, 0, 0);
	}
	return;
}

sub update_src_data {
	my ($sql_id, $out_field, $out_data) = @_;
	if ($sql_id->{srcid} == 0) {
		printlog('err', "ERROR: Data Source: " . $sql_id->{name} . " does not have a data input source assigned to it. No data will be gathered; if cacti does not gather data for this data source please deactivate it.");
		return;
	}
	$sql	= "SELECT id " .
			  "FROM src_fields " .
			  "WHERE  dataname = '$out_field' " .
			  " and   srcid = " . $sql_id->{srcid} . 
			  " and   inputoutput='out'";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr );
							exit 1;
							};
	my $sql_id_fields = $sth1->fetchrow_hashref();
	my $current_data_source_id = $sql_id->{id};
	$sql	= "SELECT id " .
				"FROM src_data " .
				"WHERE fieldid = " . $sql_id_fields->{id} . 
				" and dsid = $current_data_source_id";
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $new_data_id = 0;
	if ($sth2->rows > 0) {
		my $sql_id_data = $sth2->fetchrow_hashref();
		$new_data_id = $sql_id_data->{id};
	}
	$sql	= "REPLACE INTO src_data " .
				"(id, fieldid, dsid, value) " .
				"values (" .
				"$new_data_id, " . 
				$sql_id_fields->{id} . ", " .
				"$current_data_source_id, " .
				"'$out_data')";
	printlog('debug', "update_src_data(" . $sql_id->{name} . ") Replace SQL = $sql");
	my $affected_rows = 0;
	$affected_rows = $dbh->do($sql);
	if (! $affected_rows ) {
		printlog('err', "WARN: El REPLACE no modifico ninguna fila...");
	}
	return;	
}
#
# Cacti's support functions...
#
sub CheckDataSourceName {
	my $data_source_name = shift;
	$data_source_name =~ s/\s/_/g;
	$data_source_name =~ s/[\/\*\\\&\%\"\',\.]+//g;
	$data_source_name = substr($data_source_name,0,19);
	return lc($data_source_name);
}

sub GetDataSourceName {
	my $dsid = shift;
	if ($dsid == 0) { return ""; }
	$sql = "select name, dsname from rrd_ds where id=$dsid";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	if ($sth1->rows <= 0) {
		printlog('err', "ERROR: DataSource Name NOT found!");
		return "";
	}
	my $sql_id = $sth1->fetchrow_hashref();
	if ($sql_id->{dsname} eq "") {
		return CheckDataSourceName($sql_id->{name});
	} else {
		return $sql_id->{dsname};
	}
}

sub GetDataSourcePath {
	my ($data_source_id, $expand_paths) = @_;
	my $data_source_path = '';
	if ($data_source_id == 0) { return ""; }
	$sql = "select name, dspath from rrd_ds where id = $data_source_id";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr );
							exit 1;
							};
	if ($sth1->rows > 0) {
		my $sql_id = $sth1->fetchrow_hashref();
		if ($sql_id->{dspath} eq "") {
			$data_source_path = "<path_rra>/" . CheckDataSourceName($sql_id->{name}) . ".rrd";
		} else {
			if ($sql_id->{dspath} !~ /\//) {
				$data_source_path = "<path_rra>/" . $sql_id->{dspath};
			} else {
				$data_source_path = $sql_id->{dspath};
			}
		}
		if ($expand_paths) {
			$data_source_path =~ s/<path_rra>/$config{"path_rra"}/g;
		}
		return $data_source_path;
	}
}

sub rrdtool_function_create {
	my ($dsid, $show_source) = @_;
	my $data_source_path = GetDataSourcePath($dsid, 1);
	my $create_ds = "";
	my $create_rra = ""; 
	my @args = ();
	if (!$show_source) {
		if ( -f $data_source_path ) {
			printlog('err', "WARN: rrd file $data_source_path already exists!");
			return -1;
		}
	}
	$sql = "select 
			d.step, 
			r.xfilesfactor, r.steps, r.rows,
			c.name as cname,
			(r.rows*r.steps) as rs 
			from rrd_ds d left join lnk_ds_rra l on l.dsid=d.id
			left join rrd_rra r on l.rraid=r.id
			left join lnk_rra_cf rc on rc.rraid=r.id
			left join def_cf c on rc.consolidationfunctionid=c.id
			where d.id=$dsid
			order by rc.consolidationfunctionid, rs";

	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	if ($sth1->rows <= 0) {
		printlog('err', "ERROR: There are no RRA's assigned to DSID $dsid!");
		return -1;
	}
	my $sql_id = $sth1->fetchrow_hashref();
	$create_ds .= " \\\n--step " . $sql_id->{step} . " \\\n";
	push @args, "--step";
	push @args, $sql_id->{step};
	$sql = "select 
			d.id, d.heartbeat, d.minvalue, d.maxvalue,d.subdsid,
			t.name
			from rrd_ds d left join def_ds t on d.datasourcetypeid=t.id
			where d.id=$dsid
			or d.subdsid=$dsid
			order by d.id";
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $sql_id_ds;
	my $rows = $sth2->rows;
	while ($sql_id_ds = $sth2->fetchrow_hashref()) {
		if ( ($rows > 1 and $sql_id_ds->{subdsid} != 0) or ($rows == 1)) {
			my $data_source_name = GetDataSourceName($sql_id_ds->{id});
			$create_ds .= "DS:$data_source_name:" . 
				$sql_id_ds->{name} . ":" . 
				$sql_id_ds->{heartbeat} . ":" . 
				$sql_id_ds->{minvalue} . ":" . 
				$sql_id_ds->{maxvalue} . " \\\n";
			push @args, "DS:$data_source_name:" . 
				$sql_id_ds->{name} . ":" . 
				$sql_id_ds->{heartbeat} . ":" . 
				$sql_id_ds->{minvalue} . ":" . 
				$sql_id_ds->{maxvalue};
		}
	}
	do {
		$create_rra .= "RRA:" . $sql_id->{cname} . ":" . 
			$sql_id->{xfilesfactor} . ":" . 
			$sql_id->{steps} . ":" . 
			$sql_id->{rows} . " \\\n";
		push @args, "RRA:" . $sql_id->{cname} . ":" . 
			$sql_id->{xfilesfactor} . ":" . 
			$sql_id->{steps} . ":" . 
			$sql_id->{rows};
	} while ($sql_id = $sth1->fetchrow_hashref());
	
	printlog('debug', $config{"path_rrdtool"} . " create \\\n$data_source_path$create_ds$create_rra");
	if (!$show_source) {
		RRDs::create($data_source_path, @args);
		if ($RRDs::error) {
			printlog('err', "ERROR: rrdtool create $data_source_path failed! (" . $RRDs::error . ")");
		} else {
			printlog('debug', "RRA file $data_source_path Created!");
		}
	}
	return;
}

sub rrdtool_function_update {
	my ($dsid, $multi_data_source, $show_source) = @_;
	my $data_source_path= GetDataSourcePath($dsid, 1);
	my $update_string	= "N";
	my $template_string	= "";
	
	if ($multi_data_source eq "") {
		$sql = "select id from rrd_ds where subdsid=$dsid";
		my $sth1 = $dbh->prepare($sql);
		$sth1->execute() or do {
								printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
								exit 1;
								};
		if ($sth1->rows == 0) {
			$multi_data_source = 0;
		} else {
			$multi_data_source = 1;
		}
	}
	
	if ($multi_data_source == 1) {
		$sql = "select 
				d.dsname,
				a.value
				from rrd_ds d left join src_fields f on d.subfieldid=f.id 
				left join src_data a on d.id=a.dsid
				where d.subdsid=$dsid
				and f.inputoutput=\"out\"
				and f.updaterra=\"on\"
				order by d.id";
	} else {
		$sql = "select 
				d.dsname,
				a.value
				from rrd_ds d left join src_data a on d.id=a.dsid
				left join src_fields f on a.fieldid=f.id 
				where d.id=$dsid
				and f.inputoutput=\"out\"
				and f.updaterra=\"on\"
				order by d.id";
	}
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do { 
							printlog('err', "ERROR: No se pudo ejecutar Query MySQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $rows = $sth2->rows();
	my $data_value = "U";
	my $i = 0;
	while (my $sql_id = $sth2->fetchrow_hashref()) {
		if ($sql_id->{value} =~ /[0-9\.]+/i) {
			$data_value = $sql_id->{value};
		}
		$update_string .= ":$data_value";
		$template_string .= $sql_id->{dsname};
		$i++;
		if ($i < $rows) { $template_string .= ":"; }
	}

	my $command_line = "update $data_source_path --template $template_string $update_string";

	printlog('debug', $config{"path_rrdtool"} . " $command_line");	
	if (!$show_source) {
		RRDs::update($data_source_path, "--template", $template_string, $update_string);
		if ($RRDs::error) {
			printlog('err', "ERROR: rrdtool update $data_source_path failed! (" . $RRDs::error . ")");
		} else {
			printlog('debug', "RRA file $data_source_path Updated!");
		}
	}
	return;
}

sub printlog {
	my($level, $msg, $facility);
	$level		= shift @_;
	$msg		= shift @_;
	$facility	= ( ( !defined($config{'syslog_facility'}) ) ? 'local5' : $config{'syslog_facility'} );
	if ( $level =~ /debug/i && !$config{'debug'} ) {
		return;
	}
	if ( !  openlog($0, 'cons,pid', $facility) ) {
		print STDERR "No se pudo abrir SYSLOG: $0\t$facility\.$level\t$msg\n";
		return;
	}
	syslog($level, '%s', $msg);
	closelog();
	if ($config{'debug'}) {
		open  LOG, ">>/tmp/$0.log" or return;
		print LOG localtime() . " [$0 -> $facility\.$level] $msg\n";
		close(LOG);
	}
	return;
}

Take care.... now I'm going to sleep

Roberto Carlos Navas
Internet de Telemovil
rcnavas@telemovil.com

[raX]

You are awesome! And the first person I might add to complete a script that can be used in place of cmd.php. I am going to go ahead and add this to the 'Additional Scripts' section of the cacti page so others will be able to easily find it. I will also point back to this thread for reference.

Sorry I haven't looked at it myself yet, but I will make sure to do that sometime here in the near future.

Thanks again for your help.

-Ian

[rcnavas]

raX:

Thanks for the comments, I'm posting a version 0.2 with more instructions and a couple of modifications.

Instructions:
* Please check/follow pre-requisites explained in my original post.
* For this example I'm assuming your putting this script in a file called: spine.pl which will be located at /usr/local/bin... remember to chmod 0755 spine.pl
* At the top of the script, please modify the configuration described as follows:

Code: Select all

# Configuration parameters
%config = (	'debug'			=> 1,			# Need debugging info? 0=No 1=Yes
			'syslog_facility'	=> 'local4',		# Syslog facility to send logging
			'MYSQL_HOST'		=> 'localhost',	# MySQL server where CACTI Database is
			'MYSQL_DB'		=> 'cacti',		# MySQL cacti database
			'MYSQL_USER'		=> 'cacti',		# MySQL username
			'MYSQL_PASS'		=> 'secret',		# MySQL password
			'snmp_timeout'		=> 5,			# How many seconds to wait for a SNMP response
			'snmp_retries'		=> 2,			# How many times to retry SNMP get
			'snmp_version'		=> 1,			# SNMP protocol version to use
);

* On your syslog configuration file (usually at /etc/syslog.conf) add the following line:

Code: Select all

local4.*                                         /var/log/spine.log

* Restart syslogd
* in /etc/crontab add the following line:

Code: Select all

*/5 * * * * apache /usr/local/bin/spine.pl /var/www/html/cacti >> /dev/null 2>&1

* Note: 'apache' is the username the apache web server runs as... your setup may vary.
* (Optional) If you want to still process all Data Sources which Data Input is NOT INTERNAL (all those that fork a separate program) you'll need to modify cmd.php as follows:
REPLACE in cmd.php:

Code: Select all

$sql_id = mysql_query("select d.id, d.name, d.srcid, 
        s.formatstrin, s.formatstrout, s.id as sid, s.type
        from rrd_ds d 
        left join src s 
        on d.srcid=s.id 
        where d.active=\"on\"
        and d.subdsid=0",$cnn_id);

WITH THIS:

Code: Select all

$sql_id = mysql_query("select d.id, d.name, d.srcid, 
        s.formatstrin, s.formatstrout, s.id as sid, s.type
        from rrd_ds d 
        left join src s 
        on d.srcid=s.id 
        where d.active=\"on\"
        and d.subdsid=0
        and (s.type='' or s.type IS NULL)",$cnn_id);

* Leave cmd.php running on crontab as usual, this will let cmd.php only process those Data Sources which needs to fork a program to gather its data.... and the new perl script will take care of all SNMP Data Sources.
* If you only have SNMP Data Sources, please DO NOT leave running cmd.php since both scripts will gather data twice every 5 minutes and things can get worse.

If you follow this instructions, the script will log all it's doing or any possible error to /var/log/spine.log if you want to troubleshoot.

Ok now, here goes the new code:

Code: Select all

#!/usr/bin/perl -w
# Program:		spine.pl
# Description:	Subtituto del cmd.php incluido en el CACTI
#				quien es el que se encarga de colectar los
#				datos via SNMP para actualizar las graficas RRD.
# Version:		0.2 (17/Aug/2002)
# Autor:		Roberto Carlos Navas <rcnavas@telemovil.com>

# Perl Modules to be used.
use DBI;
use Net::SNMP;
use RRDs;
use Sys::Syslog;

# Configuration parameters
%config = (	'debug'			=> 1,			# Need debugging info? 0=No 1=Yes
			'syslog_facility'	=> 'local4',		# Syslog facility to send logging
			'MYSQL_HOST'		=> 'localhost',	# MySQL server where CACTI Database is
			'MYSQL_DB'		=> 'cacti',		# MySQL cacti database
			'MYSQL_USER'		=> 'cacti',		# MySQL username
			'MYSQL_PASS'		=> 'secret',		# MySQL password
			'snmp_timeout'		=> 5,			# How many seconds to wait for a SNMP response
			'snmp_retries'		=> 2,			# How many times to retry SNMP get
			'snmp_version'		=> 1,			# SNMP protocol version to use
);

# SNMP Numeric OIDs for getting Interface Octects Counters
%oidOctets = (	'in'		=> ".1.3.6.1.2.1.2.2.1.10",
				'out'		=> ".1.3.6.1.2.1.2.2.1.16",
				'hcin'		=> ".1.3.6.1.2.1.2.2.1.10",
				'hcout'		=> ".1.3.6.1.2.1.2.2.1.16"
);

# You should not touch the code below this line...
# MAIN program starts here...

printlog('debug', "STARTING spine.pl " . localtime()); 
$dbh	= DBI->connect(	'dbi:mysql:host=' . $config{'MYSQL_HOST'} . ';database=' . $config{'MYSQL_DB'},
						$config{'MYSQL_USER'},
						$config{'MYSQL_PASS'}	) or do {
														printlog('err', "ERROR: Could not connect to MySQL server! (" . $DBI::errstr . ")");
														exit 1;
														};

get_cacti_config();

$sql	= "SELECT d.id, d.name, d.srcid, s.formatstrin, s.formatstrout, s.id as sid, s.type
			FROM rrd_ds d LEFT JOIN src s ON d.srcid=s.id
			WHERE 
			d.active = 'on' AND 
			d.subdsid= 0";
$sth1	= $dbh->prepare($sql);
$sth1->execute() or do {
						printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
						exit 1;
						};
while ($sql_id = $sth1->fetchrow_hashref()) {
	printlog('debug', "Processing Data Source: " . $sql_id->{name});
	$sql	= "SELECT d.fieldid, d.dsid, d.value, f.srcid, f.dataname
				FROM src_data d LEFT JOIN src_fields f ON d.fieldid=f.id
				WHERE
				d.dsid  =" . $sql_id->{id} . " and 
				f.srcid =" . $sql_id->{srcid};
	$sth2	= $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: Could Not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	while ($sql_id_field = $sth2->fetchrow_hashref()) {
		printlog('debug', " DS Field: " . $sql_id_field->{dataname} . " Value: " . $sql_id_field->{value});
		$sql_id_field_value{$sql_id_field->{dataname}} = $sql_id_field->{value};
	}
	if ($sql_id->{type} eq '') {
		# We have to fork a external program...
		# Not implemented Yet...
		printlog('debug', "Datasource " . $sql_id->{name} . "Needs to fork a program... not implemented yet!");
		next;
	}
	# It's an internal SNMP function...
	# It shouldn't be a multi data source, so only one output parameter is expected...
	if ($sql_id->{formatstrout} =~ /<(\S+)>/i) {
		$out_field	= $1;
	} else {
		printlog('err', "ERROR: formatstrout = " . $sql_id->{formatstrout} . " is not valid!");
		next;
	}
	($snmp, $error) = Net::SNMP->session(
										-hostname		=> $sql_id_field_value{"ip"},
										-community		=> $sql_id_field_value{"community"},
										-port			=> 161,
										-version		=> $config{"snmp_version"},
										-retries		=> $config{"snmp_retries"},
										-timeout		=> $config{"snmp_timeout"},
										-nonblocking	=> 0x1 );
	if (!defined($snmp)) {
		printlog('err', "ERROR: SNMP connect error: " . $error);
		next;
	}
	if ( $sql_id->{type} eq "snmp_net" ) {
		$oid = $oidOctets{$sql_id_field_value{"inout"}} . "." . $sql_id_field_value{"ifnum"};
	} elsif	( $sql_id->{type} eq "snmp" ) {
		$oid = $sql_id_field_value{"oid"};
	} else {
		printlog('err', "ERROR: Datasource " . $sql_id->{name} . "has an invalid type: " . $sql_id->{type});
		next;
	}
	printlog('debug', " OID to get = $oid");
	$result = $snmp->get_request(
								-varbindlist	=> [ $oid ],
								-callback		=> [ \&get_snmp_data, $oid, $sql_id, $out_field ] );
	if (!$result) {
		printlog('err', "ERROR: During SNMP get_request Setup (" . $snmp->error() . ")");
		$snmp->close();
	}
}

printlog('debug', "SENDING ALL SNMP requests...");
Net::SNMP->snmp_dispatcher();
printlog('debug', "END spine.pl " . localtime());
exit 0;

#
# FUNCTIONS....
#

# get_cacti_config: Gather configuration parameters from cacti MySQL DB
sub get_cacti_config {
	my($sth, $parameter);
	$sql	= "SELECT Name, Value FROM settings";
	$sth	= $dbh->prepare($sql);
	$sth->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr );
							exit 1;
							};
	while ( $parameter = $sth->fetchrow_hashref() ) {
		$config{ $parameter->{Name} }	= $parameter->{Value};
		printlog('debug', "get_cacti_config(): " . $parameter->{Name} . "=" . $parameter->{Value});
	}
	$sth->finish();
	$config{"path_cacti"}	= $config{"path_webroot"} . $config{"path_webcacti"};
	$config{"path_images"}	= $config{"path_cacti"} . "/graphs";
	$config{"path_rra"}		= $config{"path_cacti"} . "/rra";
	$config{"path_log"}		= $config{"path_cacti"} . "/log/rrd.log";
	if (! -d $config{"path_cacti"} ) {
		printlog('err', "ERROR: cacti path " . $config{"path_cacti"} . " is not a directory or does not exists!");
		exit 1;
	}
	return;
}

# printlog: To send a message to the log (syslog)
sub printlog {
	my($level, $msg, $facility);
	$level		= shift @_;
	$msg		= shift @_;
	$facility	= ( ( !defined($config{'syslog_facility'}) ) ? 'local5' : $config{'syslog_facility'} );
	if ( $level =~ /debug/i && !$config{'debug'} ) {
		return;
	}
	if ( !  openlog($0, 'cons,pid', $facility) ) {
		print STDERR "No se pudo abrir SYSLOG: $0\t$facility\.$level\t$msg\n";
		return;
	}
	syslog($level, '%s', $msg);
	closelog();
	if ($config{'debug'}) {
		open  LOG, ">>/tmp/$0.log" or return;
		print LOG localtime() . " [$0 -> $facility\.$level] $msg\n";
		close(LOG);
	}
	return;
}

# get_snmp_data: Handles the SNMP response when the DS is type SNMP Network
sub get_snmp_data {
	my ($session, $oid, $sql_id, $out_field) = @_;
	my $host		= $session->hostname;
	my $out_data	= '';
	if (!defined($session->var_bind_list)) {
		my $error  = $session->error;
		printlog('err', "ERROR: get_snmp_data($host) Error: " . $error);
	} else {
		my $result = $session->var_bind_list->{$oid};
		printlog('debug', "get_snmp_data($host) $oid = $result");
		$out_data = "$result";
	}
	update_src_data($sql_id, $out_field, $out_data);
	rrdtool_function_create($sql_id->{id}, 0);
	if ($sql_id->{srcid} != 0) {
		rrdtool_function_update($sql_id->{id}, 0, 0);
	}
	return;
}

# update_src_data: Store received SNMP data on CACTI's MySQL tables...
sub update_src_data {
	my ($sql_id, $out_field, $out_data) = @_;
	if ($sql_id->{srcid} == 0) {
		printlog('err', "ERROR: Data Source: " . $sql_id->{name} . " does not have a data input source assigned to it. No data will be gathered; if cacti does not gather data for this data source please deactivate it.");
		return;
	}
	$sql	= "SELECT id " .
			  "FROM src_fields " .
			  "WHERE  dataname = '$out_field' " .
			  " and   srcid = " . $sql_id->{srcid} . 
			  " and   inputoutput='out'";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr );
							exit 1;
							};
	my $sql_id_fields = $sth1->fetchrow_hashref();
	my $current_data_source_id = $sql_id->{id};
	$sql	= "SELECT id " .
				"FROM src_data " .
				"WHERE fieldid = " . $sql_id_fields->{id} . 
				" and dsid = $current_data_source_id";
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $new_data_id = 0;
	if ($sth2->rows > 0) {
		my $sql_id_data = $sth2->fetchrow_hashref();
		$new_data_id = $sql_id_data->{id};
	}
	$sql	= "REPLACE INTO src_data " .
				"(id, fieldid, dsid, value) " .
				"values (" .
				"$new_data_id, " . 
				$sql_id_fields->{id} . ", " .
				"$current_data_source_id, " .
				"'$out_data')";
	printlog('debug', "update_src_data(" . $sql_id->{name} . ") Replace SQL = $sql");
	my $affected_rows = 0;
	$affected_rows = $dbh->do($sql);
	if (! $affected_rows ) {
		printlog('err', "WARN: MySQL REPLACE didn't affect any rows...??");
	}
	return;	
}

#
# CACTI's ported functions...
#
sub CheckDataSourceName {
	my $data_source_name = shift;
	$data_source_name =~ s/\s/_/g;
	$data_source_name =~ s/[\/\*\\\&\%\"\',\.]+//g;
	$data_source_name = substr($data_source_name,0,19);
	return lc($data_source_name);
}

sub GetDataSourceName {
	my $dsid = shift;
	if ($dsid == 0) { return ""; }
	$sql = "select name, dsname from rrd_ds where id=$dsid";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	if ($sth1->rows <= 0) {
		printlog('err', "ERROR: DataSource Name NOT found!");
		return "";
	}
	my $sql_id = $sth1->fetchrow_hashref();
	if ($sql_id->{dsname} eq "") {
		return CheckDataSourceName($sql_id->{name});
	} else {
		return $sql_id->{dsname};
	}
}

sub GetDataSourcePath {
	my ($data_source_id, $expand_paths) = @_;
	my $data_source_path = '';
	if ($data_source_id == 0) { return ""; }
	$sql = "select name, dspath from rrd_ds where id = $data_source_id";
	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr );
							exit 1;
							};
	if ($sth1->rows > 0) {
		my $sql_id = $sth1->fetchrow_hashref();
		if ($sql_id->{dspath} eq "") {
			$data_source_path = "<path_rra>/" . CheckDataSourceName($sql_id->{name}) . ".rrd";
		} else {
			if ($sql_id->{dspath} !~ /\//) {
				$data_source_path = "<path_rra>/" . $sql_id->{dspath};
			} else {
				$data_source_path = $sql_id->{dspath};
			}
		}
		if ($expand_paths) {
			$data_source_path =~ s/<path_rra>/$config{"path_rra"}/g;
		}
		return $data_source_path;
	}
}

sub rrdtool_function_create {
	my ($dsid, $show_source) = @_;
	my $data_source_path	= GetDataSourcePath($dsid, 1);
	my $create_ds			= '';
	my $create_rra			= ''; 
	my @args				= ();
	if (!$show_source) {
		if ( -f $data_source_path ) {
			printlog('err', "WARN: rrd file $data_source_path already exists!");
			return -1;
		}
	}
	$sql = "select 
			d.step, 
			r.xfilesfactor, r.steps, r.rows,
			c.name as cname,
			(r.rows*r.steps) as rs 
			from rrd_ds d left join lnk_ds_rra l on l.dsid=d.id
			left join rrd_rra r on l.rraid=r.id
			left join lnk_rra_cf rc on rc.rraid=r.id
			left join def_cf c on rc.consolidationfunctionid=c.id
			where d.id=$dsid
			order by rc.consolidationfunctionid, rs";

	my $sth1 = $dbh->prepare($sql);
	$sth1->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	if ($sth1->rows <= 0) {
		printlog('err', "ERROR: There are no RRA's assigned to DSID $dsid!");
		return -1;
	}
	my $sql_id = $sth1->fetchrow_hashref();
	push @args, "--step";
	push @args, $sql_id->{step};
	$sql = "select 
			d.id, d.heartbeat, d.minvalue, d.maxvalue,d.subdsid,
			t.name
			from rrd_ds d left join def_ds t on d.datasourcetypeid=t.id
			where d.id=$dsid
			or d.subdsid=$dsid
			order by d.id";
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do {
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $sql_id_ds;
	my $rows = $sth2->rows;
	while ($sql_id_ds = $sth2->fetchrow_hashref()) {
		if ( ($rows > 1 and $sql_id_ds->{subdsid} != 0) or ($rows == 1)) {
			my $data_source_name = GetDataSourceName($sql_id_ds->{id});
			push @args, "DS:$data_source_name:" . 
				$sql_id_ds->{name} . ":" . 
				$sql_id_ds->{heartbeat} . ":" . 
				$sql_id_ds->{minvalue} . ":" . 
				$sql_id_ds->{maxvalue};
		}
	}
	do {
		push @args, "RRA:" . $sql_id->{cname} . ":" . 
			$sql_id->{xfilesfactor} . ":" . 
			$sql_id->{steps} . ":" . 
			$sql_id->{rows};
	} while ($sql_id = $sth1->fetchrow_hashref());
	
	printlog('debug', "rrdtool create $data_source_path " . join(' ', @args));
	if (!$show_source) {
		RRDs::create($data_source_path, @args);
		if ($RRDs::error) {
			printlog('err', "ERROR: rrdtool create $data_source_path failed! (" . $RRDs::error . ")");
		} else {
			printlog('debug', "RRA file $data_source_path Created!");
		}
	}
	return;
}

sub rrdtool_function_update {
	my ($dsid, $multi_data_source, $show_source) = @_;
	my $data_source_path= GetDataSourcePath($dsid, 1);
	my $update_string	= "N";
	my $template_string	= "";
	
	if ($multi_data_source eq "") {
		$sql = "select id from rrd_ds where subdsid=$dsid";
		my $sth1 = $dbh->prepare($sql);
		$sth1->execute() or do {
								printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
								exit 1;
								};
		if ($sth1->rows == 0) {
			$multi_data_source = 0;
		} else {
			$multi_data_source = 1;
		}
	}
	
	if ($multi_data_source == 1) {
		$sql = "select 
				d.dsname,
				a.value
				from rrd_ds d left join src_fields f on d.subfieldid=f.id 
				left join src_data a on d.id=a.dsid
				where d.subdsid=$dsid
				and f.inputoutput=\"out\"
				and f.updaterra=\"on\"
				order by d.id";
	} else {
		$sql = "select 
				d.dsname,
				a.value
				from rrd_ds d left join src_data a on d.id=a.dsid
				left join src_fields f on a.fieldid=f.id 
				where d.id=$dsid
				and f.inputoutput=\"out\"
				and f.updaterra=\"on\"
				order by d.id";
	}
	my $sth2 = $dbh->prepare($sql);
	$sth2->execute() or do { 
							printlog('err', "ERROR: Could not execute SQL! Error: " . $dbh->errstr);
							exit 1;
							};
	my $rows = $sth2->rows();
	my $data_value = "U";
	my $i = 0;
	while (my $sql_id = $sth2->fetchrow_hashref()) {
		if ($sql_id->{value} =~ /[0-9\.]+/i) {
			$data_value = $sql_id->{value};
		}
		$update_string .= ":$data_value";
		$template_string .= $sql_id->{dsname};
		$i++;
		if ($i < $rows) { $template_string .= ":"; }
	}

	my $command_line = "update $data_source_path --template $template_string $update_string";

	printlog('debug', "rrdtool " . $command_line);	
	if (!$show_source) {
		RRDs::update($data_source_path, "--template", $template_string, $update_string);
		if ($RRDs::error) {
			printlog('err', "ERROR: rrdtool update $data_source_path failed! (" . $RRDs::error . ")");
		} else {
			printlog('debug', "RRA file $data_source_path Updated!");
		}
	}
	return;
}

This new version only needs to know how to connect to the MySQL server and then it will gather all other configuration from there.

Let me know of any bug or how does improve the polling time and CPU load... I'm wondering how much can scale.

Take care!

Roberto Carlos Navas
Internet de Telemovil

[met]

WOW!!! Looks impressive!
Have you any plan to add multi output DS?
I can help, if needed.

Regards,

Marco.

[Ricokey]

Roberto,

I am polling 350+ ds and the load was around 1 .9 .9 and just making the 5 min interval, now they are 500+ in under 40s and the load is .26 .15 .1 . Pretty cool. I'm also interested on how much it can scale...We have a poller that is handling 9000+ polls/5 min for this netowrk monitoring system...looking into how it works now....I'm trying to figure out something that can poll 50,000+ OIDs...

awesome job

-Gerard

[bdbrumm]

I've been running this script for a couple of days now.
Previously, my box was not making the 5 minute interval, had stalled php and SNMP processes and was "in a world of hurt". After converting to the script, my load average is 0.01, 0.20, 0.21 with ~330 data sources -- lower than the php implementation with no (0) data sources!
Spine.pl take approx. 35 seconds to complete!

My box runs Red Hat 7.1, 162MB RAM. PII-233
cacti 0.6.8, php-4.2.2, apache 1.3.22

Excellent script! I recommend changing to this even for small applications.
Regards,
Bruce

[yid]

First of all, great script. I was adding/removing datasources willy-nilly, and ran into a problem with the RRD not creating, but spine.pl said that they were being created. Found the problem quickly though:

You're checking the $RRDs::error as though it were a scalar, but its actually a method.

Code: Select all

if($RRDs::error) {
    printlog('err', "ERROR:  rrdtool create $data_source_path failed! (" . $RRDs::error . ")");

needs to be:

Code: Select all

my $ERR=RRDs::error;
if($ERR) {
    printlog('err', "ERROR: rrdtool create $date_source_path failed! (" . $ERR . ")");

This needs to be changed for both "create" and "update" lines 375 and 449.

Thanks,
Aaron

[wade]

Just wondering if anymore work has been done on this? I have it running on a cacti 0.6.8a install and it is working great so far, now my server doesn't shoot to a load average of 8 or 9 when polling. And it completes in less than 60 sec unlike the 3 min or so of 6 cmd.php processes. It would be great if this could call scripts so I can use the built in ping monitor. Thanks again for the great script!

[frits]

Does this script also fixes the "gaps" in some graphs?

[moojoo]

Hi there,

after installing cacti i first looked for a possibility to launch cmd.php without having a PHP-CGI-binary installed. So i installed a cronjob like this:

*/5 * * * * wget -O - http://localhost/cacti/cmd.php

The most important thing is to be sure, that the cacti-directory is not served to remote computers. So this solution is not a security-hole or something like this.

moojoo

[dhelquist]

This perl script is great! Only problem I had with it is the fact that it breaks snmpv2 counters, which I depend on for some links. I put together a quick hack of the original (with all the changes listed in the posts above) that allows hcin and hcout to work correctly. (They were just using the snmpv1 OID's rather than the 64bit v2 OID's)

Change is minimal, and works for me.

Available as either the complete spine.pl or a diff at:

http://foo.oldbrownjeep.net/spine/

If you patch the original using the .diff file, it will leave your current settings in spine.pl, otherwise you will need to follow the directions posted above.

If anyone notices any errors with this - let me know.

-dallas

(Edited - wrong URL. Doh.)

[VeSS]

Does anyone know if this will support the 0.80 code?

[crazily]

Hello rcnavas

First of all, spine.pl is a very good alternative to speeding up cacti.

I just have a little problem.
The InterfaceIndex-Numbers of the switches, routers,.... do change when I reboot them.

Now I would to fix it, that cacti don't see about the InterfaceIndex-Number, separate about the InterfaceDescription.

After 2 days looking after this problem, I have found that it function with the cmd.php, but not with the spine.pl.

Now I think, that this is because of the perl-script.

Could you say me, what I have to change in the script ??
Or could you made it and place it then in this forum ??
(I don't have know-how in perl)

I hope, this will help me.

Thank you in advance.

greets
crazily

[scarab]

hi all,

i can't get cron to execute the spine.pl script correctly. if i run it manually it works, but if i execute it from cron nothing. again, if i run it manually, it logs to the spine.log in /tmp, but when it's executed from cron, nothing.

i'm almost tearing my hait out, i couldn't get cmd.php to work either, i now i'm having trouble with this. can anyone offer me some advice please?

many thanks!

[Guest]

Are you putting the entry in /etc/crontab or a user's crontab? (I am talking red-hat linux here)

hi all,

i can't get cron to execute the spine.pl script correctly. if i run it manually it works, but if i execute it from cron nothing. again, if i run it manually, it logs to the spine.log in /tmp, but when it's executed from cron, nothing.

i'm almost tearing my hait out, i couldn't get cmd.php to work either, i now i'm having trouble with this. can anyone offer me some advice please?

many thanks!