Start Free Trial

asked on

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

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

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

ASKER

@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?

ASKER

@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?

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

ASKER

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?

ASKER

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?

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

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

ASKER

Thanks for working on this, Soostibi. With this include only version, could I include both inbox and sentItmes with all their subfolders?

ASKER

How would that work?

In line 2 just enumerate the folders requested:

$typestoinclude = "Inbox", "SentItems"

ASKER

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).

ASKER

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).

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?

ASKER

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.

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

ASKER

This is good, but how can we get it to print to a csv file?

ASKER

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?

ASKER

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.

I'm a little busy these days, so I'll give a solution this evening (UTC+2).

ASKER

Thank you again.

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

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

ASKER

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

ASKER

The first script works perfectly though! :)

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

ASKER

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

ASKER

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!

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

ASKER

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 (https://www.experts-exchange.com/questions/26988990/An-iteration-to-the-question-How-do-I-omit-specific-mailbox-folders-from-being-searched-in-PowerShell-when-running-a-script-to-pull-mailbox-and-mailbox-folder-information.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

ASKER

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)

Sorry, the "M" is capital:

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

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?