Search for matching Mac address in two separate columns in a CSV

Hello,

I’m trying to create a extra column in a CSV file based on a matching Mac address.

The current CSV looks like this:

name;model;type;mac;uplink_mac;uplink_port
AP_HAL_01;U7PG2;uap;80:2a:a8:56:0d:5e;78:8a:20:c5:32:fb
AP_G07_01;U7PG2;uap;fc:ec:da:34:5b:9d;78:8a:20:c5:32:fb
SW08_O31;US8P150;usw;78:8a:20:c5:32:fb;04:18:d6:f0:f9:22;
AP_C61_01;U7PG2;uap;80:2a:a8:13:57:dd;78:8a:20:c5:32:8c
SW08_G00;US8P150;usw;78:8a:20:c5:32:8c;04:18:d6:f0:f9:22;
AP_O36_01;U7PG2;uap;80:2a:a8:16:d3:21;04:18:d6:c3:24:ff
AP_O09_01;U7PG2;uap;80:2a:a8:56:0d:7d;04:18:d6:c3:24:ff
SW48_B18_02;US48P500;usw;04:18:d6:c3:24:ff;80:2a:a8:1c:0e:cd;

The output should look like this:

name;model;type;mac;uplink_mac;uplink_device;uplink_port;
AP_HAL_01;U7PG2;uap;80:2a:a8:56:0d:5e;SW08_O31;78:8a:20:c5:32:fb
AP_G07_01;U7PG2;uap;fc:ec:da:34:5b:9d;SW08_O31;78:8a:20:c5:32:fb
SW08_O31;US8P150;usw;78:8a:20:c5:32:fb;;04:18:d6:f0:f9:22;
AP_C61_01;U7PG2;uap;80:2a:a8:13:57:dd;SW08_O31;78:8a:20:c5:32:8c
SW08_O31;US8P150;usw;78:8a:20:c5:32:8c;;04:18:d6:f0:f9:22;
AP_O36_01;U7PG2;uap;80:2a:a8:16:d3:21;SW48_B18_02;04:18:d6:c3:24:ff
AP_O09_01;U7PG2;uap;80:2a:a8:56:0d:7d;SW48_B18_02;04:18:d6:c3:24:ff
SW48_B18_02;US48P500;usw;04:18:d6:c3:24:ff;;80:2a:a8:1c:0e:cd;

Hoping somebody can help me with this.

Thanks,

Martijn

I think it would have been enough to post just a few lines (10 to 15) to illustrate what you’re looking for.

But anyway you forgot to describe what you want to do and you forgot to post your code. :wink:

Hello Olaf,

Sorry I edited my post. I’l try to explain what I’m trying to do (my English isn’t that good).

In the CSV file there are two columns with mac addresses, I want to match the “uplink_Mac” with the “mac” column, if a match is found then add the name in an extra column “uplink_device”.
So the goal is that I can see to which switch a access point is connected to, instead of showing the mac address of the switch.

# Get Registered Clients
    $uDevices = Invoke-RestMethod -Method Get -Uri "$($uController)/api/s/$($uSiteID)/stat/device" -WebSession $UBNT -Headers $uHeaders
    # Get Active Clients
    # $uActiveClients = Invoke-RestMethod -Method Get -Uri "$($uController)/api/s/$($uSiteID)/stat/sta" -WebSession $UBNT -Headers $uHeaders

$ExportCSVLijst			= "C:\Users\mvdheijden\OneDrive\Scripts\Unifi\Output\Devices.csv"

$uDevices.data | 
    Select-Object -Property name, model, type, mac, 
        @{Name="uplink_mac"; Expression={$_.last_uplink.uplink_mac}},
        @{Name="uplink_port"; Expression={$_.last_uplink.uplink_remote_port}},
        ip |
		Export-Csv -Path $ExportCSVLijst -NoTypeInformation

Oh … the code looks familiar … :wink:

$uDevices.data | 
    Select-Object -Property name, model, type, mac, 
        @{Name = 'uplink_mac' ; Expression = {$_.last_uplink.uplink_mac}},
        @{Name = 'uplink_port' ; Expression = {$_.last_uplink.uplink_remote_port}},
        @{Name = 'uplink_device' ; Expression = {$_.last_uplink.uplink_mac -eq $_.mac}},
        ip |
            Export-Csv -Path $ExportCSVLijst -NoTypeInformation

This way you compare the two values and get a $true or a $false.

Don’t worry. It’s more than enough. And I’m not a native English spreaker as well. :wink:

:wink: Thanks for your response.

That’s almost it, but the match isn’t on the same line in the CSV. So some lines in the CSV are AP’s and some Are Switches.

 

I think this is what you are looking for.

$csv = @"
name;model;type;mac;uplink_mac;uplink_port
AP_HAL_01;U7PG2;uap;80:2a:a8:56:0d:5e;78:8a:20:c5:32:fb
AP_G07_01;U7PG2;uap;fc:ec:da:34:5b:9d;78:8a:20:c5:32:fb
SW08_O31;US8P150;usw;78:8a:20:c5:32:fb;04:18:d6:f0:f9:22;
AP_C61_01;U7PG2;uap;80:2a:a8:13:57:dd;78:8a:20:c5:32:8c
SW08_G00;US8P150;usw;78:8a:20:c5:32:8c;04:18:d6:f0:f9:22;
AP_O36_01;U7PG2;uap;80:2a:a8:16:d3:21;04:18:d6:c3:24:ff
AP_O09_01;U7PG2;uap;80:2a:a8:56:0d:7d;04:18:d6:c3:24:ff
SW48_B18_02;US48P500;usw;04:18:d6:c3:24:ff;80:2a:a8:1c:0e:cd;
"@

# Convert string object to array of objects
$test = $csv | ConvertFrom-Csv -Delimiter ';'

# Put name and mac in hashtable
$hashlist = @{}
$test | Select-Object name,mac | 
ForEach-Object {$hashlist.Add($_.mac,$_.name)}

# If uplink_mac exists in hashtable keys then print value (name)
$result = 
$test | Select-Object name,model,type,mac,uplink_mac,
@{n='uplink_device';exp={If ($hashlist[$_.uplink_Mac]){$hashlist[$_.uplink_Mac]}}}

# Display result in table format
$result | Format-Table -AutoSize

Sometimes a picture is worth a thousand words. :wink: Now I got it what you was looking for.

Great solution. Thanks.

That’s exactly what i was looking for.

Thanks a lot, guys!