locked
Need help on to get info from csv file using multiple columns RRS feed

  • Question

  • Hi Team,

    Can anyone please help me with below reference to create the script. My csv looks like below:

    ServerName  IPAddress  SubnetMask MACAddress
    Server01 10.0.0.1 255.255.255.0   00:00:00:00:00:01
    Server02 10.0.0.2 255.255.255.0 00:00:00:00:00:02
    Server03 10.0.0.3 255.255.255.0 00:00:00:00:00:03

    I am using this cmdlet to search the NIC via mac and then will assign the mentioned IPAddress and SubnetMask to that Mac: 

    Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName server01 | Where-Object { $_.MACAddress -eq "00:00:00:00:00:01" }

    I have tested the cmdlet with one server and assigned the IP/Subnet successfully. But I am stuck when I want to query further servers with corresponding mac. I need help so that I can run script one time only with querying all servers to corresponding mac.

    Please let me know if you have any further question.


    JPS


    • Edited by jps0319 Saturday, January 25, 2020 3:36 PM
    Saturday, January 25, 2020 3:36 PM

All replies

  • Start with:

    help import-csv -online
    help foreach-object -online

    Also the correct way to use WMI is as follows:

    Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName server01 -FIlter "MACAddress = '00:00:00:00:00:01'"


    \_(ツ)_/

    Saturday, January 25, 2020 3:41 PM
  • That doesn't look like a CSV. There are no commas separating the columns. If the space between the represent tab characters you can use Import-CSV with the -Delimiter parameter. If the spaces between the columns are really spaces you can try something like this:

       $x = -split <one line of your data>


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Saturday, January 25, 2020 4:43 PM
  • Hi Rich,

    I am sorry for confusion. This is the csv file only, I typed  it manually while posting my query. I am attaching screen shot of my csv. Please refer to that.

    


    JPS

    Sunday, January 26, 2020 11:00 AM
  • Please read the help I posted.  You need to start at the beginning.

    Your file must be a quoted, comma separated file.  If it is the help will teach you how to use this with PowerShell.


    \_(ツ)_/


    • Edited by jrv Sunday, January 26, 2020 12:19 PM
    Sunday, January 26, 2020 12:18 PM
  • Well, that's a screenshot of a spreadsheet in some application (e.g., Excel). You'll have export it to a CSV file.

    The basics of what you probably want to do look like this:

    import-csv <your-file>| foreach { # what you want to do with the data returned by GWMI only you know
    Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $_.ServerName -Filter "MACAddress = '$($_.MACAddress)'"
    }



    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)




    Sunday, January 26, 2020 6:25 PM
  • Well, that's a screenshot of a spreadsheet in some application (e.g., Excel). You'll have export it to a CSV file.

    The basics of what you probably want to do look like this:

    import-csv <your-file>| 
        foreach {
            # what you want to do with the data returned by GWMI only you know
            Get-WmiObject . . . -Filter "MACAddress = '$_.MACAddress'"
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Rich - look at the WMI line.  I know it is an example but one part will be a problem for anyone with no experience with PS.


    \_(ツ)_/

    Sunday, January 26, 2020 7:28 PM
  • Thanks jrv, I dig down to foreach and csv also however I understood csv file handling for my task using below link:

    https://stackoverflow.com/questions/55126976/bulk-ip-configuration-using-csv-input


    JPS

    Sunday, January 26, 2020 8:47 PM
  • If you remotely change the IP you will lose the connection.  Remoting is not necessary.

    I posted a link to the method.

    https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/enablestatic-method-in-class-win32-networkadapterconfiguration

    There is a code example of how to do this.


    \_(ツ)_/

    Sunday, January 26, 2020 9:53 PM
  • Is that better? I changed the line to eliminate the ellipsis, provide the class name, and get the computer name from the CSV.

    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)


    Monday, January 27, 2020 3:52 AM
  • Still wrong.  Look at the filter.


    \_(ツ)_/

    Monday, January 27, 2020 4:23 AM
  • Yes jrv, I used same method to complete my task. Here is the script I used. 

                                                                             
    $csv = Import-Csv -Path C:\JPS\Servers.csv


    foreach ($row in $csv) {
        $server = $row.servername
        $ip = $row.ipaddress
        $mask = $row.subnetmask
        $mac =$row.macaddress
          
    $nic = Get-WmiObject -Class Win32_NetworkAdapterConfiguration -ComputerName $server  -Filter "MACAddress = '$mac'"
    $nic.EnableStatic("$ip","$mask")



    JPS

    Monday, January 27, 2020 9:15 AM
  • It looks like everybody needs to go back to school.

    Import-Csv C:\JPS\Servers.csv |
        ForEach-Object{
            $nic = Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $_.servername  -Filter "MACAddress = '$($_.macaddress)'"
            $nic.EnableStatic(@($_.ipaddress),@($_.subnetmask))
        } 

    Being wrong either by a little or conceptually will only lead to greater failures over time.  That was teh best lesson I learned in first grade.


    \_(ツ)_/

    Monday, January 27, 2020 9:36 AM
  • <Facepalm> DOH! </Facepalm>


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, January 27, 2020 3:46 PM
  • <Facepalm> DOH! </Facepalm>


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    See. It was a simple thing that we all do al of the time.  It kills me but making me/you look at it may stop the brain fart in the future.


    \_(ツ)_/

    Monday, January 27, 2020 4:30 PM
  • "Failure is the best way of learning."

    "Perfection is not going to be optimal for learning." (Seems to be that a 15% failure rate is).

    "Learn from the mistakes of others. You can’t live long enough to make them all yourself." (Eleanor Roosevelt).

    The older I get, the more the last one seems so true.


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    Monday, January 27, 2020 9:00 PM
  • No.  Asking good questions is the best way.  Each question points to more questions.  As long as you are always asking new questions you are lewarning.

    The phrase "Failure is the best way of learning." is a bastardization of "Learn from your mistakes.".

    Observation, research, reading what others have discovered is the best approach to learning.


    \_(ツ)_/

    Monday, January 27, 2020 9:43 PM