Home > Back-end >  Replace second from last grouping of the MAC address in a csv file
Replace second from last grouping of the MAC address in a csv file

Time:09-20

I am struggling for a couple of days to manipulate the output of a script, that generates the full IPtable of a vm, for the purpose of restoring set settings once the host is upgraded. Bellow is the .ps1 file:

$sudatapath = "c:\UpgradeData"
$OSV = [environment]::OSVersion.Version
$psVersion = $PSVersionTable.PSVersion
If ($psVersion) {
$psversion=2
}
Else{
$psversion=1
}
write-host "vmretainer ran" >$sudatapath\vmretainerran.txt
#Check if task needs scheduling
if( -not (test-path "$sudatapath\iprestoredfinal.flg") -and ($psversion -eq 2))
{
write-host $psversion
schtasks /create /tn "vmipretainer" /sc onstart /rl highest /ru system /tr "powershell.exe -Executionpolicy bypass -NonInteractive -NoLogo -NoProfile -File c:\UpgradeData\vmipretainer.ps1" /F
}

if( -not (test-path "$sudatapath\iprestoredfinal.flg") -and ($psversion -eq 1))
{
write-host $psversion
schtasks /create /tn "vmipretainer" /sc onstart /rl highest /ru system /tr "powershell.exe c:\UpgradeData\vmipretainer.ps1 -noprofile –Noninteractive" /F
}


if( -not (test-path "$sudatapath\ipexported.flg"))
{
if( -not (test-path "$sudatapath")) {mkdir $sudatapath}
write-host "ipexport ran" >$sudatapath\ipexportran.txt


if ($psversion -eq 2)
{
$ResultIPTable = @()
$NICs = Get-WMIObject Win32_NetworkAdapterConfiguration -computername . | where{$_.IPEnabled -eq $true} 
Foreach($NIC in $NICs) {
    $IPTable = @{}
    $IPTable.Add("sIP",$NIC.IPAddress[0])
    $IPTable.Add("sMACAddress",$NIC.MACAddress)
    $IPTable.Add("sgateway",[string]$NIC.DefaultIPGateway)
    $IPTable.Add("smask",$NIC.IPSubnet[0])
    $IPTable.Add("sdns",[string]$NIC.DNSServerSearchOrder)
    $IPTable.Add("sdescription",$NIC.Description)
    $IPTable.Add("sDHCPEnabled",$NIC.DHCPEnabled)
    $IPTable.Add("sDNSDomain",$NIC.DNSDomain)
    $ResultIPTable  = New-Object PsObject -Property $IPTable
    }
$ResultIPTable | Export-Csv -NoTypeInformation "$sudatapath\ipsettings.csv"
#$ResultIPTable | format-table -property *
    $gd = Get-Date
    echo "exportedsettings" $gd >"$sudatapath\ipexported.flg"
exit


}

if ($psversion -eq 1)
{
$result2008table = @()
$NICs = Get-WMIObject Win32_NetworkAdapterConfiguration -computername . | where{$_.IPEnabled -eq $true} 
Foreach($NIC in $NICs) {
$2008table = @{}
$2008table = New-Object -TypeName PSObject
$2008table | Add-Member -MemberType NoteProperty -Name sIP -Value $NIC.IPAddress[0]
$2008table | Add-Member -MemberType NoteProperty -Name sMACAddress -Value $NIC.MACAddress
$2008table | Add-Member -MemberType NoteProperty -Name sgateway -Value ([string]$NIC.DefaultIPGateway) 
$2008table | Add-Member -MemberType NoteProperty -Name smask -Value $NIC.IPSubnet[0]
$2008table | Add-Member -MemberType NoteProperty -Name sdns -Value ([string]$NIC.DNSServerSearchOrder)
$2008table | Add-Member -MemberType NoteProperty -Name sdescription -Value $NIC.Description
$2008table | Add-Member -MemberType NoteProperty -Name sDHCPEnabled -Value $NIC.DHCPEnabled
$2008table | Add-Member -MemberType NoteProperty -Name sDNSDomain -Value $NIC.DNSDomain
$Result2008Table  = $2008table
$Result2008Table | format-table -property * 
$Result2008Table | Export-Csv -NoTypeInformation "$sudatapath\ipsettings.csv"
$gd = Get-Date
    echo "exportedsettings" $gd >"$sudatapath\ipexported.flg"
}
}
    }
if (test-path "$sudatapath\ipexported.flg")
{
    write-host "Restoring NIC IP"
    write-host "Restoring NIC IP" >$sudatapath\iprestoreran.txt
$NICIMPORTS = Import-Csv "$sudatapath\ipsettings.csv"
$i = 0
$NICs = Get-WMIObject Win32_NetworkAdapterConfiguration -computername . | where{$_.IPEnabled -eq $true}
    foreach ($NICIMPORT in $NICIMPORTS){ 
        $sIP = $NICIMPORT.sIP 
        $sMACAddress = $NICIMPORT.sMACAddress 
        $sgateway = $NICIMPORT.sgateway.trim('{}')  
        $smask = $NICIMPORT.smask 
        $sdns = $NICIMPORT.sdns.trim('{}') 
#       $sdescription = $NICIMPORT.sdescription 
        $sDHCPEnabled = $NICIMPORT.sDHCPEnabled
#       $sDNSDomain = $NICIMPORT.sDNSDomain
        
        if( $NICs[$i].IPAddress[0] -ne $sIP)
        {
            write-host "IPs do not match $NICs.IPAddress[0] $sIP"
            write-host "IPs do not match $NICs.IPAddress[0] $sIP" >$sudatapath\ipchangeran.txt
            $sndns=($sdns.Split(" ")[0],$sdns.Split(" ")[1])
            write-host "Restoring IP Address from CSV"
            $NICs[$i].EnableStatic($sIP, $smask)
            $NICs[$i].SetGateways($sgateway)
            $NICs[$i].SetDNSServerSearchOrder($sndns)
            $gd = Get-Date
                                                                    
        }   
        $i  
    }
    echo "IPrestored" $gd > "$sudatapath\iprestored.flg" 
    if (Get-item "$sudatapath\ipexported.flg" | Where LastWriteTime -lt (Get-Date).AddDays(-60)){schtasks.exe /delete /f /tn vmipretainer}
} 

I tried numerous attempts, all have failed. Here is one of them:

$sudatapath = "c:\UpgradeData"
$CSV = Import-CSV Import-Csv "$sudatapath\ipsettings.csv"

$newCsv = foreach ($row in $CSV) {
$row | Select-Object sMAcadress, @{Expression = {$row.sMACAddress.remove($13, $14).insert($13, '99')}}
$newCsv | Export-Csv -NoTypeInformation '$sudatapath\ipsettings2.csv'

}

I am looking for somethin inside the original script that does the thing that I want, at the moment the csv file looks like this:

"sdns","sMACAddress","sDNSDomain","sdescription","sDHCPEnabled","smask","sIP","sgateway"
"","00:15:5D:73:48:00",,"Microsoft Hyper-V Network Adapter","False","255.255.255.192","10.52.115.31","10.52.115.62"
"","00:15:5D:73:48:01",,"Microsoft Hyper-V Network Adapter #2","False","255.255.255.192","192.168.10.31",""

I want to change the value in column sMACAddress like this:

aa:bb:cc:dd:ee:ff to aa:bb:cc:dd:99:ff wihtout knowing the stored value of the column (macaddresses are dynamic, this is done for a match after the upgrade, that is why I need a specific value for the second to last pair, for the host I managed to figured it out, it is this script that is driving me crazy) This made me crazy for now and I am trying to ask others if there is any solution to this. Thank you for your input and have a great day.

CodePudding user response:

If all you're looking for is the replacement of the 5th octet in the MAC Address for every row, one option is to use RegEx from this inside of a calculated property:

@"
"sdns","sMACAddress","sDNSDomain","sdescription","sDHCPEnabled","smask","sIP","sgateway"
"","00:15:5D:73:48:00",,"Microsoft Hyper-V Network Adapter","False","255.255.255.192","10.52.115.31","10.52.115.62"
"","00:15:5D:73:48:01",,"Microsoft Hyper-V Network Adapter #2","False","255.255.255.192","192.168.10.31",""
"@ | ConvertFrom-Csv | 
    Select-Object -ExcludeProperty 'sMACAddress' -Property *, 
        @{
            Name = 'sMACAddress';
            Expression = {
                $_.sMACAddress -replace "..(?=:..$)",'99'
            }
        }

This should give you results of:

sMACAddress      
-----------      
00:15:5D:73:99:00
00:15:5D:73:99:01

Here's a RegEx Demo that explains the pattern matching.

  • Related