[RADIATOR] Account log to MySQL

[Michael]

If you have a lot of different devices, and lots of auth activity, I 
would suggest setting up your authlog sql table to have a unique index 
for user/type/reason, and an sql query that inserts, but if the same 
error already exists, just increment a count column and the timestamp.  
This way, if a device that is rejected, and tries to connect 12 million 
times cause it's a stupid Dlink router, you don't end up with 12 million 
rows in your sql table.

Only applies for larger setups i guess.


On 03/02/15 04:39 PM, Chad Roseburg wrote:
> Goal:
> Capture successful logins as well as failures for stats purposes.
>
> I am setting up logging to a local MySQL instance. Here's what I've done:
>
> * Following instructions in the 'mysqlcreate.sql' file, I created the 
> radius table and user(s).
> * Created the Mysql tables using the provided 'mysqlCreate.sql' in 
> goodies.
> * Added the following stanza to my Handler just below the SIP Authby 
> stanza:
>
> ---------- conf -------------
> <Handler>
> <Handler>
>        <AuthBy SIP2>
>                 Port   6001
>                 Host siphost.com <http://siphost.com>
>                 Delimiter |
>                 LoginUserID sipuser
>                 LoginPassword supersecret
>                 LocationCode Radiator
>                  SendChecksum no
>                  VerifyChecksum no
>                 NoDefault
>                 EAPType GTC
>         </AuthBy>
>         <AuthLog SQL>
>                 DBSource  dbi:mysql:radius:localhost
>                 DBUsername      radius
>                 DBAuth          secrets
>                 LogSuccess
>                 SuccessQuery    insert into RADAUTHLOG (TIME_STAMP, 
> USERNAME, TYPE, REASON) values (%t, '%n', 1)
>                 LogFailure
>                 FailureQuery    insert into RADAUTHLOG (TIME_STAMP, 
> USERNAME, TYPE, REASON) values (%t, '%n', 0, %1)
>         </AuthLog>
> </Handler>
> ------------------------------ /conf -------------------------------
>
> I'm not seeing anything with:
> SELECT * FROM RADAUTHLOG;
>
> Is it just a quiet day or am I missing something?
>
> Last question is: does USERNAME refer to the client?
>
> Thank you!
>
> -- 
> Chad Roseburg
> Automation Dept.
> North Central Regional Library
>
>
> _______________________________________________
> radiator mailing list
> radiator at open.com.au
> http://www.open.com.au/mailman/listinfo/radiator

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.open.com.au/pipermail/radiator/attachments/20150203/0d38a738/attachment.html