Bulk neighbours export to csv

[Jacob_Ghattas]

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.
Jacob

[rhinoau]

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

Found by doing this:

$ grep -Rl neighbours *
app/Http/Controllers/Device/Tabs/NeighboursController.php
app/Http/Controllers/DeviceController.php
...

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();
    }

[stpaulgroup]

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

[Jacob_Ghattas]

10xs Rhinoau

[ottorei]

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'
FIELDS TERMINATED BY ',';

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.

[zombah]

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

[Jacob_Ghattas]

Thank u Ottorei

[Jacob_Ghattas]

Thank u Zombah

[Jacob_Ghattas]

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:
#!/bin/sh
for HOSTID in cat devices_aug12.csv | cut -d"," -f2
do
HOSTNAME=cat devices_aug12.csv | grep ^\,$HOSTID\, | cut -d"," -f3
HOSTRST=cat links_aug12.csv | grep ^\,$HOSTID\, | replace ",$HOSTID," ",$HOSTNAME,"
echo “$HOSTRST” >> LINKS2
done

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:
#!/bin/sh
for PORTID in cat LINKS2 | cut -d"," -f3
do
PORTNAME=cat ports_aug12.csv | grep \,$PORTID, | grep -v ^\,$PORTID, | cut -d"," -f4
HOSTRSTR=cat LINKS2 | grep \,$PORTID\, | grep -v ^\,$PORTID, | replace ",$PORTID," ",$PORTNAME,"
echo $HOSTRSTR >> LINKS_LAST &
done
####################

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