none
Department in powershell script RRS feed

  • Question

  • Hi all!

    I've complied this script (below) and it basically lists all of my mailboxes above a certain size, and sorts them by the largest mailbox.

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB -and $_.Department -eq ""} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, Department

    In my output table, the column department does show, but doesn't pull through the actual AD department field.

    Is there anything obvious I'm missing? Any help greatly appreciated!

    Wednesday, March 2, 2016 4:58 PM

Answers

  • ok so your script:

    get-user |Select DisplayName,Department,@{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} | Sort TotalItemSize

    works well now that I'm running it correctly. it doesn't however only look at mailboxes larger than 250mb which is what I was trying to achieve

    • Marked as answer by MartinBUK Wednesday, March 2, 2016 9:55 PM
    Wednesday, March 2, 2016 7:50 PM

All replies

  • Your Where-Object filter specifies that you want a blank department property.

    -- Bill Stewart [Bill_Stewart]

    Wednesday, March 2, 2016 5:02 PM
    Moderator
  • I thought that was maybe the case, but couldn't see how to include all departments.
    Wednesday, March 2, 2016 5:03 PM
  • I don't believe the Get-Mailbox cmdlet exposes the department attribute.


    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, March 2, 2016 5:07 PM
    Moderator
  • If I run

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, Department

    I get all I want, minus anything actually showing in the department column, and unsure how to make that appear.

    Thanks


    • Edited by MartinBUK Wednesday, March 2, 2016 5:12 PM
    Wednesday, March 2, 2016 5:10 PM
  • Good Day

    Is the Department column a property or an object attribute?
    Regards

    Wednesday, March 2, 2016 5:15 PM
  • honestly ive no idea. ive just pulled this script together based on a few others
    Wednesday, March 2, 2016 5:19 PM
  • Just to confirm 
    Like Richard Mueller said the Get-Mailbox cmdlet doesnt have the Department property
    So in order to get the user departement you will have to use the AD module
    Regards
    Wednesday, March 2, 2016 5:26 PM
  • I believe, the department property is an attribute of the corresponding AD user object, not the mailbox. So, as Alvaro suggests, you would need to use something like Get-ADUser to retrieve the value for each user.

    Richard Mueller - MVP Enterprise Mobility (Identity and Access)

    Wednesday, March 2, 2016 5:28 PM
    Moderator
  • You can get the department information from Get-User if you don't have the AD module available on your Exchange servers.

    Wednesday, March 2, 2016 5:29 PM
  • so what code do I used to get it into the script?
    Wednesday, March 2, 2016 5:36 PM
  • get-user|
    	Select DisplayName,Department,
            @{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}}


    \_(ツ)_/


    • Edited by jrv Wednesday, March 2, 2016 6:13 PM
    Wednesday, March 2, 2016 5:39 PM
  • so?

    get-mailbox -resultsize unlimited | get-user| Select DisplayName,Department, @{n='TotalItemSize';e={($_|Get-aMilboxStatistics).totalitemsize}} | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, Department

    does that look right?

    Wednesday, March 2, 2016 5:43 PM
  • so?

    get-mailbox -resultsize unlimited | get-user| Select DisplayName,Department, @{n='TotalItemSize';e={($_|Get-aMilboxStatistics).totalitemsize}} | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, Department

    does that look right?

    No.  I just posted the answer just add the sort to the end:

    get-user|
    Select DisplayName,Department,
           
    @{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} |
    Sort TotalItemSize


    \_(ツ)_/


    • Edited by jrv Wednesday, March 2, 2016 6:13 PM
    Wednesday, March 2, 2016 6:00 PM
  • Just add the missing "M" in the Get-MailboxStatics to the script above and you are all set
    Wednesday, March 2, 2016 6:11 PM
  • Just add the missing "M" in the Get-MailboxStatics to the script above and you are all set

    Thanks - I fail to understand how pasting tested code can lose a character when pasted???

    \_(ツ)_/

    Wednesday, March 2, 2016 6:13 PM
  • sorry guys, I'm even more confused now.

    do I not need my script at all, and just use the one you provided?

    Wednesday, March 2, 2016 6:16 PM
  • Did you even try it?

    \_(ツ)_/

    Wednesday, March 2, 2016 6:18 PM
  • Yup, ive ran this

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | get-user|Select DisplayName,Department,@{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} Where {$_.TotalItemSize -gt 250MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize

    and now I get an error about the where command

    Wednesday, March 2, 2016 6:56 PM
  • That is not the code I posted.  Your code won't work.  Stop trying to use it.  Think about what you are doing.  Look up the CmdLets and learn how they work.

    I posted tested code.  It gets exactly what you want.  You do not need to keep mindlessly adding to it.


    \_(ツ)_/

    Wednesday, March 2, 2016 6:58 PM
  • sorry. I ran your code and I simply get a >> prompt with no info displayed.
    Wednesday, March 2, 2016 7:01 PM
  • Don't give up. That means PowerShell is waiting for you to finish the command.

    Press Enter.


    -- Bill Stewart [Bill_Stewart]

    Wednesday, March 2, 2016 7:04 PM
    Moderator
  • sorry. I ran your code and I simply get a >> prompt with no info displayed.

    You did not copy and paste this code correctly.  It m absolutely works:

    get-user|
    Select DisplayName,Department,
           
    @{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} |
    Sort TotalItemSize

    Copy and paste it exactly as it is.  Do not make any changes.


    \_(ツ)_/

    Wednesday, March 2, 2016 7:07 PM
  • You did not copy and paste this code correctly.  It m absolutely works:

    get-user|
    Select DisplayName,Department,
           
    @{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} |
    Sort TotalItemSize

    Copy and paste it exactly as it is.  Do not make any changes.


    \_(ツ)_/

    Caveat - this does not work in Exchange 2010 though.

    Wednesday, March 2, 2016 7:12 PM
  • got it - the only changes I made was to put it onto one line. it works now. and although it does show department which is great, it now doesn't show mailbox size, nor does it sort by size.

    thank you

    Wednesday, March 2, 2016 7:12 PM
  • also just read your caveat, its 2010 I'm running :(
    Wednesday, March 2, 2016 7:17 PM
  • Tell me that this doesn't show department:

    get-user | Select DisplayName,Department


    \_(ツ)_/

    Wednesday, March 2, 2016 7:31 PM
  • ok so your script:

    get-user |Select DisplayName,Department,@{n='TotalItemSize';e={($_|Get-MailboxStatistics).totalitemsize}} | Sort TotalItemSize

    works well now that I'm running it correctly. it doesn't however only look at mailboxes larger than 250mb which is what I was trying to achieve

    • Marked as answer by MartinBUK Wednesday, March 2, 2016 9:55 PM
    Wednesday, March 2, 2016 7:50 PM
  • Now add the size filter and you have the next piece.

    \_(ツ)_/

    Wednesday, March 2, 2016 8:16 PM
  • This will get you what you want.  It is a variation of your script and it does work on Exchange 2010.

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, @{n='Department';e={($_.lastloggedonuseraccount|Get-user).Department}}

    Hope that helpsjrussell97

    Wednesday, March 2, 2016 8:18 PM
  • This will get you what you want.  It is a variation of your script and it does work on Exchange 2010.

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, @{n='Department';e={($_.lastloggedonuseraccount|Get-user).Department}}

    Hope that helpsjrussell97

    fantastic, that does exactly what I want! thanks

    just one more thing, there seems to be a corrupt mailbox, is there a way of excluding that from the list, so that it doesn't even look at it?

    I presumed

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, @{n='Department';e={($_.lastloggedonuseraccount|Get-user).Department}} | where {$_.ObjectClass –eq “MWService”}

    would work, but no joy


    • Edited by MartinBUK Wednesday, March 2, 2016 8:49 PM
    Wednesday, March 2, 2016 8:46 PM
  • ok now wrote:

    get-mailbox -resultsize unlimited | Get-MailboxStatistics | Where {$_.TotalItemSize -gt 500MB -and $_.ObjectClass –eq “MWService”} | Sort-Object -Property TotalItemSize -Descending | Format-Table DisplayName, TotalItemSize, @{n='Department';e={($_.lastloggedonuseraccount|Get-user).Department}}

    which looks closer, but now I just get no results

    Wednesday, March 2, 2016 8:52 PM
  • ok I've now realised that objectclass -eq "usename" is wrong - is there another way to exclude a particular user?
    Wednesday, March 2, 2016 8:57 PM
  • Get-Mailbox returns a SamAccountName property.

    Wednesday, March 2, 2016 9:12 PM
  • One final question. Is there a way to sort, instead of by size, but by department.

    So for example all of the mailboxes with the word "IT" are grouped together, and then "accounts" etc?
    Wednesday, March 2, 2016 9:56 PM
  • Change the Format-Table command to Select-Object and then sort at the end of the pipeline.

    Wednesday, March 2, 2016 11:03 PM