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

Welcome Forums General PowerShell Q&A Search for matching Mac address in two separate columns in a CSV

Viewing 7 reply threads
  • Author
    Posts
    • #204465
      Participant
      Topics: 2
      Replies: 6
      Points: 38
      Rank: Member

      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

    • #204495
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      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. πŸ˜‰

      • This reply was modified 3 months ago by Olaf.
      • This reply was modified 3 months ago by Olaf.
    • #204513
      Participant
      Topics: 2
      Replies: 6
      Points: 38
      Rank: Member

      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
    • #204525
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      Oh … the code looks familiar … πŸ˜‰

      $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.

      (my English isn’t that good).

      Don’t worry. It’s more than enough. And I’m not a native English spreaker as well. πŸ˜‰

      • This reply was modified 3 months ago by Olaf.
    • #204540
      Participant
      Topics: 2
      Replies: 6
      Points: 38
      Rank: Member

      πŸ˜‰ 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

       

    • #204729
      Participant
      Topics: 5
      Replies: 321
      Points: 436
      Helping Hand
      Rank: Contributor

      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
      
    • #204780
      Participant
      Topics: 4
      Replies: 2231
      Points: 5,414
      Helping Hand
      Rank: Community MVP

      … 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.

      Sometimes a picture is worth a thousand words. πŸ˜‰ Now I got it what you was looking for.

      I think this is what you are looking for. …

      Great solution. Thanks.

    • #204840
      Participant
      Topics: 2
      Replies: 6
      Points: 38
      Rank: Member

      That’s exactly what i was looking for.

      Thanks a lot, guys!

Viewing 7 reply threads
  • The topic ‘Search for matching Mac address in two separate columns in a CSV’ is closed to new replies.