I’ve recently worked on migrating some very old Cacti instances to a more recent version, going from some very early 0.8.x 32-bit version running on MySQL 4 to the more current 0.8.8c 64-bit release on MariaDB 5.5. Everything went really well, except for one small issue.

In our Cacti instances, we refer to devices using IP addresses. While migrating, I had to rebuild the poller caches to make sure that everything works properly, but for some reason, it began polling the wrong device addresses. It seems that when the cache is rebuilt, it gets the device address that was initially retrieved by SNMP, instead of the one that’s actually configured. Since some devices did change IP addresses during their lifespan, this created some inconsistencies in the database.

In order to repair this inconsistency, I’ve came out with the following MySQL query to run against the Cacti database. Before running it, I suggest backing up your database.

UPDATE `data_input_data`
       JOIN `data_input_fields`
     ON `data_input_data`.`data_input_field_id` = `data_input_fields`.`id`
       JOIN `data_template_data`
     ON `data_input_data`.`data_template_data_id` =
        `data_template_data`.`id`
       JOIN `data_local`
     ON `data_template_data`.`local_data_id` = `data_local`.`id`
       JOIN `host`
     ON `data_local`.`host_id` = `host`.`id`
SET    `value` = `host`.`hostname`
WHERE  `data_input_fields`.`data_name` = 'management_ip'

I haven’t tested it against devices using a DNS hostname. Also, don’t forget to rebuild your poller cache after running the query.