Deleting Old Years Users Data Script


#!/bin/bash

SET @year = “2013”;

Create Temporary radacct Table
CREATE TABLE radacct2 (
username varchar(64) NOT NULL default ”,
acctsessiontime int(12) default NULL,
acctinputoctets bigint(20) default NULL,
acctoutputoctets bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Cumulate Datas From Previous Years From Radacct(radacct2)
SET @yearlike = CONCAT(@year, “%”);
SET @cumuldatetime = CONCAT(@year, “-12-31 23:59:59”);

INSERT INTO radacct2 (username, acctsessiontime, acctinputoctets, acctoutputoctets)
SELECT username, SUM(acctsessiontime), SUM(acctinputoctets), SUM(acctoutputoctets)
FROM radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

Delete all Data From radacct Specified Year
DELETE FROM radacct WHERE acctstarttime LIKE @yearlike;

Create Temporary rm_radacct Table
CREATE TABLE rm_radacct2 (
username varchar(64) NOT NULL default ”,
acctsessiontime int(12) default NULL,
ulbytes bigint(20) default NULL,
dlbytes bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Cumulate Datas From Previous Years From rm_radacct(rm_radacct2)
SET @yearlike = CONCAT(@year, “%”);
SET @cumuldatetime = CONCAT(@year, “-12-31 23:59:59”);

INSERT INTO rm_radacct2 (username, acctsessiontime, ulbytes, dlbytes)
SELECT username, SUM(acctsessiontime), SUM(ulbytes), SUM(dlbytes)
FROM rm_radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

Delete all Data From rm_radacct Specified Year
DELETE FROM rm_radacct WHERE acctstarttime LIKE @yearlike;

Substract Deleted Values (radacct2) From radacct
UPDATE rm_users, radacct2
SET rm_users.uptimelimit = rm_users.uptimelimit – radacct2.acctsessiontime,
rm_users.uplimit     = rm_users.uplimit     – radacct2.acctinputoctets,
rm_users.downlimit   = rm_users.downlimit   – radacct2.acctoutputoctets,
rm_users.comblimit   = rm_users.comblimit   – radacct2.acctoutputoctets – radacct2.acctinputoctets
WHERE rm_users.username = radacct2.username;

Substract Deleted Values (rm_radacct2) From radacct
UPDATE rm_users, rm_radacct2
SET rm_users.uptimelimit = rm_users.uptimelimit + rm_radacct2.acctsessiontime,
rm_users.uplimit     = rm_users.uplimit     + rm_radacct2.ulbytes,
rm_users.downlimit   = rm_users.downlimit   + rm_radacct2.dlbytes,
rm_users.comblimit   = rm_users.comblimit   + rm_radacct2.dlbytes + rm_radacct2.ulbytes
WHERE rm_users.username = rm_radacct2.username;

Drop Temporary Table
DROP TABLE radacct2;
DROP TABLE rm_radacct2;

Leave a Reply

Please log in using one of these methods to post your comment:

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