Monitoring Zarafa Outgoingqueue

From Zarafa wiki

(Difference between revisions)
Jump to: navigation, search
 
Line 129: Line 129:
   #my $CTIME = get_item("SELECT createtime from hierarchy where id=$HIERARCHYID", 0);                 
   #my $CTIME = get_item("SELECT createtime from hierarchy where id=$HIERARCHYID", 0);                 
   # For Zarafa 7.0.x                                                                                 
   # For Zarafa 7.0.x                                                                                 
-
   my $CTIME = get_item("select FROM_UNIXTIME((((val_hi << 32) + val_lo)-116444736000000000)/10000000) from properties where tag=0x3008 and hierarchyid=$HIERARCHYID", 0);                                
+
   my $CTIME = get_item("select FROM_UNIXTIME((((val_hi << 32) + val_lo)-116444736000000000)/10000000) from properties where tag=0x3008 and hierarchyid=$HIERARCHYID", 0);
 +
                               
   my $FROM = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=16378", 0);  
   my $FROM = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=16378", 0);  
   my $RCPT = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=3588", 0);
   my $RCPT = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=3588", 0);

Latest revision as of 10:21, 12 October 2011

Monitoring the outgoingqueue of Zarafa


Monitoring the outgoingqueue of Zarafa can be done through the mysql client interface. So when connected to the zarafa database with a mysql client you can use the following query to monitor the outgoingqueue:

SELECT * FROM outgoingqueue

This query shows all items in the outgoing queue. Each item however has a flag, shown in the "flags" column of each record. This flag can have several values. Each of these values has a different meaning:

  • 0: Local queue -> Outlook still has to submit the item, so that the spooler will pick it up.
  • 1: Master queue -> Zarafa spooler has to pick the item up and send it to the smtp server.
  • 2: Do send mail -> Zarafa spooler must place item in "Sent Items" folder of the user store.
  • 3: Combination of 1 + 2 -> Zarafa spooler must pick up the item and send to smtp + place the item in the "Sent Items" folder.


When you want to monitor only the items that are already submitted by Outlook and are waiting to be send to the smtp server, you will have to use the following mysql query:

SELECT * FROM outgoingqueue WHERE flags=3


Below is a simple perl script which connects to the mysql database and will show the outgoing zarafa queue in a way that postfix show its mailqueue. This script can be used with some parameters to change its functionality:

  • -f <flag id> : Shows all items in outgoing queue with flag <flag id>, default is 3.
  • -d <queue id> : Deletes item from outgoing queue with queue id <queue id>. When used with �"-d ALL" all items from outgoing queue will be deleted.
  • -c : Only shows number of items in the outgoing queue.


Prerequisites for this script

  • perl
  • per libs: DBI, DBD::mysql, Getopt::Long


The Script: (remember to change the mysql variables on top in the script)

#!/usr/bin/perl
#

# === MySQL variables change this ===
my $host="localhost";
my $database="zarafa";
my $user="root";
my $password="";
# === End of MySQL variables ===

use strict;
use DBI;
use DBD::mysql;
use Getopt::Long;




my $show_q = 1; 

my @deletes;
my $countonly;
my $flags=3;
GetOptions('d=s' => \@deletes,
	'f=i' => \$flags,
	"c!" => \$countonly);

sub ltrim($)
{
	my $string = shift;
	$string =~ s/^\s+//;
	return $string;
}

my $db=DBI->connect("DBI:mysql:$database;host=$host", $user, $password);

if (@deletes > 0) {
  $show_q = 0;
  my $d_succes = 0;
  foreach (@deletes) {
    my $deletequery = "DELETE FROM outgoingqueue WHERE hierarchy_id=$_";
    if ($_=="ALL") { $deletequery = "DELETE FROM outgoingqueue" }
    my $q_delete=$db->prepare($deletequery);
    my $retval=$q_delete->execute; 
    if ($retval > 0) {
      $d_succes=$d_succes+$retval;
    } else {
      print "Failed to delete message id: $_\n";
    }
  } 
  print "\n$d_succes messages deleted.\n";
}

if ($countonly == 1) {
  $show_q = 0;
  my $countquery = "SELECT count(*) from outgoingqueue where flags=$flags";
  my $q_count=$db->prepare($countquery);
  $q_count->execute;
  my @mailcount = $q_count->fetchrow();
  print "-- $mailcount[0] Requests.\n";
}

if ($show_q != 1) {
  exit 0;
}

my $q_mailq=$db->prepare("SELECT * FROM outgoingqueue where flags=$flags");
$q_mailq->execute;

sub get_item {
  my($q, $idx) = @_;
  my $sth = $db->prepare($q);
  $sth->execute;
  my @ar = $sth->fetchrow();
  return $ar[$idx];
}

print "-Queue ID-\t-From-\t----Arrival Time----\t-Flags-\t--Recipient----------\n";

my $qcount = 0;
while (my @result = $q_mailq->fetchrow_array()) {
  $qcount++;
  my $HIERARCHYID = $result[1];
  my $FLAGS = $result[2];

  # For Zarafa 6.40                                                                                  
  #my $CTIME = get_item("SELECT createtime from hierarchy where id=$HIERARCHYID", 0);                
  # For Zarafa 7.0.x                                                                                 
  my $CTIME = get_item("select FROM_UNIXTIME((((val_hi << 32) + val_lo)-116444736000000000)/10000000) from properties where tag=0x3008 and hierarchyid=$HIERARCHYID", 0);
                                 
  my $FROM = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=16378", 0); 
  my $RCPT = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=3588", 0);
  my $CC = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=3587", 0);
  my $BCC = get_item("SELECT val_string FROM properties where hierarchyid=$HIERARCHYID and tag=3586", 0);
  my $RCPTALL="$RCPT; $CC; $BCC";

  my @RCPTAR = split(/;/, $RCPTALL); 
  print "$HIERARCHYID\t\t$FROM\t$CTIME\t$FLAGS\t";

  my $i=0;
  foreach (@RCPTAR) {
    if ($i > 0) { print "\t\t\t\t\t\t\t" }
    print ltrim($_)."\n";
    $i++;
  }

}

print "\n-- $qcount Requests.\n";

Mail Arrival Time

Check in the script for the $CTIME value. For 6.40 and 7 this value needs to be filled with a different query.

Personal tools