The inventory database contains the inventory table which stores the inventory information. From here you can query the details about a host or group of hosts.
First you want to log into the database system from a terminal. These examples are taken from Linux, but also apply to a Windows system.
$ sudo mysql -u root -p [sudo] password for parth: Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 31 Server version: 10.6.8-MariaDB-1:10.6.8+maria~focal mariadb.org binary distribution Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Now that you have authenticated to the database system, you need to select which database you want to work with. In this case it is the inventory database, so we will 'use' it. Notice that the prompt changes to include 'inventory' as a reminder of which database you are connected to and using.
MariaDB [(none)]> use inventory; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [inventory]>
Here we can list the columns and their data definitions in the inventory table.
As seen below, this produces a list showing the columns and their basic characteristics.
'Field' is the name of each field. These are the names used when updating, inserting, or simply querying the data.
'Type' is the datatype. The format is type(size). int(11) defines the cells in this column to each be an integer with up to 11 digits total. varchar(16) is a Variable Character value made up of character/text data and can contain up to 16 characters. If fewer characters are needed, only the amount needed is used. If that is exceed, and error will occur. Others include blob (Binary Large OBject).
'Null' indicates whether the values can be NULL.
Key flags a column as being a key of some sort. Keys are values that are each unique within each table. They are used to associate records with others that are in other tables.
'Default' defines the default value of each cell. The null value is usually used. Null is not a specific value, it is the absence of a value. Zero and spaces are all values while a null represents the absence of a value.
'Extra' defines other characteristics. Most common is auto_increment which states that each time a new record is inserted into this table, the largest previous integer value previously used is to incremented by 1 so that each value is unique for this table.
Schemas as of 07.29.24
MariaDB [inventory]> show tables; +--------------------------+ | Tables_in_inventory | +--------------------------+ | inventory | | inventory_decommissioned | | switchmap | | vlans | +--------------------------+ 4 rows in set (0.001 sec) 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 | | | switchport | varchar(16) | YES | | NULL | | | hostmac | varchar(18) | YES | | NULL | | | managed | varchar(16) | YES | | NULL | | | trunk | 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 | | +-------------+---------------+------+-----+---------+----------------+ 25 rows in set (0.001 sec) MariaDB [inventory]> describe switchmap; +------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | mac | varchar(18) | YES | | NULL | | | switchport | varchar(8) | YES | | NULL | | | hostmac | varchar(18) | YES | | NULL | | | vlan | varchar(18) | YES | | NULL | | | notes | varchar(1024) | YES | | NULL | | | lastseen | date | YES | | NULL | | +------------+---------------+------+-----+---------+----------------+ 7 rows in set (0.002 sec)
This table is intended to be useful in may ways. Consequently it contains data items that do not appear to be relevant. This is because this table deals with;
and so on.
The idea is that there are two tables (at this point). The first is inventory. It contains detailed information about each host/device involved with the CapCity networks, both wired and WiFi. The second is the switchmap table. Each record represents a specific port on each of the switches, and which device is plugged into that port.
Inventory is used for all sorts of things (more on that in this page...) while the switchmap table deals with the vlans used by CapCity.
For each vlan, the physical ports that connect the devices that are members of a given vlan are defined. So each switch port is assigned a specific vlan.
The ethernet cable lobes that connect a device (computer, laptop, printer...) to a switch are called active lines. The ethernet lines that connect 2 switches are called trunk lines. The difference is that an active line will carry traffic for only one vlan while trunk lines will carry traffic for any number of vlans (the max is actually 4096).
I intend to create an SQL view that combines many of the columns in the switchmap to records for specific devices listed in the inventory table. This will provide a one-stop-shop for all relevant datum for a given host.
Notice that the mac column in both inventory and switchmap act as keys to link information about a specific device.
SQL examples
To make this database accessible for common use, I have two alternatives. The cool approach is to develop a 2-tier web based application as a way to create a GUI that allows a person to query and maintain the database. Cool, but it would involve several different languages, several (!) program files, and not so obvious logic to make it work (I have developed several full-stack web applications, so I know how to do this, but it would then require folks who follow after me to be functional in those different languages and the highly conceptual nature of full stack apps.
The not so cool, but more sustainable approach is to develop documentation with examples (such as this page) that give you copy-&-paste example of the SQL queries (commands) to accomplish most of the things you would do with this database.
This would include the ability to dump an entire table or just a portion to a CSV file that could then be opened in a spreadsheet for further processing.
Basic Examples
/* Login. You will first be prompted for the Linux root password, and then for the mysql root account password */ $ sudo mysql -u root -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | inventory | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.001 sec) /* Exit the MySQL interrupter */ mysql>> QUIT; /* List all databases on the sql server */ mysql> show databases; /* Switch to a database */ mysql> use inventory; /* To see all the tables in the db */ mysql> show tables; +--------------------------+ | Tables_in_inventory | +--------------------------+ | inventory | | inventory_decommissioned | | switchmap | | vlans | +--------------------------+ 4 rows in set (0.000 sec) /* To see database's field formats */ mysql> describe [table name]; mysql> describe inventory; mysql> describe switchmap; /* Returns the columns and column information for the designated table */ mysql> show columns from 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 | | | switchport | varchar(16) | YES | | NULL | | | hostmac | varchar(18) | YES | | NULL | | | managed | varchar(16) | YES | | NULL | | | trunk | 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 | | +-------------+---------------+------+-----+---------+----------------+ 25 rows in set (0.001 sec) /* Show all columns for records that match specific criteria. In this example show everything from inventory. In this example make sure to end with the \G operator which causes the output to be formatted as shown below. */ mysql> SELECT * FROM inventory WHERE mac="10:b3:c6:ae:aa:c8" \G; *************************** 1. row *************************** id: 1667 ip: 127.0.0.169 mac: 10:b3:c6:ae:aa:c8 wifiip: wifimac: vlan: NULL name: switchaeaac8 type: Cisco SG350-28 function: switch location: Computer room ports: 80/tcp http GoAhead WebServer, 443/tcp ssl/http GoAhead WebServer switch: switch switchport: NULL hostmac: NULL managed: managed trunk: NULL sn: DNI235106BS os: Cisco SG250 switch firmware: 2.5.9.54 dante: ssid: channel: disposition: In Production notes: lastseen: 2024-07-28 1 row in set (0.001 sec)
In the example above, notice that the id column is given. As a best practice, whenever you change or delete a record, use the "where" clause to specifically identity the id of the record you want changed. Without the 'where' clause you usually apply that change to ALL records.
/* Count rows */ mysql> SELECT COUNT(*) FROM inventory; /* Count specific types of rows */ mysql> SELECT COUNT(*) FROM inventory where switch="switch"; /* Delete a row(s) from a table */ mysql> DELETE from [table name] where [field name] = 'whatever'; /* or */ mysql> DELETE from inventory where id = "2953";
More Complicated Examples
/* Show unique records */ mysql> SELECT DISTINCT [column name] FROM [table name]; /* Show selected records sorted in an ascending (asc) or descending (desc) */ mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC; /* You can list specific columns of all switches */ mysql> select id,mac,type from inventory where switch="switch"; /* You can list all of the phones */ mysql> select id, ip from inventory where type like "Yea%"; /* You can list each of the unique values in the type column */ mysql> select distinct(type) from inventory order by type; /* View all info in record number 1728 */ mysql> select * from inventory where id="1728" \G; /* View some details about all "netgear" switches */ mysql> select id,ip,mac,location from inventory where type like "%netgear%" \G; /* or */ mysql> select id,ip,mac,location from inventory where type like "%netgear%"; /* View the unique values in the type column where the switch column contains "switch" */ mysql> select unique(type) from inventory where switch="switch" order by type; /* Set the managed column to "managed" where the value of type begins with "Cisco" */ mysql> update inventory set managed="managed" where type like "Cisco%"; /* Load a CSV file into a table */ mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3); /* Dump just the inventory database */ sudo mysqldump -u root -p --databases inventory >~/bkup/mysql/inventoryDB.sql /* This a an actual production example */ /* ben@benb:~/bkup/mysql$ sudo mysqldump -u root -p --databases inventory > inventoryDB.10.05.2020-13:21:49.sql */ /* Change a user password */ mysql> SET PASSWORD FOR 'dbadmin'@'localhost' = bigshark; /* or */ mysql> ALTER USER dbadmin@localhost IDENTIFIED BY 'littlewhale'; /* Restore database (or database table) from backup */ mysql> [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql mysql> /* Export the entire Inventory table to CSV format */ /* Note that you can export only into the '/var/lib/mysql-files/' directory */ select id, ifnull(ip,'None'),ifnull(mac,'Unknown'),ifnull(name,'Unknown'), ifnull(location,'unknown'),ifnull(ports,'unknown'),ifnull(type,'Unknown'), ifnull(sn,''),ifnull(os,''),ifnull(firmware,''),ifnull(dante,''), ifnull(ssid,''),ifnull(channel,''),ifnull(frequency,''),ifnull(band,''), ifnull(channelwidth,''),ifnull(strength,''),ifnull(encryption,''), ifnull(notes,''),ifnull(ethernet,'') from inventory into outfile '/var/lib/mysql-files/inventory.csv.export.date.time.csv' fields enclosed by '"' terminated by ';' escaped by '"' lines terminated by '\r\n'; mysql> select mac from inventory where mac != ''; mysql> select ip,os from inventory where os like '%win%7%'; +--------------+-----------------------------------------------------+ | ip | os | +--------------+-----------------------------------------------------+ | 127.0.0.4 | Windows Server 2008 R2 Standard 7601 Service Pack 1 | | 127.0.0.137 | Win 7 Home Premium Service Pack 1 | | 127.0.0.170 | Win 7 Pro | | 127.0.0.175 | Win 7 | | 127.0.0.214 | Win 7 Pro | | 127.0.0.235 | Win 7 Pro | +--------------+-----------------------------------------------------+ 6 rows in set (0.00 sec) /* GROUP BY */ mysql> select mac,count(*) as Total from inventory group by mac order by Total desc; /* Insert new record if a field value does not exist */ mysql> INSERT INTO inventory (mac) SELECT 'e8:9f:80:43:55:04' WHERE NOT EXISTS (Select mac From inventory WHERE mac ='e8:9f:80:43:55:04') LIMIT 1;
More to come...
Using the MySQL/mariadb Database