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

382 Vishnupriya July 30, 2024 General 3415

Generate excel report for ezeelogin user details

Overview: This Article Provides Instructions for Generating Ezeelogin User Reports: It covers steps for displaying user details, including usernames, last login times, and user groups, and exporting these details to a CSV file for Excel. It also includes guidance on generating reports for active users and suspended users, with details exported for easy viewing in Excel.

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:

Step 1. Login to mysql

root@jumpserver: ~ mysql -u root -p

mysql > use db_name;

Step 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 |

+-----------+-----------+---------------------+

Step 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

Step 1. Login to mysql

    root@jumpserver:~ mysql -u root -p 
 mysql > use db_name; 

Step 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

Step 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 the correct db_name and dbprefix. You can find it from /usr/local/etc/ezlogin/ez.conf

How to generate user reports as CSV files in Ezeelogin?

Adding Users Exclusively to Databases

License Expired when generating user reports