Code: Select all
#!/usr/bin/perl
use DBI;
use Mail::Mailer;
use Getopt::Long;
## need to scope all vars from confixx_main.conf
## for using use strict...
## use strict;
# allow processing of options with out '-' or '--' prefix
Getopt::Long::Configure('require_order', 'prefix_pattern=--|-||', 'ignore_case');
my @opts = (
'debug',
'lock', ## lock user if he is over =)
'traffik-delta=i', ## deltas are (max - cur) allowed
'disk-delta=i', ## (measured by kB)
);
my %args = ();
unless (&GetOptions(%args, @opts)) {
print STDERR "Incorrect usage...n";
exit 1;
}
my ($sql, $dbh, $sth, @row);
my $space_delta = $args{'disk-delta'} || 0;
my $traffik_delta = $args{'traffik-delta'} || 0;
my $lock = $args{'lock'} || 0;
my $debug = $args{'debug'} || 0;
my ($currentMonth, $currentYear) = (localtime)[4,5];
$currentMonth = $currentMonth + 1;
$currentYear = $currentYear + 1900;
msg("Current month is '$currentMonth'nCurrent year is '$currentYear'");
# connect to Confixx DB
&loadConfFile;
$dbh = DBI->connect($db_address, $dbUser, $dbPw)
or die( "Can't connect to ConfixxDbn error is '$DBI::errstr'n");
## get list of users with storage space over (skip space unlimited users)
$sql = "SELECT kunde AS user, emailadresse AS email, (kbhomedir+kbdb+kbpop) AS storagespace, maxkb AS maxspace FROM kunden WHERE maxkb != '-1' AND (kbhomedir+kbdb+kbpop) > (maxkb - '".$space_delta."')";
$sth = $dbh->prepare($sql);
$sth->execute or die("can't select kunden : $DBI::errstr");
if( !$sth->rows ) {
msg( "MSG: NO kunden with traffik over allowed");
} else {
msg("MSG: have kunden with storage space over");
while (@row = $sth->fetchrow_array) {
msg("MSG: processed user is '$row[0]' - $row[1] - $row[2] - $row[3]");
sendSpaceNotification( $row[1], $row[2], $row[3]);
if ($lock) {
lockUser($row[0]);
}
}
}
## get list of users with traffik in current month over (skip traffik unlimited users)
$sql = "SELECT kunden.kunde AS user, kunden.emailadresse AS email, (transfer.ftp + transfer.web + transfer.email + transfer.pop) AS monthtraffik, kunden.maxtransfer AS maxtraffik FROM kunden, transfer WHERE transfer.monat = '$currentMonth' AND transfer.jahr = '$currentYear' AND kunden.maxtransfer != '-1' AND (transfer.ftp + transfer.web + transfer.email + transfer.pop) > (kunden.maxtransfer - '".$traffik_delta."')";
$sth = $dbh->prepare($sql);
$sth->execute or die("can't select kunden : $DBI::errstr");
if( !$sth->rows ) {
msg("MSG: NO kunden with traffik over allowed");
} else {
msg("MSG: have kunden with traffik over");
while (@row = $sth->fetchrow_array) {
msg("MSG: processed user is '$row[0]' - $row[1] - $row[2] - $row[3]");
sendTraffikNotification($row[1], $row[2], $row[3]);
if ($lock) {
lockUser($row[0]);
}
}
}
exit 0;
#
## Subroutines
#
sub sendSpaceNotification {
my ($email, $space, $maxspace) = @_;
msg("MSG: send space notification to '$email' - '$space' - '$maxspace'");
my $text = "Hello!n
Your used storage space is more then allowed.n
Used storage space is '$space' kbn
Max storage space is '$maxspace' kbn
Allowed delta is '".$space_delta."' kbn
";
sendNotification($text, $email);
}
sub sendTraffikNotification {
my ($email, $traffik, $maxtraffik) = @_;
msg("MSG: send traffik notification to '$email' - '$traffik' - '$maxtraffik'");
my $text = "Hello!n
Your used traffik is more then allowed.n
Used traffik is '$traffik' kbn
Max traffik is '$maxtraffik' kbn
Allowed delta is '".$traffik_delta."' kbn
";
sendNotification($text, $email);
}
sub sendNotification {
my ($body, $to_address) = @_;
msg("MSG: send notification with '$body' - '$to_address'");
my $from_address = "root@localhost";
my $subject = "You is over =)";
my $mailer = Mail::Mailer->new("sendmail");
$mailer->open({ From => $from_address,
To => $to_address,
Subject => $subject,
}) or die "Can't open: $!n";
print $mailer $body;
$mailer->close( ) or die('Cant close');
}
sub lockUser {
my ($user) = @_;
msg("lock user '$user'");
$dbh->do("UPDATE kunden SET gesperrt = '1' WHERE kunde = '$user'");
}
sub msg {
my ($text) = @_;
print STDOUT $text."n" if $debug;
}
sub loadConfFile{
my ($file, $base);
if(-T "/root/confixx/confixx_main.conf"){
$file = "/root/confixx/confixx_main.conf";
}
else{
$0 = $^X unless ($^X =~ m%(^|[/\])(perl)|(perl.exe)$%i);
($base) = $0 =~ m%^(.*)[/\]%;
$base ||= ".";
$file = "$base/confixx_main.conf";
unless(-T $file){
die("Couldn't find confixx_main.conf");
}
}
do $file;
}
Verstehe ich es richtig, den gesamten Code den du jetzt gepostet hast 1:1 in den standart crontab vom System eintragen ?slex wrote:du kanst diese utility im crontab ergänzen...
natürlich nicht. Ist ein Perl-Script. DAS wird durch cron aufgerufenMarkusCGN wrote:Verstehe ich es richtig, den gesamten Code den du jetzt gepostet hast 1:1 in den standart crontab vom System eintragen ?
Code: Select all
* 8 * * * /root/statusmail.pl --login=<MYSQLLOGIN_TO_CONFIXX_DB> --password=<MYSQLPASS_TO_CONFIXX_DB> --db=<NAME_VON_CONFIXX_DB>Liegt vielleicht doch ein BUG vor oder hab ich da was falsch gemacht ?Ausgabe des Befehls /root/statusmail.pl --login= --password= --db= ..
/bin/sh: -c: line 0: syntax error near unexpected token `newline'
/bin/sh: -c: line 0: `/root/statusmail.pl --login=<MYSQLLOGIN_TO_CONFIXX_DB> --password=<MYSQLPASS_TO_CONFIXX_DB> --db=<NAME_VON_CONFIXX_DB>'
Also entweder stehe ich hier auf die Leitung oder ich überseh hier irgendwas.DBI connect('confixx:localhost;mysql_socket=/var/lib/mysql/mysql.sock','root',...) failed: Access denied for user: 'root@localhost' (Using password: YES) at /root/statusmail.pl line 49
Can't connect to Confixx DB
Code: Select all
# connect to Confixx DB
my $dbAddress = 'DBI:mysql:'.$db.':localhost;mysql_socket=/var/lib/mysql/mysql.sock';
unless ($dbh = DBI->connect($dbAddress, $login, $password)) {
print STDERR "Can't connect to Confixx DBn";
exit 2;
}Confixx hat eine Konfigurationsdatei - wenn Ihr dieses Includet, steht Euer Passwort nicht im Klartext im halben System rum und Ihr habt alles wunderprächtig verfügbar.slex wrote:statusmail.pl --login=<MYSQLLOGIN_TO_CONFIXX_DB>
--password=<MYSQLPASS_TO_CONFIXX_DB> --db=<NAME_VON_CONFIXX_DB> --host=<HOST_VON_MYSQL> --port=<PORT_VON_MYSQL>
Code: Select all
&loadConfFile;
use DBI;
$dbh = DBI->connect($db_address, $dbUser, $dbPw)
or die( <ext('db_connect', '#2001', "$DBI::errstr") );
sub loadConfFile{
my ($file, $base);
if(-T "/root/confixx/confixx_main.conf"){
$file = "/root/confixx/confixx_main.conf";
}
else{
$0 = $^X unless ($^X =~ m%(^|[/\])(perl)|(perl.exe)$%i);
($base) = $0 =~ m%^(.*)[/\]%;
$base ||= ".";
$file = "$base/confixx_main.conf";
unless(-T $file){
die("Couldn't find confixx_main.conf");
}
}
do $file;
}Ich hab den Beitrag nur überflogen und das was Du da geschrieben hast, setzt anscheinend auch wieder Abstraktion voraus ...antondollmaier wrote:@flo: danke, dass du meinen Beitrag bestätigt hast ;)