Changeset 841 for trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php

Timestamp:

Dec 31, 2016, 12:19:25 AM (8 years ago)

Author:

chronos

Message:

• Fixed: Use SQL transaction for updating network device online state.

File:

• trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php (modified) (1 diff)

trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php

@@ -13 +13 @@
       $Interfaces[$DbRow['IP']] = $DbRow;
 
-      // Load netwatch status from all DHCP routers
-      $DbResult3 = $this->Database->query('SELECT `DHCP`, `AddressRange`, `Mask` FROM `NetworkSubnet` '.
-          'WHERE (`Configure` = 1) AND (`Member` IS NULL) GROUP BY DHCP');
-      while($Subnet = $DbResult3->fetch_assoc())
+    // Load netwatch status from all DHCP routers
+    $DbResult3 = $this->Database->query('SELECT `DHCP` FROM `NetworkSubnet` '.
+      'WHERE (`Configure` = 1) AND (`Member` IS NULL) GROUP BY `DHCP`');
+    while($Subnet = $DbResult3->fetch_assoc())
+    {
+      echo('router '.$Subnet['DHCP']."\n");
+      $Routerboard = new RouterosAPI();
+      $Routerboard->Connect($Subnet['DHCP'], $this->System->Config['API']['UserName'],
+        $this->System->Config['API']['Password']);
+      if(!$Routerboard->Connected) continue;
+      $Routerboard->Write('/tool/netwatch/getall', false);
+      $Routerboard->Write('=.proplist=host,status');
+      $Read = $Routerboard->Read(false);
+      $List = $Routerboard->ParseResponse($Read);
+      foreach($List as $Properties)
       {
-        echo($Subnet['AddressRange'].'/'.$Subnet['Mask'].' on router '.$Subnet['DHCP']."\n");
-        $Routerboard = new RouterosAPI();
-        $Routerboard->Connect($Subnet['DHCP'], $this->System->Config['API']['UserName'],
-          $this->System->Config['API']['Password']);
-        if(!$Routerboard->Connected) continue;
-        $Routerboard->Write('/tool/netwatch/getall', false);
-        $Routerboard->Write('=.proplist=host,status');
-        $Read = $Routerboard->Read(false);
-        $List = $Routerboard->ParseResponse($Read);
-        foreach($List as $Properties)
-        {
-          $IP = $Properties['host'];
-          if($Properties['status'] == 'up') $Online = 1;
+        $IP = $Properties['host'];
+        if($Properties['status'] == 'up') $Online = 1;
           else $Online = 0;
 
-          if($Online)
-          {
-            if(array_key_exists($IP, $Interfaces))
-              $Interfaces[$IP]['NewOnline'] = 1;
-              else echo('IP '.$IP.' not found.'."\n");
-          }
+        if($Online)
+        {
+          if(array_key_exists($IP, $Interfaces))
+            $Interfaces[$IP]['NewOnline'] = 1;
+            else echo('IP '.$IP.' not found.'."\n");
         }
       }
+    }
 
-      foreach($Interfaces as $Index => $Interface)
+    $this->Database->query('BEGIN');
+
+    foreach($Interfaces as $Index => $Interface)
+    {
+      // Update last online time if still online
+      if($Interface['NewOnline'])
+        $DbResult = $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
+          array('LastOnline' => TimeToMysqlDateTime($StartTime)));
+
+      if($Interface['Online'] != $Interface['NewOnline'])
       {
-        // Update last online time if still online
-        if($Interface['NewOnline'])
-          $DbResult = $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
-              array('LastOnline' => TimeToMysqlDateTime($StartTime)));
+        // Online state changed
+        $DbResult = $this->Database->query('INSERT INTO `NetworkInterfaceUpDown` (`Interface`,
+         `State`, `Time`, `Duration`) VALUES ('.$Interface['Id'].', '.$Interface['NewOnline'].', "'.
+          TimeToMysqlDateTime($StartTime).'", NULL)');
+        // Update previous record duration in UpDown table
+        $this->Database->query('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND, '.
+          '`TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`) '.
+          'AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1)) '.
+          'WHERE (`TM`.`Duration` IS NULL) AND (`TM`.`Interface` ='.$Interface['Id'].')');
+        $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
+          array('Online' => $Interface['NewOnline']));
+      }
+    }
 
-        if($Interface['Online'] != $Interface['NewOnline'])
-        {
-          // Online state changed
-          $DbResult = $this->Database->query('INSERT INTO `NetworkInterfaceUpDown` (`Interface`,
-           `State`, `Time`, `Duration`) VALUES ('.$Interface['Id'].', '.$Interface['NewOnline'].', "'.
-            TimeToMysqlDateTime($StartTime).'", NULL)');
-          // Update previous record duration in UpDown table
-          $this->Database->query('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND, '.
-            '`TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`) '.
-            'AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1)) '.
-            'WHERE (`TM`.`Duration` IS NULL) AND (`TM`.`Interface` ='.$Interface['Id'].')');
-          $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
-            array('Online' => $Interface['NewOnline']));
-        }
-      }
+    // Set offline all interfaces which were not updated as online
+    $DbResult = $this->Database->select('NetworkInterface', '*', '(`Online` = 1) AND '.
+      '(`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'")');
+    while($DbRow = $DbResult->fetch_assoc())
+    {
+      echo('IP '.$DbRow['LocalIP'].' online but time not updated.'."\n");
+    }
+    $DbResult = $this->Database->select('NetworkInterface', '*', '(`Online` = 0) AND '.
+      '(`LastOnline` >= "'.TimeToMysqlDateTime($StartTime).'")');
+    while($DbRow = $DbResult->fetch_assoc())
+    {
+      echo('IP '.$DbRow['LocalIP'].' not online but time updated.'."\n");
+    }
 
-      // Set offline all interfaces which were not updated as online
-      $DbResult = $this->Database->select('NetworkInterface', '*', '(`Online` = 1) AND '.
-          '(`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'")');
-      while($DbRow = $DbResult->fetch_assoc())
-      {
-        echo('IP '.$DbRow['LocalIP'].' online but time not updated.'."\n");
-      }
-      $DbResult = $this->Database->select('NetworkInterface', '*', '(`Online` = 0) AND '.
-          '(`LastOnline` >= "'.TimeToMysqlDateTime($StartTime).'")');
-      while($DbRow = $DbResult->fetch_assoc())
-      {
-        echo('IP '.$DbRow['LocalIP'].' not online but time updated.'."\n");
-      }
+    // Update device online state
+    $DbResult = $this->Database->select('NetworkInterface', '`Device`, SUM(`Online`) AS `SumOnline`', '`Online` = 1 GROUP BY `Device`');
+    while($Device = $DbResult->fetch_assoc())
+    {
+      if($Device['SumOnline'] > 0)
+        $this->Database->update('NetworkDevice', 'Id='.$Device['Device'], array('LastOnline' => TimeToMysqlDateTime($StartTime), 'Online' => 1));
+    }
+    $this->Database->update('NetworkDevice', '`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'"', array('Online' => 0));
 
-      // Update device online state
-      $DbResult = $this->Database->select('NetworkInterface', '`Device`, SUM(`Online`) AS `SumOnline`', '`Online` = 1 GROUP BY `Device`');
-      while($Device = $DbResult->fetch_assoc())
-      {
-        if($Device['SumOnline'] > 0)
-          $this->Database->update('NetworkDevice', 'Id='.$Device['Device'], array('LastOnline' => TimeToMysqlDateTime($StartTime), 'Online' => 1));
-      }
-      $DbResult = $this->Database->update('NetworkDevice', '`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'"', array('Online' => 0));
+    $this->Database->query('COMMIT');
   }