JCayetano
Enthusiast
Enthusiast

Unable to export to CSV file

Jump to solution

Hello folks , I need some help I am stuck with an easy task! but I could not make it to work

I need to create a  new csv file that will contain only the VM names of those that failed to the telnet test

Note: for this test since the Telnet is success ( if ($cond -eq 'True')) I am using that , eventually I will move the code to accomplish this when the telnet fails  if ($cond -eq 'False').

So I have a script that reads the vms from a csv file

(so far I am testing with 2 vms ),

CSV source as follow (dr_adhoc.csv):

vmname

VM-DR-XXXYXZ01

VM-DR-XXXYXZ02

$vms = Import-CSV  H:....\dr_adhoc.csv

foreach ($vm in $vms){

      $vmn = $vm.vmname

write-host "--- DR Phase 3/3 : Validating the telnet  connection to  :---" -ForegroundColor Cyan

Test-NetConnection -ComputerName $vmn -Port $port | Select-Object TcpTestSucceeded

$cond=$?

      #write-host $cond

       if ($cond -eq 'True'){

       Get-VM -name $vmn | Select-Object name | Export-Csv "H:\Scripts\X\Y\Z\X\VM_list$(get-date -f MM-dd-yyyy).csv"  -NoTypeInformation

        }

Those were the results :

when I used Get-VM -name $vmn | Select-Object name |  Export-Csv "H:\Scripts\X\Y\Z\X\VM_list$(get-date -f MM-dd-yyyy).csv"  -NoTypeInformation

The csv file was created  however it only contain the last server and ' " '  and not both:

"Name"

"VM-DR-XXXYXZ02"

when I used Get-VM -name $vmn |select name >> H:\Scripts\X\Y\Z\X\VM_list$(get-date -f MM-dd-yyyy).csv

it is the most closest solution ,  however ...  as you can see it has more than the vm names  I had colored  on red the ones that I don't need to see

also it has blank lines Smiley Sad

Name                                                                                                                                                        

----                                                                                                                                                        

VM-DR-XXXYXZ01                                                                                                                                         

Name                                                                                                                                                        

----                                                                                                                                                        

VM-DR-XXXYXZ02                                                                                                                                            

Basically I would like to have a handy list of those vms that were not allowing the tcp connections , in that way I can re use that list to re run another script to resolve that tcp connection.  :  )

Thank you so much for the help!

1 Solution

Accepted Solutions
LucD
Leadership
Leadership

I'm afraid that is the standard CSV format, see Comma-separated values

If you want to avoid that you will have do your own formatting and send to a file with Out-File or Add-Content.

An alternative is to change the CSV file afterwards with something like this

(Get-Content -Path .\myfile.csv) | Foreach-Object {$_ -replace '"', ''} | Out-File -FilePath .\myfile.csv


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

9 Replies
LucD
Leadership
Leadership

You are overwriting the CSV file for each VM, since you placed the Export-Csv inside the loop.

Try like this

$vms = Import-CSV  H:....\dr_adhoc.csv

$report = @()


foreach ($vm in $vms){

   $vmn = $vm.vmname

   Write-Host "--- DR Phase 3/3 : Validating the telnet connection to :---" -ForegroundColor Cyan


   Test-NetConnection -ComputerName $vmn -Port $port | Select-Object TcpTestSucceeded

   $cond=$?

   if ($cond -eq 'True') {

   $report += Get-VM -name $vmn | Select-Object name

   }

}


$report | Export-Csv "H:\Scripts\X\Y\Z\X\VM_list$(get-date -f MM-dd-yyyy).csv"  -NoTypeInformation


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

JCayetano
Enthusiast
Enthusiast

Hello@@  Luc D , awesome!

Thank you so much , you were correct I was overwriting it.

I got what I needed , I am just curious if there is a way to omit the ' " " '

Like in linux using a grep - v  would be one of many ways to omit them

Here is the output ,

"Name"

"VM-DR-XXXYXZ01"

"VM-DR-XXXYXZ02"

0 Kudos
LucD
Leadership
Leadership

I'm afraid that is the standard CSV format, see Comma-separated values

If you want to avoid that you will have do your own formatting and send to a file with Out-File or Add-Content.

An alternative is to change the CSV file afterwards with something like this

(Get-Content -Path .\myfile.csv) | Foreach-Object {$_ -replace '"', ''} | Out-File -FilePath .\myfile.csv


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

JCayetano
Enthusiast
Enthusiast

Right , I added  the follow and that made the day!

$report | Export-Csv "$Tempvmlist"  -NoTypeInformation -UseCulture

(Get-Content -Path "$Tempvmlist") | Foreach-Object {$_ -replace '"', ''} | Out-File -FilePath $Radminvmlist$(get-date -f MM-dd-yyyy).csv

Remove-Item –path "$Tempvmlist" -ErrorAction Ignore

CSV output as expected :

Name

VM-DR-XXXYXZ01

VM-DR-XXXYXZ02

 

Thanks LucD​ for your help!

0 Kudos
JCayetano
Enthusiast
Enthusiast

Hello LucD,

​Thanks for helping me on the CSV file  problem .

​I got stuck one more time Smiley Sad 

so I added some functions to check the file shares on linux  and I would like to have a handy csv list of  all the vms that failed to mount the drives.

​I am missing something because the csv file is empty Smiley Sad I already printed the value of the array variable and the VM name is there , it is just not adding in the csv file .

​If you happened to have some minutes , would you mind to help me ? Smiley Happy

​... Here is what I have:

$vms = Import-CSV H:...~dr_adhoc.csv

#$report = @()

unable_mount = @()

foreach ($vm in $vms){

   

      $vmn = $vm.vmname

 

  write-host "--- DR Validation Started : ---" -ForegroundColor Cyan

        $OS = Get-VM -name $vmn | Select-Object Guest -WarningAction SilentlyContinue

       

if ( ($OS -match "Linux") -or ($OS -match "red hat") )

                {

  function Enterprisenfs

                         {

                          $Enterprisemount=(Invoke-VMScript -VM $vmn -ScriptText "df -h | grep apps | grep -v log | wc -l" -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue)

                           write-host "Entreprise Value (1/0)?:" $Enterprisemount

                                               

                            [int]$intNfs = [convert]::ToInt32($Enterprisemount)

                         

                         switch ($intNfs)

                         {

                            1 { write-host "NFS mount present proceeding with the next DR validation" -ForegroundColor Cyan

                                break

                              }

                            0 {

                                write-host "NFS mount not found this step will mount the file system" -ForegroundColor Cyan

                                Invoke-VMScript -VM $vmn -ScriptText "mount -a " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

                                 $enttemp=(Invoke-VMScript -VM $vmn -ScriptText "mount -a " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue)

                                                        

                                                          Write-Host "mount status" $enttemp

                                                          if (($enttemp -match "busy") -or ($enttemp -match "Failed") )

                                                              {

                                                               Write-Host "Unable to mount, Adding server in csv file"

                                                               #This line does not work## $unable_mount += Get-VM -name $vmn | Select-Object nameT

   ##This line in some how works b## Get-VM -name $vmn | Select-Object name >> H:~\VMunable_tomount.csv

                                                               break

                                                               }

                                Invoke-VMScript -VM $vmn -ScriptText "df -h | grep iodocapps | grep -v log " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

                                Enterprisenfs

                                break

                              }

                         }

# I also tried to place the instruction here to capture the vm name that had the problem mounting the shares#

#This line does not work## $unable_mount += Get-VM -name $vmn | Select-Object name

                         }

    }

$unable_mount| Export-Csv "$Tempvmlist"  -NoTypeInformation -UseCulture

(Get-Content -Path "$Tempvmlist") | Foreach-Object {$_ -replace '"', ''} | Out-File -FilePath $Radminvmlist$(get-date -f MM-dd-yyyy).csvRemove-Item –path "$Tempvmlist" -ErrorAction Ignore

​I got stuck and could not find it my problem .

​Thanks

0 Kudos
LucD
Leadership
Leadership

Try something like this.
I moved the function to the top of the script.
The function returns the name of the VMs where the mount failed.

function Invoke-Enterprisenfs {

   param(

   [string]$VMName

   )


   $result = Invoke-VMScript -VM $VMName -ScriptText "df -h | grep apps | grep -v log | wc -l" -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

   $intNfs = [int]$result.ScriptOutput

   write-host "Entreprise Value (1/0)?:" $intNfs


   switch ($intNfs) {

   1 {

   write-host "NFS mount present proceeding with the next DR validation" -ForegroundColor Cyan

   break

   }

   0 {

   write-host "NFS mount not found this step will mount the file system" -ForegroundColor Cyan

   $result = Invoke-VMScript -VM $vmn -ScriptText "mount -a " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

   Write-Host "mount status" $result.ScriptOutput

   if (($result.ScriptOutput -match "busy") -or ($result.ScriptOutput -match "Failed") ) {

   Write-Host "Unable to mount, Adding server in csv file"

   $VMName

   break

   }

   $result = Invoke-VMScript -VM $vmn -ScriptText "df -h | grep iodocapps | grep -v log " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

   break

   }

   }

}


$vms = (Import-CSV H:...~dr_adhoc.csv).vmname

$unable_mount = @()


Write-Host "--- DR Validation Started : ---" -ForegroundColor Cyan

foreach ($vm in $vms) {

   Write-Host "Looking at $($vm)"

   $OS = (Get-VM -name $vm -WarningAction SilentlyContinue).Guest.OSFullName

   if ( $OS -and ($OS -match "Linux" -or $OS -match "red hat")) {

   Write-Host "`tOS: $($OS)"

   $unable_mount += Invoke-Enterprisenfs -VMName $vm

   }

}


$unable_mount| Export-Csv "$Tempvmlist"  -NoTypeInformation -UseCulture

(Get-Content -Path "$Tempvmlist") |

   Foreach-Object {$_ -replace '"', ''} |

   Out-File -FilePath $Radminvmlist$(Get-Date -f MM-dd-yyyy).csvRemove-Item –path "$Tempvmlist" -ErrorAction Ignore


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
JCayetano
Enthusiast
Enthusiast

Interesting , I tried that  ... I got  the  length of the vm name

content of the vm file Smiley Sad

Length 12

Import-Module "VMware.VimAutomation.Core"

$mountvmlist = "H:~VM_unablemount"

$Tempvmlist = "H:~VM_temp_list.csv"

function Invoke-Enterprisenfs {

    param(

       [string]$VMName

          )

      $result = Invoke-VMScript -VM $VMName -ScriptText "df -h | grep apps | grep -v log | wc -l" -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

      $intNfs = [int]$result.ScriptOutput

       write-host "Entreprise Value (1/0)?:" $intNfs

   switch ($intNfs) {

      1 {

          write-host "NFS mount present proceeding with the next DR validation" -ForegroundColor Cyan

          break

         }

      0 {

         write-host "NFS mount not found this step will mount the file system" -ForegroundColor Cyan

         $result = Invoke-VMScript -VM $vmn -ScriptText "mount -a " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

         Write-Host "mount status" $result.ScriptOutput

            if (($result.ScriptOutput -match "busy") -or ($result.ScriptOutput -match "Failed") ) {

              Write-Host "Unable to mount, Adding server in csv file"

               $VMName

                break

        }

    $result = Invoke-VMScript -VM $vmn -ScriptText "df -h | grep iodocapps | grep -v log " -GuestUser $Guestlinux -GuestPassword $GuestPasslinux -ScriptType Bash -WarningAction SilentlyContinue

    break

   }

   }

}

$vms = (Import-CSV D:\Scripts\vscript\RH\Juan\DR\dr_adhoc.csv).vmname

$unable_mount = @()

Write-Host "--- DR Validation Started : ---" -ForegroundColor Cyan

foreach ($vm in $vms) {

   Write-Host "Looking at $($vm)"

   $OS = (Get-VM -name $vm -WarningAction SilentlyContinue).Guest.OSFullName

   if ( $OS -and ($OS -match "Linux" -or $OS -match "red hat")) {

   Write-Host "`tOS: $($OS)"

   $unable_mount += Invoke-Enterprisenfs -VMName $vm

   write-host "vm list: $unable_mount"

   }

}

$unable_mount| Export-Csv "$Tempvmlist"  -NoTypeInformation -UseCulture

(Get-Content -Path "$Tempvmlist") | Foreach-Object {$_ -replace '"', ''} |  Out-File -FilePath $mountvmlist$(Get-Date -f MM-dd-yyyy).csv -ErrorAction Ignore

0 Kudos
LucD
Leadership
Leadership

Try chaning this line

$VMName

into this

$VMName | Select @{N = 'VMName'; E = {$_}}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

JCayetano
Enthusiast
Enthusiast

Awesome!

That made the day

vmname

VM-DR-XXXYXZ01

VM-DR-XXXYXZ02

Thanks LucD for the help

0 Kudos