VMware Cloud Community
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Help required to combine the output in CSV

I am running the below script to collect some properties of the VM and it displays the output in the screen, I would like to export them to the CSV file

$vms = Get-VM

write "VM's with Active Snapshots:"

foreach ($vm in $vms | where { $_ | Get-Snapshot}) {

  write $vm.name

}

write "VMware tools outdated:"

foreach ($vm in $vms | where { $_.ExtensionData.Guest.ToolsStatus -eq "toolsOld"}) {

  write $vm.name

}

write "VM's with CD-ROM Connected:"

foreach ($vm in $vms | where { $_ | Get-CDDrive | where { $_.ConnectionState.Connected -eq "true"}}) {

  write $vm.name

}

write "VM's with Floppy Connected:"

foreach ($vm in $vms | where { $_ | Get-FloppyDrive | where { $_.ConnectionState.Connected -eq "true"}}) {

  write $vm.name

}

Write "VM's with Limits"

Get-VM | Get-VMResourceConfiguration | Where-Object {$_.CpuLimitMhz -ne '-1'-Or $_.MemLimitMB -ne '-1'} | Select-Object VM

write "VM's Created in Last 24 hours:"

Get-VIEvent -maxsamples 10000 -Start (Get-Date).AddDays(-1) |

where {$_.Gettype().Name-eq "VmRemovedEvent"} |

Select @{N="VM";E={$_.VM.Name}},UserName

write "VM's Deleted in Last 24 hours:"

Get-VIEvent -maxsamples 10000 -Start (Get-Date).AddDays(-1) |

where {$_.Gettype().Name-eq "VmCreatedEvent" -or $_.Gettype().Name-eq "VmBeingClonedEvent" -or $_.Gettype().Name-eq "VmBeingDeployedEvent"} |

Select @{N="VM";E={$_.VM.Name}},UserName

Looking for the following output:

PropertiesVM name
VM's with Active SnapshotsVM1, Vm2
VMware tools outdatedVM3, Vm4
VM's with Floppy ConnectedVM5, Vm6
VM's with LimitsVM9, Vm8
VM's Created in Last 24 hours:VM1 by user1, Vm2 by User2

Thanks!!

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

Try like this

$report = @()

$vms = Get-VM

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Active Snapshots"
 
"VM name" = &{
   
$result = $vms | where {$_ | Get-Snapshot} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VMware tools outdated"
 
"VM name" = &{
   
$result = $vms | where {$_.ExtensionData.Guest.ToolsStatus -eq "toolsOld"} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Floppy Connected"
 
"VM name" = &{
   
$result = $vms | where {$_ | Get-FloppyDrive | where { $_.ConnectionState.Connected -eq "true"}} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Limits"
 
"VM name" = &{
   
$result = Get-VMResourceConfiguration -VM $vms | Where-Object {$_.CpuLimitMhz -ne '-1'-Or $_.MemLimitMB -ne '-1'} | %{$_.VM.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's Created in Last 24 hours:"
 
"VM name" = &{
   
$result = Get-VIEvent -MaxSamples ([int]::MaxValue) -Start (Get-Date).AddDays(-1) |
     
where {$_.Gettype().Name -eq "VmCreatedEvent" -or $_.Gettype().Name -eq "VmBeingClonedEvent" -or $_.Gettype().Name -eq "VmBeingDeployedEvent"} |
     
%{"{0} created by {1}" -f $_.VM.Name,$_.UserName}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report | Select Properties,"VM name" | Export-Csv report.csv -NoTypeInformation -UseCulture

I included the tests to avoid the errors with the Join function


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

View solution in original post

Reply
0 Kudos
19 Replies
LucD
Leadership
Leadership
Jump to solution

This is a simple script that should do this

$vms = Get-VM
New-Object PSObject -Property @{
 
"VM's with Active Snapshots" = [string]::Join(',',($vms | where {$_ | Get-Snapshot} | %{$_.Name}))
 
"VMware tools outdated" = [string]::Join(',',($vms | where {$_.ExtensionData.Guest.ToolsStatus -eq "toolsOld"} | %{$_.Name}))
 
"VM's with Floppy Connected" = [string]::Join(',',($vms | where {$_ | Get-FloppyDrive | where { $_.ConnectionState.Connected -eq "true"}} | %{$_.Name}))
 
"VM's with Limits" = [string]::Join(',',(Get-VMResourceConfiguration -VM $vms | Where-Object {$_.CpuLimitMhz -ne '-1'-Or $_.MemLimitMB -ne '-1'}))
 
"VM's Created in Last 24 hours:" = [string]::Join(',',
    (
Get-VIEvent -MaxSamples ([int]::MaxValue) -Start (Get-Date).AddDays(-1) |
   
where {$_.Gettype().Name-eq "VmCreatedEvent" -or $_.Gettype().Name-eq "VmBeingClonedEvent" -or $_.Gettype().Name-eq "VmBeingDeployedEvent"} |
   
%{$_.VM.Name}))
}

But be aware that the code doesn't test if some of these categories do not return a value.

For example, if there are no VMs with a floppy connected, you will probably get an error on the Join function.


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Thanks Luc, Need some modification in the script and notice the below:

  1. The  “Vm’s with limit doesn’t give the vm names” instead gives the output as NumCpuShares/ CpuSharesLevel and same for memory for all the VM’s
  2. Is it possible to get the username of the person who create the VM
  3. When I try to get export it to a CSV file it gives the properties in column , but I am looking to get the properties in row and corresponding VM details next to them
If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try like this

$report = @()

$vms = Get-VM

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Active Snapshots"
 
"VM name" = &{
   
$result = $vms | where {$_ | Get-Snapshot} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VMware tools outdated"
 
"VM name" = &{
   
$result = $vms | where {$_.ExtensionData.Guest.ToolsStatus -eq "toolsOld"} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Floppy Connected"
 
"VM name" = &{
   
$result = $vms | where {$_ | Get-FloppyDrive | where { $_.ConnectionState.Connected -eq "true"}} | %{$_.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's with Limits"
 
"VM name" = &{
   
$result = Get-VMResourceConfiguration -VM $vms | Where-Object {$_.CpuLimitMhz -ne '-1'-Or $_.MemLimitMB -ne '-1'} | %{$_.VM.Name}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report += New-Object PSObject -Property @{
 
Properties = "VM's Created in Last 24 hours:"
 
"VM name" = &{
   
$result = Get-VIEvent -MaxSamples ([int]::MaxValue) -Start (Get-Date).AddDays(-1) |
     
where {$_.Gettype().Name -eq "VmCreatedEvent" -or $_.Gettype().Name -eq "VmBeingClonedEvent" -or $_.Gettype().Name -eq "VmBeingDeployedEvent"} |
     
%{"{0} created by {1}" -f $_.VM.Name,$_.UserName}
   
if($result){
      [
string]::Join(',',$result)
    }
  }
}

$report | Select Properties,"VM name" | Export-Csv report.csv -NoTypeInformation -UseCulture

I included the tests to avoid the errors with the Join function


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

It looks good expect for the VM created event, it doesn’t provide any output here, but when I run a one-liner to check for any VM’s created is last 1 day it gives me the output but blank when checked with the script

Thanks!!

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Just noticed that there was no blank in front of the -eq operator.

I updated that line above. The event part works for me now.


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Tried again still blank with no output for vm creation

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Tried it again in my environment, I get results.

Perhaps something went wrong in the copy/paste.

Can you attach your version as a file attachment so I can have a look ?


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Script attached

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Got it (I think), there is an extra blank in the propertyname "VM name".

In the Select there is no blank, for PowerShell that is another property.

merge.png


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

aravinds3107
Virtuoso
Virtuoso
Jump to solution

That works fine!!

Thanks...

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Hi, Is it possible to break the output which we get using Join String and place them in the separate line, As of now we get the output as VM1, VM2, VM3,etc…

Is it possible we get the Output as below

PropertiesVM name
VM's with Active SnapshotsVM1
Vm2
VMware tools outdatedVM3
Vm4
VM's with Floppy ConnectedVM5
Vm6
VM's with LimitsVM9
Vm8
VM's Created in Last 24 hours:VM1 by user1
Vm2 by User2
If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try with the following conversion script

&{foreach($row in (Import-Csv C:\Aravind\VM.csv -UseCulture)){

    $first = $true

    if($row."VM name" -match ','){

        ($row."VM name").Split(',') | %{

            New-Object PSObject -Property @{

                Properties = &{

                    if($first){

                        $row.Properties

                        $first = $false

                    }}

                "VM name" = $_

            }

        }

    }

    else{$row}

}} | Export-Csv new-report.csv -NoTypeInformation -UseCulture


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

The entire format gets changed when i run the above script as below

VM nameProperties
VM4VM's with Active Snapshots
VM1VM's with Active Snapshots
VM5VMware tools outdated
VM6VMware tools outdated
VM6VM's with CD-ROM Connected
Vm6VM's with Floppy Connected
VM6VM's with Limits
VM1 created by DEMOCLOUD\aravindVM's Created in Last 24 hours
TestVM4 created by DEMOCLOUD\aravindVM's Created in Last 24 hours
TestVM2 created by DEMOCLOUD\aravindVM's Created in Last 24 hours
TestVM1 created by DEMOCLOUD\aravindVM's Created in Last 24 hours
DataONTAP created by DEMOCLOUD\aravindVM's Deleted in Last 24 hours
DataONTAP created by DEMOCLOUD\aravindVM's Deleted in Last 24 hours
TestVM4 created by DEMOCLOUD\aravindVM's Deleted in Last 24 hours
TestVM3 created by DEMOCLOUD\aravindVM's Deleted in Last 24 hours
If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try with this version

&{foreach($row in (Import-Csv C:\Aravind\VM.csv -UseCulture)){
   
$first = $true
   
if($row."VM name" -match ','){
      (
$row."VM name").Split(',') | %{
       
New-Object PSObject -Property @{
         
Properties = &{
           
if($first){
             
$row.Properties
             
$first = $false
            }
           
else{""}
          }
         
"VM name" = $_
        }
      }
    }
   
else{$row}
}}
| Select Properties,"VM name" |
Export-Csv new-report.csv -NoTypeInformation -UseCulture


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

Still I get it in the same format...

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

You mean the columns in the wrong order, or always a value for the Properties column ?


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

Reply
0 Kudos
aravinds3107
Virtuoso
Virtuoso
Jump to solution

For each value there is a properties column...

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

My bad, I forgot about the scope of the $first variable.

Try this version

&{foreach($row in (Import-Csv C:\Aravind\VM.csv -UseCulture)){
   
$script:first = $true
   
if($row."VM name" -match ','){
      (
$row."VM name").Split(',') | %{
       
New-Object PSObject -Property @{
         
Properties = &{
           
if($script:first){
             
$row.Properties
             
$script:first = $false
            }
           
else{""}
          }
         
"VM name" = $_
        }
      }
    }
   
else{$row}
}}
| Select Properties,"VM name"


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

aravinds3107
Virtuoso
Virtuoso
Jump to solution

It looks good now.. Thanks

If you find this or any other answer useful please consider awarding points by marking the answer correct or helpful |Blog: http://aravindsivaraman.com/ | Twitter : ss_aravind
Reply
0 Kudos