View Complete Server and User list via MySQL.
How to view the complete Server and User list via MySQL?
Overview: This article offers detailed instructions on how to view the complete server and user lists through MySQL.
This operation can only be performed through the backend with root access to the gateway server.
To display the full server and user list via MySQL follow the below steps:
Step 1: Login to MySQL. The database name (db_name)can be found in the Ezeelogin configuration file located at /usr/local/etc/ezlogin/ez.conf.
root@gateway:~# mysql -u root -p
mysql > use db_name;
Step 2: Use the following query to view the complete user list. Replace dbprefix_ with the correct prefix found in /usr/local/etc/ezlogin/ez.conf
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 |
+----------------+----------------+----------+--------------------------+-----------+
Step 3: Use the following query to view the complete server list. Replace dbprefix_ with the correct prefix found in /usr/local/etc/ezlogin/ez.conf
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 |
+------------+-------------+----------+--------------+----------------+
Use the correct db_name and dbprefix, which can be found in the /usr/local/etc/ezlogin/ez.conf file.
Related Articles:
How to view database connectivity credentials and configuration file?
Basic MySQL commands for troubleshooting database related issues in Ezeelogin