Skip to Content

View Complete Server and User list via MySQL.

How to view the complete Server and User list via MySQL?

To display the full server and user list via MySQL

1. Login to MySQL

root@jumpserver:~ mysql -u root -p

mysql > use db_name; 

2.Use the following query to view the full user list .

MariaDB [db_name]> SELECT A.username AS username, A.firstname AS firstname, A.lastname AS lastname, A.email AS email, B.name AS usergroup FROM dbprefix_users AS A INNER JOIN dbprefix_usergroups AS B ON A.usergroup_id = B.id;

+----------------+----------------+----------+--------------------------+-----------+
| username       | firstname      | lastname | email                    | usergroup |
+----------------+----------------+----------+--------------------------+-----------+
| admin          | Administrator  | NULL     |                          | Admins    |
| adam           | adam           |          | [email protected]           | Dummy     |
| luke           | luke           |          | luke                     | Dummy     |
| chris          | chris          |          | [email protected]          | Dummy     |
+----------------+----------------+----------+--------------------------+-----------+

3.Use the following query to view the full server list.

MariaDB [db_name]> SELECT A.name AS name, A.description AS description, A.ssh_port AS ssh_port, B.name AS servergroup, C.ip AS IPAddress FROM dbprefix_servers AS A INNER JOIN dbprefix_servergroups AS B ON A.servergroup_id = B.id INNER JOIN dbprefix_ips AS C ON A.id = C.server_id;

+------------+-------------+----------+--------------+----------------+
| name       | description | ssh_port | servergroup  | IPAddress      |
+------------+-------------+----------+--------------+----------------+
| centos8    |             | 22       | LinuxServers | 192.168.56.194 |
| centos7    |             | 22       | LinuxServers | 192.168.56.194 |
| Windows    |             | 22       | LinuxServers | 192.168.56.181 |
| production |             | 22       | LinuxServers | 192.168.56.194 |
+------------+-------------+----------+--------------+----------------+

You have to use the correct db_name and dbprefix. You can find it from /usr/local/etc/ezlogin/ez.conf