Exchange 2007 Report from PowerShell Script problem

I have this script running on my Windows 2008 r2 Exchange 2007 server.

$exclude = GC C:\PS-Scripts\exclude.txt
$mailboxes  = Get-Mailbox -ResultSize Unlimited | ?{$exclude -notcontains $_.samaccountname}

$exclude = GC C:\PS-Scripts\exclude.txt
$mailboxes  = Get-Mailbox -ResultSize Unlimited | ?{$exclude -notcontains $_.samaccountname}
$report = @()
foreach ($mailbox in $mailboxes)
{
    $inboxstats = Get-MailboxFolderStatistics $mailbox -FolderScope Inbox | Where {$_.FolderPath -eq "/Inbox"}

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Size (Mb)" -Value $inboxstats.FolderandSubFolderSize.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Items" -Value $inboxstats.ItemsinFolder
    $report += $mbObj
}

I'm having these problems:
1. When i paste the script in powershell, do a select All, Copy & Paste into notepad, the report width is very wide and it wraps.
2. I need to capture the output in both CSV and as a regular text file that open in notepad.
3. The exclude option doesn't work. I have two text files I've tried (one with employees) and (One with email addresses) in the following format and nothing works.

joe@me.com
Pete@me,com
You@me.com

Joe Peterson
Mary Peterson
Al Peterson

Your suggestions are appreciated.
LVL 25
Tony GiangrecoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amit KumarCommented:
Please try below mentioned code, it will export all data in csv and txt both:

$exclude = GC C:\PS-Scripts\exclude.txt
$mailboxes  = Get-Mailbox -ResultSize Unlimited | ?{$exclude -notcontains $_.samaccountname}

$exclude = GC C:\PS-Scripts\exclude.txt
$mailboxes  = Get-Mailbox -ResultSize Unlimited | ?{$exclude -notcontains $_.samaccountname}
$report = @()
foreach ($mailbox in $mailboxes)
{
    $inboxstats = Get-MailboxFolderStatistics $mailbox -FolderScope Inbox | Where {$_.FolderPath -eq "/Inbox"}

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Size (Mb)" -Value $inboxstats.FolderandSubFolderSize.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Items" -Value $inboxstats.ItemsinFolder
    $report = $report += $mbObj
# new changes to export variable report in csv and txt, please mention path if required.
    $report | export-csv -path mbxstat.csv
    $report | output-file -path mbxstat.txt
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tony GiangrecoAuthor Commented:
I'll try it later when I get to the office.

Is there anyway to add  (count & folder size) Sent Items and Deleted Items to the report?
Tony GiangrecoAuthor Commented:
Any ideas on the exclude problem?
Check Out How Miercom Evaluates Wi-Fi Security!

It's not just about Wi-Fi connectivity anymore. A wireless security breach can cost your business large amounts of time, trouble, and expense. Plus, hear first-hand from Miercom on how WatchGuard's Wi-Fi security stacks up against the competition plus a LIVE demo!

Amit KumarCommented:
same way you did it for Inbox folder
Amit KumarCommented:
Oh I fogot it... let me check it again
Amit KumarCommented:
Try below one, and prepare csv file rather txt file for importing excluding data. Also use only e-mail address with no header.  Put only e-mail address in csv file

$exclude = GC C:\PS-Scripts\exclude.csv
$mailboxes  = Get-Mailbox -ResultSize Unlimited | where-object {$_.PrimarySMTPAddress -ne $exclude}

$report = @()
foreach ($mailbox in $mailboxes)
{
    $inboxstats = Get-MailboxFolderStatistics $mailbox -FolderScope Inbox | Where {$_.FolderPath -eq "/Inbox"}

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Size (Mb)" -Value $inboxstats.FolderandSubFolderSize.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Items" -Value $inboxstats.ItemsinFolder
    $report = $report += $mbObj
    $report | export-csv -path mbxstat.csv
    $report | output-file -path mbxstat.txt
}

Open in new window

Tony GiangrecoAuthor Commented:
Ok, I'll try it later today. Thanks
Amit KumarCommented:
Find updated one with sentitems and deleted items and item count as well.

Be remember to prepare csv file with no header and e-mail address only.

$exclude = GC C:\PS-Scripts\exclude.csv
$mailboxes  = Get-Mailbox -ResultSize Unlimited | where-object {$_.PrimarySMTPAddress -ne $exclude}

$report = @()
foreach ($mailbox in $mailboxes)
{
    $inboxstats = Get-MailboxFolderStatistics $mailbox -FolderScope Inbox | Select-Object FolderandSubFolderSize, ItemsinFolder
    $sentstats = Get-MailboxFolderStatistics $mailbox -FolderScope SentItems | Select-Object FolderandSubFolderSize, ItemsinFolder
    $deletedstats = Get-MailboxFolderStatistics $mailbox -FolderScope DeletedItems | Select-Object FolderandSubFolderSize, ItemsinFolder

    $mbObj = New-Object PSObject
    $mbObj | Add-Member -MemberType NoteProperty -Name "Display Name" -Value $mailbox.DisplayName
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Size (Mb)" -Value $inboxstats.FolderandSubFolderSize.value.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "SentItem Size (Mb)" -Value $sentstats.FolderandSubFolderSize.value.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "DeletedItem Size (Mb)" -Value $deletedstats.FolderandSubFolderSize.value.ToMB()
    $mbObj | Add-Member -MemberType NoteProperty -Name "Inbox Items" -Value $inboxstats.ItemsinFolder
    $mbObj | Add-Member -MemberType NoteProperty -Name "Sent Items" -Value $sentstats.ItemsinFolder
    $mbObj | Add-Member -MemberType NoteProperty -Name "Deleted Items" -Value $deletedstats.ItemsinFolder
    $report = $report += $mbObj
    $report | export-csv -path mbxstat.csv -nti
    $report | output-file -path mbxstat.txt -append 
}

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Your code repeats the first two lines, but that might be a paste error here.
Your excludes do not work because you compare apples and pears. A SamAccountName is just the username, e.g. "joe". Either change your exclude file to only list user names, or switch to checking UserPrincipalName.

Amit's suggestion is "wrong", as it overwrites the files with the accumulated result each time. The result is ok, but the way is not.

On another note: Avoid using variables if the operation can get lengthy, and a lot of objects might get returned, as with Get-Mailbox. Better to use a pipe. And better to use a hash table to build custom objects.
$exclude = GC C:\PS-Scripts\exclude.txt
Get-Mailbox -ResultSize Unlimited |
  ? {$exclude -notcontains $_.UserPrincipalName} |
  % {
    $mailbox = $_
    $inboxstats = Get-MailboxFolderStatistics $mailbox -FolderScope Inbox | ? {$_.FolderPath -eq "/Inbox"}

    New-Object PsObject -Property @{
      'Display Name'    = $mailbox.DisplayName
      'Inbox Size (Mb)' = $inboxstats.FolderandSubFolderSize.ToMB()
      'Inbox Items'     = $inboxstats.ItemsinFolder
    }
  } | set-variable $report

$report | export-csv -NoType mbxstats.csv
$report | format-table -auto | Out-File mbxstats.txt

Open in new window

Amit KumarCommented:
CSV file won't overwrite but yes txt file will but i also mentioned append after editing my comment in last... E-mail address also can be used to exclude users still whatever works. I wish either ur or mine whichever script work for user I am happy.. at least i got to learn smthing thanks anyways.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Including deleted and sent is very similar in my code snippet, but I like it more flexible:
$exclude = GC C:\PS-Scripts\exclude.txt
Get-Mailbox -ResultSize Unlimited |
  ? {$exclude -notcontains $_.UserPrincipalName} |
  % {
    $mailbox = $_
    foreach ($folder in 'Inbox', 'SentItems', 'DeletedItems')
    {
      Get-MailboxFolderStatistics $mailbox -FolderScope $folder | 
        ? { $_.FolderPath -eq "/$folder"} |
        Set-Variable $folder+"stats"
    }
    New-Object PsObject -Property @{
      'Display Name'      = $mailbox.DisplayName
      'Inbox Items'       = $inboxstats.ItemsinFolder
      'Inbox Size (Mb)'   = $inboxstats.FolderandSubFolderSize.ToMB()
      'Sent Items'        = $sentItemsstats.ItemsInFolder
      'Sent Size (Mb)'    = $sentItemsstats.FolderandSubFolderSize.ToMB()
      'Deleted Items'     = $deletedItemsStats.ItemsInFolder
      'Deleted Size (Mb)' = $deletedItemsStats.FolderandSubFolderSize.ToMB()
    }
  } | set-variable $report

$report | export-csv -NoType mbxstats.csv
$report | format-table -auto | Out-File mbxstats.txt

Open in new window

Still could be improved ...
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Amit, the CSV file will also get overwritten each time. Your last change makes the text file worse, as it appends the cummulated output (because of the -append). Both exports should just be moved out of the loop. And this does not make much sense:
 $report = $report += $mbObj
you assign the the result of append data to $result to $result ;-). It should just be
 $report += $mbObj
Amit KumarCommented:
May be but i made couple of scripts in my env and i got expected results.. Still i will try your suggestion tooo...
Tony GiangrecoAuthor Commented:
Thanks for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Exchange

From novice to tech pro — start learning today.