Deleting entries in the mysql database table gwactivity_logs
Deleting entries in the Ezeelogin database table gwactivity_logs for house keeping.
Its would be required to reduce the size of the Ezeelogin mysql database table gwactivity_logs as it would cause the Ezeelogin software upgrade to slow down significantly ( 2-4 hours ) because of the huge size of the database table. The table can be truncated based on the ssh gateway users ’login time’ so that the upgrade operations completes quickly.
This data from the gwactivity_log would be displayed in the GUI under Users->Shell Activity->Gateway Activity Logs as shown.
Before truncating the gatewayactivity_logs you can export the logs from GUI for audit purposes under Users>Shell Activity>Export
.png)
Generate a mysql dump of the Ezeelogin database or a table dump before you perform the operation in case you need to revert due to any unforeseen reasons.
#Determine the Ezeelogin database in use
[[email protected] ~]# grep db_name /usr/local/etc/ezlogin/ez.conf
db_name ezlogin_wggmp
#Generate a backup of the db.
[[email protected] ~]# mysqldump ezlogin_wggmp > ezlogin_wggmp.sql
#Generate a backup of the gateway activity logs table.
[[email protected] ~]# mysqldump ezlogin_wggmp thwm_gwactivity_logs > ezlogin_wggmp_thwm_gwactivity_logs.sql
#Deleting the entries in the table gwactivity_logs using the Ezeelogin query runner script.
[[email protected]~gate]# php /usr/local/ezlogin/ez_queryrunner.php "delete from prefix_gwactivity_logs where login_time < ’2020-01-01’"
The gwactivity_logs mysql table structure would looks as follows.
[[email protected]~gate]#MariaDB [ezlogin_wggmp]> desc thwm_gwactivity_logs;
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------------------+-------------------------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | 0 | |
| uid | int(10) unsigned | NO | | 0 | |
| remote_ip | varchar(45) | NO | | | |
| remote_port | char(5) | NO | | | |
| local_ip | varchar(45) | NO | | | |
| local_port | char(5) | NO | | | |
| login_time | datetime | YES | | NULL | |
| logout_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| idle_time | int(10) unsigned | NO | | 0 | |
| remote_time | int(10) unsigned | NO | | 0 | |
| status | varchar(100) | NO | | | |
+-------------+---------------------+------+-----+---------------------+-------------------------------+
Alternatively, the mysql command to delete the entries based on the user Login Time would be
MariaDB [ezlogin_wggmp]> delete from thwm_gwactivity_logs where login_time < ’2020-01-01’;
Query OK, 235 rows affected (0.009 sec)