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

Thanks to an expert from this site, I was able to get the following script working. What this script does is check if the IndexedProperty is present in both datasets ($DataA and $DataB), then returns data from both sets corresponding to this indexed property to an Export-CSV. Now, I would like to go a step further and replace the Import-CSV function at the beginning. This is because the imported data (DataA and DataB) are already set in another function in the script ($DataA is set with Get-ADUser and $DataB is set with Get-MailboxStatistics).

For the record, the script below works, but in the new version, the top 2 lines are removed and $DataA and $DataB are already set with Get-ADUser and Get-MailboxStatistics.
$DataA = Import-CSV D:\Import\DataA.csv
$DataB = Import-CSV D:\Import\DataB.csv

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'

$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window


I'm receiving the following error. When I run the $DataA and $DataB separately in PowerShell, I do get the output from each variable, so they are not empty.

ERROR: System.Management.Automation.MethodInvocationException: Exception calling "Add" with "2" argument(s): "Key cannot be null.
Parameter name: key" ---> System.ArgumentNullException: Key cannot be null.
Parameter name: key
   at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
   at CallSite.Target(Closure , CallSite , Object , Object , Object )
   --- End of inner exception stack trace ---
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Open in new window

SingAbout MartinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris DentPowerShell DeveloperCommented:
When you updated it to remove the CSV, did you bring across the addition of the distinguishedName (from Get-Mailbox)? It only throws that error based on the content of $DataB, and only if the indexed field (distinguishedName) is null.
0
SingAbout MartinAuthor Commented:
The only changes I made were removing the top 2 lines:

$DataA = Import-CSV D:\Import\DataA.csv
$DataB = Import-CSV D:\Import\DataB.csv

And changing the variable before Get-ADUser to $DataA and for Get-MailboxStatistics to $DataB. The strange thing is that when I request the $DataA and $DataB variables separately, I do not see the distinguishedname column. I tried to use Clear-Varable $DataA and $DataB but without success. It appears as if something is wrong with these variables, because the exports that I make do contain the DN columns.
0
Chris DentPowerShell DeveloperCommented:
It's not that you've reverted to the previous version where distinguishedName wasn't populating is it?

Can you share the snippets you're adding again?
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

SingAbout MartinAuthor Commented:
No, I have not reverted to the previous version. The DistinguishedName is included in both commands. I'm using the following script:

$DataA = Get-ADUser -SearchBase "OU=Users,DC=Contoso,DC=com" -SearchScope 1 -Properties samaccountname,LastLogon,whenCreated,distinguishedname 
-Filter {LastLogon -lt $time -AND enabled -eq $true -AND whenCreated -lt $time} | 
          select-object SamAccountName,Name,@{Name="LastLogon"; Expression={[DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss')}},
          WhenCreated,@{Name="ReferenceDate"; Expression={($datetoday)}},
          @{N='ADAccountDaysInactive'; E={$($(Get-Date) - $([DateTime]::FromFileTime($_.lastLogon))).Days}},distinguishedname | 
          Export-CSV -Path D:\Export\DataA.csv -NoTypeInformation -Append -Encoding UTF8

$mbxall = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | Where {$_.OrganizationalUnit -eq "Contoso.com/Users"} |
        Select-Object alias,displayname,distinguishedname
		$mbxinactive = ForEach($mbx in $mbxall)
		{
		  $DataB = Get-MailboxStatistics $mbx.alias | where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} | 
          Select-Object displayname,lastlogontime,lastloggedonuseraccount,@{Name="ReferenceDate"; Expression={($datetoday)}},
          @{N='MailboxDaysInactive'; E={ ((Get-Date) - ($_.LastLogonTime)).Days} },@{N='DistinguishedName'; E={($mbx.distinguishedname)} } |
          Export-CSV -Path D:\Export\DataB.csv -NoTypeInformation -Append
        }

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'

$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window


Normally, I would import the exports that are made here into $DataA and $DataB and then it would work.
0
Chris DentPowerShell DeveloperCommented:
You still have export-csv for DataB (so no output to assign).

And the scope for DataB means it would never hold information for more than one mailbox.

I'm on a train, I'll post a more detailed fix when I'm home if you haven't fixed it yourself.
0
Chris DentPowerShell DeveloperCommented:
Updated. A few changes, mostly aesthetic, a few questions for you in the comments at the top.

Used splatting for Get-ADUser, too many parameters.

Restructured mailbox report section.
# Note: LastLogon is *not* replicated between domain controllers. LastLogonDate (based on lastLogonTimeStamp) is.
#       ADAccountDaysInactive. Are you sure you don't want TotalDays?
#       MailboxDaysInactive. Same question as above.
$dataAParams = @{
    SearchBase  = "OU=Users,DC=Contoso,DC=com"
    SearchScope = 'OneLevel'
    Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
    Filter      = { LastLogon -lt $time -AND enabled -eq $true -AND whenCreated -lt $time }
}
$DataA = Get-ADUser @dataAParams |
    Select-Object SamAccountName,
                  Name,
                  @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                  WhenCreated,
                  @{Name="ReferenceDate"; Expression={ $datetoday }},
                  @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                  distinguishedname

$DataB = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "Contoso.com/Users" } |
    ForEach-Object {
        $mailbox = $_

        $_ | Get-MailboxStatistics | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
            Select-Object DisplayName,
                          LastLogonTime,
                          LastLoggedOnUserAccount,
                          @{Name="ReferenceDate"; Expression={ $datetoday }},
                          @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                          @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
    }

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'

$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window

1
SingAbout MartinAuthor Commented:
Thank you Chris that looks much better. Unfortunately I'm not able to test this right now. I'll be able to test this tomorrow morning & then I'll also answer the questions.
0
SingAbout MartinAuthor Commented:
Good morning Chris,

When I run the new script, I run into the following error. I can verify that the old script still works, and all Exchange and Active Directory commands are available in the shell (working from ISE connecting to our Exchange 2010 server). I tried looking for a solution, suggesting to use ForEach instead of ForEach-Object, but without success. When I run Get-PSSession I can verify that my current Exchange session is in the state of "Opened".

ERROR: System.Management.Automation.PSInvalidOperationException: No valid sessions were specified.  Ensure you provide valid sessions that are in the Opened state and are available to run commands.
   at Microsoft.PowerShell.Commands.InvokeCommandCommand.BeginProcessing()
   at System.Management.Automation.Cmdlet.DoBeginProcessing()
   at System.Management.Automation.CommandProcessorBase.DoBegin()

Open in new window


As for your questions:
1. To be honest I have not heard of LastLogonDate, but only for LastLogon and LastLogonTimeStamp. If I understand correctly, the LastLogon gets the time stamp from the domain controller on which the query is run, whereas LastLogonTimeStamp is replicated to all domain controllers in the domain, but is inaccurate to a maximum of 14 days(?). In our situation, we have only 2 domain controllers, so it may be better to include a simple ForEach cycle to query each domain controller and get the highest logon time stamp from LastLogon. But maybe the LastLogonDate is better, I don't know. The purpose of the script is to identify enabled users that have not logged on to their AD account AND Exchange mailbox for at least 60 days.

2 & 3. TotalDays, how is this better than the current situation? The only thing we calculate is the number of days since the LastLogon time, and based on that value, decide wether a user is inactive or not (if the Exchange inactive days also equals or exceeds 60 days of inactivity) and then disable those users. When this works correctly, I would be expanding the script to identity disabled users (from another OU) and find users that exceed 180 days of inactivity (for deletion).

This is currently the entire script:

Import-Module ActiveDirectory
$exuri = ‘http://exchangeserver.contoso.com/PowerShell/?SerializationLevel=Full’
$ExSession = New-PSSession –ConfigurationName Microsoft.Exchange –ConnectionUri $exuri -Credential $Credentials –Authentication Kerberos
Import-PSSession $ExSession

$DaysInactive = 60
$time = (Get-Date).Adddays(-($DaysInactive))
$datetoday = (Get-Date).ToString('yyyy-MM-dd hh:mm:ss')
$dnou = "OU=Users,DC=Contoso,DC=com"
$ErrorActionPreference = 'SilentlyContinue'
   
$dataAParams = @{
    SearchBase  = "OU=User,DC=Contoso,DC=com"
    SearchScope = 'OneLevel'
    Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
    Filter      = { LastLogon -lt $time -AND enabled -eq $true -AND whenCreated -lt $time }
}
$DataA = Get-ADUser @dataAParams |
    Select-Object SamAccountName,
                  Name,
                  @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                  WhenCreated,
                  @{Name="ReferenceDate"; Expression={ $datetoday }},
                  @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                  distinguishedname

$DataB = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "Contoso.com/Users" } |
    ForEach-Object {
        $mailbox = $_

        $_ | Get-MailboxStatistics | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
            Select-Object DisplayName,
                          LastLogonTime,
                          LastLoggedOnUserAccount,
                          @{Name="ReferenceDate"; Expression={ $datetoday }},
                          @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                          @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
    }

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'

$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Remove-PSSession $ExSession

Open in new window

0
Chris DentPowerShell DeveloperCommented:
I'm not sure why you're seeing that error, I take it you get that when Get-MailboxStatistics is running (as opposed to Get-Mailbox)? We can, clearly, change the script again. If the original works, it would make sense to do so.

Is this the approach you had already tried?
$mailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "Contoso.com/Users" }
$DataB = New-Object System.Collections.Generic.List[PSObject]
foreach ($mailbox in $mailboxes) {
    $result = Get-MailboxStatistics -Identity $mailbox.Identity | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
        Select-Object DisplayName,
                      LastLogonTime,
                      LastLoggedOnUserAccount,
                      @{Name="ReferenceDate"; Expression={ $datetoday }},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                      @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
    $DataB.Add($result)
}
$DataB = $DataB.ToArray()

Open in new window

1. The AD module is a bit.. odd. lastLogonTimeStamp is presented in its original form by the commands, LastLogonTimeStamp is the same value, but converted to an actual date.

You could certainly loop through each of the DCs to get lastLogon. I feel that you either must do that, or use LastLogonDate (lastLogonTimeStamp). It is possible, even if unlikely, that a user might only ever log on via a single DC which would badly skew the results. Which you choose depends a bit on whether the flex built into lastLogonTimeStamp (the 14 days you rightly cite) is a problem or not. I normally work in significantly larger environments, it tends to be the best approach there because querying large groups of DCs every time is impractical.

2,3 sorry, I should have seen that Days is the largest unit of TimeSpan so it doesn't become a problem. You get slightly less helpful values with the smaller values. For example, the behaviour of Hours (vs TotalHours) when a timespan is created by either of the snippets below.
New-TimeSpan -Hours (6 * 24)

$date = Get-Date
$date.AddHours(6 * 24) - $date

Open in new window

Point of interest rather than relevance now though.
1
SingAbout MartinAuthor Commented:
When I try this approach I get the following error:

ERROR: System.Management.Automation.RuntimeException: Cannot index into a null array.
   at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   at System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

Open in new window

0
Chris DentPowerShell DeveloperCommented:
Not liking me much, is it?

Does $DataB have values this time?
0
SingAbout MartinAuthor Commented:
Haha indeed! Just checked and $DataB is empty right now. $DataA does have values including the DN.
0
Chris DentPowerShell DeveloperCommented:
Please can we run this modified version to verify you're getting something (anything) back:
$mailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "Contoso.com/Users" }
foreach ($mailbox in $mailboxes) {
    Get-MailboxStatistics -Identity $mailbox.Identity | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
        Select-Object DisplayName,
                      LastLogonTime,
                      LastLoggedOnUserAccount,
                      @{Name="ReferenceDate"; Expression={ $datetoday }},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                      @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
}

Open in new window

0
SingAbout MartinAuthor Commented:
Certainly, I can verify that I do get an output then including the DN.
0
Chris DentPowerShell DeveloperCommented:
Simplified version, second attempt to load up the DataB variable.
$mailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "Contoso.com/Users" }
$DataB = @()
foreach ($mailbox in $mailboxes) {
    $result = Get-MailboxStatistics -Identity $mailbox.Identity | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
        Select-Object DisplayName,
                      LastLogonTime,
                      LastLoggedOnUserAccount,
                      @{Name="ReferenceDate"; Expression={ $datetoday }},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                      @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
    $DataB += $result
}

Open in new window

1
SingAbout MartinAuthor Commented:
Still getting the "No valid sessions were specified.  Ensure you provide valid sessions that are in the Opened state and are available to run commands.". The $DataA is populated, $DataB is not. The script:


$DaysInactive = 60
$time = (Get-Date).Adddays(-($DaysInactive))
$datetoday = (Get-Date).ToString('yyyy-MM-dd hh:mm:ss')
$dnou = "OU"
$ErrorActionPreference = 'SilentlyContinue'

$dataAParams = @{
    SearchBase  = "OU"
    SearchScope = 'OneLevel'
    Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
    Filter      = { LastLogon -lt $time -AND enabled -eq $true -AND whenCreated -lt $time }
}
$DataA = Get-ADUser @dataAParams |
    Select-Object SamAccountName,
                  Name,
                  @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                  WhenCreated,
                  @{Name="ReferenceDate"; Expression={ $datetoday }},
                  @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                  distinguishedname

$mailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where { $_.OrganizationalUnit -eq "OU" }
$DataB = New-Object System.Collections.Generic.List[PSObject]
foreach ($mailbox in $mailboxes) {
    $result = Get-MailboxStatistics -Identity $mailbox.Identity | Where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} |
        Select-Object DisplayName,
                      LastLogonTime,
                      LastLoggedOnUserAccount,
                      @{Name="ReferenceDate"; Expression={ $datetoday }},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days }},
                      @{Name='DistinguishedName'; Expression={ $mailbox.distinguishedname }}
    $DataB.Add($result)
}
$DataB = $DataB.ToArray()

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'

$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window

0
Chris DentPowerShell DeveloperCommented:
It was a bit much to hope fixing the loop style would resolve that one. I'm at a bit of a loss with that one, no Exchange services I can test any of this on to figure out where it might be going wrong.

Back to the beginning. If this runs successfully, please can we try the second version.
$mbxall = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | Where {$_.OrganizationalUnit -eq "Contoso.com/Users"} |
        Select-Object alias,displayname,distinguishedname
		$mbxinactive = ForEach($mbx in $mbxall)
		{
		  $DataB = Get-MailboxStatistics $mbx.alias | where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} | 
          Select-Object displayname,lastlogontime,lastloggedonuseraccount,@{Name="ReferenceDate"; Expression={($datetoday)}},
          @{N='MailboxDaysInactive'; E={ ((Get-Date) - ($_.LastLogonTime)).Days} },@{N='DistinguishedName'; E={($mbx.distinguishedname)} } |
          Export-CSV -Path D:\Export\DataB.csv -NoTypeInformation -Append
        }

Open in new window

And the second version;
$mbxall = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | 
    Where {$_.OrganizationalUnit -eq "Contoso.com/Users"} |
    Select-Object alias,displayname,distinguishedname

$DataB = @()
$mbxinactive = ForEach($mbx in $mbxall) {
    $DataB += Get-MailboxStatistics $mbx.alias | where {$_.LastLogonTime -lt $time -AND $_.WhenMailboxCreated -lt $time} | 
        Select-Object displayname,lastlogontime,lastloggedonuseraccount,@{Name="ReferenceDate"; Expression={($datetoday)}},
        @{N='MailboxDaysInactive'; E={ ((Get-Date) - ($_.LastLogonTime)).Days} },@{N='DistinguishedName'; E={($mbx.distinguishedname)} }
}

Open in new window

Shouldn't make any difference, only aesthetic changes. But it'd be good to know if one works and the other doesn't.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SingAbout MartinAuthor Commented:
Both versions work. The first one exports the CSV with output and the second one, if I request $DataB it is populated.
0
Chris DentPowerShell DeveloperCommented:
Cool, let's forget the modified versions and go with the one that works then. Anything more starts to become a waste of time for you.
0
SingAbout MartinAuthor Commented:
Hi Chris,

Thank you. The script works now! Additionally, I've tried to implement the ForEach cycle for the DC's, but I'm not sure if I did it correctly. Hope you can check it.

$DaysInactive = 60
$Time = (Get-Date).Adddays(-($DaysInactive))
$CurrentTimeStamp = (Get-Date).ToString('yyyy-MM-dd hh:mm:ss')
$OrganizationalUnit = "OU=Users,DC=nl,DC=Contoso,DC=com"
$OrganizationalUnit2 = "Contoso.com/Users"
$DomainControllers = Get-ADDomainController -Filter {Name -like "*"}
$ErrorActionPreference = 'SilentlyContinue'

$dataAParams = @{
    SearchBase  = "OU=Users,DC=Contoso,DC=com"
    SearchScope = 'OneLevel'
    Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
    Filter      = { LastLogon -lt $Time -AND enabled -eq $true -AND whenCreated -lt $Time }
    Server      = $DomainControllerHostname
}
foreach($DomainController in $DomainControllers)
{
$DomainControllerHostname = $DomainController.HostName
$DataA = Get-ADUser @dataAParams |
    Select-Object SamAccountName,
                  Name,
                  @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                  WhenCreated,
                  @{Name="ReferenceDate"; Expression={ $CurrentTimeStamp }},
                  @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                  distinguishedname
}

$AllMailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $OrganizationalUnit | 
    Where {$_.OrganizationalUnit -eq $OrganizationalUnit2} |
    Select-Object alias,displayname,distinguishedname

$DataB = @()
$InactiveMailbox = ForEach($Mailbox in $AllMailboxes) {
    $DataB += Get-MailboxStatistics $Mailbox.alias | Where {$_.LastLogonTime -lt $Time -AND $_.WhenMailboxCreated -lt $Time} | 
        Select-Object displayname,
                      lastlogontime,
                      lastloggedonuseraccount,
                      @{Name="ReferenceDate"; Expression={($CurrentTimeStamp)}},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days} },
                      @{Name='DistinguishedName'; Expression={($Mailbox.distinguishedname)} }
}

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'
$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV -Path D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window

0
Chris DentPowerShell DeveloperCommented:
Alas it's overwriting DataA with information from each of the two DCs. It needs something a bit more complex to assembly the results from two datasources.
$DataAValues = @{}
foreach($DomainController in $DomainControllers) {
    $dataAParams = @{
        SearchBase  = "OU=Users,DC=Contoso,DC=com"
        SearchScope = 'OneLevel'
        Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
        Filter      = { LastLogon -lt $Time -AND enabled -eq $true -AND whenCreated -lt $Time }
        Server      = $DomainControllerHostname
    }
    Get-ADUser @dataAParams |
        Select-Object SamAccountName,
                    Name,
                    @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                    WhenCreated,
                    @{Name="ReferenceDate"; Expression={ $CurrentTimeStamp }},
                    @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                    distinguishedname |
        ForEach-Object {
            if ($DataAValues.Contains($_.SamAccountName)) {
                if ($DataAValues[$_.SamAccountName].LastLogon -lt $_.LastLogon) {
                    $DataAValues[$_.SamAccountName].LastLogon = $_.LastLogon
                }
            } else {
                $DataAValues.Add($_.SamAccountName, $_)
            }
        }
}
$DataA = $DataAValues.Values

Open in new window

1
SingAbout MartinAuthor Commented:
Thank you Chris. The script still works now, I do get a very similar output as yesterday, only difference now is that it appears that the data/columns with LastLogon etc. is missing in the export. When I open the export I see all the information of the mailboxes, but not the AD users. The first 3 columns are now "IsSynchronized", "SyncRoot" and "Count". When I request $DataA, the information is there.

$DaysInactive = 60
$Time = (Get-Date).Adddays(-($DaysInactive))
$CurrentTimeStamp = (Get-Date).ToString('yyyy-MM-dd hh:mm:ss')
$OrganizationalUnit = "OU=Users,DC=Contoso,DC=com"
$OrganizationalUnit_Format = "Contoso.com/Users"
$DomainControllers = Get-ADDomainController -Filter {Name -like "*"}
$ErrorActionPreference = 'SilentlyContinue'

$DataAValues = @{}
foreach($DomainController in $DomainControllers) {
    $DomainControllerHostname = $DomainController.HostName
    $dataAParams = @{
        SearchBase  = "OU=Users,DC=Contoso,DC=com"
        SearchScope = 'OneLevel'
        Properties  = 'samaccountname', 'LastLogon', 'whenCreated', 'distinguishedname'
        Filter      = { LastLogon -lt $Time -AND enabled -eq $true -AND whenCreated -lt $Time }
        Server      = $DomainControllerHostname
    }
    Get-ADUser @dataAParams |
        Select-Object SamAccountName,
                    Name,
                    @{Name="LastLogon"; Expression={ [DateTime]::FromFileTime($_.lastLogon).ToString('yyyy-MM-dd hh:mm:ss') }},
                    WhenCreated,
                    @{Name="ReferenceDate"; Expression={ $CurrentTimeStamp }},
                    @{Name='ADAccountDaysInactive'; Expression={ ((Get-Date) - ([DateTime]::FromFileTime($_.lastLogon))).Days }},
                    distinguishedname |
        ForEach-Object {
            if ($DataAValues.Contains($_.SamAccountName)) {
                if ($DataAValues[$_.SamAccountName].LastLogon -lt $_.LastLogon) {
                    $DataAValues[$_.SamAccountName].LastLogon = $_.LastLogon
                }
            } else {
                $DataAValues.Add($_.SamAccountName, $_)
            }
        }
}
$DataA = $DataAValues.Values

$AllMailboxes = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $OrganizationalUnit | 
    Where {$_.OrganizationalUnit -eq $OrganizationalUnit_Format} |
    Select-Object alias,displayname,distinguishedname

$DataB = @()
$InactiveMailbox = ForEach($Mailbox in $AllMailboxes) {
    $DataB += Get-MailboxStatistics $Mailbox.alias | Where {$_.LastLogonTime -lt $Time -AND $_.WhenMailboxCreated -lt $Time} | 
        Select-Object displayname,
                      lastlogontime,
                      lastloggedonuseraccount,
                      @{Name="ReferenceDate"; Expression={($CurrentTimeStamp)}},
                      @{Name='MailboxDaysInactive'; Expression={ ((Get-Date) - ($_.LastLogonTime)).Days} },
                      @{Name='DistinguishedName'; Expression={($Mailbox.distinguishedname)} }
}

$properties = $DataA[0].PSObject.Properties.Name + $DataB[0].PSObject.Properties.Name

$IndexedProperty = 'distinguishedName'
$DataBIndex = @{}
for ($i = 0; $i -lt $DataB.Count; $i++) {
    $DataBIndex.Add($DataB[$i].$IndexedProperty, $i)
}

foreach ($itemA in $DataA) {
    if ($DataBIndex.Contains($itemA.$IndexedProperty)) {
        $itemB = $DataB[$DataBIndex.($itemA.$IndexedProperty)]

        foreach ($property in $itemB.PSObject.Properties) {
            $itemA | Add-Member $property.Name $property.Value
        }
 
        $itemA | Select-Object $properties | Export-CSV -Path D:\Export\Result.csv -Append -NoTypeInformation
    }
}

Open in new window

0
Chris DentPowerShell DeveloperCommented:
Apologies. It just needs one line changing.

This:
$DataA = $DataAValues.Values

Open in new window

Becomes this:
$DataA = $DataAValues.Values | Select-Object *

Open in new window

That "should" fix the problem.
1
SingAbout MartinAuthor Commented:
Thanks very much Chris it all works now! Have a great day.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.