On Mon, 2003-08-18 at 05:30, Adam Carmichael wrote:
> Hi All!
>
> I'm currently running FreeRADIUS 0.9.0 on several *BSD boxes with
> MySQL4 for logging accounting and retrieving authentication
> information. I am interested in knowing how to log authentication
> attempts and even possibly why an attempt failled.
I'm using a simple script that reads radius.log and put that in a mysql
table that can be accessed by our helpdesk by using a simple
php-interface to help people with their dialin problems. Maybe you can
do something with it.
Succes, Chris
The db struct of radproblems is:
mysql> describe radproblems;
+------------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+--------------+------+-----+---------------------+----------------+
| RadProblemId | bigint(21) | | PRI | NULL |
auto_increment |
| UserName | varchar(255) | | MUL |
| |
| Password | varchar(255) | | |
| |
| AuthTime | datetime | | | 0000-00-00 00:00:00
| |
| Realm | varchar(64) | YES | |
| |
| NASIPAddress | varchar(15) | | |
| |
| CalledStationId | varchar(30) | | |
| |
| CallingStationId | varchar(30) | | MUL |
| |
| TerminateCause | varchar(64) | | |
| |
+------------------+--------------+------+-----+---------------------+----------------+
9 rows in set (0.00 sec)
The import script:
cat /usr/local/bin/parse-radiuslog.sh
#!/bin/sh
# Input format:
# Mon Mar 10 11:07:06 2003 : Auth: Login incorrect (rlm_ldap: Bind as
user failed): [user/password] (from client nas port 16578 cli 012345678)
INFILE="/var/log/freeradius/radius.log"
TMPFILE="/var/log/freeradius/radius.tmp"
ADDTOFILE="/var/log/freeradius/radius.parsed"
SQLTMPFILE="/var/log/freeradius/radius.tmp.sql"
if [ -f $TMPFILE ]
then
rm $TMPFILE
fi
if [ -f $SQLTMPFILE ]
then
rm $SQLTMPFILE
fi
mv $INFILE $TMPFILE
check=`cat "${TMPFILE}" | grep 'Auth: Login incorrect'`
if [ -z "$check" ]
then echo " "; else
cat "${TMPFILE}" | grep 'Auth: Login incorrect' | while read LINE; do
P1=`echo ${LINE} | sed -e 's/^.*\[\([^/]*\).*$/\1/' -e s/\'/#/g
-e s/\"/#/g`
P2=`echo ${LINE} | sed -e 's/^.*\(\[.*\]\).*$/\1/' -e
's/^.*\/\(.*\)]$/\1/' -e s/\'/#/g -e s/\"/#/g`
# P1=`echo ${LINE} | sed 's/^.*\(\[.*\]\).*$/\1/'`
P3=`echo ${LINE} | awk '{print $5 "-" $2 "-" $3 " " $4}' | sed
-e 's/Jan/1/' -e 's/Feb/2/' -e 's/Mar/3/' -e 's/Apr/4/' -e 's/May/5/' -e
's/Jun/6/' -e 's/Jul/7/' -e 's/Aug/8/' -e 's/Sep/10/' -e 's/Oct/10/' -e
's/Nov/11/' -e 's/Dec/12/'`
P4=`echo ${LINE} | grep ' cli ' | sed 's/^.*cli
\b\([0-9]*\).*$/\1/'`
P5=`echo ${LINE} | grep 'rlm_ldap:' | sed 's/^.*rlm_ldap:
\([A-Za-z0-9 ]
*\).*$/\1/'`
echo "INSERT INTO radproblems VALUES
('','${P1}','${P2}','${P3}','','','
','${P4}','${P5}');" | sed 's/\\/\\\\/' >> $SQLTMPFILE
done
mysql -hyour.mysql.host -usqluser -ppassword database < $SQLTMPFILE
fi
cat $TMPFILE >> $ADDTOFILE
> For example, if we have a customer who thinks their dialup account is
> being exploited - they can change their password, and then see if any
> authentication requests are being made. (Actually, just thinking about
> it, the user would not need to change their password, they could just
> see the times at which their logons (or attempted logons) occur).
>
> I have made some Google searches on the list already, and I saw a few
> posts in which Alan DeKok said that it is possible to do this -
> however the rest of the replies seemed to wonder away from what I had
> hoped.
>
>
>
>
> Thanks in advance
>
> Adam
>
>
> Adam Carmichael
> Network Operations Manager
> email: [EMAIL PROTECTED]
> web: http://www.no1.com.au
> icq: 2207644
> --------------------------------------------------------------------------------
> #1 Computer Services, Empowerment Through Internet Communications.
> --------------------------------------------------------------------------------
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
