2012-09-24 - Re: [GRASE-Hotspot] Reset user’s “Data Usage”

Header Data

From: Bob Hunt <bo***2@gmail.com>
Message Hash: 8ca1780edc8e4dc375033c3f719f1670600eeeff65bb8b00fa2b7910ddb0f083
Message ID: <CAA-jMMQ4qq=fzxa84cuZcOrc0vuPs25VoKfRWp3Bw444cHPHPg@mail.gmail.com>
Reply To: N/A
UTC Datetime: 2012-09-24 18:03:39 UTC
Raw Date: Tue, 25 Sep 2012 11:03:39 +1000

Raw message 

Hi Tim,

Thanks for the advice.

Until the 4Gb issue is resolved, I've implemented your suggested "better hack".

I've now got a "Reset Data Usage" button on the edituser page which
immediately archives all a user's data  (except current session) and
so resets the user's data usage back to zero.

This hack means I can now easily let regular users keep their usernames.

FYI, I've detailed what I did below and would be interested in any
further suggestions or comments.

Cheers,

Bob
++++++++++++++++++++++++++++++++++++++++++++

Details of code used to add a "reset users data usage" button to Grase
Admin's edituser page:

In .....radmin/templates/edituser.tpl added the following at about
line 88, after the "updateusersubmit" button:
------------------------------------------------------------------------------------
<br/><br/>
<h3>{t}Reset Data Usage{/t}</h3>
{t}Resetting a users data usage to zero will archive all the users
recent data, so will clear their "History" page {/t}<br/>
{t} You may also have to reset data and/or time limits for the user.{/t}<br/>
<button class="negative" type="submit" name="resetdatause"
value="{t}Reset Data Usage to Zero for {/t}" onClick="return
confirm('{t}Are you sure you want to reset this users data
usage?{/t}')"><img src="/grase/images/icons/tick.png" alt=""/>{t
username=$user.Username}Reset Data Usage to Zero for %1{/t}</button>
-----------------------------------------------------------------------------------


In .......radmin/edituser.php, at about line 205, immediately before
the last "}else",  added the following;
------------------------------------------------------------------------------------
$UserNameFile = "/tmp/user_to_archive";
$fh = fopen($UserNameFile, 'w') or die("can't open file");
fwrite($fh, $username);
fclose($fh);

if(isset($_POST['resetdatause']))
	{   // Process form for immediately archiving a users data

		AdminLog::getInstance()->log("data reset to zero for $username");

	exec('perl /usr/share/grase/www/radmin/scripts/archive_a_users_data');
--------------------------------------------------------------------------------------


Copied ....radmin/scripts/radius_monthly_accts to
.....radmin/scripts/archive_a_users_data. Made the new file executable
and replaced everything below line 38 the following.
----------------------------------------------------------------
open (DAT, "/tmp/user_to_archive") ||
    die "ERROR Unable to open file: $!\n";
my $name = <DAT>;
close DAT;
$user_to_archive = $name;
unlink("/tmp/user_to_archive");

$query1 = "INSERT INTO mtotaccttmp
(UserName,AcctDate,ConnNum,ConnTotDuration,ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress)
SELECT UserName,'$date_start',COUNT(*),SUM(AcctSessionTime),MAX(AcctSessionTime),MIN(AcctSessionTime),SUM(AcctInputOctets),
SUM(AcctOutputOctets),NASIPAddress FROM radacct WHERE
UserName='$user_to_archive' GROUP BY UserName,NASIPAddress;";

$query2 = "DELETE FROM radacct WHERE UserName='$user_to_archive';";

$query3 = "UPDATE radcheck,mtotaccttmp SET
radcheck.value=radcheck.value - (mtotaccttmp.InputOctets +
mtotaccttmp.OutputOctets) WHERE radcheck.Attribute='Max-Octets' AND
radcheck.UserName=mtotaccttmp.UserName;";

$query4 = "UPDATE radcheck,mtotaccttmp SET
radcheck.value=radcheck.value - mtotaccttmp.ConnTotDuration WHERE
radcheck.Attribute='Max-All-Session' AND
radcheck.UserName=mtotaccttmp.UserName;";

$query5 ="INSERT INTO mtotacct
(UserName,AcctDate,ConnNum,ConnTotDuration,ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress)
SELECT UserName,AcctDate,ConnNum,ConnTotDuration,ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress
FROM mtotaccttmp WHERE UserName='$user_to_archive';";

$query6 = "TRUNCATE mtotaccttmp;";

$query7 = "UPDATE radcheck SET radcheck.value=0 WHERE
radcheck.Attribute='Max-Octets' AND radcheck.value < 0;";

$query8 = "DELETE FROM radacct WHERE UserName='$user_to_archive';";

open TMP, ">/tmp/user_stats.query"
	or die "Could not open tmp file\n";
print TMP $query1;
print TMP $query2;
print TMP $query3;
print TMP $query4;
print TMP $query5;
print TMP $query6;
print TMP $query7;
print TMP $query8;
close TMP;

$command = "$sqlcmd -v -v -h $sql_server -u $sql_username
$sql_password $sql_database </tmp/user_stats.query" if ($sql_type eq
'mysql');
$command = "$sqlcmd  -U $sql_username -f /tmp/user_stats.query
$sql_database" if ($sql_type eq 'pg');

print `$command`;
------------------------------------------------------------------------------------------



> Date: Wed, 19 Sep 2012 21:57:48 +1000
> From: Timothy White <ti***8@gmail.com>
> Subject: Re: [GRASE-Hotspot] Reset user's "Data Usage" back to zero?
> To: GRASE Hotspot General Discussions
>         <gr***t@lists.sourceforge.net>
> Message-ID:
>         <CA***Q@mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1
>
>> Can you please let me know if you think this will break anything and
>> will you consider including something like this in Grase as I've
>> already found it very useful ?
>
> As you've already stated, it breaks the data usage tracking. I can see
> how the 4Gb limit is making it difficult, so this really is a hack
> until that is fixed.
>
> The only other hack I can see that would be better, is to "archive"
> usage earlier than we currently do. Currently we archive at the end of
> the month, for the previous months (so it can be up to 2 months before
> it gets archived). This allows you to see all the sessions for this
> current month, and also see the previous months sessions.
> I think the best solution might be (until the 4Gb issue is fixed) to
> have an "archive users usage" button for a user, that archives all
> that users sessions immediately, which will allow you to add more data
> (as archived users have their data limit reduced to reflect what has
> been archived).
>
> I'll hopefully be able to work around the 4Gb limit shortly, I think
> I'm going to have a custom module in freeradius to do the work for me.
>
> Tim

Thread