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/itservicedesk<http://www.city.ac.uk/itservicedesk>
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
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.
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. <mcn4@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, <ithelp@le.ac.uk>
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.
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-VENDOR Leicester 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 = "%t\t%{Client-IP-Address}\t%{UoL-Log-Client-IP}\tACCT Update\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}" # UoL-Log-Packet-Type is set by the detail reader (radrelay) config, which is a copy of the Packet-Type for # Auth packets (Access-Accept or Access-Reject are what we care about). If Acct-Status-Type is not set, then # we check to see if this attribute is present instead, and log as an Auth packet. This should only happen on # radman, not on the main radius servers. Auth-Access-Accept = "%{UoL-Log-Packet-Date}\t%{Client-IP-Address}\t%{UoL-Log-Client-IP}\tAUTH Accept\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%{Operator-Name}" Auth-Access-Reject = "%{UoL-Log-Packet-Date}\t%{Client-IP-Address}\t%{UoL-Log-Client-IP}\tAUTH Reject\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%{Operator-Name}" } This is also called on the main radius servers, so each system has its own logs in /srv/log/radius/radius-${hostname}-%D.log by date. The web interface and cron reports all just pull from these linelogs. 95% of any looking at the logs happens on the central server, and if more detail is needed then that tells us directly which RADIUS server to go to to get the full detail logs. 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 and reading files is fast. Now that this relays over, I am thinking about putting SQL on the backend, and it doesn't matter if it goes away as it all queues up in relay-detail files on the live servers. Note that it may not be good for anyone's sanity to actually try and copy the above method... Cheers, Matthew -- Matthew Newton, Ph.D. <mcn4@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, <ithelp@le.ac.uk>
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.
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. <mcn4@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, <ithelp@le.ac.uk>
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.
Many thanks for the responses, think SQL is the way forward and then query the database. Cheers Paulo -----Original Message----- From: Matthew Newton [mailto:mcn4@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. <mcn4@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, <ithelp@le.ac.uk> - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (5)
-
Alan DeKok -
Leal, Paulo -
Matthew Newton -
Michael Schwartzkopff -
Phil Mayers