The mailing list for listmasters using Sympa

[Omen Wild <address@concealed>]

Quoting Dan Pritts <address@concealed> on Wed, Jan 05 13:05:
>
> with the move to the spool in the database in 6.x i'm no longer able to
> take a quick look at the filesystem to see the state of the queue.  
> 
> I didn't find anything in the docs about this.  Is there a tool to list
> the bulk queue

I wrote the following two scripts for use at UC Davis.  The first one
(sympa-spool-count) is called by SNMP to count the messages in the Sympa
spools so we can page when Sympa starts backing up.  You will need to
replace YourPasswordHere with your database password.

The second one (sympa-spool-watcher.pl) queries the Database queue
(MySQL) in our case, and prints the current queue.  You will need to
replace YourPasswordHere with your database password, as well as point
it to your sympa.conf near the top.

Not exactly what you were asking for, but a starting place.

-- 
Omen Wild
Security Administrator
(530) 752-1700
#!/bin/bash

spool_dirs="/var/sympa/spool/msg/ /var/sympa/spool/distribute/"

for spool_dir in $spool_dirs; do
  echo -n $spool_dir:

  # Funky echo $() to strip the whitespace from wc's output
  echo $(ls -1 $spool_dir/*@* 2>/dev/null | wc -l)
done

echo -n "/MySQL/Outgoing/Spool/:"
{ 
mysql --user=sympa-ro --password="YourPasswordHere" sympa << EOF
SELECT COUNT(DISTINCT(messagekey_bulkmailer)) FROM bulkmailer_table
EOF
} | /usr/bin/tail -1
#! /ucd/bin/perl

use strict;
use warnings;

$| = 1;

use DBI;
use List::Util qw/max/;

my $dbh = open_sympa_db();

open(CONFIG, "</PATH/TO/sympa.conf") or die;
my $bulk_max_count = -1;
while (my $line = <CONFIG>) {
  if ($line =~ m/^bulk_max_count\s+(\d+)/) {
	 $bulk_max_count = $1;
  }
}
close(CONFIG);

print "Sympa's MySQL bulkmailer table contents (. means no messages waiting) ($bulk_max_count bulk mailers):\n";

my $sql = q/
	SELECT * FROM bulkmailer_table 
	ORDER BY
		priority_message_bulkmailer DESC, 
		lock_bulkmailer ASC,
		listname_bulkmailer DESC
/;

#   $VAR1 = \{
#             'priority_packet_bulkmailer' => '5',
#             'receipients_bulkmailer' => 'address@concealed, address@concealed',
#             'reception_date_bulkmailer' => '1289945241',
#             'priority_message_bulkmailer' => '8',
#             'packetid_bulkmailer' => 'f3d42aed4d81fbb307e89ec8cbda3a68',
#             'verp_bulkmailer' => '1',
#             'returnpath_bulkmailer' => 'address@concealed',
#             'messagekey_bulkmailer' => 'd8241dc75b7fa8c90755af6e7976de61',
#             'listname_bulkmailer' => 'a-generic-list',
#             'delivery_date_bulkmailer' => '1289945241',
#             'robot_bulkmailer' => 'ucdavis.edu',
#             'lock_bulkmailer' => undef
#           };

my $sth = $dbh->prepare($sql);

# Keep state for the outer loop
my $print_header = 1;
my $max_list_len = 0;

while (1) {

  my $start_time = time();
  my %lists;
  my $total_recipients = 0;

  my $rv = $sth->execute;
  if (int($rv) == 0) {
	 print ".";
	 $print_header = 1;
  } else {
	 my @data;

	 while (my $entry = $sth->fetchrow_hashref()) {
		$max_list_len = max($max_list_len, length($entry->{listname_bulkmailer}));
		$entry->{_delay} = $start_time - $entry->{reception_date_bulkmailer};
		push (@data, $entry);
		$lists{$entry->{listname_bulkmailer}}++;
	 }

	 my $format_str = "%${max_list_len}.${max_list_len}s %4s %4s %7s %11s %10s %s\n";
	 my $header = sprintf($format_str, 
								 "List Name", 
								 "Prio", 
								 "Pkts", 
								 "Status", 
								 "#Recipients", 
								 "Delay(sec)", 
								 "" . localtime(),
								);

	 if ($print_header != 0) {
		$print_header = 0;
		print "\n", $header;
	 }

	 my $packets_backed_up = max(scalar(@data) - $bulk_max_count, 0);

	 # Keep state for the inner loop
	 my $previous_list = $data[0]->{listname_bulkmailer};
	 my $total_recipients_list = 0;
	 my $packets = 0;

	 while (my $entry = shift(@data)) {
		my @recipients = split(",", $entry->{receipients_bulkmailer});
		$total_recipients += scalar(@recipients);
		$total_recipients_list += scalar(@recipients);
		$packets++;

		my $sending_status = $entry->{lock_bulkmailer} ? "Send" : "Queued";

		if ($entry->{listname_bulkmailer} ne $previous_list or scalar(@data) == 0) {
		  # New list or we hit the end of the data, better print stats
		  printf($format_str,
					$entry->{listname_bulkmailer}, 
					$entry->{priority_message_bulkmailer}, 
					$packets,
					$sending_status,
					$total_recipients_list,
					$entry->{_delay},
					"",
				  );

		  $previous_list = $entry->{listname_bulkmailer};
		  $total_recipients_list = 0;
		  $packets = 0;
		}
	 }

	 print "\n";
  }

  sleep 1;
}


sub open_sympa_db {
  my $database = 'sympa';
  my $db_user = 'sympa-ro';
  my $db_pw = 'YourPasswordHere';
  my $connect_string = "DBI:mysql:database=$database;host=localhost";
  $dbh = DBI->connect($connect_string, $db_user, $db_pw, 
							 { RaiseError => 1, AutoCommit => 0 }
							);
  
  if (! $dbh) {
	 die "MySQL connect: $DBI::errstr\n\t";
  }

  END {
	 # Make sure the DB connection gets releases
	 if (defined($dbh)) {
		#print "$PROGRAM: closing database handle.\n";
		$dbh->disconnect();
	 }
  }

  return $dbh;
}

Attachment: smime.p7s
Description: S/MIME cryptographic signature