GLPI inventory 1.2.3 looses MAC address from networkports when migrating from GLPI 9.x with fusionventory

AldarisPale commented 1 year ago

Describe the bug

MAC address disappears from networkport after installing glpiinventory plugin on GLPI 10.x after migrating from GLPI 9.x with fusioninventory plugin.

To reproduce

Have GLPI 9.x with fusioninventory plugin running.
Make sure that there's a MAC address defined in Ethernet Port section. For example networkport.form.php?id=1
Install GLPI 10.x, but avoid activating glpiinventory plugin yet.
Confirm that MAC address is still visible at networkport.form.php?id=1
Install glpiinventory plugin (does not need to be activated)
Confirm that networkport.form.php?id=1 has now empty MAC address field

Expected behavior

MAC address should be still there

Operating system

Other (See additional context below)

GLPI Agent version

Other (See additional context below)

GLPI version

10.0.9

GLPIInventory plugin

Other (See additional context below)

Additional context

Affected glpiinventory plugin is 1.2.3. No idea if previous versions are affected as well.

AldarisPale commented 1 year ago

If it helps this is visible in GLPI 9.x:

FusionInventory historical" and "Ethernet port - Historical" are empty even before the migration, as they should be.
network port -> Network device field is set
network port -> Port number is 0 (probably default value)
network port -> Name is empty (probably default value)
Ethernet port -> network outlet is ---- (probably default value)
Ethernet port ->Ethernet port type is ---- (probably default value)
Ethernet port -> MAC is set <--- this is the field which is being emptied after installing glpi inventory plugin
Ethernet port -> Network card is "Equipment without network card" (probably default value)
Ethernet port -> Ethernet port speed is " " (probably default value)
Ethernet port -> Connected to is set to Not connected (probably default value)
Network name -> Network name is empty (probably default value)
Network name -> IP address of the equipment
Network name -> Internet domain is set to ---- (probably default value)

trasher commented 1 year ago

Try with GLPI latest release, several fixes has been made on inventory part.

AldarisPale commented 1 year ago

@trasher sorry, I mistyped before. GLPI version is 10.0.9, not 10.0.7 as I indicated when I created the ticket. As I understand, 10.0.9 is the current latest version.

AldarisPale commented 1 year ago

I've updated the bug report to show 10.0.9 instead of 10.0.7 as it was previously.

trasher commented 1 year ago

Could you check if other fields are impacted? Many information were stored in glpi_plugin_fusioninventory_networkports and are now in glpi_networkports; goal is to know if the whole update is failing or if it's limited to mac only; the update code of the plugin is a real nightmare, I do not see what would be wrong reading it...

AldarisPale commented 1 year ago

MAC address seems to be the only field which gets blanked. I've confirmed that the following fields remain intact:

network port -> Network device
network port -> Port number
network port -> Name
network port -> comments
Ethernet port ->Ethernet port type
Ethernet port -> Ethernet port speed
Network name -> IP address

This is the output when install glpiinventory plugin from console.

Processing plugin "glpiinventory"...
SQL Warnings:
1831: Duplicate index 'plugin_glpiinventory_timeslots_id' defined on the table 'glpi.glpi_plugin_glpiinventory_timeslotentries'. This is deprecated and will be disallowed in a future release. in query "ALTER TABLE `glpi_plugin_glpiinventory_timeslotentries` CHANGE  `plugin_fusioninventory_timeslots_id` 
`plugin_glpiinventory_timeslots_id` int unsigned NOT NULL DEFAULT 0   , ADD INDEX `plugin_glpiinventory_timeslots_id` 
(`plugin_glpiinventory_timeslots_id`) "
SQL Warnings:
1831: Duplicate index 'last_inventory_update' defined on the table 'glpi.glpi_plugin_glpiinventory_inventorycomputercomputers'. This is deprecated and will be disallowed in a future release.
in query "ALTER TABLE `glpi_plugin_glpiinventory_inventorycomputercomputers` ADD INDEX `last_inventory_update` (`last_inventory_update`) "
SQL Warnings:
1831: Duplicate index 'agents_id_state' defined on the table 'glpi.glpi_plugin_glpiinventory_taskjobstates'. This is deprecated and will be disallowed in a future release.
1831: Duplicate index 'plugin_glpiinventory_agents_items_states' defined on the table 'glpi.glpi_plugin_glpiinventory_taskjobstates'. This is deprecated and will be disallowed in a future release.
in query "ALTER TABLE `glpi_plugin_glpiinventory_taskjobstates` ADD INDEX `agents_id_state` (`agents_id`, `state`) ,
ADD INDEX `plugin_glpiinventory_agents_items_states` (`agents_id`, `plugin_glpiinventory_taskjobs_id`, `items_id`, `itemtype`, `id`, `state`) "
Plugin "glpiinventory" has been installed and can be activated.

trasher commented 1 year ago

OK, that's strange because all migrations should be done in one query :/ I'll be off few weeks, I'll take a look when I'll be back.

AldarisPale commented 1 year ago

Many information were stored in glpi_plugin_fusioninventory_networkports and are now in glpi_networkports

Thanks for the tip, @trasher . For reasons unknown it looks like if I drop table glpi_plugin_fusioninventory_networkports before installing the glpiinventory plugin, the MAC addresses will remain intact.

Before installing glpiinventory plugin:

select count(*) from glpi_plugin_fusioninventory_networkports;
+----------+
| count(*) |
+----------+
| 54       |
+----------+

select count(*) from glpi_networkports;
+----------+
| count(*) |
+----------+
| 12465    |
+----------+

AldarisPale commented 1 year ago

Hey, @trasher . Hope your time-off went superbly! What should the correct solution be in this case? Dropping the table manually looks very hackish and might break some other things I did not notice yet.

stonebuzz commented 1 year ago

Hi @AldarisPale can you check if MAC address is set from glpi_networkports and not from glpi_plugins_fusioninventory_networkports before migrating to the glpiinventory plugin

I suspect that the MAC column is empty in the glpi_plugin_fusioninventory_networkports table and set from glpi_networkports.

when managing network ports, we 'simply' update the glpi_networkports table from the glpi_plugin_glpiinventory_networkports table (formerly glpi_plugin_fusioninventory_networkports)

    $migration->displayMessage("Use core network ports");
    if ($DB->tableExists('glpi_plugin_glpiinventory_networkports')) {
        $DB->queryOrDie(
            "UPDATE `glpi_networkports` AS `ports`
            INNER JOIN (
              SELECT
                `networkports_id`,
                `ifmtu`,
                `ifspeed`,
                `ifinternalstatus`,
                `ifconnectionstatus`,
                `iflastchange`,
                `ifinoctets`,
                `ifinerrors`,
                `ifoutoctets`,
                `ifouterrors`,
                `ifstatus`,
                `mac`,
                `ifdescr`,
                `ifalias`,
                `portduplex`,
                `trunk`,
                `lastup`
              FROM `glpi_plugin_glpiinventory_networkports`
          ) AS `plugin_ports` ON `plugin_ports`.`networkports_id` = `ports`.`id`
          SET
             `ports`.`ifmtu` = `plugin_ports`.`ifmtu`,
             `ports`.`ifspeed` = `plugin_ports`.`ifspeed`,
             `ports`.`ifinternalstatus` = `plugin_ports`.`ifinternalstatus`,
             `ports`.`ifconnectionstatus` = `plugin_ports`.`ifconnectionstatus`,
             `ports`.`iflastchange` = `plugin_ports`.`iflastchange`,
             `ports`.`ifinbytes` = `plugin_ports`.`ifinoctets`,
             `ports`.`ifinerrors` = `plugin_ports`.`ifinerrors`,
             `ports`.`ifoutbytes` = `plugin_ports`.`ifoutoctets`,
             `ports`.`ifouterrors` = `plugin_ports`.`ifouterrors`,
             `ports`.`ifstatus` = `plugin_ports`.`ifstatus`,
             `ports`.`mac` = `plugin_ports`.`mac`,
             `ports`.`ifdescr` = `plugin_ports`.`ifdescr`,
             `ports`.`ifalias` = `plugin_ports`.`ifalias`,
             `ports`.`portduplex` = `plugin_ports`.`portduplex`,
             `ports`.`trunk` = `plugin_ports`.`trunk`,
             `ports`.`lastup` = `plugin_ports`.`lastup`
          ;"
        );
        $migration->dropTable('glpi_plugin_glpiinventory_networkports');
    }

Best regards

AldarisPale commented 1 year ago

@stonebuzz this seems to be the case. Before migration, glpi_plugin_fusioninventory_networkports contains 54 rows. Only columns id,networkports_id seem to contain actual information, rest of the column's values are either 0 or <null>.

I wonder what and why populates/populated glpi_plugin_fusioninventory_networkports in the first place.

AldarisPale commented 1 year ago

After taking a look, glpi_plugin_fusioninventory_networkports seems to be populated when a network port has been defined manually in GLPI.

I wonder what would be the way forward. Ports were defined manually for a reason - to record used IP and MAC addresses for equipment for which automatic inventory is impossible. This information is being used downstream by network security devices.

stonebuzz commented 1 year ago

I think we're in a special case

can you "just" delete the following lines (to prevent overwrite the MAC with an empty value)

File : glpiinventory/install/update.native.php

Line : 262 and 281

and retry

AldarisPale commented 1 year ago

@stonebuzz that seems to do the trick.

stonebuzz commented 1 year ago

Nice.

As this is the first time I've heard of this problem and it would seem "logical" that manually added data should replace dynamic data

I don't know if we need to fix something in the data migration (FusionInventyory -> core)

If the problem persists or if it becomes a nuisance for the community, we'll think about a more permanent solution.

Sincerely

glpi-project / glpi-inventory-plugin