Database Cleanup Operations: tbllog_register, tblclients > ip,host, tblorders > ipaddress

[whattheserver]

First off thanks to the original thread for the inspiration for the first script. I was unable to reply there with my bash version of this. I also had another script so I figured id share all it here.

#!/bin/bash
## Author: Michael Ramsey
## Objective clean 2 weeks prior to tbllog_register
## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database
## save as whmcs-register-clean.sh chmod it to 700
## example cronjob 
## 0 0 * * 0 /bin/bash /home/username/scripts/whmcs-register-clean.sh >/dev/null 2>&1

date2w=$(date --date='2 week ago' +"%Y-%m-%d")
/usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "DELETE FROM tbllog_register WHERE created_at < $date2w" database_name

 

The second one is more for privacy based companies that want to prevent any client IP's from being unnecessarily saved and prevent DB growth for really large companies with active clients logins. We run privacy based logless VPN service and wanted to ensure there is no IP's ever saved of any of our clients. This included the client and support portal in addition to the VPN nodes and freeradius auth server. More information about this setup is in our blog post here > https://whattheserver.com/whmcs-client-last-login-ip-log-clearing-script/

#!/bin/bash
## Author: Michael Ramsey
## Objective clear last login IP and host from tblclients
## Please ensure that the database user used for this has both Select and Delete permissions for the whmcs database
## save as whmcs-client-clear-ip.sh chmod it to 700
## example cronjob 
## * * * * * /bin/bash /home/username/scripts/whmcs-client-clear-ip.sh >/dev/null 2>&1

#Clear clients last login IP address in table tblclients > ip,host
/usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblclients SET ip = '', host = ''" database_name

#Clear Order Ipaddress in table tblorders > ipaddress
/usr/bin/mysql -h "localhost" -u "database_username" "-pPasswordhere" -e "UPDATE tblorders SET ipaddress = ''" database_name

 

whmcs-register-clean.sh

whmcs-client-clear-ip.sh