• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Daily report of quarantined e-mails in Exchange 2007

Hi, in our office we have a quarantine mailbox where all of our non-rejected spam goes.  Naturally, once in awhile it picks up message that are not spam.

Rather than having everyone check this spam inbox, I was thinking it would be really cool to automate this so that everyone would get a report each day of the messages that were sent to them and quarantined on the previous day.

So basically I'm guessing it would be a powershell script that queries the spam inbox, by date and To: field, and then sends an email to that person, listing the From: field and message subject.

What is the best way to do this?
0
jamorlando
Asked:
jamorlando
  • 7
  • 3
1 Solution
 
Mkris9Commented:
dont think its possible to use powershell to get the list of emails in a mailbox. but saying that i found the following to get the list of flagged emails

http://blogs.technet.com/b/heyscriptingguy/archive/2009/12/14/hey-scripting-guy-december-14-2009.aspx
0
 
jamorlandoAuthor Commented:
I tested this script on my computer and it worked.

So basically, I would need to set up a new machine with Outlook installed and have the default inbox be our spam inbox.  Then I would schedule a nightly powershell script to run.

Before I go further, is this the best way to do this?  Is there free software out there that already handles this?
0
 
Mkris9Commented:
Not that i know of. but can always suggest the best teacher. www.google.com.. :)
0
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
jamorlandoAuthor Commented:
Never heard of it.  I use askjeeves.com (kidding)
0
 
Mkris9Commented:
never heard of that as well.. ;). heard something about someone having a some sort of a search engine called 'ding'.. ;)
0
 
GusGallowsCommented:
I did something similar years ago, with the help of Sue Mosher, using a server side VB script that hit a Public Folder to generate a daily email containing all new messages of the day. I will see if I can find it and convert it to work with a mailbox. No promises, this is from the Exchange 2000 days and much has changed, but it stands to reason that it should still be possible.
0
 
jamorlandoAuthor Commented:
Gus, were you ever able to find that script?
0
 
jamorlandoAuthor Commented:
I set up a PC with Outlook installed with the Spam inbox.  Opened up a new thread in the Outlook forum.
http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26535480.html
0
 
jamorlandoAuthor Commented:
First step ... I am able to export email folders from a date range to a PST file.

Export-Mailbox -Identity SPAM -IncludeFolders '\Inbox\Joe' -StartDate "10/13/2010" -EndDate "10/13/2010" -PSTFolderPath 'C:\Joe Yesterday Spam.pst'

Anyone know how to convert this to a .csv or .txt file so that I can parse it with a script?
0
 
jamorlandoAuthor Commented:
0
 
jamorlandoAuthor Commented:
Actually ... I adandoned PSTViewerPro, because it's buggy.
I buckled down and forced myself to write some powershell.
Here's my script.  (To have this work you need to have message rules set up that puts people's quarantined spam into a subfolder with the inbox)

#Script by Jamie Orlando
#Last updated: 10/25/2010
#
#Recurses through subfolders in Inbox and e-mails each user the headers from their spam messages on the previous day
#
#***   Make sure the following command is entered into PowerShell before proceeding:
#***   Set-ExecutionPolicy RemoteSigned

$olFolderInbox = 6
$ol = new-object -comobject "Outlook.Application"
$mapi = $ol.getnamespace("mapi")
$inbox = $mapi.GetDefaultFolder($olFolderInbox)
$inboxFolders = $inbox.Folders | Select Name
$yesterdayDate = (Get-Date).AddDays(-1).toShortDateString()

foreach ($CurrentFolder in $inboxFolders)
{
   $CurrentRecipient = $CurrentFolder.Name  

   $msgs = $inbox.Folders.Item("$CurrentRecipient")
   $countSpam = 0
   
   foreach ($CurrentMsg in ($msgs.items | Sort-Object CreationTime))
   {
      $CurrentMessageDate = $CurrentMsg.CreationTime.toShortDateString()

      if($CurrentMessageDate -eq $yesterdayDate)
      {
         $CurrentMsg.Body | Out-File -Filepath C:\spambody.txt
         "Time: " + $CurrentMsg.CreationTime.toShortTimeString() | Out-File "C:\spamreport.txt" -Append
         $EmailFrom = select-string -pattern "From: " C:\spambody.txt | Select-Object Line | Select -First 1
         $EmailFrom.Line | Out-File "C:\spamreport.txt" -Append
         $EmailSubject = select-string -pattern "Subject: " C:\spambody.txt | Select-Object Line | Select -First 1
         $EmailSubject.Line | Out-File "C:\spamreport.txt" -Append
         "" | Out-File "C:\spamreport.txt" -Append
         remove-item "c:\spambody.txt"
         $countSpam++
      }
   }

   if($countSpam -ge 1)
   {
      $SendingServer = "EXCHANGE01.ourcompany.com"
      $FromAddress = "SPAM@ourcompany.com"
      $ToAddress = $CurrentRecipient + "@ourcompany.com"
      $MessageSubject = $countSpam + " message(s) were quarantined on: " + $yesterdayDate
      $SpamOutput = Get-Content "c:\spamreport.txt" | Out-String

      $MessageBody = @"
On $yesterdayDate, $countSpam spam messages were quarantined for $CurrentRecipient.
If any of these messages are not spam, please e-mail IT_NOTIFY@ourcompany.com to retrieve the message.
Thank you,
IT Staff
------------
 
$SpamOutput
"@

      $SMTPMessage = New-Object System.Net.Mail.MailMessage $FromAddress, $ToAddress, $MessageSubject, $MessageBody
      $SMTPClient = New-Object System.Net.Mail.SMTPClient $SendingServer
      $SMTPClient.Send($SMTPMessage)

      remove-item "c:\spamreport.txt"
   }
}
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now