howto list inactive users in cyrus:

[Blake Hudson]

-------- Original Message  --------
Subject: Re: howto list inactive users in cyrus:
From: Chris Conn <cconn at abacom.com>
To: Blake Hudson <blake at ispn.net>
Date: Friday, January 09, 2009 10:38:38 AM
> Blake Hudson wrote:
>> I brought this subject up on the list a few times over the years and
>> never received any interest in updating cyradm/fud to report on the
>> last pop(login) date correctly.
>>
>> Rather than try to fix cyrus myself, I decided to write my own script
>> which tails the mail log and looks for successful pop/imap/smtp
>> logins and updates our user sql database with the current time and IP
>> the user used to access his or her account.
>>
>> If someone were interested in fixing this feature of cyrus it would
>> be great.
>>
>> -Blake
>
> Hello,
>
> You wouldn't care to post this script to the list or to share the loop
> you use to extract that information from the log?  I am currently
> looking to work on one and if you don't mind I'd appreciate the
> timesaver.
>
> Thanks in advance,
>
> Chris

This is accomplished via two scripts. First, a shell script that starts,
monitors, and restarts the process that pipes input to the actual
logging script. And second, the script that gets data from stdin and
puts that data into sql. Once the data is in sql, we can do a number of
operations to classify users as active or inactive based on the last
login or other criteria we already track in sql.

login-runner.sh
---------------------------------------------------
#!/bin/sh
#
# login-runner.sh
#
# Checks for running login-runner, if none starts a tail of the maillog
# that outputs pop/imap logins to login2sql.php which puts the data into
a db.
#

l2sLOG="/var/log/someLogFile.log"
l2sRCP="monitoring-address at someDomain.com"
l2sPIDS=`ps ax | grep -v grep | grep '/usr/bin/php ./login2sql.php' |
awk '{print $1}'`

if [ "$l2sPIDS" != "" ]; then
    COUNT=`echo $l2sPIDS | wc -w | tr -d " "`
  else
    COUNT=0
fi


if [ $COUNT -lt 1 ]; then
    STAMP=`date "+%m/%d/%y %H:%M:%S"`
    ALERT="$COUNT processes.  Restarting login2sql."
    echo "$STAMP        `basename $0`:  $ALERT" >> $l2sLOG
    if [ "$l2sRCP" != "" ]; then
      echo "`basename $0`:  $ALERT" | mail -s "login2sql Restarted" $l2sRCP
    fi
    #kill $PIDS 2>/dev/null;

    tail -F /var/log/maillog | grep ': login:' | awk -F ']' '{print $2
$3}' | awk -F '[' '{print $2 $3}' | awk '{print $1,$2}'| ./login2sql.php

else
    echo "$COUNT processes already running. Exiting..."    
fi
---------------------------------------------------


login2sql.php
---------------------------------------------------
#!/usr/bin/php
<?php
/*
    Takes data from stdin and writes it to MySQL database.
    Expects data in format "ip emailAddress" ex:
    1.2.3.4 bob at bob.com
    1.2.3.5 billy at billy.com
*/

if(!defined("STDIN"))
{
        define("STDIN", fopen('php://stdin','r'));
}

mysql_connect($server,$user,$pass) or die ("Couldn't connect to MySQL
server...");
mysql_select_db($db) or die("Database not available...");
while(!feof(STDIN))
{
        $array = explode(" ",trim(fgets(STDIN, 128)));
        if($ip = $array['0'] and $email = $array['1'])
        {
                $ip = mysql_real_escape_string($ip);
                $email = mysql_real_escape_string($email);
                mysql_query("update mailbox set last=now(), ip='$ip'
where email='$email'") or die("Query Failed!");
                echo "Updated $email - $ip\n";
        }
}

mysql_close();
die("Ran out of input...");

?>
---------------------------------------------------