I really need to learn perl. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of joe Sent: Tuesday, June 22, 2004 10:06 PM To: [EMAIL PROTECTED] Subject: RE: [ActiveDir] OT: Exchange accounting
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/ 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/