-------- Original Message --------
Subject: Re: howto list inactive users in cyrus:
From: Chris Conn <cconn@xxxxxxxxxx>
To: Blake Hudson <blake@xxxxxxxx>
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@xxxxxxxxxxxxxx"
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@xxxxxxx
1.2.3.5 billy@xxxxxxxxx
*/
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...");
?>
---------------------------------------------------
----
Cyrus Home Page: http://cyrusimap.web.cmu.edu/
Cyrus Wiki/FAQ: http://cyrusimap.web.cmu.edu/twiki
List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html
