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
    "An error occurred. Please contact administrator." - got error while trying to access remote server from jump server
    Viewed 2864 times since Tue, May 8, 2018
    web interface very slow to load
    Viewed 1047 times since Wed, Sep 19, 2018
    unable to ssh because ip not allowed because none of user’s groups are listed in AllowGroups
    Viewed 11751 times since Thu, Jun 15, 2017
    Could not connect to database infinite loop on restore
    Viewed 3274 times since Wed, Jun 14, 2017
    vzctl enter veid fails. Unable to get term attr: Invalid argument
    Viewed 3048 times since Wed, Jun 14, 2017
    what port should i open on my server firewall to fetch ezeelogin license?
    Viewed 3561 times since Wed, Aug 16, 2017
    Can we map existing user group in ldap to ezeelogin as ezeelogin user group ?
    Viewed 4812 times since Mon, Sep 25, 2017
    How to fix: “EPROTO” Error after upgrading Node’s version in RDP?
    Viewed 773 times since Wed, May 20, 2020
    502 on login with nginx
    Viewed 4901 times since Wed, Jun 14, 2017
    Unable to open ssh session
    Viewed 3248 times since Thu, Jun 15, 2017