Avatar of SingAbout Martin
SingAbout Martin
 asked on

Matching variables and Compare-Object

I have the following situation to which I cannot find a solution, but I think it must be very easy. I have the following variables:

$DataA - This variable contains the "Name" of specific AD Users, as well as columns "LastLogon" and "WhenCreated".
$DataB - This variable contains the "DisplayName" of specific Exchange users as well as columns "LastLogonTime" and "LastLoggedOnUserAccount".
$DataC - This variable contains only the names of the users that are present in both $DataA and $DataB under the column name "InputObject" as a result of the Compare-Object command.

The variable $DataA and $DataB contain information that I want to see in the $DataC or a new variable. In $DataC I have a list of names, and I want the corresponding attributes from $DataA and $DataB combined to a new variable. So for example:

$DataC contains a column with name "InputObject" and a value of "Test User".
$DataA contains a column with name "Name" and a value of "Test User, AND a column with "LastLogon" and a value of 2017-04-03 02:26.
$DataB contains a column with name "DisplayName" and a value of "Test User, AND a column with "LastLogonTime" and a value of 2017-04-03 02:26.

In a new $DataD variable I want to have those combined. Alternatively, it would also be good if it is possible to select-objects from other variables within a Compare-Object command. That's because $DataC is generated through Compare-Object, but I cannot seem to add columns with information from $DataA or $DataB. Hopefully you can help.
PowershellExchangeActive Directory

Avatar of undefined
Last Comment
SingAbout Martin

8/22/2022 - Mon
Chris Dent

How big are each of these data sets? A few hundred? Thousands?
SingAbout Martin

ASKER
Both $DataA and $DataB contain less than 100 entries, however this may increase to a few hundred. The $DataC variable contains even less entries.
Chris Dent

Okay. How close is this?
# Sample sets
$DataA = "Name,LastLogon,WhenCreated", "Bob,01/01/2016,01/01/2015", "Tim,01/01/2014,01/01/2013" | ConvertFrom-Csv
$DataB = "DisplayName,LastLogonTime,LastLoggedOnUserAccount", "Bob,01/01/2016,Bob", "Tim,01/01/2014,Dave" | ConvertFrom-Csv

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

foreach ($itemA in $DataA) {
    # Match DataB
    $itemB = $DataB | Where-Object DisplayName -eq $itemA.Name

    foreach ($property in $itemB.PSObject.Properties) {
        $itemA | Add-Member $property.Name $property.Value
    }

    $itemA | Select-Object $properties
}

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Dent

If the set sizes become much larger there are huge benefits to be had by indexing the arrays by the value you intend to join on. The repeated use of Where-Object will add a significant burden otherwise.
# Sample sets
$DataA = "Name,LastLogon,WhenCreated", "Bob,01/01/2016,01/01/2015", "Tim,01/01/2014,01/01/2013" | ConvertFrom-Csv
$DataB = "DisplayName,LastLogonTime,LastLoggedOnUserAccount", "Bob,01/01/2016,Bob", "Tim,01/01/2014,Dave" | ConvertFrom-Csv

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

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

foreach ($itemA in $DataA) {
    # Match DataB
    if ($DataBIndex.Contains($itemA.Name)) {
        $itemB = $DataB[$DataBIndex.($itemA.Name)]
    }

    foreach ($property in $itemB.PSObject.Properties) {
        $itemA | Add-Member $property.Name $property.Value
    }

    $itemA | Select-Object $properties
}

Open in new window

SingAbout Martin

ASKER
I think you are very close. I think I didn't specify the problem clear enough.

We have the $DataA and $DataB variables, these are source variables using which variable $DataC is calculated using Compare-Object. However, $DataC contains only limited information (only the information that is used to match from both A and B, which is the Name) and not the other columns which I want. $DataC contains only the names of the users, whereas I also want to have the LastLogon (from A) and LastLogonTime (from B) to be included in a new $DataD (for example). I hope it is clear enough, otherwise I will further explain the situation.
SOLUTION
Chris Dent

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SingAbout Martin

ASKER
To make it more clear (maybe), here is the data I'm working with.

This is DataA
 $DataA = Get-ADUser -SearchBase "OU=DN of OU" -SearchScope 1 -Properties LastLogon,whenCreated -Filter {LastLogon -lt $time -AND enabled -eq $true -AND whenCreated -lt $time} | 
        select-object 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}} | 
        Export-CSV -Path D:\Export\DataA.csv -NoTypeInformation -Append -Encoding UTF8

Open in new window


This is DataB
$mbxall = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | Where {$_.OrganizationalUnit -eq "consoto.com/Users"} |
        Select-Object alias,displayname
		$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} } |
          Export-CSV -Path D:\Export\DataB.csv -NoTypeInformation -Append

Open in new window


This is DataC
$DataC = Compare-Object $csv1.name $csv2.displayname -IncludeEqual | ? {$_.SideIndicator -eq "=="} | Export-CSV D:\Export\Difference.csv -NoTypeInformation

Open in new window


And I want to include more columns to the Difference.csv result than only the name, such as LastLogon and LastLogonTime from both variables.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SingAbout Martin

ASKER
Alright thank you I will try that out.
Chris Dent

I  really am aware of that. I strongly advise you run the sample. You can reduce the set to those which exist in both, but you don't need Compare-Object to do it.

One tick, I'll show you another approach, but I'm afraid I'm still not going to use DataC, it serves no purpose.
Chris Dent

This approach uses Group-Object instead.

Before the command can run DataB needs modify, a field (the key) with the same name as that used in DataA needs adding. It needs a Name field. Select-Object is used to fabricate the field.

The two sets of data are then joined together, and Group-Object is let lose. Every user with 2 records will have one from DataA, and one from DataB. A new output object is created by writing all of the properties from DataA and DataB onto a new object.
# Sample sets
$DataA = "Name,LastLogon,WhenCreated", "Bob,01/01/2016,01/01/2015", "Tim,01/01/2014,01/01/2013" | ConvertFrom-Csv
$DataB = "DisplayName,LastLogonTime,LastLoggedOnUserAccount", "Bob,01/01/2016,Bob", "Tim,01/01/2014,Dave" | ConvertFrom-Csv

# Fabricate a key property
$DataB = $DataB | Select-Object @{n='Name';e={ $_.DisplayName }}, *

# Join the arrays
@($DataA) + @($DataB) |
    Group-Object Name |
    Where-Object Count -ge 2 |   # Filter out any users which do not have an entry in both lists
    ForEach-Object {
        # Create a new object for the output
        $psObject = New-Object PSObject

        # Write every field from each of the matching objects to the output object
        foreach ($item in $_.Group) {
            foreach ($property in $item.PSObject.Properties) {
                $psObject  | Add-Member $property.Name $property.Value -Force
            }
        }

        $psObject
    }

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Dent

The second example does not order properties at all, if aesthetics are important it would benefit from that. In the previous example the first row of each data set was read to determine the list of properties (columns).

The output from the second example looks like this:
LastLogon               : 01/01/2016
WhenCreated             : 01/01/2015
Name                    : Bob
DisplayName             : Bob
LastLogonTime           : 01/01/2016
LastLoggedOnUserAccount : Bob

LastLogon               : 01/01/2014
WhenCreated             : 01/01/2013
Name                    : Tim
DisplayName             : Tim
LastLogonTime           : 01/01/2014
LastLoggedOnUserAccount : Dave

Open in new window

SingAbout Martin

ASKER
First of, thank you very much for your detailed answers & well documented snippets. Very much appreciated. When I run the following code:

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

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

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

foreach ($itemA in $DataA) {
    # Match DataB
    if ($DataBIndex.Contains($itemA.Name)) {
        $itemB = $DataB[$DataBIndex.($itemA.Name)]
    }

    foreach ($property in $itemB.PSObject.Properties) {
        $itemA | Add-Member $property.Name $property.Value
    }

    $itemA | Select-Object $properties | Export-CSV D:\Export\Result.csv
}

Open in new window


I do get an output, but the formatting is a bit strange (everything is on 1 line, see below), and the user that I'm seeing in Result.csv is not present in $DataB.

#TYPE Selected.System.Management.Automation.PSCustomObject
@{Name=Test User; LastLogon=2017-03-24 02:41:59; WhenCreated=09/27/15 10:51:22 AM
SOLUTION
Chris Dent

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SingAbout Martin

ASKER
Thank you, the formatting is now good and I have the multiple columns combined! However, the output is not correct. In the first column of Result.csv (called "Name") I'm seeing names that are not present in $DataB. Also, the column LastLoggedOnUserAccount is also incorrect as it contains the wrong value that is not present for this user in $DataB.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

This version does not filter down to users only present in both data sets.

For the entry where the wrong account is listed, does the name exist more that once in the CSV files?
SingAbout Martin

ASKER
Ok, it is a requirement though that the name MUST be present in both datasets. This is because presence in both datasets indicates that we want to see this specific information (LastLogonTime, LastLogon, LastLoggedOnUserAccount etc.) in a new output.

As for the wrong listed account LastLoggedOnUserAccount (and also DisplayName) for a specific user in the Result.csv, the name in the "Name" column is not unique (which is also not desirable).
Chris Dent

Are you able to add distinguished name to bith data sets? That is strongly unique, much better to key off.

Just having a coffee, I'll add the present in both constraint in a moment.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SingAbout Martin

ASKER
Certainly I can do that, just checked and "DistinguishedName" attribute is available for both Get-Mailbox and Get-ADUser.
ASKER CERTIFIED SOLUTION
Chris Dent

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SingAbout Martin

ASKER
Thank you for the script. I thought it would be very easy to add the DN to the output of Get-MailboxStatistics but apparantly it's not so easy. In the below script the DN column is empty. I can't seem to fix this. After this I can test your script with the DN as primary key.


        $mbxall = Get-Mailbox -ResultSize Unlimited -OrganizationalUnit $dnou | Where {$_.OrganizationalUnit -eq "contoso.com/Users"} |
        Select-Object alias,displayname,distinguishedname
		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={($mbxall.distinguishedname)} } |
          Export-CSV -Path D:\Export\DataB.csv -NoTypeInformation -Append
        }

Open in new window

SOLUTION
Chris Dent

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SingAbout Martin

ASKER
Chris, you are a true PowerShell hero. Everything works as exepcted now including the latest script you provided yesterday.
Special thanks for your excellent assistance! Have a great day.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

Glad I could help. All the best :)
SingAbout Martin

ASKER
Solved thanks to Chris' excellent support, very knowledgeable with Powershell.