#!/usr/bin/perl							#!/usr/bin/perl
#############################################################	#############################################################
#                                                            	#                                                            
# Astrad tools  Switzernet(c)2011                            	# Astrad tools  Switzernet(c)2011                            
#                                                            	#                                                            
#############################################################	#############################################################
# my modules							# my modules
use strict;							use strict;
use warnings;							use warnings;
use POSIX;							use POSIX;
use Config::IniFiles;						use Config::IniFiles;
use Switch;							use Switch;
use DBI;							use DBI;
use Getopt::Long;						use Getopt::Long;
my $action     = '';						my $action     = '';
my $help     = 0;						my $help     = 0;
#############################################################	#############################################################
sub usage {							sub usage {
   my $message = $_[0];						   my $message = $_[0];
   if (defined $message && length $message) {			   if (defined $message && length $message) {
      $message .= "\n"						      $message .= "\n"
         unless $message =~ /\n$/;				         unless $message =~ /\n$/;
   }								   }

   my $command = $0;						   my $command = $0;
   $command =~ s#^.*/##;					   $command =~ s#^.*/##;

   print STDERR (						   print STDERR (
      $message,							      $message,
      "Usage : $command -a <ACTION>\n" .			      "Usage : $command -a <ACTION>\n" .
      "Action:\n".						      "Action:\n".
      "       sipconfig        Show configuration.\n" .		      "       sipconfig        Show configuration.\n" .
      "       location2        location in DBA server.\n" .	      "       location2        location in DBA server.\n" .
      "       location3        location in Astrad server.\n" 	      "       location3        location in Astrad server.\n" 
      "       tonotify         Accounts to notify with sendin	      "       tonotify         Accounts to notify with sendin
      "       toping           Accounts to ping with sending 	      "       toping           Accounts to ping with sending 
      "       astmultiua       Accounts with multipleua on th	      "       astmultiua       Accounts with multipleua on th
      "       dbamultiua       Accounts with multipleua on al	      "       dbamultiua       Accounts with multipleua on al
      "       account=%s       Show information of this accou	      "       account=%s       Show information of this accou
      "       checkpass=%s:%s  check password md5 (checkpass=	      "       checkpass=%s:%s  check password md5 (checkpass=
      "       \n"						      "       \n"
   );								   );

   die("\n")							   die("\n")
}								}
Getopt::Long::GetOptions(					Getopt::Long::GetOptions(
   'a=s' => \$action,						   'a=s' => \$action,
) or usage("Invalid commmand line options.");			) or usage("Invalid commmand line options.");
 								 
usage("The user name must be specified.")			usage("The user name must be specified.")
   unless defined $action;					   unless defined $action;
#############################################################	#############################################################
my $config_file = '/etc/astrad/config/switzer.conf';		my $config_file = '/etc/astrad/config/switzer.conf';
my ($User,$Pass,$master_host,$master_user,$acc,$NAS_IP,$pass)	my ($User,$Pass,$master_host,$master_user,$acc,$NAS_IP,$pass)
#############################################################	#############################################################
sub load_config {						sub load_config {
    my $conf=Config::IniFiles->new(-file => $config_file);	    my $conf=Config::IniFiles->new(-file => $config_file);
    return 0 if !defined $conf ;				    return 0 if !defined $conf ;
    $User  = $conf->val('ASTER_DB','DB_USER');			    $User  = $conf->val('ASTER_DB','DB_USER');
    $Pass  = $conf->val('ASTER_DB','DB_PASS');			    $Pass  = $conf->val('ASTER_DB','DB_PASS');
    $master_host = $conf->val('DB3','DB_HOST');			    $master_host = $conf->val('DB3','DB_HOST');
    $master_user = $conf->val('DB3','DB_USER');			    $master_user = $conf->val('DB3','DB_USER');
    $NAS_IP   = $conf->val('GLOBAL','NAS_IP');			    $NAS_IP   = $conf->val('GLOBAL','NAS_IP');
    return 1;							    return 1;
}								}
#############################################################	#############################################################
sub db_connect {						sub db_connect {
my $dbh = DBI->connect_cached("DBI:mysql:".$_[0].";host=".$_[	my $dbh = DBI->connect_cached("DBI:mysql:".$_[0].";host=".$_[
return $dbh if $dbh ;						return $dbh if $dbh ;
exit;								exit;
}								}
#############################################################	#############################################################
$acc='oussama';							$acc='oussama';
usage("Invalid action value.") unless ($action =~ m/^(sipconf	usage("Invalid action value.") unless ($action =~ m/^(sipconf
if ($action =~ m/^account=/i) {					if ($action =~ m/^account=/i) {
   usage("Invalid usage of account action.") unless ($action 	   usage("Invalid usage of account action.") unless ($action 
   $action='account';						   $action='account';
   $acc=$1;							   $acc=$1;
   } 								   } 
if ($action =~ m/^checkpass=/i) {				if ($action =~ m/^checkpass=/i) {
   usage("Invalid usage of checkpass action.") unless ($actio	   usage("Invalid usage of checkpass action.") unless ($actio
   $action='checkpass';						   $action='checkpass';
   $acc=$1;							   $acc=$1;
   $pass=$2;							   $pass=$2;
   }								   }
load_config();							load_config();
#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);	#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);
#my $dbh2=db_connect("astrad",$master_host,$master_user,'');	#my $dbh2=db_connect("astrad",$master_host,$master_user,'');
#print "OK $action\n-$User,$Pass,$master_host,$master_user\n"	#print "OK $action\n-$User,$Pass,$master_host,$master_user\n"
switch ($action) {						switch ($action) {
   case "sipconfig"  { sipconfig();  }				   case "sipconfig"  { sipconfig();  }
   case "location2"  { location2();  }				   case "location2"  { location2();  }
   case "location3"  { location3();  }				   case "location3"  { location3();  }
   case "tonotify"   { tonotify();   }				   case "tonotify"   { tonotify();   }
   case "toping"     { toping();     }				   case "toping"     { toping();     }
   case "astmultiua" { astmultiua(); }				   case "astmultiua" { astmultiua(); }
   case "dbamultiua" { dbamultiua(); }				   case "dbamultiua" { dbamultiua(); }
   case "account"    { account($acc); }				   case "account"    { account($acc); }
   case "checkpass"  { checkpass($acc,$pass);}			   case "checkpass"  { checkpass($acc,$pass);}
   else { usage("Bug: previous case not true.") }		   else { usage("Bug: previous case not true.") }
}								}
#############################################################	#############################################################
sub sipconfig{							sub sipconfig{
my $query='SELECT * FROM sipconfig;';				my $query='SELECT * FROM sipconfig;';
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -N -e '$query'";	my $command="mysql -u$User -p$Pass asterisk -N -e '$query'";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub location2{							sub location2{
my $query="SELECT * FROM location2 WHERE domain='$NAS_IP';";	my $query="SELECT * FROM location2 WHERE domain='$NAS_IP';";
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -h $master_host -u$master_user astrad -e \	my $command="mysql -h $master_host -u$master_user astrad -e \
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub location3{							sub location3{
my $query='SELECT * FROM location3;';				my $query='SELECT * FROM location3;';
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e '$query'";	my $command="mysql -u$User -p$Pass asterisk -e '$query'";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub tonotify{							sub tonotify{
my $query='SELECT username FROM location3 WHERE username NOT  |	my $query='SELECT username FROM location3 WHERE username NOT 
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";	my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub toping{							sub toping{
my $query='SELECT username, ipaddr, port FROM multiple_ua WHE |	my $query='SELECT username, ipaddr, port FROM multiple_ua WHE
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";	my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub astmultiua{							sub astmultiua{
my $query='SELECT * FROM multiple_ua WHERE domain=(SELECT val	my $query='SELECT * FROM multiple_ua WHERE domain=(SELECT val
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";	my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub dbamultiua{							sub dbamultiua{
my $query='SELECT * FROM multiple_ua;';				my $query='SELECT * FROM multiple_ua;';
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -h $master_host -u$master_user asterisk -e	my $command="mysql -h $master_host -u$master_user asterisk -e
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}
#############################################################	#############################################################
sub account{							sub account{
my $account=shift;						my $account=shift;
my $query="SELECT * FROM location3 WHERE username='$account';	my $query="SELECT * FROM location3 WHERE username='$account';
print "\n----------------------------------------------------	print "\n----------------------------------------------------
print "--  On Astrad:\n--------------------------------------	print "--  On Astrad:\n--------------------------------------
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";	my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n";							print "\n";
print "\n----------------------------------------------------	print "\n----------------------------------------------------
$query="SELECT name,ipaddr,port,regseconds,md5secret FROM sip |	$query="SELECT name,ipaddr,port,regseconds,md5secret FROM sip
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -u$User -p$Pass asterisk -e \"$query\"";	$command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n";							print "\n";
$query="SELECT name,md5secret,LENGTH(deny) FROM sipusers WHER |	$query="SELECT name,md5secret FROM sipdevices WHERE name='$ac
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -u$User -p$Pass asterisk -e \"$query\"";	$command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n----------------------------------------------------	print "\n----------------------------------------------------
print "--  On DBA:\n-----------------------------------------	print "--  On DBA:\n-----------------------------------------
$query="SELECT * FROM location2 WHERE username='$account';";	$query="SELECT * FROM location2 WHERE username='$account';";
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$q	$command="mysql -h $master_host -u$master_user astrad -e \"$q
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n";							print "\n";
$query="SELECT * FROM location2_history WHERE username='$acco	$query="SELECT * FROM location2_history WHERE username='$acco
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$q	$command="mysql -h $master_host -u$master_user astrad -e \"$q
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n";							print "\n";
$query="SELECT name,md5secret FROM sippeers WHERE name='$acco |	$query="SELECT name,md5secret FROM sipdevices WHERE name='$ac
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -h $master_host -u$master_user asterisk -e \"	$command="mysql -h $master_host -u$master_user asterisk -e \"
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n";						      <
$query="SELECT name,md5secret,LENGTH(deny) FROM sipusers WHER <
print "SQL Query:\n\t$query\n";				      <
print "Result   :\n";					      <
$command="mysql -h $master_host -u$master_user asterisk -e \" <
system($command) == 0 or die "system $command failed: $?";    <
print "\n";						      <
$query="SELECT LENGTH(get_deny());";			      <
print "SQL Query:\n\t$query\n";				      <
print "Result   :\n";					      <
$command="mysql -h $master_host -u$master_user astrad -e \"$q <
system($command) == 0 or die "system $command failed: $?";    <
}								}
#############################################################	#############################################################
sub checkpass {							sub checkpass {
#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);	#my $dbh1=db_connect("asterisk","localhost",$User,$Pass);
#my $dbh2=db_connect("astrad",$master_host,$master_user,'');	#my $dbh2=db_connect("astrad",$master_host,$master_user,'');
my ($account,$password)=@_;					my ($account,$password)=@_;
print "$account,$password\n";					print "$account,$password\n";
my $query="(SELECT md5secret FROM sippeers WHERE name='$accou |	my $query="(SELECT md5secret FROM sipdevices WHERE name='$acc
print "\n----------------------------------------------------	print "\n----------------------------------------------------
print "--  On Astrad:\n--------------------------------------	print "--  On Astrad:\n--------------------------------------
print "SQL Query:\n\t$query\n";				      |	print "SQL Query (should return only one line with encrypted 
print "Result   :\n";						print "Result   :\n";
my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";	my $command="mysql -u$User -p$Pass asterisk -e \"$query\"";
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
print "\n----------------------------------------------------	print "\n----------------------------------------------------
print "--  On DBA:\n-----------------------------------------	print "--  On DBA:\n-----------------------------------------
$query="(SELECT md5secret FROM  users WHERE username='$accoun |	$query="(SELECT md5secret FROM users WHERE username='$account
print "SQL Query:\n\t$query\n";					print "SQL Query:\n\t$query\n";
print "Result   :\n";						print "Result   :\n";
$command="mysql -h $master_host -u$master_user astrad -e \"$q	$command="mysql -h $master_host -u$master_user astrad -e \"$q
system($command) == 0 or die "system $command failed: $?";	system($command) == 0 or die "system $command failed: $?";
}								}