| /alcasar.sh |
|---|
| 1726,32 → 1726,27 |
| 30 3 * * * root /usr/sbin/urpmi --auto-update --auto 2>&1 |
| EOF |
| # mise à jour des stats de connexion (accounting). Scripts provenant de "dialupadmin" (rpm freeradius-web) (cf. wiki.freeradius.org/Dialup_admin). |
| # on écrase le crontab d'origine installé par le RPM "freeradius-web" (bug remonté à qa.mandriva.com : 46739). |
| # 'tot_stats' (tout les jours à 01h01) : aggrégat des connexions journalières par usager (renseigne la table 'totacct') |
| # 'monthly_tot_stat' (tous les jours à 01h05) : aggrégat des connexions mensuelles par usager (renseigne la table 'mtotacct') |
| # 'truncate_raddact' (tous les 1er du mois à 01h10) : supprime les entrées journalisées plus vieilles que '$back_days' jours (défini ci-après) |
| # 'clean_radacct' (tous les 1er du mois à 01h15) : ferme les session ouvertes de plus de '$back_days' jours (défini ci-après) |
| $SED "s?^\$back_days.*?\$back_days = 365;?g" /usr/bin/truncate_radacct |
| $SED "s?^\$back_days.*?\$back_days = 30;?g" /usr/bin/clean_radacct |
| rm -f /etc/cron.daily/freeradius-web |
| rm -f /etc/cron.monthly/freeradius-web |
| # Connection stats update (accounting). These Perl scripts are from "dialup_admin" (cf. wiki.freeradius.org/Dialup_admin). |
| # 'alcasar-tot_stats' (everyday at 01h01 pm) : aggregating the daily connections of users (write in the table 'totacct') |
| # 'alcasar-monthly_tot_stat' (everyday at 01h05 pm) : aggregating the monthly connections of users (write in table 'mtotacct') |
| # 'alcasar-truncate_raddact' (every month, the first at 01h10 pm) : removing the log sessions of users older than 365 days |
| # 'alcasar-clean_radacct' (every month, the first at 01h15 pm) : closing the sessions openned for more than 30 days |
| cat << EOF > /etc/cron.d/freeradius-web |
| 1 1 * * * root /usr/bin/tot_stats > /dev/null 2>&1 |
| 5 1 * * * root /usr/bin/monthly_tot_stats > /dev/null 2>&1 |
| 10 1 1 * * root /usr/bin/truncate_radacct > /dev/null 2>&1 |
| 15 1 1 * * root /usr/bin/clean_radacct > /dev/null 2>&1 |
| 1 1 * * * root $DIR_DEST_BIN/alcasar-tot_stats > /dev/null 2>&1 |
| 5 1 * * * root $DIR_DEST_BIN/alcasar-monthly_tot_stats > /dev/null 2>&1 |
| 10 1 1 * * root $DIR_DEST_BIN/alcasar-truncate_radacct > /dev/null 2>&1 |
| 15 1 1 * * root $DIR_DEST_BIN/alcasar-clean_radacct > /dev/null 2>&1 |
| EOF |
| cat << EOF > /etc/cron.d/alcasar-watchdog |
| # activation du "chien de garde" (watchdog) toutes les 3' |
| */3 * * * * root $DIR_DEST_BIN/alcasar-watchdog.sh > /dev/null 2>&1 |
| EOF |
| # activation du "chien de garde des services" (watchdog) toutes les 18' |
| # Enabling the watchdog every 18' |
| cat << EOF > /etc/cron.d/alcasar-daemon-watchdog |
| # activation du "chien de garde" (daemon-watchdog) toutes les 18' |
| */18 * * * * root $DIR_DEST_BIN/alcasar-daemon.sh > /dev/null 2>&1 |
| EOF |
| # suppression des crons usagers |
| # removing the users crons |
| rm -f /var/spool/cron/* |
| } # End cron |
| /scripts/alcasar-clean_radacct |
|---|
| 0,0 → 1,52 |
| #!/usr/bin/perl |
| # |
| # Clean stale open sessions from the radacct table. |
| # we only clean up sessions which are older than $back_days |
| # Works with mysql and postgresql |
| # |
| use POSIX; |
| use File::Temp qw(tempfile tempdir); |
| $conf=shift||'/etc/freeradius-web/admin.conf'; |
| $back_days = 30; |
| open CONF, "<$conf" |
| or die "Could not open configuration file\n"; |
| while(<CONF>){ |
| chomp; |
| ($key,$val)=(split /:\s*/,$_); |
| $sql_type = $val if ($key eq 'sql_type'); |
| $sql_server = $val if ($key eq 'sql_server'); |
| $sql_username = $val if ($key eq 'sql_username'); |
| $sql_password = $val if ($key eq 'sql_password'); |
| $sql_database = $val if ($key eq 'sql_database'); |
| $sql_accounting_table = $val if ($key eq 'sql_accounting_table'); |
| $sqlcmd = $val if ($key eq 'sql_command'); |
| } |
| close CONF; |
| die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq ''); |
| die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd); |
| if ($sql_type eq 'mysql'){ |
| $sql_password = (!$sql_password) ? '' : "-p$sql_password"; |
| } |
| $sql_password =~ s/(\W)/\\$1/g; |
| ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; |
| $date = POSIX::strftime("%Y-%m-%d %T",$sec,$min,$hour,($mday - $back_days),$mon,$year,$wday,$yday,$isdst); |
| print "$date\n"; |
| if (POSIX::strftime("%Y-%m-%d %T",localtime) eq $date){ |
| die "Could not set correct back date.\n"; |
| } |
| $query = "DELETE FROM $sql_accounting_table WHERE AcctStopTime IS NULL AND AcctStartTime < '$date';"; |
| print "$query\n"; |
| my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n"; |
| print $fh $query; |
| close $fh; |
| $command = "$sqlcmd -h$sql_server -u$sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql'); |
| $command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg'); |
| $command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay'); |
| `$command`; |
| Property changes: |
| Added: svn:executable |
| +* |
| \ No newline at end of property |
| /scripts/alcasar-monthly_tot_stats |
|---|
| 0,0 → 1,64 |
| #!/usr/bin/perl |
| use POSIX; |
| use File::Temp qw(tempfile tempdir); |
| # Log in the mtotacct table aggregated accounting information for |
| # each user spaning in one month period. |
| # If the current month has not ended it will log information up to |
| # the current month day |
| # Works only with mysql and postgresql |
| # |
| $conf=shift||'/etc/freeradius-web/admin.conf'; |
| open CONF, "<$conf" |
| or die "Could not open configuration file\n"; |
| while(<CONF>){ |
| chomp; |
| ($key,$val)=(split /:\s*/,$_); |
| $sql_type = $val if ($key eq 'sql_type'); |
| $sql_server = $val if ($key eq 'sql_server'); |
| $sql_username = $val if ($key eq 'sql_username'); |
| $sql_password = $val if ($key eq 'sql_password'); |
| $sql_database = $val if ($key eq 'sql_database'); |
| $sql_accounting_table = $val if ($key eq 'sql_accounting_table'); |
| $sqlcmd = $val if ($key eq 'sql_command'); |
| } |
| close CONF; |
| die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq ''); |
| die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd); |
| if ($sql_type eq 'mysql'){ |
| $sql_password = (!$sql_password) ? '' : "-p$sql_password"; |
| } |
| $sql_password =~ s/(\W)/\\$1/g; |
| ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; |
| if ($mday == 1){ |
| $mon--; |
| } |
| $date_start = POSIX::strftime("%Y-%m-%d",0,0,0,1,$mon,$year,$wday,$yday,$isdst); |
| $date_end = POSIX::strftime("%Y-%m-%d",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst); |
| $query1 = "DELETE FROM mtotacct WHERE AcctDate = '$date_start';"; |
| $query2 = "INSERT INTO mtotacct (UserName,AcctDate,ConnNum,ConnTotDuration, |
| ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress) |
| SELECT UserName,'$date_start',SUM(ConnNum),SUM(ConnTotDuration), |
| MAX(ConnMaxDuration),MIN(ConnMinDuration),SUM(InputOctets), |
| SUM(OutputOctets),NASIPAddress FROM totacct |
| WHERE AcctDate >= '$date_start' AND |
| AcctDate <= '$date_end' GROUP BY UserName,NASIPAddress;"; |
| print "$query1\n"; |
| print "$query2\n"; |
| my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n"; |
| print $fh "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle'); |
| print $fh $query1; |
| print $fh $query2; |
| close $fh; |
| $command = "$sqlcmd -h $sql_server -u $sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql'); |
| $command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg'); |
| $command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle'); |
| $command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay'); |
| `$command`; |
| Property changes: |
| Added: svn:executable |
| +* |
| \ No newline at end of property |
| /scripts/alcasar-tot_stats |
|---|
| 0,0 → 1,61 |
| #!/usr/bin/perl |
| use POSIX; |
| use File::Temp qw(tempfile tempdir); |
| # Log in the totacct table aggregated daily accounting information for |
| # each user. |
| # We keep a row per user for each day. |
| # Works with mysql and postgresql |
| # |
| $conf=shift||'/etc/freeradius-web/admin.conf'; |
| open CONF, "<$conf" |
| or die "Could not open configuration file\n"; |
| while(<CONF>){ |
| chomp; |
| ($key,$val)=(split /:\s*/,$_); |
| $sql_type = $val if ($key eq 'sql_type'); |
| $sql_server = $val if ($key eq 'sql_server'); |
| $sql_username = $val if ($key eq 'sql_username'); |
| $sql_password = $val if ($key eq 'sql_password'); |
| $sql_database = $val if ($key eq 'sql_database'); |
| $sql_accounting_table = $val if ($key eq 'sql_accounting_table'); |
| $sqlcmd = $val if ($key eq 'sql_command'); |
| } |
| close CONF; |
| die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq ''); |
| die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd); |
| if ($sql_type eq 'mysql'){ |
| $sql_password = (!$sql_password) ? '' : "-p$sql_password"; |
| } |
| $sql_password =~ s/(\W)/\\$1/g; |
| ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; |
| $date_start = POSIX::strftime("%Y-%m-%d %T",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst); |
| $date_small_start = POSIX::strftime("%Y-%m-%d",0,0,0,($mday - 1),$mon,$year,$wday,$yday,$isdst); |
| $date_end = POSIX::strftime("%Y-%m-%d %T",0,0,0,$mday,$mon,$year,$wday,$yday,$isdst); |
| $query1 = "DELETE FROM totacct WHERE AcctDate = '$date_start';"; |
| $query2 = "INSERT INTO totacct (UserName,AcctDate,ConnNum,ConnTotDuration, |
| ConnMaxDuration,ConnMinDuration,InputOctets,OutputOctets,NASIPAddress) |
| SELECT UserName,'$date_small_start',COUNT(*),SUM(AcctSessionTime), |
| MAX(AcctSessionTime),MIN(AcctSessionTime),SUM(AcctInputOctets), |
| SUM(AcctOutputOctets),NASIPAddress FROM radacct |
| WHERE AcctStopTime >= '$date_start' AND |
| AcctStopTime < '$date_end' GROUP BY UserName,NASIPAddress;"; |
| print "$query1\n"; |
| print "$query2\n"; |
| my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n"; |
| print $fh "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle'); |
| print $fh $query1; |
| print $fh $query2; |
| close $fh; |
| $command = "$sqlcmd -h $sql_server -u $sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql'); |
| $command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg'); |
| $command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle'); |
| $command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay'); |
| `$command`; |
| Property changes: |
| Added: svn:executable |
| +* |
| \ No newline at end of property |
| /scripts/alcasar-truncate_radacct |
|---|
| 0,0 → 1,55 |
| #!/usr/bin/perl |
| # |
| # Delete sessions from the radacct table which are older than $back_days |
| # Works with mysql and postgresql |
| # |
| use POSIX; |
| use File::Temp qw(tempfile tempdir); |
| $conf=shift||'/etc/freeradius-web/admin.conf'; |
| $back_days = 365; |
| open CONF, "<$conf" |
| or die "Could not open configuration file\n"; |
| while(<CONF>){ |
| chomp; |
| ($key,$val)=(split /:\s*/,$_); |
| $sql_type = $val if ($key eq 'sql_type'); |
| $sql_server = $val if ($key eq 'sql_server'); |
| $sql_username = $val if ($key eq 'sql_username'); |
| $sql_password = $val if ($key eq 'sql_password'); |
| $sql_database = $val if ($key eq 'sql_database'); |
| $sql_accounting_table = $val if ($key eq 'sql_accounting_table'); |
| $sqlcmd = $val if ($key eq 'sql_command'); |
| } |
| close CONF; |
| die "sql_command directive is not set in admin.conf\n" if ($sqlcmd eq ''); |
| die "sql command '$sqlcmd' not found or does not seem to be executable\n" if (! -x $sqlcmd); |
| if ($sql_type eq 'mysql'){ |
| $sql_password = (!$sql_password) ? '' : "-p$sql_password"; |
| } |
| $sql_password =~ s/(\W)/\\$1/g; |
| ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; |
| $date = POSIX::strftime("%Y-%m-%d %T",$sec,$min,$hour,($mday - $back_days),$mon,$year,$wday,$yday,$isdst); |
| print "$date\n"; |
| if (POSIX::strftime("%Y-%m-%d %T",localtime) eq $date){ |
| die "Could not set correct back date.\n"; |
| } |
| $query = ""; |
| $query = "LOCK TABLES $sql_accounting_table WRITE;" if ($sql_type eq 'mysql'); |
| $query .= "DELETE FROM $sql_accounting_table WHERE AcctStopTime < '$date' AND AcctStopTime IS NOT NULL ;"; |
| $query .= "UNLOCK TABLES;" if ($sql_type eq 'mysql'); |
| print "$query\n"; |
| my ($fh, $tmp_filename) = tempfile() or die "Could not open tmp file\n"; |
| print $fh "ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';\n" if ($sql_type eq 'oracle'); |
| print $fh $query; |
| close $fh; |
| $command = "$sqlcmd -h$sql_server -u$sql_username $sql_password $sql_database < $tmp_filename" if ($sql_type eq 'mysql'); |
| $command = "$sqlcmd -U $sql_username -f $tmp_filename $sql_database" if ($sql_type eq 'pg'); |
| $command = "$sqlcmd $sql_username/$pass" . "@" . "$sql_database <$tmpfile.$server" if ($sql_type eq 'oracle'); |
| $command = "$sqlcmd '$sql_server' '$sql_port' '' '$sql_username' '$sql_password' < $tmp_filename" if ($sql_type eq 'sqlrelay'); |
| `$command`; |
| Property changes: |
| Added: svn:executable |
| +* |
| \ No newline at end of property |