[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I omit specific mailbox folders from being searched in PowerShell when running a script to pull mailbox and mailbox folder information?

Posted on 2011-04-25
35
Medium Priority
?
1,612 Views
Last Modified: 2013-06-19
I am running a PowerShell script to get mailbox information from our Exchange server, which outputs the users name email address, total items, retention policy, newest item and oldest item.  I have a script that does this (below).  However, I would like to omit the following folders from being included in the search: Calendar, Tasks, Contacts, and Notes.  All other folders in the mailbox, whether created manually by the end-user or not, should be searched.  

I see a lot of info on how to search specific folders with the –FolderScope switch but nothing on how to omit specific folders.  How can this be done?  

Thanks in advance!


$mbxs = Get-Mailbox
$mbxs | %{
    $newest = $oldest = $null
    $mb = $_
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems | %{
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){
            $newest = $_.NewestItemReceivedDate.tolocaltime()}
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){
                $oldest = $_.OldestItemReceivedDate.tolocaltime()}
    }
    $stat = $mb | Get-MailboxStatistics
    New-Object -TypeName psobject -Property @{
        DisplayName = $mb.displayname
        SMTPAddress = $mb.PrimarySMTPAddress.tostring()
        TotalItems = $stat.itemcount
        RetentionPolicy = $mb.retentionpolicy
        NewestItem = $newest
        OldestItem = $oldest      
    }
} | Select-Object -Property DisplayName, SMTPAddress, TotalItems, RetentionPolicy, NewestItem, OldestItem |
    Export-Csv C:\emailRetentionToLocalTime.csv –NoTypeInformation



0
Comment
Question by:TigerBlood
  • 20
  • 14
35 Comments
 
LVL 13

Expert Comment

by:soostibi
ID: 35464861
Here you are:
$mbxs = Get-Mailbox 
$typestoexclude = "Calendar", "Tasks", "Contacts", "Notes"
$mbxs | %{
    $newest = $oldest = $null
    $mb = $_
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems | ?{$typestoexclude -notcontains $_.foldertype} | %{
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){
            $newest = $_.NewestItemReceivedDate.tolocaltime()}
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){
                $oldest = $_.OldestItemReceivedDate.tolocaltime()}
    }
    $stat = $mb | Get-MailboxStatistics
    New-Object -TypeName psobject -Property @{
        DisplayName = $mb.displayname
        SMTPAddress = $mb.PrimarySMTPAddress.tostring()
        TotalItems = $stat.itemcount
        RetentionPolicy = $mb.retentionpolicy
        NewestItem = $newest
        OldestItem = $oldest      
    }
} | Select-Object -Property DisplayName, SMTPAddress, TotalItems, RetentionPolicy, NewestItem, OldestItem  | 
    Export-Csv C:\emailRetentionToLocalTime.csv –NoTypeInformation

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35468896
@Soostibi: So, besides the folders ilisted contained in $typestoexclude, does this scan all other folders in the mailbox, including those manually created by the user?

What would the script look like if I wanted to do the inverse of the above (i.e. $typestoinclude ["Calendar", "Tasks", "Contacts", "Notes"]) and all other folders wer omitted from the search?

0
 

Author Comment

by:TigerBlood
ID: 35468938
@Soostibi: In addition to my first question above:

//--//
So, besides the folders ilisted contained in $typestoexclude, does this scan all other folders in the mailbox, including those manually created by the user?//--//

Are subfolders under Inbox, etc. included in the search?  If not, how could we add that so that subfolders under Inbox, etc., are searched too?
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
LVL 13

Expert Comment

by:soostibi
ID: 35469599
Subfolders are included by default.
inverse: replace 'notcontains' with 'contains' in line 6.
0
 

Author Comment

by:TigerBlood
ID: 35469801
ah... I actually just ran a test by putting in 'contains' right before you replied :)  . That worked.

With using 'contains', is there a way to have it include subfolders too?  Say, I want the search to contain 'Inbox', how would I get the sript to also include subfolders under Inbox?
0
 

Author Comment

by:TigerBlood
ID: 35470573
One of the issues I have encountered is that the oldestItem in my mailbox was reported as 8/3/2008, yet my mailbox was not created until 10/1/2010.  This turned out to be an item in the Notes folder, that was forwarded to me and that I had saved.  It would appear that the original creation date of this item retained its date value.  Is there another date field that holds the new creation date? If so, how can we call on this date value?
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35470576
Unfortunately get-mailboxfolderstatistics does not have a 'recurse' switch, so it's not easy to set that. You have to get all folder data and exlude those, which are not a subfolder of the inbox. Not very efficient, but here is my try. In the first few line you can set the parameters.
Unfortunately it is not easy to combine the two solutions (exclude version and include version), so this is an include only version.
$mbxs = Get-Mailbox 
$typestoinclude = "Inbox"
$recurse = $true

filter get-folders ($roottypes, [switch] $recurse){
$mb = $_
$roottypes | %{
$type = $_
$root = $mb | Get-MailboxFolderStatistics -FolderScope $type -IncludeOldestAndNewestItems |?{$_.foldertype -eq $type} 
if($recurse){
    $mb | get-mailboxfolderstatistics -IncludeOldestAndNewestItems |  ?{$_.folderpath -match "^$($root.folderpath)"} 
}
else {$root}
}
}

$mbxs | %{
    $newest = $oldest = $null
    $mb = $_
    $mb | Get-folders $typestoinclude -recurse:$recurse | %{
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){
            $newest = $_.NewestItemReceivedDate.tolocaltime()}
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){
                $oldest = $_.OldestItemReceivedDate.tolocaltime()}
    }
    $stat = $mb | Get-MailboxStatistics
    New-Object -TypeName psobject -Property @{
        DisplayName = $mb.displayname
        SMTPAddress = $mb.PrimarySMTPAddress.tostring()
        TotalItems = $stat.itemcount
        RetentionPolicy = $mb.retentionpolicy
        NewestItem = $newest
        OldestItem = $oldest      
    }
} | Select-Object -Property DisplayName, SMTPAddress, TotalItems, RetentionPolicy, NewestItem, OldestItem

Open in new window

0
 
LVL 13

Expert Comment

by:soostibi
ID: 35470595
I do not know about other date fields on Note items... Especially what can be queried by Get-MailboxFolderStatistics.
0
 

Author Comment

by:TigerBlood
ID: 35470642
Thanks for working on this, Soostibi.  With this include only version, could I include both inbox and sentItmes with all their subfolders?
0
 

Author Comment

by:TigerBlood
ID: 35470665
How would that work?
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35471021
In line 2 just enumerate the folders requested:

$typestoinclude = "Inbox", "SentItems"
0
 

Author Comment

by:TigerBlood
ID: 35471223
Awesome, Soostibi.  This

I have one last question.  How would I have the script list a new row for each folder/ subfolder that is searched?  Each row should contain the following info (DisplayName, SMTPAddress, Folder Name, TotalItems, RetentionPolicy, NewestItem, OldestItem).

0
 

Author Comment

by:TigerBlood
ID: 35471232
This will allow me to identify the location of items that might raise question marks when reviewing the output from the script (e.g. items older than when the mailbox was created).
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35478368
Could you define your request in more detail? Would you like to see a row for every folder in the output? Or just two rows: one for the oldest, one for the newest item?
0
 

Author Comment

by:TigerBlood
ID: 35478600
If there can be a row for each folder in each mailbox that gives both the oldest and newest items that would be great.
The displayname, smtp address, foldername, and total numer of item per folder for each mailbox  should also be listed for each row too.
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35478788
What about this?
$mbx = Get-Mailbox 
$mbx | %{
    $mb = $_
    $stat = $mb | Get-MailboxStatistics
    $mb | Get-MailboxFolderStatistics -FolderScope $type -IncludeOldestAndNewestItems | %{
        New-Object -TypeName PSObject -Property @{
            DisplayName = $mb.displayname
            SMTPAddress = $mb.PrimarySMTPAddress.tostring()
            FolderName = $_.name
            ItemsInFolder = $_.itemsinfolder
            RetentionPolicy = $mb.retentionpolicy
            NewestItem = $_.NewestItemReceivedDate
            OldestItem = $_.OldestItemReceivedDate
            FolderType = $_.foldertype    
        }
    }
} | Select-Object displayname, smtpaddress, RetentionPolicy, foldername, ItemsInFolder, newestitem, oldestitem, foldertype | ft

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35478954
This is good, but how can we get it to print to a csv file?
0
 

Author Comment

by:TigerBlood
ID: 35480912
Disregard my last comment - I was missing a ' | '.

The above script you wrote lays out each folder in the mailbox perfectly and has helped me identify the folder type field value "User Created" - this was the folderType name I was looking for but didn't know its name before now.

Can your script be tweaked so that the output csv file can contain:
"DisplayName", "SMTPAddress", "Retention Policy", FolderName", "ItemsInFolder", "NewestItem, "OldestItem", and “Folder Type” for only the folder types: Inbox, Sent Items and User Created folders?

If the above can be done can the script:
1. Shows a row for every folder in the output
And a separate script that can:
2. Show 2 rows for each mailbox with the overall newestItem and overall oldestItem?
0
 

Author Comment

by:TigerBlood
ID: 35488631
Thanks again for helping with this.  

Please let me know if I should post this in a separate question.  I figured it makes sense to ask my last question here as it refers to the same script.  Your help with this is greatly appreciated, Soostibi.
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35489982
I'm a little busy these days, so I'll give a solution this evening (UTC+2).
0
 

Author Comment

by:TigerBlood
ID: 35490812
Thank you again.
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35494844
First script:
$mbx = Get-Mailbox
$types = "Inbox", "SentItems", "User Created"
$mbx | %{  
    $mb = $_  
    $stat = $mb | Get-MailboxStatistics 
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems | ?{$types -contains $_.foldertype}| %{  
        New-Object -TypeName PSObject -Property @{  
            DisplayName = $mb.displayname  
            SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
            FolderName = $_.name  
            ItemsInFolder = $_.itemsinfolder  
            RetentionPolicy = $mb.retentionpolicy  
            NewestItem = $_.NewestItemReceivedDate  
            OldestItem = $_.OldestItemReceivedDate  
            FolderType = $_.foldertype      
        }  
    }  
} | Select-Object displayname, smtpaddress, RetentionPolicy, foldername, ItemsInFolder, newestitem, oldestitem, foldertype | ft

Open in new window

0
 
LVL 13

Expert Comment

by:soostibi
ID: 35494939
Hope this is the second:

$mbxs = Get-Mailbox
$mbxs | %{  
    $newest = $oldest = $null  
    $newestrow = $oldesrow = $null
    $mb = $_  
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems |  %{  
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){  
            $newest = $_.NewestItemReceivedDate.tolocaltime()
            $newestrow = $_ | Add-Member -Name New -MemberType noteproperty -Value "newest" -PassThru
        }  
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){  
            $oldest = $_.OldestItemReceivedDate.tolocaltime()
            $oldestrow = $_ | Add-Member -Name Old -MemberType noteproperty -Value "oldest" -PassThru
        }  
    }  
    $newestrow,$oldestrow | %{
        New-Object -TypeName psobject -Property @{  
            DisplayName = $mb.displayname  
            SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
            RetentionPolicy = $mb.retentionpolicy  
            FolderName = $_.name
            ItemsInFolder = $_.itemsinfolder
            NewestItem = if($_.new -eq "newest"){$newest};
            OldestItem = if($_.old -eq "oldest"){$oldest};
            FolderType = $_.foldertype
        }      
    }  
} | Select-Object -Property DisplayName, SMTPAddress, RetentionPolicy, FolderName, ItemsInFolder, NewestItem, OldestItem, FolderType  |   
        ft

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35495222
Can the second script be restricted to only search Folder Types "Inbox", "SentItems", and "User Created"?  If it can do that, then job done!
0
 

Author Comment

by:TigerBlood
ID: 35495238
The first script works perfectly though! :)
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35496793
Filtered version of the second.
$mbxs = Get-Mailbox
$types = "Inbox", "SentItems", "User Created"
$mbxs | %{  
    $newest = $oldest = $null  
    $newestrow = $oldesrow = $null
    $mb = $_  
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems | ?{$types -contains $_.foldertype} |%{  
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){  
            $newest = $_.NewestItemReceivedDate.tolocaltime()
            $newestrow = $_ | Add-Member -Name New -MemberType noteproperty -Value "newest" -PassThru
        }  
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){  
            $oldest = $_.OldestItemReceivedDate.tolocaltime()
            $oldestrow = $_ | Add-Member -Name Old -MemberType noteproperty -Value "oldest" -PassThru
        }  
    }  
    $newestrow,$oldestrow | %{
        New-Object -TypeName psobject -Property @{  
            DisplayName = $mb.displayname  
            SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
            RetentionPolicy = $mb.retentionpolicy  
            FolderName = $_.name
            ItemsInFolder = $_.itemsinfolder
            NewestItem = if($_.new -eq "newest"){$newest};
            OldestItem = if($_.old -eq "oldest"){$oldest};
            FolderType = $_.foldertype
        }      
    }  
} | Select-Object -Property DisplayName, SMTPAddress, RetentionPolicy, FolderName, ItemsInFolder, NewestItem, OldestItem, FolderType  |   
        ft

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35497413
Hey soostibi, we are so close with this now.  I have attached a snippet from the .csv file output (Note: I changed the display names and smtp addresses to generic values for security reasons)

Just couple of things:

1. For mailboxes where both the oldest and newest items reside in the same folder (say Inbox), both rows for that user are populated with an identical entry.  Can PowerShell spot this and purge the duplicate row?  We have quite a few users that fall into this category (User1, user2 and user3 are a few examples in the attachment)

2. For some mailboxes the script is reporting a value for newest (or oldest) item twice (in both rows for that user).   I went into some of these mailboxes to spot check.  Let’s use user11 as an example from my attachment.  The oldest item in user11’s mailbox is in the “Important Notification’s” user created folder (not the Inbox).  The oldest item in the Inbox was actually 6/1/2009.  (User12, user13, user14, user15 and user20 are other examples in the attachment).  Is looks like the script is carrying over the value for the newest item (or oldest item) to the second row of that user’s mailbox and reporting it in the csv file.  Is there a way to stop that so that it is recorded only once?

All other user’s info is pulling as it should (i.e. 1 entry for newest item and 1 entry for oldest item).  User9 and User10 are examples of how each mailbox should be reported (in an ideal world! :))

Thanks again soostibi!
 emailRetentionOutput.csv
0
 
LVL 13

Accepted Solution

by:
soostibi earned 2000 total points
ID: 35500881
This is a corrected one for the second.
$mbxs = Get-Mailbox 
$types = "Inbox", "SentItems", "User Created"
$mbxs | %{  
    $newest = $oldest = $null  
    $newestrow = $oldestrow = $null
    $mb = $_  
    $mb | Get-MailboxFolderStatistics -IncludeOldestAndNewestItems | ?{$types -contains $_.foldertype} |%{  
        if($_.NewestItemReceivedDate -and (!$newest -or $newest -lt $_.NewestItemReceivedDate.tolocaltime())){  
            $newest = $_.NewestItemReceivedDate.tolocaltime()
            $newestrow = $_ 
        }  
        $dummy = 1
        if($_.OldestItemReceivedDate -and (!$oldest -or $oldest -gt $_.OldestItemReceivedDate.tolocaltime())){  
            $oldest = $_.OldestItemReceivedDate.tolocaltime()
            $oldestrow = $_ 
        }
    }  
    if($newestrow -and $oldestrow -and $newestrow.identity.tostring() -eq  $oldestrow.identity.tostring()){
        New-Object -TypeName psobject -Property @{  
            DisplayName = $mb.displayname  
            SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
            RetentionPolicy = $mb.retentionpolicy  
            FolderName = $newestrow.name
            ItemsInFolder = $newestrow.itemsinfolder
            NewestItem = $newest
            OldestItem = $oldest
            FolderType = $newestrow.foldertype
        }      
    }
    else{
        if($newestrow){
            New-Object -TypeName psobject -Property @{  
                DisplayName = $mb.displayname  
                SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
                RetentionPolicy = $mb.retentionpolicy  
                FolderName = $newestrow.name
                ItemsInFolder = $newestrow.itemsinfolder
                NewestItem = $newest
                OldestItem = $null
                FolderType = $newestrow.foldertype
            }
        }
        if($oldestrow){      
            New-Object -TypeName psobject -Property @{  
                DisplayName = $mb.displayname  
                SMTPAddress = $mb.PrimarySMTPAddress.tostring()  
                RetentionPolicy = $mb.retentionpolicy  
                FolderName = $oldestrow.name
                ItemsInFolder = $oldestrow.itemsinfolder
                NewestItem = $null
                OldestItem = $oldest
                FolderType = $oldestrow.foldertype
            }      
        }
    }
} | Select-Object -Property DisplayName, SMTPAddress, RetentionPolicy, FolderName, ItemsInFolder, NewestItem, OldestItem, FolderType  |   
        ft

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35504955
Soostibi, you are without doubt the guru when it comes to PowerShell!!  This works like a charm.  Thanks!!

One last thing I was wondering was if the csv output file can pull the system date and append it to the filename, something like: yyyy-mm-dd-fileName.csv

0
 

Author Closing Comment

by:TigerBlood
ID: 35505012
This works like a charm!  Soostibi stuck with this all the way and as we discovered we needed to slightly tweak this to get to the finishing line.  This is a fantastic PowerShell script that produces a report that a compliance dept. might request for validation that Exchange 2011 retention policies are actually working as expected.  The report essentially allows you to spot check any mailbox on your Exchange 2010 server for it's oldest email item and compare it to the Retention Policy that is applied to the mailbox.

If you require a sophisticated PowerShell script be created, I highly recommend, Soostibi!
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35505185
For csv output replace the 'ft' in line 57 to:


Export-Csv -Path "c:\yourpath\$(get-date -f yyyy-mm-dd)-fileName.csv" -NoTypeInformation

Open in new window

0
 

Author Comment

by:TigerBlood
ID: 35505290
Perfect!  

I also have one further iteration to this script I am trying to complete.  I actually posted a new question for it but I will post the question here too (http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_26988990.html):

-----------------------------------------------
I would still like to keep the mailbox seach restricted to Inbox, SentItems and UserCreated.  The change I am looking to make would be to what is outputted to the csv file and keeping the info per mailbox to one line only.

Can we have one 1 line per user still catching the same info (not including the folder type column)?  The csv output file should look like this:

displayName, SMTPAddress, RetentionPolicy, ItemsInMailbox, NewestItem, NewestItemFolder, OldestItem, OldestItemFolder
0
 

Author Comment

by:TigerBlood
ID: 35505755
Strange, the output filename for the dated csv file came out as:

2011-34-02-fileName

after a little investigation, I saw the time when I executed this was 11:34am - the "mm" grabs the minutes from the time.  For month we need to use "MM".  Year and day stays lowercase (otherwise they spit out as YYYY and DD respectively)
0
 
LVL 13

Expert Comment

by:soostibi
ID: 35505870
Sorry, the "M" is capital:

Export-Csv -Path "c:\yourpath\$(get-date -f yyyy-MM-dd)-fileName.csv" -NoTypeInformation
0
 

Expert Comment

by:ITMASTER68
ID: 39260577
this was just what I needed thanks, I do have a question.  Can this script be tweaked to not only search a users main mailbox, but also there Online archive?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If something goes wrong with Exchange, your IT resources are in trouble.All Exchange server migration processes are not designed to be identical and though migrating email from on-premises Exchange mailbox to Cloud’s Office 365 is relatively simple…
Stellar Exchange Toolkit: this 5 in 1 toolkit comes loaded with mega-software tool. Here’s an introduction to tools’ usage and advantages:
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question