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

0

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?
0
Tony GiangrecoAuthor Commented:
Any ideas on the exclude problem?
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.

Amit KumarCommented:
same way you did it for Inbox folder
0
Amit KumarCommented:
Oh I fogot it... let me check it again
0
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

0
Tony GiangrecoAuthor Commented:
Ok, I'll try it later today. Thanks
0
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

0
QlemoBatchelor, 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

0
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.
0
QlemoBatchelor, 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 ...
0
QlemoBatchelor, 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
0
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...
0
Tony GiangrecoAuthor Commented:
Thanks for the help
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.