none
mailbox stats RRS feed

  • Question

  • Hi Experts
    I want to import a csv file and get the below output to csv file.
    DisplayName, FirstName, LastNAme, Alias,UserPrincipalName,SamAccounttName,OWAStatus(Enabled/Disabled),PrimarySMTPAddress.

    I have email address in the csv file in the below format
    users
    user1@contoso.com
    user2@contoso.com 

    I am trying to write the below ps1 but i am not able to, experts guide me on this.

    $Result= Import-csv "C:\users.csv"
    $Import = foreach($b in $Result) {Get-Mailbox -ResultSize Unlimited $b.user}
    $Import | Foreach-Object {    
    $user = $_    
    $stats = Get-MailboxStatistics $user.name
    $a=get-user $user.name      
    New-Object -TypeName PSObject -Property @{        
    DisplayName = $user.DisplayName
    FirstName = $a.FirstName
    LastName = $a.Lastname
    userprincipalname = $user.userprincipalname 
    sAMAccountName = $user.sAMAccountName              
    OWAEnable = $user.OWAenabled
    Alias = $user.Alias
    PrimarySMTPAddress = 

    } |select DisplayName,FirstName,LastName,userprincipalname,sAMAccountName,OWAenabled,Alias,PrimarySMTPAddress | Export-CSV C:\output.csv -NoTypeInformation -Append

    Monday, September 2, 2019 1:17 PM

All replies

  • Use this tool. This is the best tool ever i use.

    https://gallery.technet.microsoft.com/scriptcenter/Generate-Mailbox-Size-and-3f408172

    Once getting the report, You can delete unnecessary columns.

    Monday, September 2, 2019 2:44 PM
  • Monday, September 2, 2019 2:47 PM
  • Try below:

    $Result = Import-Csv "<PATH OF YOUR CSV>"
    
    $Result | foreach {
        $user = Get-User $_.Users
        Get-Mailbox $_.Users | 
            Select-Object @{n = "FirstName"; e = {$user.FirstName}}, @{n = "LastName"; e = {$user.LastName}}, DisplayName, Alias, UserPrincipalName, SamAccountName, @{n = "OWAStatus(Enabled/Disabled)"; e = {Get-CASMailbox $_.Users | Select -ExpandProperty OWAEnabled}}, PrimarySMTPAddress
            } | Export-Csv "<PATH FOR THE OUTPUT CSV>"

    Monday, September 2, 2019 3:11 PM
  • Hello Glenn, 

    I wrote a script for your requirement.Please check it and let me know if this helps for your requirement.

    ################################################################

    Write-Host "Collecting user mailbox list" -BackgroundColor DarkGreen

    $users = Get-Recipient | Where-Object {$_.RecipientType -eq "UserMailbox"} | Select-Object -ExpandProperty alias
    $output = @()

    foreach ($user in $users)

    {

    $collection1 = Get-User -Identity "$user" | select-object displayname,firstname,lastname,UserPrincipalName,SamAccountName
    $collection2 = Get-Mailbox -Identity "$user" | select-object alias,Primarysmtpaddress
    $collection3 = Get-CASMailbox -Identity "$user" | select-object Owaenabled

    $props = [Ordered]@{

    'Displayname'           =   $collection1.displayname;
    'Firstname'         =   $collection1.firstname;
    'Lastname'              =   $collection1.lastname;
    'UserPrincipalName'     =   $collection1.UserPrincipalName;
    'SamAccounttName'       =   $collection1.SamAccountName;
    'Alias'                 =   $collection2.alias;
    'Primarysmtpaddress'    =   $collection2.Primarysmtpaddress;
    'OwaEnabled'            =   $collection3.Owaenabled

    }

    $result = New-Object -TypeName psobject -Property $props
    $output += $result

    }

    $output | Export-Csv c:\nithya\data.csv -Append -NoTypeInformation

    ########################################################################


    Thanks & Regards S.Nithyanandham

    Monday, September 2, 2019 6:04 PM
  • We cannot retrieve the OwaEnabled paramater from the Get-Mailbox cmdlet, it should be Get-CasMailbox.

    $Result= Import-csv "C:\temp\users.csv"
    $Import = foreach($b in $Result) {Get-Mailbox -ResultSize Unlimited $b.users}
    $Import | Foreach-Object {    
    $user = $_    
    $a= get-user $user.name  
    $c= Get-CasMailbox $user.name   
    New-Object -TypeName PSObject -Property @{        
    DisplayName = $user.DisplayName
    FirstName = $a.FirstName
    LastName = $a.Lastname
    UserPrincipalName = $user.UserPrincipalName 
    SamAccountName = $user.SamAccountName              
    OwaEnabled = $c.OwaEnabled
    Alias = $user.Alias
    PrimarySMTPAddress = $user.PrimarySmtpAddress
    } 
    } |select DisplayName,FirstName,LastName,userprincipalname,SamAccountName,OwaEnabled,Alias,PrimarySMTPAddress | Export-CSV "C:\temp\result.csv" -NoTypeInformation -Append

    CSV format:

    Output:

    Regards,

    Manu Meng


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Tuesday, September 3, 2019 7:02 AM
    Moderator
  • Just checking in to see if above information was helpful. Please let us know if you would like further assistance.

    Regards, 

    Manu Meng


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Friday, September 6, 2019 9:50 AM
    Moderator
  • Thanks alot

    @Manu, i will check today and get back to you.

    If i put userprincipal name in csv file rather than email address will it work for me.


    Wednesday, September 11, 2019 5:51 PM
  • Thanks alot

    @Manu, i will check today and get back to you.

    If i put userprincipal name in csv file rather than email address will it work for me.


    It will work. UPN could also be the identity of the Get-Mailbox cmdlet.

    Regards,

    Manu Meng


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Thursday, September 12, 2019 10:10 AM
    Moderator