Show names of end hosts in interface table

[cvicente]

Currently we only show other network devices in the neighbors column. I'd like to see the names of connected machines. This information is available in the DB (fw_table -> MAC -> IP -> name)

[candlerb]

+1. I would like to be able to query: "show me all (end-user) devices connected to port X of switch Y"

[vink78]

I've something similar at my office: Check: https://github.com/vink78/Netdot/commit/ed17cb17dc01e075850e49df6b30d305dcd7ecc8 It's a report based on what is connected to a switch using last FWTABLE and ARPTABLE

The SQL query may be improved.

[candlerb]

This devarp report looks really interesting, and I tried merging it into my netdot 1.0.7 instance.

It displays MAC addresses reachable via each port nicely. However in almost all cases it shows "0.0" as the IP address, with a link to /netdot/management/ip.html?id=

But if I click on the MAC address it shows the right ARP entries do exist. Hence I suspect a problem with the query.

[vink78]

candlerb: It's based on the last ARP cache entry table (look at line 44: SELECT max(id) AS id FROM arpcache GROUP BY device). I agree it needs some rewrite to be used by others.

[candlerb]

Hmm, I think the problem is here:

SELECT max(id) AS id FROM arpcache GROUP BY device

I have multiple routers in different data centres, hence IPs are in different ARP tables.

UPDATE: OK our replies crossed over :-)

[candlerb]

Here is a query which seems to work, albeit it is dog slow.

SELECT interface.name, physaddr.address, physaddr.id, arp.ipaddr, ipblock.address, rrptr.rr, rrptr.ptrdname
FROM fwtableentry
JOIN interface ON interface.id=fwtableentry.interface
JOIN physaddr ON physaddr.id=fwtableentry.physaddr
LEFT JOIN
  (SELECT arp1.physaddr, arp1.ipaddr
   FROM arpcacheentry arp1
   LEFT JOIN arpcacheentry arp2 ON arp1.physaddr=arp2.physaddr AND arp1.arpcache < arp2.arpcache
   WHERE arp2.physaddr IS NULL
  ) AS arp ON arp.physaddr=physaddr.id
LEFT JOIN ipblock ON ipblock.id=arp.ipaddr
LEFT JOIN rrptr ON rrptr.ipblock=ipblock.id
WHERE fwtableentry.fwtable=$fwtable AND interface.neighbor IS NULL
ORDER BY CAST(interface.number AS SIGNED), physaddr.address

Logic for most recent ARP entry is based on https://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

It works if one MAC address maps to multiple IP addresses in the most recent ARP table.

Minor formatting issues:

• The report still displays "0.0" for unknown IP addresses, it would be better to be blank.
• It would be nice if the interface name were hyperlinked (to /netdot/management/interface.html?id=XXX)

[vink78]

candlerb: I've updated my fork according your suggestions. Can you check https://github.com/vink78/Netdot/tree/devarp_report ?

[candlerb]

The interface hyperlinks work, cool.

Missing IP addresses still display as 0.0. Note that NetAddr::IP->short is intentionally broken: e.g. 10.0.0.41 displays as 10.41.

# perl -e 'use NetAddr::IP; $a = NetAddr::IP->new(0x0a000029); print $a->short, "\n";'
10.41

I say "intentionally" because it's documented in the manpage:

=item C<-E<gt>short()>

Returns the address part in a short or compact notation.

  (ie, 127.0.0.1 becomes 127.1).

Works with both, V4 and V6.

So you need use NetAddr::IP->addr instead.

I fixed both problems as follows:

--- devarp_report.html.prev 2017-11-09 19:17:58.407992129 +0000
+++ devarp_report.html  2017-11-09 20:07:35.440000896 +0000
@@ -84,9 +84,9 @@
     push ( @row, '<a href="/netdot/management/mac.html?id='.$r->[2].'">'.$r->[3].'</a>' );

     # IP Address
-    my $nip = NetAddr::IP->new( $r->[5] );
-    if (defined $nip and $nip->short ne '0.0.0.0') {
-   push ( @row, '<a href="/netdot/management/ip.html?id='.$r->[4].'">'.$nip->short.'</a>' );
+    if ($r->[4] && $r->[5]) {
+        my $nip = NetAddr::IP->new( $r->[5] );
+   push ( @row, '<a href="/netdot/management/ip.html?id='.$r->[4].'">'.$nip->addr.'</a>' );
     } else {
    push ( @row, '&nbsp;' );
     }

[vink78]

I update the report based on Netdot Model . Can you check my last commit https://github.com/vink78/Netdot/commit/0bc448c4253837311776006057ceb228f68b70bc ? Can you give feedback on speed?

[candlerb]

Speed seems to be the same - on my system it takes about 8 seconds from click to display, with both old a new reports, and this is with a pretty small setup (34 devices including netdot itself).

It's better if you include the physaddr in the ordering, so that when you have repeated physaddrs, they appear next to each other in the display:

ORDER BY CAST(interface.number AS SIGNED), fwtableentry.physaddr

This occurs when there are multiple alias IP addresses in the ARP table for the same MAC address.

[cvicente]

Thank you both for your work on this. I tried the report on a smallish lab setup and it never finished. By the way, I believe that the place for this is in device.html, interfaces tab. I would like to rename the "neighbor" column into "connected devices". I will continue to look at the queries as time allows.

[candlerb]

I think the query performance problem is finding the latest ARP entry for every given MAC address (which is left joining arpcacheentry to itself, an N^2 join)

Unfortunately, just using the most recent arpcache table is not sufficient, because different subnets appear in different arpcache tables. Taking only the latest arpcache for each device might be OK, and/or only considering arpcaches in the last 4 hours (say).

On my relatively small system there are currently 1131 arpcache tables, collected from 13 devices:

mysql> select device,min(tstamp),max(tstamp),count(*) from arpcache group by device;
+--------+---------------------+---------------------+----------+
| device | min(tstamp)         | max(tstamp)         | count(*) |
+--------+---------------------+---------------------+----------+
|      1 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|      6 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|      7 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|      8 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     10 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     12 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     13 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     19 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     24 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     25 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     34 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     35 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
|     36 | 2017-11-12 04:00:02 | 2017-11-15 18:00:02 |       87 |
+--------+---------------------+---------------------+----------+
13 rows in set (0.00 sec)

And 61594 arpcacheentry rows:

mysql> select count(distinct arpcache),count(*) from arpcacheentry;
+--------------------------+----------+
| count(distinct arpcache) | count(*) |
+--------------------------+----------+
|                     1131 |    61594 |
+--------------------------+----------+
1 row in set (0.02 sec)

mysql> select arpcache.device,count(*),count(distinct physaddr) from arpcacheentry
join arpcache on arpcacheentry.arpcache=arpcache.id group by arpcache.device;
+--------+----------+--------------------------+
| device | count(*) | count(distinct physaddr) |
+--------+----------+--------------------------+
|      1 |      609 |                        4 |
|      6 |      174 |                        2 |
|      7 |       87 |                        1 |
|      8 |      174 |                        2 |
|     10 |    30791 |                      344 |
|     12 |      174 |                        2 |
|     13 |      174 |                        2 |
|     19 |     6906 |                      137 |
|     24 |      376 |                        6 |
|     25 |      881 |                       25 |
|     34 |      957 |                       10 |
|     35 |      957 |                       10 |
|     36 |    19334 |                      132 |
+--------+----------+--------------------------+
13 rows in set (0.09 sec)

[candlerb]

Ah, I have a better idea. Most arpcache entries are duplicates (same MAC-IP pairs), so just find the distinct ones:

mysql> select ace.physaddr,ace.ipaddr,max(tstamp) from arpcacheentry ace
join arpcache ac on ac.id=ace.arpcache
where ac.tstamp >= timestampadd(hour,-8,now())
group by ace.physaddr,ace.ipaddr;
...
| 51639528 | 7319239 | 2017-11-15 19:00:02 |
+----------+---------+---------------------+
747 rows in set (0.02 sec)

You'll get one ore more IP addresses for each MAC address. If there happen to be any stale ones included, you'll get an indication of when they were most recently seen.

The full query is then:

SELECT interface.name, physaddr.address, physaddr.id, arp.ipaddr,
       ipblock.address, rrptr.rr, rrptr.ptrdname, arp.tstamp
FROM fwtableentry
JOIN interface ON interface.id=fwtableentry.interface
JOIN physaddr ON physaddr.id=fwtableentry.physaddr
LEFT JOIN
  (SELECT ace.physaddr,ace.ipaddr,max(tstamp) as tstamp FROM arpcacheentry ace
   JOIN arpcache ac ON ac.id=ace.arpcache
   WHERE ac.tstamp >= timestampadd(hour,-8,now())
   GROUP BY ace.physaddr,ace.ipaddr
  ) AS arp ON arp.physaddr=physaddr.id
LEFT JOIN ipblock ON ipblock.id=arp.ipaddr
LEFT JOIN rrptr ON rrptr.ipblock=ipblock.id
WHERE fwtableentry.fwtable=$fwtable AND interface.neighbor IS NULL
ORDER BY CAST(interface.number AS SIGNED), physaddr.address

This query works instantly for me.

[cvicente]

@candlerb is a genius

[cvicente]

I would appreciate a review of https://github.com/cvicente/Netdot/pull/90

Thanks

[vink78]

May I propose a fix on #90 ? Can you review https://github.com/vink78/Netdot/commit/ff3ae370a2b67f020a063b2bebb90699686de186

[cvicente]

@vink78 Thank you for the review and the proposed fix. I decided to get all the data in one query to make the UI template render faster. Check the latest commit in the PR.

[vink78]

@cvicente It works as expected. Minor issue: When a MAC address is associated with an IPv4 and an IPv6 address, and, when both addresses point to the same A record, you may see duplicate entries.

[cvicente]

@vink78 Thank you again so much. It's coming along. See latest update.