Orphaned entries in the locations database table

[alanbboyd]

While doing some housekeeping on our production LibreNMS I noticed the list of Locations under Devices -> All Devices seemed to be larger than expected.

Looking at the locations table in the LibreNMS MySQL table showed there were a large number of entries which had no corresponding links to them from the devices table (devices.location_id = location.id).

All our device locations are pulled from SNMP, and when the SNMP location is changed on the device, LibreNMS adds this new value to the locations table. The old SNMP location value stays where it is in that table even if there are no longer any devices tied to that location.

Behaviour is confirmed on both our production (v.1.47) and test/dev (v.1.51-20-g178717e) deployments.

I can see this may be by design as you can manually override syslocation in the GUI, but it would be good to have (say) something analogous to the Purge all deleted option under Ports -> Deleted to deal with any such orphaned locations.

Thanks

[murrant]

Yeah, a delete all unused button would be a good idea in the webui, I considered that when making the page, but never implemented it.

I just sorted the locations by device count and clicked delete until the orphans were gone. After that I didn’t have a big need for a button.

[alanbboyd]

No worries, and thanks. I just went with the nuclear option in the DB:

delete from locations 
 where id not in (
    select devices.location_id from devices 
    where devices.location_id is not NULL
);

[ntaken]

Smart. Started do a bash script interacting with the API but found out that you could not use the location id number to delete a location. As many locations have white spaces in their name my script became useless.

Not fun delete 100+ location by hand. Database it is.

[santoshkotla]

You da man!!!