Uploaded image for project: 'Observium'
  1. Observium
  2. OBS-3415

Error in Syslog IP->Device_id matching

Details

    • Improvement
    • Resolution: Fixed
    • Major
    • None
    • Professional Edition
    • None
    • None

    Description

      Syslog lines are not matched to devices by ip address. I dont know since when.

      But the problem is, that you are using 

       

      $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);

      in the 

       

      function dbFetchRows($sql, $parameters = array(), $print_query = FALSE)

      in mysqli.inc.php.

       

      PHP Documentation says: 

      syslog.inc.php says:

       

      $query = 'SELECT * FROM `ipv'.$ip_version.'_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv'.$ip_version.'_address` = ? AND (`deleted` = ? OR `deleted` IS NULL);';

      The problem: After joining the two tables, there are 2 device_id fields - so i think PHP is using the last field (from table ports) where device_id is NULL. So even if the first device_id field contains a value, PHP sets the value to NULL.

       

      So we need to adjust the query to:

      $query = 'SELECT `ipv'.$ip_version.'_addresses`.device_id, ports.ifAdminStatus, ports.ifOperStatus FROM `ipv'.$ip_version.'_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv'.$ip_version.'_address` = ? AND (`deleted` = ? OR `deleted` IS NULL);';

      Example:

      MariaDB [observium]> SELECT ipv4_addresses.device_id FROM `ipv4_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv4_address` = '10.49.50.20' AND (`deleted` = '0' OR `deleted` IS NULL);
      -----------

      device_id

      -----------

      666

      -----------
      1 row in set (0.00 sec)

      MariaDB [observium]> SELECT device_id FROM `ipv4_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv4_address` = '10.49.50.20' AND (`deleted` = '0' OR `deleted` IS NULL);
      ERROR 1052 (23000): Column 'device_id' in field list is ambiguous

       

      After this change the syslog entries are stored for the correct device!

      Patch is attached...

      I hope you understand the problem..

       

      Attachments

        Activity

          [OBS-3415] Error in Syslog IP->Device_id matching
          landy Mike Stupalov added a comment -

          Query correctly fixed in r10600.
          Thanks.

          landy Mike Stupalov added a comment - Query correctly fixed in r10600. Thanks.
          FloMeyer Florian Meyer added a comment -

          MariaDB [observium]> SELECT * FROM `ipv4_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv4_address` = '10.49.50.20' AND (`deleted` = '0' OR `deleted` IS NULL);
          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

          port_id ipv4_address_id device_id ipv4_address ipv4_binary ipv4_prefixlen ipv4_type ipv4_network_id vrf_id ifIndex device_id port_64bit port_label port_label_base port_label_num port_label_short port_descr_type port_descr_descr port_descr_circuit port_descr_speed port_descr_notes ifDescr ifName ifIndex ifSpeed ifConnectorPresent ifPromiscuousMode ifHighSpeed ifOperStatus ifAdminStatus ifDuplex ifMtu ifType ifAlias ifPhysAddress ifHardType ifLastChange ifVlan ifTrunk ifVrf encrypted ignore disabled detailed deleted ifInUcastPkts ifInUcastPkts_rate ifOutUcastPkts ifOutUcastPkts_rate ifInErrors ifInErrors_rate ifOutErrors ifOutErrors_rate ifOctets_rate ifUcastPkts_rate ifErrors_rate ifInOctets ifInOctets_rate ifOutOctets ifOutOctets_rate ifInOctets_perc ifOutOctets_perc poll_time poll_period ifInErrors_delta ifOutErrors_delta ifInNUcastPkts ifInNUcastPkts_rate ifOutNUcastPkts ifOutNUcastPkts_rate ifInBroadcastPkts ifInBroadcastPkts_rate ifOutBroadcastPkts ifOutBroadcastPkts_rate ifInMulticastPkts ifInMulticastPkts_rate ifOutMulticastPkts ifOutMulticastPkts_rate port_mcbc ifInDiscards ifInDiscards_rate ifOutDiscards ifOutDiscards_rate ifDiscards_rate

          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

          0 7025 666 10.49.50.20

          12 | 24 | private | 2485 | NULL | 379 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
          ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
          1 row in set (0.00 sec)

          FloMeyer Florian Meyer added a comment - MariaDB [observium] > SELECT * FROM `ipv4_addresses` LEFT JOIN `ports` USING (`port_id`) WHERE `ipv4_address` = '10.49.50.20' AND (`deleted` = '0' OR `deleted` IS NULL); -------- --------------- --------- ------------ ----------- -------------- --------- --------------- ------ ------- --------- ---------- ---------- --------------- -------------- ---------------- --------------- ---------------- ------------------ ---------------- ---------------- ------- ------ ------- ------- ------------------ ----------------- ----------- ------------ ------------- -------- ----- ------ ------- ------------- ---------- ------------ ------ ------- ----- --------- ------ -------- -------- ------- ------------- ------------------ -------------- ------------------- ---------- --------------- ----------- ---------------- ------------- ---------------- ------------- ---------- --------------- ----------- ---------------- --------------- ---------------- --------- ----------- ---------------- ----------------- -------------- ------------------- --------------- -------------------- ----------------- ---------------------- ------------------ ----------------------- ----------------- ---------------------- ------------------ ----------------------- --------- ------------ ----------------- ------------- ------------------ ----------------+ port_id ipv4_address_id device_id ipv4_address ipv4_binary ipv4_prefixlen ipv4_type ipv4_network_id vrf_id ifIndex device_id port_64bit port_label port_label_base port_label_num port_label_short port_descr_type port_descr_descr port_descr_circuit port_descr_speed port_descr_notes ifDescr ifName ifIndex ifSpeed ifConnectorPresent ifPromiscuousMode ifHighSpeed ifOperStatus ifAdminStatus ifDuplex ifMtu ifType ifAlias ifPhysAddress ifHardType ifLastChange ifVlan ifTrunk ifVrf encrypted ignore disabled detailed deleted ifInUcastPkts ifInUcastPkts_rate ifOutUcastPkts ifOutUcastPkts_rate ifInErrors ifInErrors_rate ifOutErrors ifOutErrors_rate ifOctets_rate ifUcastPkts_rate ifErrors_rate ifInOctets ifInOctets_rate ifOutOctets ifOutOctets_rate ifInOctets_perc ifOutOctets_perc poll_time poll_period ifInErrors_delta ifOutErrors_delta ifInNUcastPkts ifInNUcastPkts_rate ifOutNUcastPkts ifOutNUcastPkts_rate ifInBroadcastPkts ifInBroadcastPkts_rate ifOutBroadcastPkts ifOutBroadcastPkts_rate ifInMulticastPkts ifInMulticastPkts_rate ifOutMulticastPkts ifOutMulticastPkts_rate port_mcbc ifInDiscards ifInDiscards_rate ifOutDiscards ifOutDiscards_rate ifDiscards_rate -------- --------------- --------- ------------ ----------- -------------- --------- --------------- ------ ------- --------- ---------- ---------- --------------- -------------- ---------------- --------------- ---------------- ------------------ ---------------- ---------------- ------- ------ ------- ------- ------------------ ----------------- ----------- ------------ ------------- -------- ----- ------ ------- ------------- ---------- ------------ ------ ------- ----- --------- ------ -------- -------- ------- ------------- ------------------ -------------- ------------------- ---------- --------------- ----------- ---------------- ------------- ---------------- ------------- ---------- --------------- ----------- ---------------- --------------- ---------------- --------- ----------- ---------------- ----------------- -------------- ------------------- --------------- -------------------- ----------------- ---------------------- ------------------ ----------------------- ----------------- ---------------------- ------------------ ----------------------- --------- ------------ ----------------- ------------- ------------------ ----------------+ 0 7025 666 10.49.50.20 12 | 24 | private | 2485 | NULL | 379 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | -------- --------------- --------- ------------ ----------- -------------- --------- --------------- ------ ------- --------- ---------- ---------- --------------- -------------- ---------------- --------------- ---------------- ------------------ ---------------- ---------------- ------- ------ ------- ------- ------------------ ----------------- ----------- ------------ ------------- -------- ----- ------ ------- ------------- ---------- ------------ ------ ------- ----- --------- ------ -------- -------- ------- ------------- ------------------ -------------- ------------------- ---------- --------------- ----------- ---------------- ------------- ---------------- ------------- ---------- --------------- ----------- ---------------- --------------- ---------------- --------- ----------- ---------------- ----------------- -------------- ------------------- --------------- -------------------- ----------------- ---------------------- ------------------ ----------------------- ----------------- ---------------------- ------------------ ----------------------- --------- ------------ ----------------- ------------- ------------------ ----------------+ 1 row in set (0.00 sec)

          People

            landy Mike Stupalov
            FloMeyer Florian Meyer
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: