[unisog] Parsing Radius Logs

Michael Holstein michael.holstein at csuohio.edu
Thu Apr 14 16:59:26 GMT 2005

I use FreeRADIUS into MySQL and this is the relevent part of my search 
(this is perl, and this is just the relevent subs). The DB schema for 
this is part of the FreeRADIUS distro.

I actually search by MAC since other parts of my script grab info from 
Ciscoworks and the DHCPD logs (also in MySQL), but you get the idea. 
What information you want to start with will determine what the best SQL 
query is to get 'rest' of the info.

Sorting by AcctStopTime backwards gives you the most recent user. 
Obviously you can specify other parameters in your SQL query (like times).


Michael Holstein CISSP GCIA
Cleveland State University

# find a wireless user
sub findwireless {
         $_ =~ s/\s+$//;
         $query = $dbh_log->prepare("select dhcplogs.msg from dhcplogs 
where dhcplogs.msg like '%$_%' order by date,time desc");
         $query->execute || die "Unable to find IP in DHCP logs: 
         @wirelessmac = $query->fetchrow_array;
         @wirelessinfo = split '\ ', @wirelessmac[0];
         @mac = split '\:', @wirelessinfo[8];
         $radiusmac = "@mac[0]@mac[1]\. at mac[2]@mac[3]\. at mac[4]@mac[5]";
         $query = $dbh_radius->prepare("select 
radacct.UserName,radacct.AcctStartTime,radacct.AcctStopTime from radacct 
where radacct.CallingStationID='$radiusmac' order by AcctStopTime desc, 
AcctStartTime desc");
         $query->execute || die "Unable to find MAC in radius logs: 
         @radacct = $query->fetchrow_array;
         return @radacct;

David Bowie wrote:
> I'd like to tap the collective wisdom regarding RADIUS while folks are 
> still thinking about implementation.
> Are there any free/good tools for parsing RADIUS logs with the goal of  
> identifying a specific user when provided an IP, date and time?  
> Traditionally, this has been a manual effort.
> --djb
> _______________________________________________
> unisog mailing list
> unisog at lists.sans.org
> http://www.dshield.org/mailman/listinfo/unisog

More information about the unisog mailing list