Bulk neighbours export to csv

Greetings guys,

I was looking for a way to export the Neighbours list of all devices with readable interfaces and hostnames (as it shows on http://librenmssrv/device/XXX/neighbours) to one big file… including a the relevant fields suchs as hostname,port,remote_hostname,remote_port…
Is there anyway I can do it?
I tried looking on the db tables but could find any table having it…

Appreciate your help.

Took me a while too, it’s the links table.

Found by doing this:

$ grep -Rl neighbours *

Then in app/Http/Controllers/Device/Tabs/NeighboursController.php I saw:

class NeighboursController implements DeviceTab
    public function visible(Device $device): bool
        return \DB::table('links')->where('local_device_id', $device->device_id)->exists();

@jhartlov Would your script work in this instance? Maybe a bit more detailed?

10xs Rhinoau :slight_smile:

Maybe try something like this:

select B.hostname as local_hostname, 
C.ifName as local_port, 
A.remote_hostname as remote_hostname,
select D.ifName 
from ports as D 
where D.port_id = A.remote_port_id
) as remote_port

from links as A
inner join devices as B on A.local_device_id = B.device_id 
join ports as C on A.local_port_id = C.port_id
where A.remote_device_id != 0

INTO OUTFILE '/tmp/neighbors.csv'

That is just a quick and probably not the best way to achieve this. If this works correctly, maybe we could implement an API-endpoint for this kind of thing.

It is possible with API and python for example, here is example with couple pandas merges: https://github.com/zombah/librenms-api-scripts/blob/main/librenms-api-list-links.py

Thank u Ottorei

Thank u Zombah

Basically what I did is that I exported the 3 tables I had to use:
mysql librenms -u librenms -pXXXX -B -e “SELECT device_id,sysName FROM devices;” > devices_aug12
mysql librenms -u librenms -pXXXX -B -e “SELECT port_id,device_id,ifName FROM ports;” > ports_aug12
mysql librenms -u librenms -pXXXX -B -e “SELECT local_device_id,local_port_id,remote_hostname,remote_port FROM links;” > links_aug12

did some columns reordering and added some “,” between them all:
cat ports_aug12 | awk ‘{print $1","$2","$3","}’ > ports_aug12.csv
cat links_aug12 | awk ‘{print $1","$2","$3","$4","}’ > links_aug12.csv
cat ports_aug12.csv | awk ‘{print “,”$0}’ > ports_aug12.csv_
cat ports_aug12.csv | replace “,” " " | awk ‘{print “,”$2","$1","$3","}’ > ports_aug12.csv_
had some issues there…
and then did 2 bash scripts:

1: for replacing the device ID by the device hostname and outputting to a file:
for HOSTID in cat devices_aug12.csv | cut -d"," -f2
HOSTNAME=cat devices_aug12.csv | grep ^\,$HOSTID\, | cut -d"," -f3
HOSTRST=cat links_aug12.csv | grep ^\,$HOSTID\, | replace ",$HOSTID," ",$HOSTNAME,"
echo “$HOSTRST” >> LINKS2

2:took the file outputted from script 1 and replace the portID by the port name and redirected the output to a final file including it all:
for PORTID in cat LINKS2 | cut -d"," -f3
PORTNAME=cat ports_aug12.csv | grep \,$PORTID, | grep -v ^\,$PORTID, | cut -d"," -f4
HOSTRSTR=cat LINKS2 | grep \,$PORTID\, | grep -v ^\,$PORTID, | replace ",$PORTID," ",$PORTNAME,"

Not much of a programmer but had to do it with bash :wink:
Hope someone will find it helpfull one day :slight_smile:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.