daily usage report

[Nathan Ward]

> On 20/08/2018, at 12:30 AM, Alan DeKok <aland at deployingradius.com> wrote:
> 
>>> On 19/08/2018, at 9:38 PM, Imdad Hasan <imdadalikadiwala0 at gmail.com> wrote:
>>> 
>>> Actually i want to make daily usage report, but if that session is older
>>> than one or two days than i dont get proper daily data usage report. For
>>> now, i was make script that disconnect all users with pod at midnight 12.
> 
>  Why not just create a daily usage report?
> 
>  Make another table, with columns "username", "date", and "usage".  Then, once a day at midnight, loop over the "radacct" table for all open sessions.  Take the usage from radacct, and insert it into the new table.

Responding to the OP’s subsequent mis-post, I’ve achieved this by writing a DB trigger, which on INSERT or UPDATE updates another table which contains sessions divided by an interval (i.e. hour, day, whatever is appropriate).

This means that the data is live and it doesn’t require a periodic report run. I found periodic reports can be a bit of a drama if they fail for some reason.

I haven’t got it in production yet - but have it running against a mirror of production accounting (~400pps, so not huge, but not tiny). I’ll dig up the SQL (MySQL) and post it someplace if it’s useful for inspiration to others (I’m using ‘inspiration’ here to distinguish it from code you should use without understanding it.. YMMV etc. etc.)

>>> Any other way to achieve this idea without disconnecting users.
>> 
>> Sure there are plenty of ways. What is the problem re. the sessions being older than one or two days? Please be explicit about your problem and what you’re trying to achieve, and include as much detail as possible. I can guess, but, I am not exactly sure so I won’t. “Proper” is not an objective term - can you expand on that?
> 
>  My $0.02 is that when people want to do something new, they should do something *new*.
> 
>  Mangling existing tables && procedures is almost always the wrong idea.

I agree generally. I think my solution - to add a new trigger but not changing existing SQL, is a pretty good compromise. It’s not perfectly clean, but, it’s pretty close.

--
Nathan Ward