Skip to Content

How to output mysql query results in csv format and view the reports in Excel sheets?

Generate report with User Name, Last Login Date and Roles of the Ezeelogin user and view the reports in Excel sheet

To display the details such as Ezeelogin Username, Last login at which the user has been logged in, and the Usergroups to which user belongs to in the Ezeelogin jump server:

1. Login to mysql
 

[email protected]: ~ mysql -u root -p

mysql > use db_name;

 2. The following  query would display the username, last login and usergroups

mysql > SELECT A.username AS username, B.name AS usergroup, A.last_login_at AS last_login_at FROM dbprefix_users AS A INNER JOIN dbprefix_usergroups AS B ON A.usergroup_id = B.id;

+-----------+-----------+---------------------+
| username | usergroup | last_login_at |
+-----------+-----------+---------------------+  
| admin | Admins | 2021-02-26 04:31:44 |
| ram | Admins | 2021-02-26 04:31:43 |
| john | Admins | 2021-02-23 03:03:27 |  
| test | Admins | 2021-02-26 03:48:17 |
+-----------+-----------+---------------------+

3. You need to run the following query to export the details to /var/lib/mysql-files/ and to view the generated reports in Excel sheet

mysql> SELECT A.username AS username, B.name AS usergroup, A.last_login_at AS last_login_at FROM dbprefix_users AS A INNER JOIN dbprefix_usergroups AS B ON A.usergroup_id = B.id ORDER BY B.name ASC, A.username ASC INTO OUTFILE '/var/lib/mysql-files/users-usergroups.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

 

To  generate the reports of active users in the Ezeelogin gateway server and view in Excel sheet

1. Login to mysql

[email protected]:~ mysql -u root -p

mysql > use db_name;

 

2. The following query would display the active users in the Ezeelogin gateway server

 

mysql> select username from db_prefix_users where status=1;

To display the suspended users in the Ezeelogin gateway server, you need to change the status as 0 in the above query

 

 3. You need to run the following query to export the details to /var/lib/mysql-files/ and to view the generated reports in Excel sheet

mysql> select username from db_prefix_users where status=1 INTO OUTFILE '/var/lib/mysql-files/active-users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

 

you have to use correct db_name and dbprefix from ez.conf