need help combine import-csv and get-mailboxfolderstatistics

by stf12 at 2012-09-10 12:47:25

Hi Everyone,

I am relatively new to powershell, and I need some help on combining two one-liners together.

I am trying to output a sorted list of folders within a mailbox. I have 50+ mailboxes and I am trying to automate the task a bit.

my command for a single mailbox (that produce perfect result) looks like that:

Get-MailboxFolderStatistics -id <user alias> | Sort-Object FolderSize -Descending | FT folderpath, foldersize, ItemsinFolder -autosize

that line produce output like that:

FolderPath FolderSize ItemsInFolder
---------- ---------- -------------
/Calendar 31.45 KB (32,209 bytes) 8
/Inbox 15.25 KB (15,616 bytes) 12
/Inbox/Level2/Level3 6.329 KB (6,481 bytes) 2
/Contacts 552 B (552 bytes) 2
/Top of Information Store 156 B (156 bytes) 1
/Inbox/Level2 152 B (152 bytes) 1
/Sent Items 148 B (148 bytes) 1
/Junk E-Mail 148 B (148 bytes) 1
/Deleted Items 148 B (148 bytes) 1
/Tasks 0 B (0 bytes) 0
/Suggested Contacts 0 B (0 bytes) 0
/Conversation Action Settings 0 B (0 bytes) 0
/Recoverable Items 0 B (0 bytes) 0
/Purges 0 B (0 bytes) 0
/Deletions 0 B (0 bytes) 0
/Versions 0 B (0 bytes) 0
/RSS Feeds 0 B (0 bytes) 0
/Journal 0 B (0 bytes) 0
/Deleted Items/__IPSpam 0 B (0 bytes) 0
/Drafts 0 B (0 bytes) 0
/News Feed 0 B (0 bytes) 0
/Quick Step Settings 0 B (0 bytes) 0
/Outbox 0 B (0 bytes) 0
/Notes 0 B (0 bytes) 0




so I created a single column CSV file and use the following command:

Import-Csv lmusers.csv |ForEach-Object{Get-MailboxFolderStatistics -id $.Name}

That command works providing a multipage output for each mailbox with all the folders details.

example of output for a single folder:

RunspaceId : b267e055-a258-40d2-8c57-05d43fb9ce1e
Date : 4/4/2012 4:02:01 PM
Name : Sent Items
FolderPath : /Sent Items
FolderId : LgAAAAAthp5ESjIlTaQ4E+POU8wEAQBVwzT2jJ4CRbBn9mnn5Cn3AAAAvCc5AAAB
FolderType : SentItems
ItemsInFolder : 1
DeletedItemsInFolder : 0
FolderSize : 148 B (148 bytes)
ItemsInFolderAndSubfolders : 1
DeletedItemsInFolderAndSubfolders : 0
FolderAndSubfolderSize : 148 B (148 bytes)
OldestItemReceivedDate :
NewestItemReceivedDate :
OldestDeletedItemReceivedDate :
NewestDeletedItemReceivedDate :
OldestItemLastModifiedDate :
NewestItemLastModifiedDate :
OldestDeletedItemLastModifiedDate :
NewestDeletedItemLastModifiedDate :
ManagedFolder :
TopSubject :
TopSubjectSize : 0 B (0 bytes)
TopSubjectCount : 0
TopSubjectClass :
TopSubjectPath :
TopSubjectReceivedTime :
TopSubjectFrom :
TopClientInfoForSubject :
TopClientInfoCountForSubject : 0
SearchFolders : {To-Do Search, Unread Mail, Reminders, AllItems, Tracked Mail Processing, NoArchive
TagSearchFolder8534F96D-4183-41fb-8A05-9B7112AE2100}
Identity : xxxxxxxxx\Sent Items
IsValid : True



how should I construct my one line script to produce output like in the first example, but for every user in my CSV file?

helpful pointers are greatly appreciated.

Thanks.
by poshoholic at 2012-09-10 14:33:53
You can simply tack your Sort-Object and Format-Table section of the pipeline on the end of your Import-Csv to ForEach-Object pipeline, like this:

Import-Csv lmusers.csv | ForEach-Object {Get-MailboxFolderStatistics -id $
.Name} | Sort-Object FolderSize -Descending | FT folderpath, foldersize, ItemsinFolder -autosize
That should give you the tabular output you want for all users in your csv file, however you won’t be able to identify at a glance which is which. I recommend adding the name back on to the object you output, and then grouping by name in your table, which requires the use of Add-Member as well as the -GroupBy parameter of Format-Table. That command would look like this:

Import-Csv lmusers.csv | ForEach-Object {Get-MailboxFolderStatistics -id $.Name | Add-Member -Name MailboxName -MemberType NoteProperty -Value $.Name -PassThru} | Sort-Object MailboxName,FolderSize -Descending | FT folderpath, foldersize, ItemsinFolder -GroupBy MailboxName -autosize
If you actually just want to see the largest mailbox folders across all mailboxes while also identifying which users they belong to, you could skip the grouping and sorting change and just output MailboxName as a property, like this:

Import-Csv lmusers.csv | ForEach-Object {Get-MailboxFolderStatistics -id $.Name | Add-Member -Name MailboxName -MemberType NoteProperty -Value $.Name -PassThru} | Sort-Object FolderSize -Descending | FT mailboxName, folderpath, foldersize, ItemsinFolder -autosize

Note: I didn’t test any of these one-liners, so there may be a typo or other minor error in them.
by stf12 at 2012-09-11 07:56:50
Thank you. It works great