h32mysql

#!/usr/bin/perl

use strict;
use warnings;

use Data::Dumper;
use DBI;
use Getopt::Long;
use JSON;
use LWP::UserAgent;
use POSIX;

sub GetStats;
sub Main;
sub UploadStats;

our $DEBUG = 0;

Main();

sub Main
{
	my %Stats;

	my $Target	= '192.168.0.1';
	my $Password	= '';
	my $DBHost	= 'localhost';
	my $DBUser	= '';
	my $DBPassword	= '';
	my $DBName	= '';
	my $RPass	= '';

	my $ret;

	$ret = Getopt::Long::GetOptions
	(       'debug'		=> \$DEBUG,
		'target=s'	=> \$Target,
		'host=s'	=> \$DBHost,
		'user=s'	=> \$DBUser,
		'password=s'	=> \$DBPassword,
		'database=s'	=> \$DBName,
		'rpass=s'	=> \$RPass
	);

	if ($DEBUG)
	{
		print "debug:		$DEBUG\n";
		print "target:		$Target\n";
		print "host:		$DBHost\n";
		print "user:		$DBUser\n";
		print "password:	$DBPassword\n";
		print "database:	$DBName\n";
		print "rpass:		$RPass\n"
	}

	$Stats{Time} = POSIX::strftime ("%Y-%m-%d %H:%M:%S", gmtime);

	GetStats (\%Stats, $Target);

	if ($DEBUG)
	{
		print Dumper\(%Stats);
	}

	my $DBH ||= DBI -> connect
	(
		"DBI:mysql:dbname=$DBName;".
		"host=$DBHost;".
		'port=3306',
		$DBUser,
		$DBPassword,
		{
			AutoCommit => 0
		}
	);

	UploadStats($DBH, \%Stats);

	$DBH -> commit;
	$DBH -> disconnect;
}

sub GetStats
{
	my $Stats	= $_[0];
	my $Target	= $_[1];

	my $Request;
	my $Response;
	my $Content;
	my $Payload;
	my %Payload;

	my $tmp;
	my %ByChannel;
	my @ByChannel;

	my $i;

	my $UA = new LWP::UserAgent;

	$UA -> timeout(15);

	#######################################################################
	# Sorting by frequency/channel
	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.2.1.10.127.1.1.1.1.1");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	%Payload = %{decode_json($Content)};

	delete ($Payload{1});

	foreach $i (keys(%Payload))
	{
		$tmp = $i;
		$tmp =~ s/.*\.(\d+)/$1/;
		$ByChannel{$Payload{$i}} = $tmp;
	}

	foreach $i (sort(keys(%ByChannel)))
	{
		push(@ByChannel, $ByChannel{$i});
	}
	#######################################################################

	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.4.1.4491.2.1.20.1.2.1.1");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	$Payload = decode_json($Content);

	my @UpstreamPowerLevel;


	foreach $i (4, 82 .. 85)
	{
		if ($Payload->{"1.3.6.1.4.1.4491.2.1.20.1.2.1.1.$i"})
		{
			push (@UpstreamPowerLevel, ($Payload->{"1.3.6.1.4.1.4491.2.1.20.1.2.1.1.$i"} / 10));
		}
	}

	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.2.1.10.127.1.1.1.1.6");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	$Payload = decode_json($Content);

	my @DownstreamPowerLevel;

	foreach $i (@ByChannel)
	{
		push (@DownstreamPowerLevel, ($Payload->{"1.3.6.1.2.1.10.127.1.1.1.1.6.$i"} / 10));
	}

	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.4.1.4491.2.1.20.1.24.1.1");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	$Payload = decode_json($Content);

	my @RxMER;

	foreach $i (@ByChannel)
	{
		push (@RxMER, ($Payload->{"1.3.6.1.4.1.4491.2.1.20.1.24.1.1.$i"} / 10));
	}

	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.2.1.10.127.1.1.4.1.3");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	$Payload = decode_json($Content);

	my @PreRSErrors;

	foreach $i (@ByChannel)
	{
		push (@PreRSErrors, $Payload->{"1.3.6.1.2.1.10.127.1.1.4.1.3.$i"});
	}

	$Request = new HTTP::Request ('GET', "http://$Target/walk?oids=1.3.6.1.2.1.10.127.1.1.4.1.4");
	$Response = $UA -> request ($Request);
	$Content = $Response -> content();

	$Payload = decode_json($Content);

	my @PostRSErrors;

	foreach $i (@ByChannel)
	{
		push (@PostRSErrors, $Payload->{"1.3.6.1.2.1.10.127.1.1.4.1.4.$i"});
	}

	$Stats->{UpstreamPowerLevel}	= \@UpstreamPowerLevel;
	$Stats->{DownstreamPowerLevel}	= \@DownstreamPowerLevel;
	$Stats->{RxMER}			= \@RxMER;
	$Stats->{PreRSErrors}		= \@PreRSErrors;
	$Stats->{PostRSErrors}		= \@PostRSErrors;
}

sub UploadStats
{
	my $DBH		= $_[0];
	my $Stats	= $_[1];

	my $UPL_STH = $DBH -> prepare
	("
		INSERT INTO UpstreamPowerLevel
		(
			time,
			DeviceName,
			PowerLevel1,
			PowerLevel2,
			PowerLevel3,
			PowerLevel4
		)
		VALUES
		(
			?, ?, ?, ?, ?, ?
		)
	");
	$UPL_STH -> execute($Stats->{Time}, 'H3', @{$Stats->{UpstreamPowerLevel}});
	$UPL_STH -> finish();

	my $DPL_STH = $DBH -> prepare
	("
		INSERT INTO DownstreamPowerLevel
		(
			time,
			DeviceName,
			PowerLevel1,
			PowerLevel2,
			PowerLevel3,
			PowerLevel4,
			PowerLevel5,
			PowerLevel6,
			PowerLevel7,
			PowerLevel8,
			PowerLevel9,
			PowerLevel10,
			PowerLevel11,
			PowerLevel12,
			PowerLevel13,
			PowerLevel14,
			PowerLevel15,
			PowerLevel16,
			PowerLevel17,
			PowerLevel18,
			PowerLevel19,
			PowerLevel20,
			PowerLevel21,
			PowerLevel22,
			PowerLevel23,
			PowerLevel24
		)
		VALUES
		(
			?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		)
	");
	$DPL_STH -> execute($Stats->{Time}, 'H3', @{$Stats->{DownstreamPowerLevel}});
	$DPL_STH -> finish();

	my $RxMER_STH = $DBH -> prepare
	("
		INSERT INTO RxMER
		(
			time,
			DeviceName,
			RxMER1,
			RxMER2,
			RxMER3,
			RxMER4,
			RxMER5,
			RxMER6,
			RxMER7,
			RxMER8,
			RxMER9,
			RxMER10,
			RxMER11,
			RxMER12,
			RxMER13,
			RxMER14,
			RxMER15,
			RxMER16,
			RxMER17,
			RxMER18,
			RxMER19,
			RxMER20,
			RxMER21,
			RxMER22,
			RxMER23,
			RxMER24
		)
		VALUES
		(
			?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		)
	");
	$RxMER_STH -> execute($Stats->{Time}, 'H3', @{$Stats->{RxMER}});
	$RxMER_STH -> finish();

	my $PreRSE_STH = $DBH -> prepare
	("
		INSERT INTO PreRSErrors
		(
			time,
			DeviceName,
			PreRSErrors1,
			PreRSErrors2,
			PreRSErrors3,
			PreRSErrors4,
			PreRSErrors5,
			PreRSErrors6,
			PreRSErrors7,
			PreRSErrors8,
			PreRSErrors9,
			PreRSErrors10,
			PreRSErrors11,
			PreRSErrors12,
			PreRSErrors13,
			PreRSErrors14,
			PreRSErrors15,
			PreRSErrors16,
			PreRSErrors17,
			PreRSErrors18,
			PreRSErrors19,
			PreRSErrors20,
			PreRSErrors21,
			PreRSErrors22,
			PreRSErrors23,
			PreRSErrors24
		)
		VALUES
		(
			?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		)
	");
	$PreRSE_STH -> execute($Stats->{Time}, 'H3', @{$Stats->{PreRSErrors}});
	$PreRSE_STH -> finish();

	my $PostRSE_STH = $DBH -> prepare
	("
		INSERT INTO PostRSErrors
		(
			time,
			DeviceName,
			PostRSErrors1,
			PostRSErrors2,
			PostRSErrors3,
			PostRSErrors4,
			PostRSErrors5,
			PostRSErrors6,
			PostRSErrors7,
			PostRSErrors8,
			PostRSErrors9,
			PostRSErrors10,
			PostRSErrors11,
			PostRSErrors12,
			PostRSErrors13,
			PostRSErrors14,
			PostRSErrors15,
			PostRSErrors16,
			PostRSErrors17,
			PostRSErrors18,
			PostRSErrors19,
			PostRSErrors20,
			PostRSErrors21,
			PostRSErrors22,
			PostRSErrors23,
			PostRSErrors24
		)
		VALUES
		(
			?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
		)
	");
	$PostRSE_STH -> execute($Stats->{Time}, 'H3', @{$Stats->{PostRSErrors}});
	$PostRSE_STH -> finish();

	my $BWU_STH = $DBH -> prepare
	("
		INSERT INTO BandwidthUp
		(
			time,	DeviceName,	Uploaded
		)
		VALUES
		(
			?,	?,		?
		)
	");
	$BWU_STH -> execute($Stats->{Time}, 'H3', $Stats->{Uploaded});
	$BWU_STH -> finish();

#	my $BWD_STH = $DBH -> prepare
#	("
#		INSERT INTO BandwidthDown
#		(
#			time,	DeviceName,	Downloaded
#		)
#		VALUES
#		(
#			?,	?,		?
#		)
#	");
#	$BWD_STH -> execute($Stats->{Time}, 'H3', $Stats->{Downloaded});
#	$BWD_STH -> finish();
}