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:
root@jumpserver: ~ 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;
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
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 the correct db_name and dbprefix. You can find it from /usr/local/etc/ezlogin/ez.conf