[EMAIL PROTECTED] wrote:
N White wrote:
I currently have freeradius 1.0.1 running with the dialupadmin from 1.0.2 on Debian Sarge. I was just curious if there is anyway to pull monthly total hours for all users. The user statistics isn't exactly what i need, it shows daily user statistics, so if I show a week of statistics, i get 7 entries per user(assuming the user has logged on every day). Basically I'd like to be able to see how much total time in a month users/a user are connected.
I would also like to output the sames types of data, but don't understand how to do it. Does the following suggested line by Graeme do it? If so, can you give a step by step instruction of where the line would need to be entered with an example "where clause" and any other required information?
Here's 7 days for all active users including now:
SELECT username, sum(acctsessiontime) AS time FROM radacct WHERE acctstarttime > (now() - interval '7 days') GROUP BY username;
same but for a specific realm
SELECT username, sum(acctsessiontime) AS time FROM radacct
WHERE username LIKE '[EMAIL PROTECTED]' AND acctstarttime > (now() - interval '7 days') GROUP BY username;
for a specific user
SELECT username, sum(acctsessiontime) AS time FROM radacct WHERE username = <YOUR USERNAME> GROUP BY username;
Nas usage
SELECT username, count(username) AS sessions FROM radacct WHERE nasipaddress = <nas ip address>
AND acctstarttime > (now() - interval '7 days') GROUP BY username;
I use postgresql, so your milage may vary.
We started with dialupadmin. But the site you see was written with dreamweaver. It has extra tables, server side functions, triggers etc and I'm halfway through a daemon (Which I've called the Grim Reaper) which kills active sessions which are no longer receiving ALIVE packets from the radius server. The "passive disconnect" is there in the meantime to manually remove them.SELECT username, sum(acctsessiontime) AS time FROM radacct GROUP BY username ORDER BY time;
You can add a where clause to suite whatever you need.
You can have a look at some screen caps I've done of our web interface at http://www2.omniconnect.com.au/~graeme/darius/ we use jpgraph to make the fancy graphs.
These graphs look good. Did you just alter the look of the dialupadmin interface and add to it or is it a separate interface/program?
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html