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.

ssh gateway activity log

 

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

 

 

The following command would delete all entries with Login Time before 01 January 2020 from the gwactivity_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

[root@otp ~]# grep db_name /usr/local/etc/ezlogin/ez.conf

db_name ezlogin_wggmp

#Generate a backup of the db.

[root@otp ~]# mysqldump ezlogin_wggmp > ezlogin_wggmp.sql

#Generate a backup of the gateway activity logs table.

[root@otp ~]# 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.

 [root@~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.

 

[root@~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  

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_gwactivity_logs where login_time < '2020-01-01';

Query OK, 235 rows affected (0.009 sec) 

 

 

 

 

 

 

5 (1)
Article Rating (1 Votes)
Rate this article
    Attached Files
    There are no attachments for this article.
    Related Articles RSS Feed
    Could not connect to database infinite loop on restore
    Viewed 3339 times since Wed, Jun 14, 2017
    Primary node showing the public ip instead of lan ip when cluster is configured
    Viewed 1080 times since Wed, Nov 21, 2018
    PHP extension ’mcrypt’ is required.
    Viewed 6446 times since Wed, Jun 14, 2017
    Error: Wrong passphrase or corrupted key
    Viewed 403 times since Mon, Nov 9, 2020
    Unable to add server using putty private key key pair. How to convert PPK file OpenSSH format
    Viewed 5515 times since Thu, Jun 15, 2017
    404 error on login with nginx
    Viewed 4077 times since Wed, Jun 14, 2017
    License mismatch! This Ezeelogin was installed with a different license.
    Viewed 358 times since Thu, Jul 16, 2020
    Token not found in Cpanel/WHM
    Viewed 5556 times since Wed, Jun 14, 2017
    vi editor having issues while logged in via ezeelogin
    Viewed 3531 times since Wed, Jun 14, 2017
    Getting "Could not load configuration" or 500 error on login page
    Viewed 3788 times since Wed, Jun 14, 2017