Help required to combine the output in CSV

[aravinds3107]

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:

Properties	VM name
VM's with Active Snapshots	VM1, Vm2
VMware tools outdated	VM3, Vm4
VM's with Floppy Connected	VM5, Vm6
VM's with Limits	VM9, Vm8
VM's Created in Last 24 hours:	VM1 by user1, Vm2 by User2

Thanks!!

[LucD]

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.

[aravinds3107]

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

[LucD · Accepted 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

[aravinds3107]

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!!

[LucD]

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.

[aravinds3107]

Tried again still blank with no output for vm creation

[LucD]

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 ?

[aravinds3107]

Script attached

[LucD]

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.

[aravinds3107]

That works fine!!

Thanks...

[aravinds3107]

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

Properties	VM name
VM's with Active Snapshots	VM1 Vm2
VMware tools outdated	VM3 Vm4
VM's with Floppy Connected	VM5 Vm6
VM's with Limits	VM9 Vm8
VM's Created in Last 24 hours:	VM1 by user1 Vm2 by User2

[LucD]

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

[aravinds3107]

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

VM name	Properties
VM4	VM's with Active Snapshots
VM1	VM's with Active Snapshots
VM5	VMware tools outdated
VM6	VMware tools outdated
VM6	VM's with CD-ROM Connected
Vm6	VM's with Floppy Connected
VM6	VM's with Limits
VM1 created by DEMOCLOUD\aravind	VM's Created in Last 24 hours
TestVM4 created by DEMOCLOUD\aravind	VM's Created in Last 24 hours
TestVM2 created by DEMOCLOUD\aravind	VM's Created in Last 24 hours
TestVM1 created by DEMOCLOUD\aravind	VM's Created in Last 24 hours
DataONTAP created by DEMOCLOUD\aravind	VM's Deleted in Last 24 hours
DataONTAP created by DEMOCLOUD\aravind	VM's Deleted in Last 24 hours
TestVM4 created by DEMOCLOUD\aravind	VM's Deleted in Last 24 hours
TestVM3 created by DEMOCLOUD\aravind	VM's Deleted in Last 24 hours

[LucD]

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

[aravinds3107]

Still I get it in the same format...

[LucD]

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

[aravinds3107]

For each value there is a properties column...

[LucD]

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"

[aravinds3107]

It looks good now.. Thanks