from bash
mysql -u librenms -pD42nf23rewD -e "show databases;"
mysql -u librenms -pD42nf23rewD -D librenms -e "show tables;"
mysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname FROM devices WHERE hostname LIKE '172.30.95%';"
mysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname,sysName FROM devices WHERE hostname LIKE '172.30.95.%';"
|
|
|---|
| log into MariaDB | librenms@librenms:~$ mysql -u librenms -p
Enter password: [ default password with this ova file: D42nf23rewD ]
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 298
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> |
| list the databases | MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| librenms |
+--------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> |
| use a database | MariaDB [(none)]> use librenms;
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 [librenms]> |
| show tables |
| Code Block |
|---|
| title | show tables |
|---|
| collapse | true |
|---|
| MariaDB [librenms]> show tables
-> ;
+---------------------------+
| Tables_in_librenms |
+---------------------------+
| access_points |
| alert_device_map |
| alert_group_map |
| alert_location_map |
| alert_log |
| alert_rules |
| alert_schedulables |
| alert_schedule |
| alert_template_map |
| alert_templates |
| alert_transport_groups |
| alert_transport_map |
| alert_transports |
| alerts |
| api_tokens |
| application_metrics |
| applications |
| authlog |
| bgpPeers |
| bgpPeers_cbgp |
| bill_data |
| bill_history |
| bill_perms |
| bill_port_counters |
| bill_ports |
| bills |
| callback |
| cef_switching |
| ciscoASA |
| component |
| component_prefs |
| component_statuslog |
| config |
| customers |
| customoids |
| dashboards |
| dbSchema |
| device_graphs |
| device_group_device |
| device_groups |
| device_mibs |
| device_oids |
| device_perf |
| device_relationships |
| devices |
| devices_attribs |
| devices_group_perms |
| devices_perms |
| entPhysical |
| entPhysical_state |
| entityState |
| eventlog |
| graph_types |
| hrDevice |
| ipsec_tunnels |
| ipv4_addresses |
| ipv4_mac |
| ipv4_networks |
| ipv6_addresses |
| ipv6_networks |
| juniAtmVp |
| links |
| loadbalancer_rservers |
| loadbalancer_vservers |
| locations |
| mac_accounting |
| mefinfo |
| mempools |
| mibdefs |
| migrations |
| mpls_lsp_paths |
| mpls_lsps |
| mpls_saps |
| mpls_sdp_binds |
| mpls_sdps |
| mpls_services |
| mpls_tunnel_ar_hops |
| mpls_tunnel_c_hops |
| munin_plugins |
| munin_plugins_ds |
| netscaler_vservers |
| notifications |
| notifications_attribs |
| ospf_areas |
| ospf_instances |
| ospf_nbrs |
| ospf_ports |
| packages |
| pdb_ix |
| pdb_ix_peers |
| perf_times |
| plugins |
| poller_cluster |
| poller_cluster_stats |
| poller_groups |
| pollers |
| ports |
| ports_adsl |
| ports_fdb |
| ports_nac |
| ports_perms |
| ports_stack |
| ports_statistics |
| ports_stp |
| ports_vlans |
| processes |
| processors |
| proxmox |
| proxmox_ports |
| pseudowires |
| route |
| sensors |
| sensors_to_state_indexes |
| services |
| session |
| slas |
| state_indexes |
| state_translations |
| storage |
| stp |
| syslog |
| tnmsneinfo |
| toner |
| transport_group_transport |
| ucd_diskio |
| users |
| users_prefs |
| users_widgets |
| vlans |
| vminfo |
| vrf_lite_cisco |
| vrfs |
| widgets |
| wireless_sensors |
+---------------------------+
134 rows in set (0.00 sec)
MariaDB [librenms]>
|
|
| description of the table: devices | MariaDB [librenms]> desc devices;
| Code Block |
|---|
| title | desc devices; |
|---|
| collapse | true |
|---|
| MariaDB [librenms]> desc devices;
+---------------------------+----------------------------------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+----------------------------------------------+------+-----+-------------------+----------------+
| device_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| inserted | timestamp | YES | | CURRENT_TIMESTAMP | |
| hostname | varchar(128) | NO | MUL | NULL | |
| sysName | varchar(128) | YES | MUL | NULL | |
| ip | varbinary(16) | YES | | NULL | |
| overwrite_ip | varchar(40) | YES | | NULL | |
| community | varchar(255) | YES | | NULL | |
| authlevel | enum('noAuthNoPriv','authNoPriv','authPriv') | YES | | NULL | |
| authname | varchar(64) | YES | | NULL | |
| authpass | varchar(64) | YES | | NULL | |
| authalgo | enum('MD5','SHA') | YES | | NULL | |
| cryptopass | varchar(64) | YES | | NULL | |
| cryptoalgo | enum('AES','DES','') | YES | | NULL | |
| snmpver | varchar(4) | NO | | v2c | |
| port | smallint(5) unsigned | NO | | 161 | |
| transport | varchar(16) | NO | | udp | |
| timeout | int(11) | YES | | NULL | |
| retries | int(11) | YES | | NULL | |
| snmp_disable | tinyint(1) | NO | | 0 | |
| bgpLocalAs | int(10) unsigned | YES | | NULL | |
| sysObjectID | varchar(128) | YES | | NULL | |
| sysDescr | text | YES | | NULL | |
| sysContact | text | YES | | NULL | |
| version | text | YES | | NULL | |
| hardware | text | YES | | NULL | |
| features | text | YES | | NULL | |
| location_id | int(10) unsigned | YES | | NULL | |
| os | varchar(32) | YES | MUL | NULL | |
| status | tinyint(1) | NO | MUL | 0 | |
| status_reason | varchar(50) | NO | | NULL | |
| ignore | tinyint(1) | NO | | 0 | |
| disabled | tinyint(1) | NO | | 0 | |
| uptime | bigint(20) | YES | | NULL | |
| agent_uptime | int(10) unsigned | NO | | 0 | |
| last_polled | timestamp | YES | MUL | NULL | |
| last_poll_attempted | timestamp | YES | MUL | NULL | |
| last_polled_timetaken | double(5,2) | YES | | NULL | |
| last_discovered_timetaken | double(5,2) | YES | | NULL | |
| last_discovered | timestamp | YES | | NULL | |
| last_ping | timestamp | YES | | NULL | |
| last_ping_timetaken | double(8,2) | YES | | NULL | |
| purpose | text | YES | | NULL | |
| type | varchar(20) | NO | | | |
| serial | text | YES | | NULL | |
| icon | varchar(255) | YES | | NULL | |
| poller_group | int(11) | NO | | 0 | |
| override_sysLocation | tinyint(1) | YES | | 0 | |
| notes | text | YES | | NULL | |
| port_association_mode | int(11) | NO | | 1 | |
| max_depth | int(11) | NO | | 0 | |
| disable_notify | tinyint(1) | NO | | 0 | |
+---------------------------+----------------------------------------------+------+-----+-------------------+----------------+
51 rows in set (0.00 sec)
MariaDB [librenms]>
MariaDB [librenms]>
|
|
| View file |
|---|
| name | MySQL show Devices details.txt |
|---|
| height | 250 |
|---|
|
| SELECT * from devices;
From the Bash command linemysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname,sysName FROM devices WHERE hostname LIKE '172.30.95.%';" | sed 's/\t/,/g' > list_Netw_Device.cvs |
show every ROWS in the table: devices | SELECT * from devices;
| Code Block |
|---|
| title | show all the ROW |
|---|
| collapse | true |
|---|
| MariaDB [librenms]> SELECT * from devices;
+-----------+---------------------+---------------+---------------+------+--------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+--------------+------------+---------------------------+------------------------------------------------------------------------------------+---------------------+-------------------+--------------------+--------------+-------------+---------+--------+---------------+--------+----------+----------+--------------+---------------------+---------------------+-----------------------+---------------------------+---------------------+---------------------+---------------------+---------+--------+-------------+----------------+--------------+----------------------+-------+-----------------------+-----------+----------------+
| device_id | inserted | hostname | sysName | ip | overwrite_ip | community | authlevel | authname | authpass | authalgo | cryptopass | cryptoalgo | snmpver | port | transport | timeout | retries | snmp_disable | bgpLocalAs | sysObjectID | sysDescr | sysContact | version | hardware | features | location_id | os | status | status_reason | ignore | disabled | uptime | agent_uptime | last_polled | last_poll_attempted | last_polled_timetaken | last_discovered_timetaken | last_discovered | last_ping | last_ping_timetaken | purpose | type | serial | icon | poller_group | override_sysLocation | notes | port_association_mode | max_depth | disable_notify |
+-----------+---------------------+---------------+---------------+------+--------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+--------------+------------+---------------------------+------------------------------------------------------------------------------------+---------------------+-------------------+--------------------+--------------+-------------+---------+--------+---------------+--------+----------+----------+--------------+---------------------+---------------------+-----------------------+---------------------------+---------------------+---------------------+---------------------+---------+--------+-------------+----------------+--------------+----------------------+-------+-----------------------+-----------+----------------+
| 1 | 2020-04-28 04:54:49 | localhost | librenms | | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.8072.3.2.10 | Linux librenms 4.15.0-96-generic #97-Ubuntu SMP Wed Apr 1 03:25:46 UTC 2020 x86_64 | root | 4.15.0-96-generic | Generic x86 64-bit | Ubuntu 18.04 | 1 | linux | 1 | | 0 | 0 | 3445 | 0 | 2020-06-10 09:35:13 | NULL | 2.76 | 4.40 | 2020-04-28 04:55:07 | 2020-06-10 09:35:13 | 0.04 | NULL | server | NULL | ubuntu.svg | 0 | 0 | NULL | 1 | 0 | 0 |
| 2 | 2020-06-10 08:13:39 | 172.30.95.135 | sentry_6022ff | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 4 | sentry4 | 1 | | 0 | 0 | 11662389 | 0 | 2020-06-10 09:35:15 | NULL | 5.38 | 10.80 | 2020-06-10 08:41:07 | 2020-06-10 09:35:15 | 31.50 | NULL | power | AGFE0000040 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 3 | 2020-06-10 08:13:40 | 172.30.95.137 | sentry_602301 | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 3 | sentry4 | 1 | | 0 | 0 | 11662439 | 0 | 2020-06-10 09:35:08 | NULL | 5.26 | 8.88 | 2020-06-10 08:40:56 | 2020-06-10 09:35:08 | 28.30 | NULL | power | AGFE0000039 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 4 | 2020-06-10 08:13:44 | 172.30.95.134 | sentry_602300 | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 2 | sentry4 | 1 | | 0 | 0 | 11662359 | 0 | 2020-06-10 09:35:08 | NULL | 5.32 | 8.62 | 2020-06-10 08:40:47 | 2020-06-10 09:35:08 | 27.90 | NULL | power | AGFE0000035 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 5 | 2020-06-10 08:13:55 | 172.30.95.136 | sentry_6022fb | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 5 | sentry4 | 1 | | 0 | 0 | 11662381 | 0 | 2020-06-10 09:35:15 | NULL | 5.43 | 8.93 | 2020-06-10 08:40:38 | 2020-06-10 09:35:15 | 30.20 | NULL | power | AGFE0000034 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 6 | 2020-06-10 08:13:58 | 172.30.95.133 | sentry_6022fe | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 6 | sentry4 | 1 | | 0 | 0 | 11662361 | 0 | 2020-06-10 09:35:09 | NULL | 5.69 | 9.23 | 2020-06-10 08:40:29 | 2020-06-10 09:35:09 | 28.20 | NULL | power | AGFE0000037 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 7 | 2020-06-10 08:14:09 | 172.30.95.132 | sentry_6022fc | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.1718.4 | Sentry Smart PDU | NULL | Version 8.0e | STV-6501A | NULL | 7 | sentry4 | 1 | | 0 | 0 | 11662862 | 0 | 2020-06-10 09:35:08 | NULL | 5.18 | 10.38 | 2020-06-10 08:40:20 | 2020-06-10 09:35:08 | 29.10 | NULL | power | AGFE0000036 | servertech.png | 0 | 0 | NULL | 1 | 0 | 0 |
| 8 | 2020-06-10 08:14:16 | 172.30.95.139 | london se lab | NULL | NULL | public | NULL | NULL | NULL | NULL | NULL | NULL | v2c | 161 | udp | NULL | NULL | 0 | NULL | .1.3.6.1.4.1.10418.18.1.4 | MPU2016DAC 1.32.0.24546 | support@avocent.com | NULL | NULL | NULL | NULL | generic | 1 | | 0 | 0 | 11661135 | 0 | 2020-06-10 09:35:14 | NULL | 3.86 | 6.69 | 2020-06-10 08:40:10 | 2020-06-10 09:35:14 | 29.90 | NULL | server | NULL | generic.svg | 0 | 0 | NULL | 1 | 0 | 0 |
+-----------+---------------------+---------------+---------------+------+--------------+-----------+-----------+----------+----------+----------+------------+------------+---------+------+-----------+---------+---------+--------------+------------+---------------------------+------------------------------------------------------------------------------------+---------------------+-------------------+--------------------+--------------+-------------+---------+--------+---------------+--------+----------+----------+--------------+---------------------+---------------------+-----------------------+---------------------------+---------------------+---------------------+---------------------+---------+--------+-------------+----------------+--------------+----------------------+-------+-----------------------+-----------+----------------+
8 rows in set (0.00 sec)
MariaDB [librenms]> |
|
| show only ROWS hostname and ip address from table: devices | MariaDB [librenms]> SELECT hostname,ip from devices;
+---------------+------+
| hostname | ip |
+---------------+------+
| localhost | |
| 172.30.95.135 | NULL |
| 172.30.95.137 | NULL |
| 172.30.95.134 | NULL |
| 172.30.95.136 | NULL |
| 172.30.95.133 | NULL |
| 172.30.95.132 | NULL |
| 172.30.95.139 | NULL |
+---------------+------+
8 rows in set (0.00 sec) |
| exit | MariaDB [librenms]> exit
Bye
librenms@librenms:~$ |
| type = "network" | SELECT hostname,ip,type FROM devices WHERE type="network";
| Code Block |
|---|
| title | type = "network" |
|---|
| collapse | true |
|---|
| MariaDB [librenms]> SELECT hostname,ip,type FROM devices WHERE type="network";
+---------------+------+---------+
| hostname | ip | type |
+---------------+------+---------+
| 172.30.95.163 | NULL | network |
| 172.30.95.174 | NULL | network |
| 172.30.95.176 | NULL | network |
| 172.30.95.171 | NULL | network |
| 172.30.95.170 | NULL | network |
| 172.30.95.172 | NULL | network |
| 172.30.95.197 | NULL | network |
| 172.30.95.207 | NULL | network |
| 172.30.95.212 | NULL | network |
| 172.30.95.210 | NULL | network |
| 172.30.92.21 | NULL | network |
| 172.30.92.23 | NULL | network |
| 172.30.92.22 | NULL | network |
| 172.30.92.24 | NULL | network |
| 172.30.92.25 | NULL | network |
| 172.30.92.146 | NULL | network |
+---------------+------+---------+
16 rows in set (0.00 sec)
MariaDB [librenms]> |
|
| include ( and start) by "172.30.95%" | MariaDB [librenms]> SELECT hostname,ip,type FROM devices WHERE hostname LIKE "172.30.95%";
| Code Block |
|---|
| MariaDB [librenms]> SELECT hostname,ip,type FROM devices WHERE hostname LIKE "172.30.95%";
+---------------+------+---------+
| hostname | ip | type |
+---------------+------+---------+
| 172.30.95.132 | NULL | power |
| 172.30.95.136 | NULL | power |
| 172.30.95.133 | NULL | power |
| 172.30.95.137 | NULL | power |
| 172.30.95.135 | NULL | power |
| 172.30.95.134 | NULL | power |
| 172.30.95.139 | NULL | server |
| 172.30.95.163 | NULL | network |
| 172.30.95.174 | NULL | network |
| 172.30.95.176 | NULL | network |
| 172.30.95.171 | NULL | network |
| 172.30.95.170 | NULL | network |
| 172.30.95.172 | NULL | network |
| 172.30.95.197 | NULL | network |
| 172.30.95.207 | NULL | network |
| 172.30.95.212 | NULL | network |
| 172.30.95.210 | NULL | network |
+---------------+------+---------+
17 rows in set (0.01 sec)
MariaDB [librenms]> |
|
| AND | MariaDB [librenms]> SELECT hostname,ip,type FROM devices WHERE hostname LIKE "172.30.95%" AND type="network";
| Code Block |
|---|
| MariaDB [librenms]> SELECT hostname,ip,type FROM devices WHERE hostname LIKE "172.30.95%" AND type="network";
+---------------+------+---------+
| hostname | ip | type |
+---------------+------+---------+
| 172.30.95.163 | NULL | network |
| 172.30.95.174 | NULL | network |
| 172.30.95.176 | NULL | network |
| 172.30.95.171 | NULL | network |
| 172.30.95.170 | NULL | network |
| 172.30.95.172 | NULL | network |
| 172.30.95.197 | NULL | network |
| 172.30.95.207 | NULL | network |
| 172.30.95.212 | NULL | network |
| 172.30.95.210 | NULL | network |
+---------------+------+---------+
10 rows in set (0.01 sec)
MariaDB [librenms]> |
|
| List Network devcies | SELECT hostname,sysName FROM devices WHERE hostname LIKE '172.30.95.%';
| Code Block |
|---|
| title | list Network_Devices |
|---|
| librenms@librenms:~$ mysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname,sysName FROM devices WHERE hostname LIKE '172.30.95.%';"
+---------------+------------------+
| hostname | sysName |
+---------------+------------------+
| 172.30.95.70 | localhost |
| 172.30.95.71 | localhost |
| 172.30.95.132 | sentry_6022fc |
| 172.30.95.133 | sentry_6022fe |
| 172.30.95.134 | sentry_602300 |
| 172.30.95.135 | sentry_6022ff |
| 172.30.95.136 | sentry_6022fb |
| 172.30.95.137 | sentry_602301 |
| 172.30.95.139 | london se lab |
| 172.30.95.163 | ex4300-2 |
| 172.30.95.170 | ex4600-1 |
| 172.30.95.171 | ex4600-2 |
| 172.30.95.172 | ex4600-3 |
| 172.30.95.174 | srx300-1-rl102 |
| 172.30.95.176 | qfx5110-2 |
| 172.30.95.177 | qfx5100-1-rl102 |
| 172.30.95.197 | acx2200-2 |
| 172.30.95.207 | rl104-srx1500-b |
| 172.30.95.210 | srx340-1-rack104 |
| 172.30.95.212 | srx320-2 |
+---------------+------------------+
librenms@librenms:~$ |
|
|
|
|
|
| MYSQL in Bash cmd line | https://www.shellhacks.com/mysql-run-query-bash-script-linux-command-line/ mysql -u librenms -pD42nf23rewD -e "show databases;" mysql -u librenms -pD42nf23rewD -D librenms -e "show tables;" mysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname FROM devices WHERE hostname LIKE '172.30.95%';" mysql -u librenms -pD42nf23rewD -D librenms -e "SELECT hostname,sysName FROM devices WHERE hostname LIKE '172.30.95.%';" |
|
|
|
|
|
|
|
|