Skip to Content

Deleting entries in the mysql database table serveractivity_logs

It would be required to reduce the size of the Ezeelogin mysql database table serveractivity_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 serveractivity_logs would be displayed in the GUI under Users->Server Activity->Server  Activity Logs as shown.

 

 Before truncating the serveractivity_logs you can export the logs from GUI for audit purposes under Users>Server Activity>Export

 

The following command would delete all entries with Login Time before 01 January 2020 from the serveractivity_logs table in the Ezeelogin mysql database.

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]otp ~]# mysqldump ezlogin_wggmp thwm_serveractivity_logs > ezlogin_wggmp_thwm_serveractivity_logs.sql

#Deleting the entries in the table serveractivity _logs using  the Ezeelogin query runner script.

  [[email protected]~gate]#  php /usr/local/ezlogin/ez_queryrunner.php "delete from prefix_serveractivity_logs where login_time < ’2020-01-01’"

  The serveractivity _logs  mysql table  structure would looks as follows.

 

[[email protected]~gate]# MariaDB [ezlogin_wggmp]> desc thwm_serveractivity_logs;

 

+------------------+---------------------------------+------+-----+---------------------+-------------------------- 
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------------------+------+-----+---------------------+-------------------------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(10) unsigned | NO | MUL | 0 | |
| server_id | int(10) unsigned | NO | | 0 | |
| gwactivity_id | int(20) unsigned | NO | | 0 | |
| login_time | datetime | YES | | NULL | |
| logout_time | timestamp | NO | | current_timestamp() | on update current_timestamp() |
| input_idle_time | int(10) unsigned | NO | | 0 | |
| output_idle_time | int(10) unsigned | NO | | 0 | | 
| status | varchar(100) | NO | | | | 
| type | enum(’SCP’,’SHELL’,’CMD’,’RDP’) | NO | | SHELL | |

 

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

 

 

 Alternatively, the mysql command to delete the entries based on the user Login Time would be  

mysql ezlogin_wggmp
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4241
Server version: 10.4.14-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the current input statement.
 
MariaDB [ezlogin_wggmp]>

MariaDB [ezlogin_wggmp]> delete from thwm_serveractivity_logs where login_time < ’2020-01-01’;

Query OK, 235 rows affected (0.009 sec)