Beginning in 2024 I was able to begin to address several problems that will begin to be issues in the coming years. Specifically Dante, exhausted DHCP, and excessive broadcast traffic.

Portions of the solutions to these problems is to map the network, documenting each switch and which host is connected to each switchport.

This represents a very large project. First we have to document which host used every port socket so that we can create VLANs in order to reduce the multicast traffic every host has to process. This will also make troubleshooting easier.

Then we will configure the switches by creating the VLANs and assigning them to each switch socket. We will also identify which ports are trunk line (which is the ethernet cable that connects two switches.

At this point we have 154 addresses in the DHCP pool and 100 static addresses. We have 11 hosts using static addresses and 56 using the DHCP pool. That is comfortable right now, but was we grow we will exhaust the pool. By setting up VLANs we can segment our current configuration. Since each vlan has its own address space we will have x number of domains that each support 254 devices.

Broadcasts will be keep in the VLAN where they originate. This will reduce the amout of traffic each host has to process.

Setting up a VLAN for the Dante devices will make troubleshooting easier and quicker.

Below is the schema for the inventory table as of 09.08.24

alter table inventory drop column trunk;

alter table switchmap add column trunk varchar (32);

MariaDB [inventory]> describe inventory;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI | NULL    | auto_increment |
| ip          | varchar(16)   | YES  |     | NULL    |                |
| mac         | varchar(18)   | YES  |     | NULL    |                |
| wifiip      | varchar(16)   | YES  |     | NULL    |                |
| wifimac     | varchar(18)   | YES  |     | NULL    |                |
| vlan        | varchar(16)   | YES  |     | NULL    |                |
| name        | varchar(256)  | YES  |     | NULL    |                |
| type        | varchar(256)  | YES  |     | NULL    |                |
| function    | varchar(256)  | YES  |     | NULL    |                |
| location    | varchar(256)  | YES  |     | NULL    |                |
| ports       | varchar(4096) | YES  |     | NULL    |                |
| switch      | varchar(64)   | YES  |     | NULL    |                |
| managed     | varchar(16)   | YES  |     | NULL    |                |
| sn          | varchar(124)  | YES  |     | NULL    |                |
| os          | varchar(256)  | YES  |     | NULL    |                |
| firmware    | varchar(96)   | YES  |     | NULL    |                |
| dante       | varchar(256)  | YES  |     | NULL    |                |
| ssid        | varchar(256)  | YES  |     | NULL    |                |
| channel     | varchar(8)    | YES  |     | NULL    |                |
| disposition | varchar(512)  | YES  |     | NULL    |                |
| notes       | varchar(4096) | YES  |     | NULL    |                |
| lastseen    | date          | YES  |     | NULL    |                |
+-------------+---------------+------+-----+---------+----------------+

Below is the schema of the inventory table as of 09.08.24.

id            This is the unique record id
ip            IPv4 addresses
mac           The LAN MAC address of this device
wifiip        The WiFi ip of this device
wifimac       The WiFi MAC address of this device
vlan          The VLAN number for this device
name          The name of this device
type          The make/model and other descriptive info
function      What is the primary function of this device
location      Where is this device physically located
ports         Which TCP port are reported open
switch        Is this a switch or not
managed       Is this switch managed or unmanaged
sn            The Serial number
os            The Operating System running on this device
firmware      Version of the current installed firmware
dante         Does this device participate with Dante
ssid          If WiFi, this is the ssid/name of this device
channel       If WiFi, which channel does this device use
dispositionIs this device in production or not
notes         Unstructured notes
lastseen      The date this host was last reported as being online

Below is the schema for the switchmap table as of 08.16.24

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(50)      | NO   | PRI | NULL    | auto_increment |
| vlan       | varchar(16)  | YES  |     | NULL    |                |
| switchport | varchar(16)  | YES  |     | NULL    |                |
| switchmac  | varchar(18)  | YES  |     | NULL    |                |
| nodemac    | varchar(50)  | YES  |     | NULL    |                |
| trunk      | varchar(16)  | YES  |     | NULL    |                |
| managed    | varchar(16)  | YES  |     | NULL    |                |
| switchtype | varchar(128) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

Below is the schema for the switchmap table.

id              This is the record id. unique for each record.
                  Used to insure that every record is unique,
                  and as an easy way to selecting specific records.
vlan            The number representing which vlan this device supports
switchport      The port number for a given switch
switchmac       The MAC of the switch
nodemac         The MAC of the host at the end of lobe
trunk           "Trunk" identifies this port as a trunk
managed         Identifies weither the switch is managed or not
switchtype      This is the make and model of the switch