locked
Output User object Path in a CSV report RRS feed

  • Question

  • Hi, 

    I try to create a report to output username, mailboxsize, etc. 

    However, i also need the path of the object in Active Directory (Canonical name of the object).   I don't find a way to obtain it in the same report.

    Anyone ?

    Tx. 

    Get-MailboxDatabase | Get-MailboxStatistics | Select DisplayName, LastLoggedOnUserAccount, ItemCount, TotalItemSize, LastLogonTime, LastLogoffTime | Sort-Object TotalItemSize  -Des
    cending | Export-CSV c:\report.csv



    • Edited by JF136 Monday, January 13, 2020 9:54 PM
    Monday, January 13, 2020 6:49 PM

All replies

  • Monday, January 13, 2020 8:35 PM
  • Please format code correctly so it is readable and not broken,

    What you posted is broken because it has a linefeed that breaks the code.

    Understanding PowerShell and the process of programming is more than just blindly copying code.

    Get-MailboxDatabase |
    	Get-MailboxStatistics |
    	Select DisplayName, LastLoggedOnUserAccount, ItemCount, TotalItemSize, LastLogonTime, LastLogoffTime |
    	Sort-Object TotalItemSize  -Descending |
    	Export-CSV c:\report.csv

    The PowerShell Best Practices and Style Guide



    \_(ツ)_/

    Monday, January 13, 2020 10:08 PM
  • The issue is that none of the "get" commands you are using are talking to AD, which is where that information resides. Instead, you can use one command inside another's foreach loop. Here is an example:

    $Mailboxes = Get-Mailbox 
    
    $Mailboxes | foreach {
    
        $MailboxStats = Get-MailboxStatistics $_.Identity
      
        $StuffToReport = @(
            "Identity"
            "DisplayName"
            "SamAccountName"
            "UserPrincipalName"
            "PrimarySmtpAddress"
            "CustomAttribute4" 
            "RecipientTypeDetails"
            "ExchangeVersion"
            "UMEnabled"
            "Extensions"
    
            @{n = "TotalMailboxItemCount"; e = { $MailboxStats.ItemCount } }
            @{n = "TotalItemSize"; e = { $MailboxStats.TotalItemSize } }
            @{n = "LastLoggedOnUserAccount"; e = { $MailboxStats.LastLoggedOnUserAccount } }
            @{n = "LastLogonTime"; e = { [datetime]$MailboxStats.LastLogonTime } } 
            @{n = "OrganizationalUnit"; e = { $matches = $null; @($_.DistinguishedName -match $regex_dn; $Matches['ou'])[1] } }
    
        )
            
        $_ | select $StuffToReport
    
    }
    
    $Results 




    Mike Crowley

    My Blog | MikeCrowley.US

    Baseline Technologies | Baseline.Consulting

    Being ignorant is not so much a shame, as being unwilling to learn

    -Ben Franklin


    Tuesday, January 14, 2020 3:02 AM
  • Where is the canonical AD path as asked for?

    Actually the canonical name is the CN which is also known as the "Name" in AD.  It is not the same as the CanonicalPath which is only available in AD.

    Your answer DOES NOT contain the canonical name.

    Also the property array should never be inside of a loop or it will waste memory and slow the code.

    This is the way to write this in PowerShell.

    $StuffToReport = @(
        'DisplayName',
        'SamAccountName',
        'UserPrincipalName',
        'PrimarySmtpAddress',
        'CustomAttribute4',
        'RecipientTypeDetails',
        'ExchangeVersion',
        'UMEnabled',
        'Extensions',
        @{n = 'TotalMailboxItemCount'; e = { $MailboxStats.ItemCount } },
        @{n = 'TotalItemSize'; e = { $MailboxStats.TotalItemSize } },
        @{n = 'LastLoggedOnUserAccount'; e = { $MailboxStats.LastLoggedOnUserAccount } },
        @{n = 'LastLogonTime'; e = { [datetime]$MailboxStats.LastLogonTime } },
        @{n = 'OrganizationalUnit'; e = { $matches = $null; @($_.DistinguishedName -match $regex_dn; $Matches['ou'])[1] } }
    )
            
    $mailboxes = Get-Mailbox -OrganizationalUnit $OU -ResultSize $ResultSize
    $mailboxes | ForEach-Object{ Get-MailboxStatistics $_.Identity } | Select-Object $StuffToReport

    I will say what I have been saying for years now.  Stop guessing and try to actually learn PowerShell.  It isn't hard and will save you a lot of wasted time writing bad code.

    Of course it would also be nice if the Exchange CmdLets would be fixed so they could be pipelined correctly.


    \_(ツ)_/


    Tuesday, January 14, 2020 3:19 AM
  • woops, i gut out too much. I edited my post to put it back.

    You're right about the properties being defined before the loop, but the amount of RAM required to have this conversation far exceeded the amount wasted in the script.

    PS. I don't disagree with your points per se, but I'm not sure why you feel the need to be so aggressive in your post. You frequently lecture people who are coming here for answers. It'd be great if everyone was as passionate about the things we were, but you must realize that its not how the universe works, and this isn't your house.



    Mike Crowley

    My Blog | MikeCrowley.US

    Baseline Technologies | Baseline.Consulting

    Being ignorant is not so much a shame, as being unwilling to learn

    -Ben Franklin

    Tuesday, January 14, 2020 4:21 AM

  • PS. I don't disagree with your points per se, but I'm not sure why you feel the need to be so aggressive in your post. You frequently lecture people who are coming here for answers. It'd be great if everyone was as passionate about the things we were, but you must realize that its not how the universe works, and this isn't your house.


    I am making a point stick so it won't be forgotten easily.

    I don't see how one small line counts as a lecture. Perhaps you ae being overly sensitive.

    Best practices dictate placing the "template" outside of the loop.  If you were a programmer you would automatically understand that.  Most compilers would warn on the issue and running through a good script analyzer would also pick on your construct.

    It is all "Programming 101".

    "Identity" is not a property and definitely not a canonical name.

    The code also will not produce the results implied. The CmdLet does not produce those properties'  I didn't address that as I was addressing the format and code structure.  The overall issue is still not being addressed.

    Either run the code and check or look up the returned object to see what properties it contains. https://docs.microsoft.com/en-us/previous-versions/office/exchange-server-api/ff340341%28v%3dexchg.150%29

    Almost no users here know how to look up objects on Microsoft.    Again this should be "Programming 101" for anyone using PowerShell.  MOst properties specified do not exist on the object.  Do you know how this is remedied.  It takes much different approach to processing the data and is done using a custom object generator pattern.


    \_(ツ)_/

    Tuesday, January 14, 2020 9:24 AM
  • the identity property returns a CN.


    Mike Crowley

    My Blog | MikeCrowley.US

    Baseline Technologies | Baseline.Consulting

    Being ignorant is not so much a shame, as being unwilling to learn

    -Ben Franklin

    Tuesday, January 21, 2020 3:01 PM