Below are examples of the detailed steps taken to create the inventory database. To get to this point you need to first install mysql. Then, over the past several months I have been collecting the raw data into an Excel spreadsheet. That spreadsheet was then saved as a CSV file and used to import into this new database.

See the other pages regarding mysql for examples of the different commands you can issue from the command line.

The plan has been to first collect the data, then build the db, and finally to setup general access to this information. General access will be provided via a Perl script that will read the table and formation an interactive HTML page including all of the information in the table. I would also like to develop an internal website that will use CGI to query the database in real-time.

d
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    |                |
| 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    |                |
| switchport  | varchar(64)   | 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    |                |
+-------------+---------------+------+-----+---------+----------------+
20 rows in set (0.002 sec)


MariaDB [inventory]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| inventory          |
| mysql              |
| people             |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.000 sec)


mysql> LOAD DATA LOCAL
    INFILE '~/CCCC/Tech/Inventory/CCCC.Network.Inventory.csv'
    INTO TABLE inventory FIELDS TERMINATED BY ',' (ip, mac, name, location,
        type, sn, os, firmware, dante, ssid, channel, frequency,
        band, channelwidth, strength, encryption, notes) set id = NULL;

To backup a specific database;
ben@benb:~$ sudo mysqldump -u root --databases inventory > inventory.bkup.sql

To back up of an entire Database Management System:
ben@benb:~$ sudo mysqldump --all-databases --single-transaction --quick
    --lock-tables=false > full-backup-$(date +%F).sql -u root -p

To restore a MySQL backup, enter:
mysql -u [user] -p [database_name] < [filename].sql

You can use the GROUP BY operator to list each mac, and how many times it occures in the table:
select mac,count(*) as Total from inventory group by mac order by Total desc;

And of course the count of IP instances:
select ip,count(*) as Total from inventory group by ip order by Total desc;

Select every instance of a specific mac:
select id,mac from inventory where (mac = '60:f8:1d:c6:1a:82');

Using the arp-scan and nmap scans to gather intel, parse the results with a perl script and output a combined data file that can be used to update the mysql db and build an interactive HTML from that db.

Start with the .pl code I have and dev a script to run the scans, capture their results, and then update the db with those results. The only thing that really needs to be updated is matching the IP and MAC (the IP can change or be null).

I already have a bunch of extra info about devices so from the spreadsheet perspective all that might change is the IP assignment.

I can report all the online devices and match MACs to associate extra info, and any MACs that are not currently online can remain with a blank IP.

This needs to be a database actually. I can use perl as CGI on a web site, or to produce an interactive HTML (sortable, searchable, ...)

These are the columns from the spreadsheet:

For now, I can maintain the db via the console.

---[arp-scan]---

Note that during this scan, the Virtual Learning was in session so there are ~16 chrome books included here.

ben@benb:~$ sudo arp-scan -l
[sudo] password for ben:
Sorry, try again.
[sudo] password for ben:
Interface: eno1, datalink type: EN10MB (Ethernet)
Starting arp-scan 1.9 with 256 hosts (http://www.nta-monitor.com/tools/arp-scan/)
10.32.10.1  40:62:31:00:55:48   (Unknown)
10.32.10.3  00:24:e8:67:e7:17   Dell Inc.
10.32.10.4  00:1e:4f:2f:03:3e   Dell Inc.
10.32.10.5  88:dc:96:3e:3a:d0   SENAO Networks, Inc.
10.32.10.7  88:dc:96:49:b3:88   SENAO Networks, Inc.
10.32.10.8  88:dc:96:4c:4c:ee   SENAO Networks, Inc.
10.32.10.9  88:dc:96:47:d0:ae   SENAO Networks, Inc.
10.32.10.10 88:dc:96:49:b3:82   SENAO Networks, Inc.
10.32.10.12 88:dc:96:49:b3:8e   SENAO Networks, Inc.
10.32.10.13 88:dc:96:56:13:46   SENAO Networks, Inc.
10.32.10.14 88:dc:96:56:13:11   SENAO Networks, Inc.
10.32.10.15 88:dc:96:50:93:6c   SENAO Networks, Inc.
10.32.10.16 88:dc:96:83:a7:17   SENAO Networks, Inc.
10.32.10.21 7c:2e:0d:04:4a:c9   Blackmagic Design
10.32.10.22 90:a1:da:99:b2:ff   (Unknown)
10.32.10.20 dc:eb:94:75:6b:91   (Unknown)
10.32.10.23 7c:2e:0d:07:41:6b   Blackmagic Design
10.32.10.35 00:26:73:e8:86:9c   RICOH COMPANY,LTD.
10.32.10.50 00:0b:ab:34:52:ea   Advantech Technology (CHINA) Co., Ltd.
10.32.10.71 00:1d:c1:50:f6:98   Audinate Pty L
10.32.10.100    2c:64:1f:47:fe:cf   (Unknown)
10.32.10.104    a8:20:66:54:ad:be   Apple Inc
10.32.10.108    34:29:8f:91:08:30   (Unknown)
10.32.10.109    5c:96:9d:6e:ac:f5   Apple
10.32.10.112    5c:96:9d:6e:ac:f5   Apple
10.32.10.114    00:e0:4c:68:05:75   REALTEK SEMICONDUCTOR CORP.
10.32.10.113    dc:0e:a1:30:18:6e   COMPAL INFORMATION (KUNSHAN) CO., LTD
10.32.10.117    00:0e:dd:f0:1a:fc   SHURE INCORPORATED
10.32.10.120    00:11:32:d3:32:29   Synology Incorporated
10.32.10.122    68:5b:35:c9:cd:cf   Apple inc
10.32.10.123    a0:1e:0b:0e:47:8a   (Unknown)
10.32.10.124    2c:64:1f:47:fe:d1   (Unknown)
10.32.10.128    00:4e:01:ad:c5:7d   (Unknown)
10.32.10.121    30:cd:a7:19:b7:93   Samsung Electronics ITS, Printer division
10.32.10.135    c4:41:1e:26:0d:b3   (Unknown)
10.32.10.126    84:f3:eb:67:93:bb   (Unknown)
10.32.10.138    f0:de:f1:4c:d1:ed   Wistron InfoComm (Kunshan)Co
10.32.10.143    14:9d:99:7a:7d:2c   (Unknown)
10.32.10.144    08:00:7b:61:0f:2c   SANYO ELECTRIC CO. LTD.
10.32.10.145    08:00:7b:61:0f:30   SANYO ELECTRIC CO. LTD.
10.32.10.146    00:50:41:8a:1b:ed   Coretronic Corporation
10.32.10.147    08:00:7b:61:0f:26   SANYO ELECTRIC CO. LTD.
10.32.10.155    00:25:64:e7:d1:70   Dell Inc.
10.32.10.156    78:7b:8a:b0:d5:b5   (Unknown)
10.32.10.157    28:c6:8e:74:ec:e9   NETGEAR INC.,
10.32.10.125    a8:8e:24:cd:4d:f9   (Unknown)
10.32.10.132    98:9e:63:37:e5:da   (Unknown)
10.32.10.162    00:1f:16:39:6c:41   Wistron Corporation
10.32.10.163    68:5b:35:99:c5:dd   Apple inc
10.32.10.179    00:0e:dd:48:01:91   SHURE INCORPORATED
10.32.10.180    14:9d:99:7a:a1:70   (Unknown)
10.32.10.186    10:dd:b1:a8:af:1d   Apple
10.32.10.190    00:1d:c1:50:60:6d   Audinate Pty L
10.32.10.194    14:9d:99:7c:4c:60   (Unknown)
10.32.10.199    00:50:b6:9d:ed:fb   GOOD WAY IND. CO., LTD.
10.32.10.200    88:dc:96:71:4d:fe   SENAO Networks, Inc.
10.32.10.101    c4:1c:ff:ee:9c:b5   (Unknown)
10.32.10.168    e4:8b:7f:b4:43:76   Apple
10.32.10.202    28:80:88:6e:5b:c4   (Unknown)
10.32.10.204    bc:dd:c2:07:18:42   (Unknown)
10.32.10.206    00:0e:dd:48:4f:40   SHURE INCORPORATED
10.32.10.210    38:94:ed:c6:67:dc   (Unknown)
10.32.10.176    90:8c:43:2d:ed:58   (Unknown)
10.32.10.192    00:b3:62:36:7b:e7   (Unknown)
10.32.10.215    a4:8d:3b:68:c2:60   (Unknown)
10.32.10.217    84:f3:eb:67:f0:3e   (Unknown)
10.32.10.182    08:11:96:9a:22:fc   Intel Corporate
10.32.10.220    00:0e:dd:f0:2b:24   SHURE INCORPORATED
10.32.10.225    00:e0:4c:68:03:6e   REALTEK SEMICONDUCTOR CORP.
10.32.10.231    d0:67:e5:3f:7c:85   Dell Inc
10.32.10.230    dc:4f:22:33:ae:76   (Unknown)
10.32.10.242    00:0e:c6:5f:cc:f5   ASIX ELECTRONICS CORP.
10.32.10.243    3c:18:a0:90:7a:c6   (Unknown)
10.32.10.197    e0:89:7e:7e:03:db   (Unknown)
10.32.10.246    a0:6a:44:d6:ae:14   (Unknown)
10.32.10.249    04:15:52:4b:f5:c9   Apple
10.32.10.196    3c:22:fb:a8:44:d8   (Unknown)
10.32.10.177    f8:38:80:a7:59:55   (Unknown)
10.32.10.212    28:ff:3c:97:bb:8c   (Unknown)

79 packets received by filter, 0 packets dropped by kernel
Ending arp-scan 1.9: 256 hosts scanned in 2.657 seconds (96.35 hosts/sec). 79 responded
ben@benb:~$

So use the above output to build SQL statements to assign IP for existing MACs.

These are the rows I need to clean-up by removing those that have the least info(?).

mysql> select mac, count(mac) from inventory group by mac having count(mac) > 1 order by count(mac) desc;
Empty set (0.01 sec)

mysql> select ip, count(ip) from inventory group by ip having count(ip) > 1 order by count(ip) desc;
+---------------+-----------+
| ip            | count(ip) |
+---------------+-----------+
|               |      1101 |
| 192.168.1.134 |         2 |
| 192.168.0.5   |         2 |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT mac, COUNT(mac) FROM inventory GROUP BY mac HAVING COUNT(mac) > 1;
or
SELECT mac, COUNT(mac) FROM inventory GROUP BY mac HAVING COUNT(mac) > 1;

mysql> select mac, count(mac) from inventory group by mac having count(mac) > 1 order by count(mac) desc;
+-------------------+------------+
| mac               | count(mac) |
+-------------------+------------+
| dc:4f:22:33:ae:76 |          3 |
| 00:1e:4f:2f:03:3e |          3 |
| 80:11:23:b1:02:1e |          3 |
| bc:dd:c2:07:18:42 |          3 |
| 84:f3:eb:67:f0:3e |          2 |
| e0:89:7e:5c:77:70 |          2 |
| d4:5d:64:89:64:90 |          2 |
| 00:e0:4c:68:89:a3 |          2 |
| 84:f3:eb:67:93:bb |          2 |
| e0:89:7e:63:38:52 |          2 |
+-------------------+------------+
10 rows in set (0.00 sec)