Syed Jahanzaib Personal Blog to Share Knowledge !

July 23, 2019

RM: Delete Expired Users Record

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:57 PM

expired.jpg


Following script was made for DMA Radius Manager 4.1.x. It can delete X months old Expired users record from the mysql DB.

Sharing for reference purposes …

WordPress is not letting proper pasting of the code …

delete expired uesr.PNG

#!/bin/sh
#set -x
# This script delets users who have expired 2 months ago. and then delete there records from all tables.
# Syed Jahanzaib / June 2019
SQLPASS=”SQLPASS”
export MYSQL_PWD=$SQLPASS
> /tmp/expired.users.txt

#mysql -uroot -e “use radius; select username from rm_users where expiration BETWEEN ‘2010-01-01’ AND ‘2019-04-30’;” |sort > /tmp/expired.users.txt

# Fetch users who have expired 2 months ago & before, (using expired date), BE CAREFUL WHEN USING THIS
mysql -uroot -e “use radius; select username from rm_users where expiration <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)” |sort > /tmp/expired.users.txt
num=0
cat /tmp/expired.users.txt | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk ‘{print $1}’`
echo “$USERNAME —- user record from all relevant tables”
mysql -uroot -e “use radius; DELETE FROM rm_cards WHERE cardnum = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_users WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_changesrv WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radcheck WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radacct WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_radacct WHERE username = ‘$USERNAME’;”
done

 

Jz

3 Comments »

  1. Dear Jahanzaib,

    https://aacable.wordpress.com/2014/02/01/mikrotik_routing_target_web_site_to_secondary_wan_link/

    Can we use PCC with this above mentioned routing? Can you please share the customized script with me for (Per connection classifier + Routing Target Web Site to Secondary WAN Link)?

    Its urgent, Please help me.

    Like

    Comment by Syed Zain — July 31, 2019 @ 7:02 PM

  2. salam 3alaykom mr syed i face a syntax error
    i just want to delete expired card only
    so plz can you write down a script for it
    god bless you

    Like

    Comment by belal — August 5, 2019 @ 3:26 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: