Ok Al you really suck.
I was sitting there watching Friends on TBS and trying to review a book and
this stupid problem keeps popping into my head and your comment so I had to
come down and whip out a full script.
1. Build an access database (possible file name db.mdb) with a table called
Tbl_Data
2. Create the following fields and set the options specified
a. key - autonumber primary key
b. dt - Text field size=20
c. msgid - Text field size=255 Indexed with no duplicates
Zero Length Not Allowed
d. frm - Text field size=255
e. sbj - Text field size=255
I would have exported a SQL Script but it doesn't appear that Access 2003
does that which is what I usually do with MSDE.
3. Create a file dsn named something like db.dsn, populate with the
following information
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=<insert folder name where DB lives - ex: c:\test\db>
DBQ=<insert full path and file name of DB - ex: c:\test\db\db.mdb>
A complete example would be
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=c:\db
DBQ=c:\db\db.mdb
4. Create a file in the folder the script will be in called popwatch.pl, add
the following entries:
host=<insert servername>
username=<insert mailbox id>
password=<insert mailbox id's password>
dsn=<path and filename of dsn>
A complete example would be
host=2k3exc01.joe.com
username=joe
password=jumpinjoehosaphat
dsn=c:\db\db.dsn
Note: Ask me if I think it is a good idea to put a password in a text file
like this. Thanks. No the answer is no, in fact don't do it is a bad idea,
but I am doing this to get the monkey off my back, not make you secure, NO.
Thanks!
5. Copy the script below into a file called popwatch.pl (and yes I am
assuming you have activestate perl loaded thanks!) - also attached in txt
format in case it gets bonked in formatting...
___SCRIPT START___
#***************************************************************************
*************
#* PopWatch.PL
*
#*==========================================================================
============*
#* Author : [EMAIL PROTECTED]
*
#* Version: V01.00.00
*
#* Modification History:
*
#* V01.00.00 2004.06.22 Original Version
*
#*--------------------------------------------------------------------------
------------*
#* This file is a PERL script that gets POP messages and jams uniques into a
db *
#*--------------------------------------------------------------------------
------------*
#* Notes:
*
#* Use at your own risk, this is unsupported
*
#*
*
#***************************************************************************
*************
#***************************************************************************
*************
#***************************************************************************
*************
#* Load OLE Module for ODBC connectivity
*
#* Load Net::POP3 Module for email access
*
#***************************************************************************
*************
use OLE;
use Net::POP3;
#***************************************************************************
*************
#* Main Logic
*
#***************************************************************************
*************
#
# Say my name!
#
print "\nPopWatch V01.00.00pl Joe Richards ([EMAIL PROTECTED]) June
2004\n\n";
#
# Bootstrap config stuff
#
$configfilename="popwatch.cfg";
%config=();
@date=localtime();
$dt=sprintf("%4i/%02i/%02i-%02i:%02i",$date[5]+1900,$date[4]+1,$date[3],$dat
e[2],$date[1]);
#
# Read Config file (yes this could be in/from Access or SQL - you do it...)
#
ReadConfig($configfilename,\%config);
#
# Set parameters from config info
#
$host=shift || $config{host};
$username=shift || $config{username};
$password=shift || $config{password};
$dsn=$config{dsn} || "c:\\db\\db.dsn";
$dsn="FILEDSN=$dsn";
#
# Open up our database access
#
$ado=CreateObject OLE ("ADODB.Connection");
if (!$ado)
{
print "Error: Couldn't open ADO connection\n";
ADOError();
exit;
}
$ado->Open($dsn);
#
# Open up our mail access and log into the mailbox
#
$pop=Net::POP3->new($host) or die("Error: Couldn't connect to $host -
$!\n");
if (!$pop)
{
print "Error: Couldn't open POP connection\n";
print "\nThe command DID NOT complete successfully\n\n";
exit;
}
$msgs=$pop->login($username, $password);
if ($msgs eq "0E0")
{
print "No messages in inbox for user...\n";
print "\nThe command completed successfully\n\n";
exit;
}
if ($msgs==undef)
{
print "Error: Logon Failure...\n";
print "\nThe command DID NOT complete successfully\n\n";
exit;
}
print "$msgs message(s) to retrieve from the server...\n";
#
# Loop through Email and start storing it
#
$totalentries=0;
$uniqueentries=0;
my $msgnums = $pop->list;
foreach my $msgnum (sort {$a<=>$b} keys %$msgnums)
{
#
#
# Get message header, tear out from/subject/messageid
$msg=$pop->top($msgnum,0);
chomp @$msg;
($from)=grep(/from: /i,@$msg);
($subject)=grep(/subject: /i,@$msg);
($messageid)=grep(/message-id: /i,@$msg);
#
# Clean up data points we want
#
$from=~s/from: (.+)/\1/i;
$subject=~s/subject: (.+)/\1/i;
$messageid=~s/message-id: (.+)/\1/i;
#
# Insert into Database
#
$totalentries++;
$uniqueentries+=StoreRecord($dt,$messageid,$from,$subject);
}
#
# Close out connections
#
$pop->quit();
$ado->close();
#
# Final Tally
#
print "\n";
print "Total Emails Enumerated: $totalentries\n";
print "Total Emails Stored : $uniqueentries\n";
print "The command completed successfully.\n\n";
exit;
#***************************************************************************
*************
#* Subroutine: ReadConfig
*
#* This reads in the config file
*
#***************************************************************************
*************
sub ReadConfig
{
my $configfn=shift;
my $confighash=shift;
open fhcfgfile,"<$configfn" or warn("WARN: Couldn't open config file
($configfn) : $!\n");
@configinfo=<fhcfgfile>;
close fhcfgfile;
chomp @configinfo;
foreach $this (@configinfo)
{
next unless $this=~/\w/;
($key,$value)=split(/=/,$this);
$$confighash{lc($key)}=$value;
}
}
#***************************************************************************
*************
#* Subroutine: StoreRecord
*
#* This stores the emails into a DB based on the DSN
*
#***************************************************************************
*************
sub StoreRecord
{
my @[EMAIL PROTECTED];
my $rv=1;
my $fields="dt,msgid,frm,sbj";
#
# Don't bother if zero length message id, probably spam
#
if (!$arr[1])
{
print "Skip missing message ID email (@_[2] | @_[3])...\n";
return 0;
}
#
# Quote any ' chars and then slap ' around the values and then slap into a
single
# string that is separated by commas
#
map{s/'/''/g;$_="'".lc($_)."'"} @arr;
my $values=join(",",@arr);
#
# This is where the big bucks are made...
#
print "Inserting @_[1]...";
my $sql="INSERT INTO Tbl_Data ($fields) VALUES ($values)";
$ado->Execute($sql);
if (ADOError()) {$rv=0};
return $rv;
}
#***************************************************************************
*************
#* Subroutine: ADOError
*
#* Displays ADO Errors
*
#***************************************************************************
*************
sub ADOError
{
my $rv=0;
my $err=$ado->Errors();
foreach $thiserror (keys %$err)
{
$rv=1;
if ($thiserror->nativeerror eq -1605) {print "(DUPE)\n";}
else {print "(ERR:
".$thiserror->nativeerror."(".$thiserror->description."))\n";}
}
return $rv;
}
___SCRIPT END___
A few notes...
1. This may not work perfectly, it is a quick and dirty script even though I
made it rather verbose so it can be easily followed.
2. Note the change in the field names from the previous post. I think the
SQL syntax didn't like one of the field names. So I changed them all. It was
probably FROM but I don't care, I changed them all, I dislike SQL
considerably though as you can see I will still use it when it makes sense.
3. I noticed that if messages are in an inbox subfolder Exchange will not
return them to the perl POP3 client. It only returns the inbox. Not sure why
this is, don't really care at the moment. When I care I will do a network
trace and figure it out unless someone posts the answer.
4. The way this is working, it DOES NOT mark the messages as read.
5. Robbie, if I am late on the review, please blame Laura and Al - oh and
Crimson Skies. And err Halo.
Thanks,
joe
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of joe
Sent: Tuesday, June 22, 2004 4:45 PM
To: [EMAIL PROTECTED]
Subject: RE: [ActiveDir] OT: Exchange accounting
Knee biter!
I tried to leave that out as my ADO is very rusty... However looking at some
of my older code it would probably be something like the following to read a
text file generated by the other script, you can combine them yourself if
you would like.
use OLE;
$DSN="FILEDSN=c:\\db\\db.dsn";
$CONN=CreateObject OLE ("ADODB.Connection"); $CONN->Open($DSN);
$fields="msgid,from,subj";
while (<>)
{
next unless $_=~/\w/;
@arr=split(/;/,$_);
map{$_="\"$_\""} @arr;
$values=join(/,/,@arr);
my $sql="SELECT * FROM Tbl_Data WHERE msgid=".$arr[0];
my $RS=$CONN->Execute($sql);
AdoError();
if (!$RS or ($RS and $RS->EOF))
{
print "Inserting $arr[0]\n...";
$sql="INSERT INTO Tbl_Groups ($fields) VALUES ($values)";
$CONN->Execute($sql);
AdoError();
}
}
sub AdoError
{
my $Errors = $CONN->Errors();
foreach $error (keys %$Errors) { print "AdoError::",
$error->{Description}, "\n"};
}
Assuming you have a database with a DNS of c:\db\db.dsn and a table called
Tbl_Data with fields called msgid and from and subj.
Also at a guess, just quoting all of the strings probably wouldn't be good
enough, there would have to be a piece that tore out or escaped any special
characters. I would watch out for the characters (@,<,>,(,)) etc as they
tend to bite...
joe
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mulnick, Al
Sent: Tuesday, June 22, 2004 3:10 PM
To: '[EMAIL PROTECTED]'
Subject: RE: [ActiveDir] OT: Exchange accounting
Where's the part where it puts into a DB on a single row? C'mon Joe...
;-)
Al
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of joe
Sent: Tuesday, June 22, 2004 2:31 PM
To: [EMAIL PROTECTED]
Subject: RE: [ActiveDir] OT: Exchange accounting
Yuck that output came through looking pretty nasty... Try this instead...
[Tue 06/22/2004 14:24:35.83]
G:\TEMP\delete>pop 2k3exc01.joe.com joe joemamma Enumerating...
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: RE: End of the world as we know it....
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: RE: Contact Info - Joe Richards
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110976]: Email broken yet again
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110981]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: <[EMAIL PROTECTED]>
Subject: GLOBAT SUCKS
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: Web form for globat support to automate tickets
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110976]: Email broken yet again
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110981]: Email broken
And for the other
[Tue 06/22/2004 14:25:53.87]
G:\TEMP\delete>notepad pop2.pl
[Tue 06/22/2004 14:27:57.29]
G:\TEMP\delete>pop2 2k3exc01.joe.com joe joemamma Enumerating...
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: RE: End of the world as we know it....
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: RE: Contact Info - Joe Richards
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110976]: Email broken yet again
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110981]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: <[EMAIL PROTECTED]>
Subject: GLOBAT SUCKS
Message-Id: <[EMAIL PROTECTED]>
From: "joe" <[EMAIL PROTECTED]>
Subject: Web form for globat support to automate tickets
Message-Id: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110976]: Email broken yet again
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110981]: Email broken
[Tue 06/22/2004 14:28:16.76]
G:\TEMP\delete>pop2 2k3exc01.joe.com joe joemamma
[EMAIL PROTECTED] Enumerating...
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>
From: [EMAIL PROTECTED]
Subject: [Support #110981]: Email broken
[Tue 06/22/2004 14:28:23.04]
G:\TEMP\delete>
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of joe
Sent: Tuesday, June 22, 2004 2:10 PM
To: [EMAIL PROTECTED]
Subject: RE: [ActiveDir] OT: Exchange accounting
I may be going about this a little simply but I would set up a perl script
that runs daily from scheduled tasks that uses POP3 or IMAP to read the
messages and populate a text file or access database or SQL Database (MySql,
SQL Server, etc).
Here is a quick example of reading POP3 mailbox and outputting the message
info from perl...
use Net::POP3;
$host=shift || "servername";
$username=shift || "username";
$password=shift || "password";
$pop=Net::POP3->new($host) or die("Error: Couldn't connect to $host -
$!\n");
$msgs=$pop->login($username, $password); if ($msgs!=undef) {
print "Enumerating...\n";
my $msgnums = $pop->list;
foreach my $msgnum (sort {$a<=>$b} keys %$msgnums)
{
$msg=$pop->top($msgnum,0);
chomp @$msg;
($from)=grep(/from: /i,@$msg);
($subject)=grep(/subject: /i,@$msg);
($messageid)=grep(/message-id: /i,@$msg);
print "$messageid;$from;$subject\n";
}
$pop->quit();
}
else
{
print "Logon failed\n";
}
You can pull out dupe message IDs either when putting the info into the
storage medium or later when making the report since most likely the old
messages won't be deleted on each new pass. You could even adust the script
to just track message header of last message read and then loop through
messages until that message is encountered and then count the messages after
that ID and then the last message read would have its ID saved for the next
pass, etc etc...
Output would look something like:
[Tue 06/22/2004 13:42:56.84]
G:\TEMP\delete>pop 2k3exc01.joe.com joe pokeyjoe Enumerating...
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: RE: End of the world as we know it....
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: RE: Contact Info - Joe Richards
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110976]: Email broken yet again
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110981]: Email broken
Message-ID:
<[EMAIL PROTECTED]>;From:
<[EMAIL PROTECTED]>;Subject: GLOBAT SUCKS
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: Web form for globat support to automate
tickets
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110976]: Email broken yet again
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110981]: Email broken
[Tue 06/22/2004 13:43:29.66]
G:\TEMP\delete>
Here would be an example of filtering on last message ID seen...
use Net::POP3;
$host=shift || "servername";
$username=shift || "username";
$password=shift || "password";
$lastmess=lc(shift);
$pop=Net::POP3->new($host) or die("Error: Couldn't connect to $host -
$!\n");
$msgs=$pop->login($username, $password); if ($msgs!=undef) {
print "Enumerating...\n";
my $msgnums = $pop->list;
if (!$lastmess) {$newmessages=1} else {$newmessages=0};
foreach my $msgnum (sort {$a<=>$b} keys %$msgnums)
{
$msg=$pop->top($msgnum,0);
chomp @$msg;
($from)=grep(/from: /i,@$msg);
($subject)=grep(/subject: /i,@$msg);
($messageid)=grep(/message-id: /i,@$msg);
if (!$newmessages)
{
($msgid)=($messageid=~/\<(.+?)\>/);
$msgid=lc($msgid);
if ($msgid eq $lastmess) {$newmessages=1};
next;
}
print "$messageid;$from;$subject\n";
}
$pop->quit();
}
else
{
print "Logon failed\n";
}
Output
[Tue 06/22/2004 14:05:10.54]
G:\TEMP\delete>pop2 2k3exc01.joe.com joe pokeyjoe Enumerating...
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: RE: End of the world as we know it....
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: RE: Contact Info - Joe Richards
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110976]: Email broken yet again
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>;From: [EMAIL PROTECTED];Subject:
[Support #110981]: Email broken
Message-ID:
<[EMAIL PROTECTED]>;From:
<[EMAIL PROTECTED]>;Subject: GLOBAT SUCKS
Message-Id: <[EMAIL PROTECTED]>;From:
"joe" <[EMAIL PROTECTED]>;Subject: Web form for globat support to automate
tickets
Message-Id: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: failure notice
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110976]: Email broken yet again
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110981]: Email broken
[Tue 06/22/2004 14:06:31.95]
G:\TEMP\delete>pop2 2k3exc01.joe.com joe pokeyjoe
[EMAIL PROTECTED] Enumerating...
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110977]: Email broken
Message-ID: <[EMAIL PROTECTED]>;From:
[EMAIL PROTECTED];Subject: [Support #110981]: Email broken
[Tue 06/22/2004 14:06:42.27]
G:\TEMP\delete>
joe
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Hunter, Laura E.
Sent: Tuesday, June 22, 2004 10:55 AM
To: [EMAIL PROTECTED]
Subject: [ActiveDir] OT: Exchange accounting
Morning all,
Okay, here's the situation ("my parents went away on a..." Stoppit, Laura,
you're having an '80s flashback):
I have an Exchange 2000 mailbox set up as a drop-box for general customer
service support inquiries for my office. The manager of the CS area wants
to keep track of how many messages are sent to the drop-box so that she can
have a pretty chart to show off at Senior Staff meetings. :-) The solution
that's currently in place is this: every message that comes into the
drop-box is copied into a "Statistics"
sub-folder. The messages in the Inbox are forwarded/replied to/deleted as
necessary, and the CS manager exports the ~\Statistics folder to an Excel
spreadsheet or some such.
This strikes me as being a really kludgey solution, since every 6 months or
so I need to call her with "You know, there are like 30,000 messages in the
Statistics folder, you might want to think about deleting them if you've
captured the information you need."
Is there something script-y that I can do that will...(this is my idea, but
I'm open to suggestions)...add a record to a SQL table every time a message
hits the Inbox? That way the metadata is captured in a way that's conducive
to searching/querying/reporting, and is a lot more elegant than what's
currently happening.
I'm not a script-maven by any stretch, so if there were even some
Shake-n-Bake code out there that I could start with (from Robbie or
someone), that would make me quite the happy network admin.
("Laura, learn how to do scripting, it'll make you a much better admin."
I know, I know, it's on my list.)
*********************************************
Laura E. Hunter
MCT, MCSE: Security, MVP - Windows Networking Senior IT Specialist
University of Pennsylvania
********************************************
This email message is for the sole use of the intended recipient(s) and may
contain confidential and privileged information. Any unauthorized review,
use, disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply email, destroy all copies of
the original message, and repent! Repent!
Any views expressed in this email message, well-informed and intellectually
unassailable as they may be, are those of the individual sender except where
the sender specifically states them to be the views of Student Financial
Services.
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
List info : http://www.activedir.org/mail_list.htm
List FAQ : http://www.activedir.org/list_faq.htm
List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
#****************************************************************************************
#* PopWatch.PL
*
#*======================================================================================*
#* Author : [EMAIL PROTECTED]
*
#* Version: V01.00.00
*
#* Modification History:
*
#* V01.00.00 2004.06.22 Original Version
*
#*--------------------------------------------------------------------------------------*
#* This file is a PERL script that gets POP messages and jams uniques into a db
*
#*--------------------------------------------------------------------------------------*
#* Notes:
*
#* Use at your own risk, this is unsupported
*
#*
*
#****************************************************************************************
#****************************************************************************************
#****************************************************************************************
#* Load OLE Module for ODBC connectivity
*
#* Load Net::POP3 Module for email access
*
#****************************************************************************************
use OLE;
use Net::POP3;
#****************************************************************************************
#* Main Logic
*
#****************************************************************************************
#
# Say my name!
#
print "\nPopWatch V01.00.00pl Joe Richards ([EMAIL PROTECTED]) June 2004\n\n";
#
# Bootstrap config stuff
#
$configfilename="popwatch.cfg";
%config=();
@date=localtime();
$dt=sprintf("%4i/%02i/%02i-%02i:%02i",$date[5]+1900,$date[4]+1,$date[3],$date[2],$date[1]);
#
# Read Config file (yes this could be in/from Access or SQL - you do it...)
#
ReadConfig($configfilename,\%config);
#
# Set parameters from config info
#
$host=shift || $config{host};
$username=shift || $config{username};
$password=shift || $config{password};
$dsn=$config{dsn} || "g:\\temp\\delete\\db.dsn";
$dsn="FILEDSN=$dsn";
#
# Open up our database access
#
$ado=CreateObject OLE ("ADODB.Connection");
if (!$ado)
{
print "Error: Couldn't open ADO connection\n";
ADOError();
exit;
}
$ado->Open($dsn);
#
# Open up our mail access and log into the mailbox
#
$pop=Net::POP3->new($host) or die("Error: Couldn't connect to $host - $!\n");
if (!$pop)
{
print "Error: Couldn't open POP connection\n";
print "\nThe command DID NOT complete successfully\n\n";
exit;
}
$msgs=$pop->login($username, $password);
if ($msgs eq "0E0")
{
print "No messages in inbox for user...\n";
print "\nThe command completed successfully\n\n";
exit;
}
if ($msgs==undef)
{
print "Error: Logon Failure...\n";
print "\nThe command DID NOT complete successfully\n\n";
exit;
}
print "$msgs message(s) to retrieve from the server...\n";
#
# Loop through Email and start storing it
#
$totalentries=0;
$uniqueentries=0;
my $msgnums = $pop->list;
foreach my $msgnum (sort {$a<=>$b} keys %$msgnums)
{
#
#
# Get message header, tear out from/subject/messageid
$msg=$pop->top($msgnum,0);
chomp @$msg;
($from)=grep(/from: /i,@$msg);
($subject)=grep(/subject: /i,@$msg);
($messageid)=grep(/message-id: /i,@$msg);
#
# Clean up data points we want
#
$from=~s/from: (.+)/\1/i;
$subject=~s/subject: (.+)/\1/i;
$messageid=~s/message-id: (.+)/\1/i;
#
# Insert into Database
#
$totalentries++;
$uniqueentries+=StoreRecord($dt,$messageid,$from,$subject);
}
#
# Close out connections
#
$pop->quit();
$ado->close();
#
# Final Tally
#
print "\n";
print "Total Emails Enumerated: $totalentries\n";
print "Total Emails Stored : $uniqueentries\n";
print "The command completed successfully.\n\n";
exit;
#****************************************************************************************
#* Subroutine: ReadConfig
*
#* This reads in the config file
*
#****************************************************************************************
sub ReadConfig
{
my $configfn=shift;
my $confighash=shift;
open fhcfgfile,"<$configfn" or warn("WARN: Couldn't open config file ($configfn) :
$!\n");
@configinfo=<fhcfgfile>;
close fhcfgfile;
chomp @configinfo;
foreach $this (@configinfo)
{
next unless $this=~/\w/;
($key,$value)=split(/=/,$this);
$$confighash{lc($key)}=$value;
}
}
#****************************************************************************************
#* Subroutine: StoreRecord
*
#* This stores the emails into a DB based on the DSN
*
#****************************************************************************************
sub StoreRecord
{
my @[EMAIL PROTECTED];
my $rv=1;
my $fields="dt,msgid,frm,sbj";
#
# Don't bother if zero length message id, probably spam
#
if (!$arr[1])
{
print "Skip missing message ID email (@_[2] | @_[3])...\n";
return 0;
}
#
# Quote any ' chars and then slap ' around the values and then slap into a single
# string that is separated by commas
#
map{s/'/''/g;$_="'".lc($_)."'"} @arr;
my $values=join(",",@arr);
#
# This is where the big bucks are made...
#
print "Inserting @_[1]...";
my $sql="INSERT INTO Tbl_Data ($fields) VALUES ($values)";
$ado->Execute($sql);
if (ADOError()) {$rv=0};
return $rv;
}
#****************************************************************************************
#* Subroutine: ADOError
*
#* Displays ADO Errors
*
#****************************************************************************************
sub ADOError
{
my $rv=0;
my $err=$ado->Errors();
foreach $thiserror (keys %$err)
{
$rv=1;
if ($thiserror->nativeerror eq -1605) {print "(DUPE)\n";}
else {print "(ERR: ".$thiserror->nativeerror."(".$thiserror->description."))\n";}
}
return $rv;
}
