Reply

Error "HTTP 500 read failed: Connection reset by peer http://x.x.x.x/api/2.8/interfaces/show.json at querydb.pl line 130"

JMaynard
Techie
Posts: 8
13627     0

I am getting error HTTP 500 read failed: Connection reset by peer http://x.x.x.x/api/2.8/interfaces/show.json at querydb.pl line 130 when running my script and am hoping someone can tell me what to look for.  The script runs through a large number of records and this error occurs at different places in the script.  I have attached my script so if anyone can tell me what I might be doing wrong I would really apprecaite it.

Apologies in advance for the sloppy code.  Coding is not my primary job.

Line 130 is:        my $interface = $client->broker->interface->show({InterfaceID => $ifaddr->InterfaceID})->{interface};

====== Script querydb.pl =======================

use Switch;
use strict;
use warnings;
use NetMRI::API;
use DBI;
use DateTime;
use Config:Smiley Frustratedimple;
my $cfg = new Config:Smiley Frustratedimple('config.ini');
my $vlanid;
my $vl;
# Get the command line arguments (used for get a single device)
# connect to MySql db
my $username = $cfg->param("MySQL.username"); # set your MySQL username
my $password = $cfg->param("MySQL.password"); # set your MySQL password
my $database = $cfg->param("MySQL.database"); # set your MySQL database name
my $server = $cfg->param("MySQL.server"); # set your server hostname (probably localhost)
my $sql;
my $sth;
my $action;
my $sql_table;
my $filter;
my $mod;
my @result;
my @found;
my @row;
my $row;
my $dbstr;
my $dbhandle = DBI->connect("DBI:mysql:$database;host=$server", $username, $password) || die "Could not connect to database: $DBI::errstr";

my $dt   = DateTime->now;   # Stores current date and time as datetime object
my $date = $dt->ymd;   # Retrieves date as a string in 'yyyy-mm-dd' format
my $time = $dt->hms;   # Retrieves time as a string in 'hh:mm:ss' format

my $datenow = "$date $time";   # creates 'yyyy-mm-dd hh:mm:ss' string


# Do SQL actions
sub dbdata {
undef(@result);
$action = "";
$sql_table = "";
$filter = "";
$mod= "";
$action = $_[0];
$sql_table = $_[1];
$filter = $_[2];
$mod = $_[3];
$sql = ""; $sth = "";
        switch ($action){
                case "S" {
                         $sql = "select * from $sql_table where $filter \;";
                         $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr;
                         $sth->execute();
                         @result = $sth->fetchrow_array();
                        return @result;
                }
                case "U" {
                         $sql = "update $sql_table set $filter \;";
        print $sql . "\n";
                         $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr;
                         $sth->execute();
                }
                case "I" {
                         $sql = "insert into $sql_table VALUES ($filter) \;";
        print $sql . "\n";
                         $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr;
                         $sth->execute();
                }
        }
}

 

# Connect to the NetMRI.
my $client = new NetMRI::API({
   username    => $cfg->param("NetMRI.username"),
   password    => $cfg->param("NetMRI.password"),
   url         => $cfg->param("NetMRI.server1"),
   api_version => '2.8',
});

my @devices = $client->broker->device->find({

  # find all devices that are like the pattern passed
#    op_DeviceName      => '=',
#    val_c_DeviceName   => $pattern,
#    val_c_DeviceName   => '%',

  op_DeviceType      => 'like',
  val_c_DeviceType   => '%switch%',


  # retreive only the id and name to save bandwidth
  select              => [qw( DeviceID DeviceName DeviceModel )],

});

foreach my $device (@devices)
{

  # check the database for the instance
   @found = &dbdata("S","tbl_switch","switch_id =" . $device->DeviceID);
   my $dvid = $device->DeviceID;
        if (@found) {
                &dbdata("U","tbl_switch","switch_name=\"" . $device->DeviceName . "\",switch_ID=\"" . $device->DeviceID . "\",device='1' where
device='1' AND switch_ID=\"" . $device->DeviceID ."\"" );
                &dbdata("U","updated","switch_name=\"" . $device->DeviceName .  "\",switch_ID=\"" . $device->DeviceID . "\",date_updated=\"" .
$datenow . "\" where switch_ID = " . $device->DeviceID );
        } else {
                $dbstr = "'',\"" . $device->DeviceName . "\",\"" . $device->DeviceID . "\",0,\"\",1";
                &dbdata("I","tbl_switch","$dbstr");
                my $upstr = "\"" . $device->DeviceName . "\",\"" . $device->DeviceID . "\",\""  . $datenow . "\"  where switch_ID = " . $device
->DeviceID;
                &dbdata("I","updated","$upstr");
        } # END if (@found)


  my @addresses = $client->broker->interface->find({
    # find all interfaces for this device
    op_DeviceID        => '=',
    val_c_DeviceID     => $device->DeviceID,

    # retrieve only the desired fields to save bandwidth
    select              => [qw( InterfaceID ifDescr ifIndex ifName ifAdminStatus ifOperStatus ifTrunkStatus ifSpeed ifDuplex )],
  });

  # Build the table to display the interfaces

  foreach my $ifaddr (@addresses)
  {
    # retreive the interface details so that we can display the ifName
    my $interface = $client->broker->interface->show({InterfaceID => $ifaddr->InterfaceID})->{interface};

    my $intaddr   = $client->broker->if_vlan->index({DeviceID => $device->DeviceID, InterfaceID => $interface->InterfaceID,limit => '1'});

        if (ref($intaddr->{if_vlans}[0])) {
                # if there is a reference to a vlan assign it to $vlanid and then get the actual vlan in $v1
                $vlanid = $intaddr->{if_vlans}[0];
                $vl =  $client->broker->vlan->show({ VlanID => $vlanid->VlanID })->{vlan}->VlanIndex;
        } else {
                # there was not a vlan assigned so we populate the $v1 with unassigned so we dont get errors
                $vl = 'unassigned';
        }; # END If

        undef(@found);

        $sql = "SELECT * from tbl_port where device='1' AND switch_id = " . $device->DeviceID . " AND port_ifindex = " .  $interface->ifIndex .
 "\;";
        if ($row = $dbhandle->do($sql) > 0) {
                &dbdata("U","tbl_port","switch_ID=\"" . $device->DeviceID . "\" ,port_description=\"" . $interface->ifDescr . "\" ,port_ifindex
=\"" . $interface->ifIndex . "\" ,port_name=\"" . $interface->ifName .  "\" ,port_admin=\"" . $interface->ifAdminStatus . "\" ,port_operator=\"
" . $interface->ifOperStatus . "\" ,port_istrunk=\"" . $interface->ifTrunkStatus . "\" ,port_speed=\"" . $interface->ifSpeed . "\" ,port_duplex
=\"" . $interface->ifDuplex . "\" ,vlan_id=\"" . $vl . "\", device='1' WHERE device='1' AND switch_id = " . $device->DeviceID . " AND port_ifin
dex = " .  $interface->ifIndex . "\;" );

        } else {
                &dbdata("I","tbl_port", "\"" . $device->DeviceID . "\"," . "\"" . $interface->ifDescr . "\"," ."\"" . $interface->ifIndex . "\"
," ."\"" . $interface->ifName . "\"," ."\"" . $interface->ifAdminStatus . "\"," ."\"" . $interface->ifOperStatus . "\"," ."\"" . $interface->if
TrunkStatus . "\"," ."\"" . $interface->ifSpeed . "\"," ."\"" . $interface->ifDuplex . "\"," ."\"" . $vl . "\"," ."\"0\"," ."\"\"," ."\"0\",\"1
\"" );

        } # END if (@found)

  } # END foreach my $ifaddr (@addresses)


  # retrieve the vlan table from the device for only ethernet vlans
  my @vlans = $client->broker->vlan_member->find({
    op_DeviceID         => '=',
    val_c_DeviceID      => $device->DeviceID,
    op_VlanType         => '=',
    val_c_VlanType      => 'ethernet',

    # retrieve only the desired fields to save bandwidth
    select              => [qw( VlanID VlanIndex VlanName  )],

  });

  # Build the table to display the VLANS

  # get the vlan details
  foreach my $vlan (@vlans) {
    my $data = $client->broker->vlan->show({ VlanID => $vlan->VlanID })->{vlan};

# insert the vlan data into the vlan table or update it
   @found = &dbdata("S","tbl_vlan","device='1' AND switch_id =" . $device->DeviceID . " AND vlan_index =" .  $data->VlanID);
        if (@found) {
                &dbdata("U","tbl_vlan","switch_ID=\"" . $device->DeviceID . "\" ,vlan_index=\"" . $data->VlanID . "\" ,vlan_id=\"" . $data->Vla
nIndex . "\" ,vlan_name=\"" . $data->VlanName . "\", device='1' where device='1' AND vlan_index=" . $data->VlanID);
        } else {
                &dbdata("I", "tbl_vlan",$device->DeviceID . "," . $data->VlanID . "," . $data->VlanIndex . ",\"" . $data->VlanName . "\",\"1\""
);
        } # END If (@found)
  } # END foreach my $vlan (@vlans)

} # END foreach my $device

Ugggg, formatting:  Here

JMaynard
Techie
Posts: 8
13628     0
Ugggg, formatting:  Here is the code without the html formatting: use Switch; use strict; #use warnings; use NetMRI::API; use DBI; use DateTime; use Config:Smiley Frustratedimple; my $cfg = new Config:Smiley Frustratedimple('config.ini'); my $vlanid; my $vl; # Get the command line arguments (used for get a single device) my $pattern = shift @ARGV; # connect to db my $username = $cfg->param("MySQL.username"); # set your MySQL username my $password = $cfg->param("MySQL.password"); # set your MySQL password my $database = $cfg->param("MySQL.database"); # set your MySQL database name my $server = $cfg->param("MySQL.server"); # set your server hostname (probably localhost) my $sql; my $sth; my $action; my $sql_table; my $filter; my $mod; my @result; my @found; my @row; my $row; my $dbstr; my $dbhandle = DBI->connect("DBI:mysql:$database;host=$server", $username, $password) || die "Could not connect to database: $DBI::errstr"; my $dt = DateTime->now; # Stores current date and time as datetime object my $date = $dt->ymd; # Retrieves date as a string in 'yyyy-mm-dd' format my $time = $dt->hms; # Retrieves time as a string in 'hh:mm:ss' format my $datenow = "$date $time"; # creates 'yyyy-mm-dd hh:mm:ss' string # Do SQL actions sub dbdata { undef(@result); $action = ""; $sql_table = ""; $filter = ""; $mod= ""; $action = $_[0]; $sql_table = $_[1]; $filter = $_[2]; $mod = $_[3]; $sql = ""; $sth = ""; switch ($action){ case "S" { $sql = "select * from $sql_table where $filter \;"; $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr; $sth->execute(); @result = $sth->fetchrow_array(); return @result; } case "U" { $sql = "update $sql_table set $filter \;"; print $sql . "\n"; $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr; $sth->execute(); } case "I" { $sql = "insert into $sql_table VALUES ($filter) \;"; print $sql . "\n"; $sth = $dbhandle->prepare($sql) or die "Could not prepare statement: " . $dbhandle->errstr; $sth->execute(); } } } # Connect to the NetMRI. my $client = new NetMRI::API({ username => $cfg->param("NetMRI.username"), password => $cfg->param("NetMRI.password"), url => $cfg->param("NetMRI.server1"), api_version => '2.8', }); my @devices = $client->broker->device->find({ # find all devices that are like the pattern passed # op_DeviceName => '=', # val_c_DeviceName => $pattern, # val_c_DeviceName => '%', op_DeviceType => 'like', val_c_DeviceType => '%switch%', # retreive only the id and name to save bandwidth select => [qw( DeviceID DeviceName DeviceModel )], }); foreach my $device (@devices) { # check the database for the instance @found = &dbdata("S","tbl_switch","switch_id =" . $device->DeviceID); my $dvid = $device->DeviceID; if (@found) { &dbdata("U","tbl_switch","switch_name=\"" . $device->DeviceName . "\",switch_ID=\"" . $device->DeviceID . "\",device='1' where device='1' AND switch_ID=\"" . $device->DeviceID ."\"" ); &dbdata("U","updated","switch_name=\"" . $device->DeviceName . "\",switch_ID=\"" . $device->DeviceID . "\",date_updated=\"" . $datenow . "\" where switch_ID = " . $device->DeviceID ); } else { $dbstr = "'',\"" . $device->DeviceName . "\",\"" . $device->DeviceID . "\",0,\"\",1"; &dbdata("I","tbl_switch","$dbstr"); my $upstr = "\"" . $device->DeviceName . "\",\"" . $device->DeviceID . "\",\"" . $datenow . "\" where switch_ID = " . $device ->DeviceID; &dbdata("I","updated","$upstr"); } # END if (@found) my @addresses = $client->broker->interface->find({ # find all interfaces for this device op_DeviceID => '=', val_c_DeviceID => $device->DeviceID, # retrieve only the desired fields to save bandwidth select => [qw( InterfaceID ifDescr ifIndex ifName ifAdminStatus ifOperStatus ifTrunkStatus ifSpeed ifDuplex )], }); # Build the table to display the interfaces foreach my $ifaddr (@addresses) { # retreive the interface details so that we can display the ifName my $interface = $client->broker->interface->show({InterfaceID => $ifaddr->InterfaceID})->{interface}; my $intaddr = $client->broker->if_vlan->index({DeviceID => $device->DeviceID, InterfaceID => $interface->InterfaceID,limit => '1'}); if (ref($intaddr->{if_vlans}[0])) { # if there is a reference to a vlan assign it to $vlanid and then get the actual vlan in $v1 $vlanid = $intaddr->{if_vlans}[0]; $vl = $client->broker->vlan->show({ VlanID => $vlanid->VlanID })->{vlan}->VlanIndex; } else { # there was not a vlan assigned so we populate the $v1 with unassigned so we dont get errors $vl = 'unassigned'; }; # END If undef(@found); $sql = "SELECT * from tbl_port where device='1' AND switch_id = " . $device->DeviceID . " AND port_ifindex = " . $interface->ifIndex . "\;"; if ($row = $dbhandle->do($sql) > 0) { &dbdata("U","tbl_port","switch_ID=\"" . $device->DeviceID . "\" ,port_description=\"" . $interface->ifDescr . "\" ,port_ifindex =\"" . $interface->ifIndex . "\" ,port_name=\"" . $interface->ifName . "\" ,port_admin=\"" . $interface->ifAdminStatus . "\" ,port_operator=\" " . $interface->ifOperStatus . "\" ,port_istrunk=\"" . $interface->ifTrunkStatus . "\" ,port_speed=\"" . $interface->ifSpeed . "\" ,port_duplex =\"" . $interface->ifDuplex . "\" ,vlan_id=\"" . $vl . "\", device='1' WHERE device='1' AND switch_id = " . $device->DeviceID . " AND port_ifin dex = " . $interface->ifIndex . "\;" ); } else { &dbdata("I","tbl_port", "\"" . $device->DeviceID . "\"," . "\"" . $interface->ifDescr . "\"," ."\"" . $interface->ifIndex . "\" ," ."\"" . $interface->ifName . "\"," ."\"" . $interface->ifAdminStatus . "\"," ."\"" . $interface->ifOperStatus . "\"," ."\"" . $interface->if TrunkStatus . "\"," ."\"" . $interface->ifSpeed . "\"," ."\"" . $interface->ifDuplex . "\"," ."\"" . $vl . "\"," ."\"0\"," ."\"\"," ."\"0\",\"1 \"" ); } # END if (@found) } # END foreach my $ifaddr (@addresses) # retrieve the vlan table from the device for only ethernet vlans my @vlans = $client->broker->vlan_member->find({ op_DeviceID => '=', val_c_DeviceID => $device->DeviceID, op_VlanType => '=', val_c_VlanType => 'ethernet', # retrieve only the desired fields to save bandwidth select => [qw( VlanID VlanIndex VlanName )], }); # Build the table to display the VLANS # get the vlan details foreach my $vlan (@vlans) { my $data = $client->broker->vlan->show({ VlanID => $vlan->VlanID })->{vlan}; # insert the vlan data into the vlan table or update it @found = &dbdata("S","tbl_vlan","device='1' AND switch_id =" . $device->DeviceID . " AND vlan_index =" . $data->VlanID); if (@found) { &dbdata("U","tbl_vlan","switch_ID=\"" . $device->DeviceID . "\" ,vlan_index=\"" . $data->VlanID . "\" ,vlan_id=\"" . $data->Vla nIndex . "\" ,vlan_name=\"" . $data->VlanName . "\", device='1' where device='1' AND vlan_index=" . $data->VlanID); } else { &dbdata("I", "tbl_vlan",$device->DeviceID . "," . $data->VlanID . "," . $data->VlanIndex . ",\"" . $data->VlanName . "\",\"1\"" ); } # END If (@found) } # END foreach my $vlan (@vlans) } # END foreach my $device

In general, you should never

Adviser
Posts: 357
13628     0

In general, you should never get a 500 error.

Can you try manually going with your browser to:

http://x.x.x.x/api/2.8/interfaces/show.json?InterfaceID=11111

where 11111 is the interface ID in question?

 

Also, I notice that you are

Adviser
Posts: 357
13628     0

Also, I notice that you are loading the interface object even though you already have it loaded (as an aside, you could just do ...->interface->index({DeviceID => $device->DeviceID})). So, you are putting extra burden on the server. Here:

my @addresses = $client->broker->interface->find({
# find all interfaces for this device
op_DeviceID => '=',
val_c_DeviceID => $device->DeviceID,

# retrieve only the desired fields to save bandwidth
select => [qw( InterfaceID ifDescr ifIndex ifName ifAdminStatus ifOperStatus ifTrunkStatus ifSpeed ifDuplex )],
});

# Build the table to display the interfaces

foreach my $ifaddr (@addresses)  ****** This is actuall not ifAddr objects, but instead Interface objects since that is the call you used above.
{
# retreive the interface details so that we can display the ifName
my $interface = $client->broker->interface->show({InterfaceID => $ifaddr->InterfaceID})->{interface};    ***** so this is redundant

Thanks for the input John,  I

JMaynard
Techie
Posts: 8
13628     0

Thanks for the input John,  I did clean up some of the code but I am still getting the error.  I have run the script many times and the break point differs each time I run it.  It does not seem to be a timing issue as the run time varies short to long.  Is there something I should be looking at on the server to see what may be happening?

Thanks for the input John,  I

JMaynard
Techie
Posts: 8
13628     0

Thanks for the input John,  I did clean up some of the code but I am still getting the error.  I have run the script many times and the break point differs each time I run it.  It does not seem to be a timing issue as the run time varies short to long.  Is there something I should be looking at on the server to see what may be happening?

Thanks for the input John,  I

JMaynard
Techie
Posts: 8
13628     0

Thanks for the input John,  I did clean up some of the code but I am still getting the error.  I have run the script many times and the break point differs each time I run it.  It does not seem to be a timing issue as the run time varies short to long.  Is there something I should be looking at on the server to see what may be happening?

What version are you running?

Adviser
Posts: 357
13628     0

What version are you running?

I think you need to open a support case.

The version is: 6.8.3.71007

JMaynard
Techie
Posts: 8
13628     0

The version is: 6.8.3.71007

I will open a support case as other scripts seem to be working irradically, such as the one listed in these forums to print all records.  I get duplicates when I use that one.  Perhaps there is an issue with the backend database.

Showing results for 
Search instead for 
Do you mean 

Recommended for You