1

I'm relatively new to Powershell but haven't been able to find an answer online.

I'm trying to get the number of emails per disabled user in exchange 2010, but also need to get the user's title form AD as the organization groups users by type using the Title attribute in AD

I've written the following but I'm unable to get the data I need, it just returns Length and numbers to the CSV file e.g. "length" "10" "3" "34"

If I leave $title out of the assignment of $Disabled+= the user's name and item count is added to the csv file, but I really need the title also. Can anyone point out where I'm going wrong.

Import-Module ActiveDirectory

$i=0

$disUsers = Get-ADUser -Filter * -SearchBase "ou=User Disabled Accounts,dc=test,dc=com" -Properties SamAccountName,Title

$Disabled = @()

$disUsers | Foreach-Object{      
    $sam = $_.SamAccountName
    $title = $_.Title
    $mailDetail=Get-MailboxStatistics $sam | Select -Property DisplayName,ItemCount
    $Disabled += $title, $mailDetail
    $i++;   
 }


$Disabled | Export-Csv -Path $env:userprofile\desktop\DisabledADUserTitlewithMailbox.csv -NoTypeInformation

Working with the code provided by Steve unfortunately gives the following errors

Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: 'ADCDisabledMail'  Key being added: 'ADCDisabledMail'" ...     

Exception calling "Add" with "2" argument(s): "Key cannot be null. Parameter name: key"...

EDIT With help from Steven I was able to get this working with the following

'Import-Module ActiveDirectory' 

$i=0

$disUsers=Get-ADUser -Filter {mailNickName -like '*'} -SearchBase "ou=User Disabled Accounts,dc=test,dc=com" -Properties SamAccountName,Title 

$dis2 = $disUsers.count

$DisabledUser = @()

$disUsers | Foreach-Object{

Write-Host "Processing record $i of $dis2"

                $sam = $_.SamAccountName

                $title = $_.Title

$mailDetail=Get-MailboxStatistics $sam | Select-Object DisplayName, @{ Name = 'Title'; Expression = {$title}}, ItemCount  

                                $DisabledUser+= $mailDetail
  $i++;   

}

$DisabledUser | Export-Csv -Path $env:userprofile\desktop\DisabledADUserTitlewithMailbox.csv -NoTypeInformation
2
  • Your CSV contains only the Length because you are sending only strings to Export-Csv. Export-Csv looks at the first object in the pipelin and takes its properties as headers for the CSV file. Then retrieves the value of each property and outputs them for each object (one object per line). A string only has a Length property. So the result you see is expected. Commented Sep 4, 2020 at 21:48
  • Thanks @AdminOfThings for the explanation. Commented Sep 5, 2020 at 16:34

1 Answer 1

1

It sounds like what you are really trying to do is relate data to create a small report. You are dealing with data coming from different commands so you need a property to join on. In this case I would look at the LegacyExchangeDN AD attribute and the LegacyDN property returned by Get-MailboxStatistics. The code might look something like:

$DisabledUsers = @{}

Get-ADUser -SearchBase 'ou=User Disabled Accounts,dc=test,dc=com' -Filter * -Properties 'Title','legacyExchangeDN' |
ForEach-Object{ $DisabledUsers.Add( $_.legacyExchangeDN, $_ ) }

$DisabledUsers.Values.SamAccountName | 
Get-MailboxStatistics |
Select-Object DisplayName, ItemCount, @{ Name = 'Title'; Expression = { $DisabledUsers[$_.LegacyDN].Title } }

This will output something like:

DisplayName   ItemCount Title
-----------   --------- -----
Mr. Smith        113576 Executives

If you would rather it go directly to a CSV file simply add the Export-CSV command after the Select-Object command, like below:

$DisabledUsers = @{}

Get-ADUser -SearchBase 'ou=User Disabled Accounts,dc=test,dc=com' -Filter * -Properties 'Title','legacyExchangeDN' |
ForEach-Object{ $DisabledUsers.Add( $_.legacyExchangeDN, $_ ) }

$DisabledUsers.Values.SamAccountName | 
Get-MailboxStatistics |
Select-Object DisplayName, ItemCount, @{ Name = 'Title'; Expression = { $DisabledUsers[$_.LegacyDN].Title } } |
Export-CSV -Path $env:userprofile\desktop\DisabledADUserTitlewithMailbox.csv -NoTypeInformation

I would've used Get-User from the Exchange Management Shell, however it doesn't have the LegacyExchangeDN as a returned property. It does have SamAccountName, but using it would've forced me to bridge everything through Get-Mailbox. At any rate, this is a very common technique to use a hash table to reference related values in a different collection.

I'm sure some additional work will be needed to get the report just right.

An aside, try to avoid using the += operator to append arrays. The best way to get an array is to let PowerShell provide it as I did above. However, if you can't get around it the most common alternative is an ArrayList. Like most things there are several ways to go about it below is just 1 example.

# To create:
$ArrList = [Collections.ArrayList]@()

#To Add a value:
[Void]$ArrList.Add( 'ValueOrObjectHere' )

Note: Documentation / discussion of += and ArrayList's are easy to find with the Google machine...

Update:

Addressing Errors Noted in most recent edit:

The first error is basically impossible. Forgive me but I must assume you made some mistake to generate this error. LegacyExchangeDN should always start with '/o=...' and they key cited by the error was 'ADCDisabledMail' . Also LegacyExchangeDNs are naturally unique in Active Directory, so there's almost no chance you'd have a duplicate. As such, I made no effort, and none is warranted, to prevent such an unlikely error.

Note: If you are repeatedly testing the code you have to recreate the hash, $DisabledUsers = @{} else the hash will exist from the previous run and duplicate key errors are a certainty...

The second error, they 'key cannot be null' might be due to non-mailbox enabled AD accounts in the referenced OU effectively causing the LegacyExchangeDN attribute to be null for those users. Hence, null key.... You can avoid that by modifying the filter to only return mail enabled users:

$disUsers = Get-ADUser -Filter  { mailNickName -like '*' } -SearchBase "ou=User Disabled Accounts,dc=test,dc=com" -Properties SamAccountName,Title

Note: For reference, mailNickName is the alias propertry typically returned with Get-Mailbox

Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for your help I've tried your solution but it throws the following errors and the resulting CSV is empty. I've added the errors to my first post as an edit as the comment section won't take the full error
Thanks @Steven, the code was run as provided and the errors are those returned. I understand that the second error could be no mail enabled users, I'm new to the organization and was informed that all were mail enabled.
I appreciate what you were told, but you can easily confirm it by negating the filter ; -Filter { mailNickName -notlike '*' }. This will tell you if there are non-mailbox users in the given OU. Note: LegacyExchangeDN is a required property in an Exchange environment. If it were really null you'd have all kinds of other issues. Is there something I'm not understanding; do you use Office 365? Can you try running the query without adding to the hash table? Also run the negated filter as described and let me know what comes back. Note: Code is working fine in my environment...
Hi @Steven sorry for the delay, I got caught up in another issue. The organization is a on prem one only for Exchange, but we use Office 365 for outlook and other office applications. I was unable to run your code successfully but I was able to take the idea and amend my original code to get what I needed, I've included it in an edit to my question, so big thanks. As regards the ADCDisabledMail value for the LegacyExchangeDN, I'm looking into this, it might be from a previous upgrade of Exchange, and as we will be upgrading again soon its definitely something I'll check out.
I'm glad it worked out! The lesson is more important than whether my specific code worked in your environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.