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.

CSV

 

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!