LibreNMS dump devices in MySQL database

All the devices are stored in the librenms datebase devices table.

You can dump all of them with the following command from a shell prompt.

sudo mysql -u librenms  -p librenms -e "select hostname,sysName from devices"

Example output

[jim@localhost ~]$ sudo mysql -p librenms -e "select hostname,sysName from devices"
 Enter password:
 +----------------------------------------+--------------------------------+
 | hostname                               | sysName                        |
 +----------------------------------------+--------------------------------+
 | 192.168.1.1                            | linksys                        |
 | 192.168.1.9                            | wifi-unifi                     |
 +----------------------------------------+--------------------------------+

Basic MongoDB commands

Connect to Mongo database

mongo

Connect to Mongo on a different port (May be needed.  Port is for UniFi server)

mongo -port 27117

Show Databases

show dbs

Use database

use dbname

Show tables/collections

show collections

or

show tables

List contents of table/collection

db.collection.find()

Example: (This example prints everything in the “admin” collection)

db.admin.find()

Find info that line that whose name is admin

 db.admin.find({name : "admin" })

Find everything in a table, but only print columns that are named “name, email, and x_shadow”

db.admin.find({ }, { name : "", email : "", "x_shadow" : "" })

List users

show users

Authenticate

db.auth("username","password")

Insert into table/collection

d = {"data":"data"}
db.collection.insert(d)

Update a line, for example a users password.  Swap out the ObjectId for the ID that mongo gives you when you list the admins or users.  You will need to swap out the hash for the hash of the password you want.

db.admin.update({"_id" : ObjectId("a328bf90547ehc429a03ed85")}, {$set: { "x_shadow" : "$6$XB32GMXr$8dUt9huJzzL6O.gGZbs7QH1npldbzBzNDt/uUO1bI3b7Ij3YipgubtVHwincUUZjnDLh.KDI36uh2gUCID9yb1"}});

Example:

d = {"name":"admin","lang":"en_US","x_password":"password","time_created":"","last_site_name":"default"}
db.admin.insert(d)

Delete line from table/collection

db.collection.remove()

Example: (This will look for all the “rows” where the “name” contains “admin” and remove it)

db.admin.remove({ name: "admin"})

UniFI Specific

The following commands are specifically for a UniFi server.

Show UniFi Sites

db.site.find().forEach(printjson);

Or

mongo --port 27117 ace --eval "db.site.find().forEach(printjson);"

Show UniFi admins

db.admin.find().forEach(printjson);

Or

mongo --port 27117 ace --eval "db.admin.find().forEach(printjson);"

Some Basic MySQL Commands

Enter Mysql

mysql -u root -p

Create Database

create DATABASE testdb;

Select Database

use testdb;

Delete Database

drop DATABASE testdb;

Drop Table

drop table table_name;

Show Tables

show tables;

Show data in table

SELECT * FROM table_name;

Create Table

CREATE TABLE contacts_table (id INT, name VARCHAR(20), email VARCHAR(20));

Insert data into table

INSERT INTO contacts_table (id,name,email) VALUES(2,"John","John83@incredigeek.com");

List one row in table

SELECT * FROM table_name LIMIT 1;

Delete Row in Table

DELETE FROM table_name WHERE row_name=data_to_delete;

Delete all “users” from a WordPress database that do not contain admin in the username.

DELETE FROM `wp-users` WHERE user_login NOT LIKE "%admin%"

Add Auto Increment to Table

ALTER TABLE  `table_name` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Create table with Date and Time timestamp

CREATE TABLE table_name (id INT, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Show MySQL Users

select * from mysql.users;

Create MySQL User

GRANT ALL ON mysqldb.* TO username@localhost IDENTIFIED BY 'password';

Delete MySQL user

DROP USER 'username'@'localhost';

Add Column to the end of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60);

Add Column to the begging of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) FIRST;

Insert Column after Specific Column in MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) AFTER columnname;