RE: Reporting from logs
Many thanks for the responses, think SQL is the way forward and then query the database. Cheers Paulo -Original Message- From: Matthew Newton [mailto:m...@leicester.ac.uk] Sent: 25 September 2012 14:39 To: FreeRadius users mailing list Subject: Re: Reporting from logs Hi, On Tue, Sep 25, 2012 at 02:00:10PM +0100, Leal, Paulo wrote: Just wondering what kind of reporting you have managed to get out from the FreeRadius logs, we wanted some user friendly reports with user numbers, success and failed attempts, what realms were used and numbers, even possibly bandwidth consumed per user. Daily, weekly and monthly reports. We use hand-crafted linelog in carefully placed locations in the config so that we end up with a single line in a log file for each of auth accept, auth reject, acct start, acct update and acct stop. Each line is tab-separated and has fields in the same place so, for example, User-Name is always in column 7. We then have a nightly cron job that awk/greps stats out of this daily log file, as well as a custom web interface that lets us easily grep data out in real time. The best way to go is probably SQL, but this works well and is plenty fast enough for us without having the added complexities of a database running as well. Having said that, all logs are now rad-relayed to a central RADIUS server for the web site, so I could safely get a non-resilient database running there for querying. Just haven't had time or the need yet. Cheers, Matthew -- Matthew Newton, Ph.D. m...@le.ac.uk Systems Architect (UNIX and Networks), Network Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, ith...@le.ac.uk - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reporting from logs
Hi guys, We are new to eduroam and we are using FreeRadius for authentication and connection to national proxies. Just wondering what kind of reporting you have managed to get out from the FreeRadius logs, we wanted some user friendly reports with user numbers, success and failed attempts, what realms were used and numbers, even possibly bandwidth consumed per user. Daily, weekly and monthly reports. Would be grateful to find out what everyone is using to achieve this and if someone has some sample reports would be great. Cheers Paulo Paulo Leal Senior Network Analyst Enterprise Technology Services City University London Tel: 020 7040 8186 Got an IT problem? - please visit: www.city.ac.uk/itservicedeskhttp://www.city.ac.uk/itservicedesk - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
Hi guys, We are new to eduroam and we are using FreeRadius for authentication and connection to national proxies. Just wondering what kind of reporting you have managed to get out from the FreeRadius logs, we wanted some user friendly reports with user numbers, success and failed attempts, what realms were used and numbers, even possibly bandwidth consumed per user. Daily, weekly and monthly reports. Would be grateful to find out what everyone is using to achieve this and if someone has some sample reports would be great. Cheers Paulo Hi, you could use the status server to get the interesting figures: http://wiki.freeradius.org/config/Status With a simple script/cronjob you can feed these data into a RRD and generate nice graphs. Greetings, -- Dr. Michael Schwartzkopff Guardinistr. 63 81375 München Tel: (0163) 172 50 98 Fax: (089) 620 304 13 signature.asc Description: This is a digitally signed message part. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
On 25/09/12 14:00, Leal, Paulo wrote: Hi guys, We are new to eduroam and we are using FreeRadius for authentication and connection to national proxies. Just wondering what kind of reporting you have managed to get out from the FreeRadius logs, we wanted some user friendly reports with user numbers, success and failed attempts, what realms were used and numbers, even possibly bandwidth consumed per user. Daily, weekly and monthly reports. Would be grateful to find out what everyone is using to achieve this and if someone has some sample reports would be great. We use post-auth SQL logging and accounting to SQL, and I generate the reports from an SQL job. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
Hi, On Tue, Sep 25, 2012 at 02:00:10PM +0100, Leal, Paulo wrote: Just wondering what kind of reporting you have managed to get out from the FreeRadius logs, we wanted some user friendly reports with user numbers, success and failed attempts, what realms were used and numbers, even possibly bandwidth consumed per user. Daily, weekly and monthly reports. We use hand-crafted linelog in carefully placed locations in the config so that we end up with a single line in a log file for each of auth accept, auth reject, acct start, acct update and acct stop. Each line is tab-separated and has fields in the same place so, for example, User-Name is always in column 7. We then have a nightly cron job that awk/greps stats out of this daily log file, as well as a custom web interface that lets us easily grep data out in real time. The best way to go is probably SQL, but this works well and is plenty fast enough for us without having the added complexities of a database running as well. Having said that, all logs are now rad-relayed to a central RADIUS server for the web site, so I could safely get a non-resilient database running there for querying. Just haven't had time or the need yet. Cheers, Matthew -- Matthew Newton, Ph.D. m...@le.ac.uk Systems Architect (UNIX and Networks), Network Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, ith...@le.ac.uk - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
On 25/09/12 14:39, Matthew Newton wrote: The best way to go is probably SQL, but this works well and is plenty fast enough for us without having the added complexities of a database running as well. Having said that, all logs are now rad-relayed to a central RADIUS server for the web site, so I How do you relay post-auth? That's one of the main reasons we still use rlm_sql_log, as opposed to the built in detail listener. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
Hi,
On Tue, Sep 25, 2012 at 03:00:26PM +0100, Phil Mayers wrote:
On 25/09/12 14:39, Matthew Newton wrote:
How do you relay post-auth? That's one of the main reasons we still
use rlm_sql_log, as opposed to the built in detail listener.
Pure total hackery.
linelog can include '\n' in the output so can simlulate the detail
module for given attributes. The relayed auth packets are sent on
the wire as acct packets...
on the main servers, in default (and inner-tunnel, except post-auth-type reject
doesn't work there):
post-auth {
...
relay_detail_auth
...
Post-Auth-Type REJECT {
...
relay_detail_auth
...
}
}
in modules/linelog:
linelog relay_detail_auth {
filename = ${radacctdir}/relay-detail
## this is read by copy-acct-to-home-server, and also written to by
## detail for accounting packets as usual
reference = %{%{reply:Packet-Type}:-format}
format = %t\n\tPacket-Type = %{reply:Packet-Type}\n\tUser-Name =
\%{User-Name}\\n\tNAS-IP-Address = %{NAS-IP-Address}\n\tCalling-Station-Id =
\%{Calling-Station-Id}\\n\tCalled-Station-Id =
\%{Called-Station-ID}\\n\tNAS-Identifier = \%{NAS-Identifier}\\n\tRealm =
%{request:Realm}\n\tAirespace-Wlan-Id = %{Airespace-Wlan-Id}\n\tTunnel-Type:0 =
%{Tunnel-Type}\n\tTunnel-Medium-Type:0 =
%{Tunnel-Medium-Type}\n\tTunnel-Private-Group-Id:0 =
\%{Tunnel-Private-Group-Id}\\n\tHuntgroup-Name =
%{Huntgroup-Name}\n\tUoL-Log-Packet-Date = \%t\\n\tUoL-Log-Client-IP =
\%{Client-IP-Address}\\n\tUoL-Log-Realm = %{Realm}\n\tOperator-Name =
\%{Operator-Name}\\n
Access-Accept = %t\n\tPacket-Type = %{reply:Packet-Type}\n\tUser-Name
= \%{User-Name}\\n\tNAS-IP-Address = %{NAS-IP-Address}\n\tCalling-Station-Id
= \%{Calling-Station-Id}\\n\tCalled-Station-Id =
\%{Called-Station-ID}\\n\tNAS-Identifier = \%{NAS-Identifier}\\n\tRealm =
%{request:Realm}\n\tAirespace-Wlan-Id = %{Airespace-Wlan-Id}\n\tTunnel-Type:0 =
%{Tunnel-Type}\n\tTunnel-Medium-Type:0 =
%{Tunnel-Medium-Type}\n\tTunnel-Private-Group-Id:0 =
\%{Tunnel-Private-Group-Id}\\n\tHuntgroup-Name =
%{Huntgroup-Name}\n\tUoL-Log-Packet-Date = \%t\\n\tUoL-Log-Client-IP =
\%{Client-IP-Address}\\n\tUoL-Log-Packet-Type =
Auth-Access-Accept\n\tUoL-Log-Realm = %{Realm}\n\tOperator-Name =
\%{Operator-Name}\\n
Access-Reject = %t\n\tPacket-Type = %{reply:Packet-Type}\n\tUser-Name
= \%{User-Name}\\n\tNAS-IP-Address = %{NAS-IP-Address}\n\tCalling-Station-Id
= \%{Calling-Station-Id}\\n\tCalled-Station-Id =
\%{Called-Station-ID}\\n\tNAS-Identifier = \%{NAS-Identifier}\\n\tRealm =
%{request:Realm}\n\tAirespace-Wlan-Id = %{Airespace-Wlan-Id}\n\tTunnel-Type:0 =
%{Tunnel-Type}\n\tTunnel-Medium-Type:0 =
%{Tunnel-Medium-Type}\n\tTunnel-Private-Group-Id:0 =
\%{Tunnel-Private-Group-Id}\\n\tHuntgroup-Name =
%{Huntgroup-Name}\n\tUoL-Log-Packet-Date = \%t\\n\tUoL-Log-Client-IP =
\%{Client-IP-Address}\\n\tUoL-Log-Packet-Type =
Auth-Access-Reject\n\tUoL-Log-Realm = %{Realm}\n\tOperator-Name =
\%{Operator-Name}\\n
}
in the dictionary:
VENDOR Leicester 3385
BEGIN-VENDORLeicester
ATTRIBUTE UoL-Log-Packet-Type 1 integer
ATTRIBUTE UoL-Log-Packet-Date 2 string
ATTRIBUTE UoL-Log-Client-IP 3 ipaddr
ATTRIBUTE UoL-Log-Realm 4 string
ATTRIBUTE UoL-Log-Acct-Session-Id 5 string
VALUE UoL-Log-Packet-Type Auth-Access-Accept 0
VALUE UoL-Log-Packet-Type Auth-Access-Reject 1
END-VENDOR Leicester
on the central logging server:
accounting {
detail
linelog_acct
...
}
The detail log gets everything in a good enough state to find out what happened
and on which RADIUS server. Then there's another hideous linelog instantiation:
linelog linelog_acct {
filename = /srv/log/radius/radius-${hostname}-%D.log
group = radlogs
permissions = 0640
format = You should never see this log message for %{User-Name} with
packet type %{Packet-Type} (linelog_acct) %{Operator-Name}
reference = %{%{%{Acct-Status-Type}:-%{UoL-Log-Packet-Type}}:-format}
Start = %t\t%{Client-IP-Address}\t%{UoL-Log-Client-IP}\tACCT
Start\t%{Calling-Station-Id}\t%{UoL-Log-Realm}\t%{User-Name}\t%{NAS-Identifier}\t%{Called-Station-Id}\t%{Airespace-Wlan-Id}\t%{Tunnel-Private-Group-Id}\t%{Framed-IP-Address}\t%{%{Acct-Unique-Session-Id}:-%{UoL-Log-Acct-Session-Id}}\t%{Acct-Input-Octets}\t%{Acct-Output-Octets}\t%{Operator-Name}
Stop = %t\t%{Client-IP-Address}\t%{UoL-Log-Client-IP}\tACCT
Stop\t%{Calling-Station-Id}\t%{UoL-Log-Realm}\t%{User-Name}\t%{NAS-Identifier}\t%{Called-Station-Id}\t%{Airespace-Wlan-Id}\t%{Tunnel-Private-Group-Id}\t%{Framed-IP-Address}\t%{%{Acct-Unique-Session-Id}:-%{UoL-Log-Acct-Session-Id}}\t%{Acct-Input-Octets}\t%{Acct-Output-Octets}\t%{Operator-Name}
Interim-Update =
Re: Reporting from logs
On 25/09/12 15:34, Matthew Newton wrote:
Hi,
On Tue, Sep 25, 2012 at 03:00:26PM +0100, Phil Mayers wrote:
On 25/09/12 14:39, Matthew Newton wrote:
How do you relay post-auth? That's one of the main reasons we still
use rlm_sql_log, as opposed to the built in detail listener.
Pure total hackery.
linelog can include '\n' in the output so can simlulate the detail
module for given attributes. The relayed auth packets are sent on
the wire as acct packets...
You, sir, win a prize! That's simultaneously clever and vile. I'm
disappointed I didn't think of it!
My reason for avoiding SQL was because I didn't want an SQL
problem to slow down or stop the main RADIUS servers, either in
the event of database failure, or just database slowness. Writing
Absolutely. Writing to an intermediate non-blocking location is, IMO,
essential at any scale.
We started with rlm_sql_log back in the 1.1.x days. We needed to
replicate post-auth as well as accounting packets, because some of our
NASes [some switches doing mac-auth] don't generate accounting - just
re-auth ever half hour. We simulate an accounting update-or-insert on
the central SQL server using a trigger for these devices.
I almost wonder if an rlm_inject might not be generally useful; in
particular, we could generate our simulated accounting internally to the
radius servers, rather than via an SQL procedure:
modules {
inject fake_acct {
type = acct
virtual_server = blah
attrs = {
Acct-Status-Type = %{...}
}
}
}
server blah {
accounting {
# something else reads/relays the detail file
detail
}
post-auth {
if (Crappy-NAS) {
fake_acct
}
}
}
Doesn't look hard; maybe I'll take a look at it.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
On Tue, Sep 25, 2012 at 04:28:56PM +0100, Phil Mayers wrote:
On 25/09/12 15:34, Matthew Newton wrote:
linelog can include '\n' in the output so can simlulate the detail
module for given attributes. The relayed auth packets are sent on
the wire as acct packets...
You, sir, win a prize! That's simultaneously clever and vile. I'm
disappointed I didn't think of it!
Why, thank you. A colleague just read that post and said that he
hoped that he would never have to work with someone who would come
up with something like that. :)
We started with rlm_sql_log back in the 1.1.x days. We needed to
replicate post-auth as well as accounting packets, because some of
our NASes [some switches doing mac-auth] don't generate accounting -
just re-auth ever half hour. We simulate an accounting
update-or-insert on the central SQL server using a trigger for these
devices.
Which, of course, is the same as eduroam. We don't see acct from
all sites by any means (some even send acct packets without an
Acct-Status-Type attribute...)
I almost wonder if an rlm_inject might not be generally useful; in
particular, we could generate our simulated accounting internally to
the radius servers, rather than via an SQL procedure:
...
Doesn't look hard; maybe I'll take a look at it.
Agreed, that looks quite straightforward. I like it. Should make
for a much tidier config than using linelog.
I think it would also benefit from a copy_all_attrs option - it's
one thing I miss from linelog (whereas detail logs everything,
it's hard for linelog to do the same). But also with attrs={} to
add or remove others.
I've been looking at the code recently to also see if the
Post-Auth REJECT in inner-tunnel can be fixed. I can see an easy
and fairly obvious of doing it, but the right way seems to involve
the core event system, where I don't really want to go. That would
fix up the one thing that is missing from our logs (outer reject
doesn't log inner username, so it's hard to find these). I could
then stop relaying outer auths to the central log entirely, as
they're generally uninteresting.
Cheers,
Matthew
--
Matthew Newton, Ph.D. m...@le.ac.uk
Systems Architect (UNIX and Networks), Network Services,
I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom
For IT help contact helpdesk extn. 2253, ith...@le.ac.uk
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Reporting from logs
Phil Mayers wrote:
I almost wonder if an rlm_inject might not be generally useful; in
particular, we could generate our simulated accounting internally to the
radius servers, rather than via an SQL procedure:
modules {
inject fake_acct {
See raddb/sites-available/originate-coa
update acct {
...
}
Should be *relatively* easy to do. Just re-use the existing code.
Hopefully in an abstracted manner
The downside is that even in 3.x, that code is pretty hairy. I'd
really like to clean it up in a sane way.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
