Report: The creator of VM, who created the VM?

[SaqiChangx]

HI,

here I have a script and want to find out the Creator of the VM.

But the result comes is empty. I am sure there is a problem with syntax.

$report = ()

Measure-Command{

$view = Get-View -ViewType Virtualmachine

$cnt = 0 Write-Host "Found $($view.count) virtual machines"

foreach($vm in $view ){   

if (!$vm.Config) { continue } Write-Host "Processing $($cnt) - $($vm.Name)"   

$vms = [PSCustomObject]@{     

VMName = $vm.Name     

Hostname = $vm.guest.hostname     

OS = $vm.Config.GuestFullName     

IPAddress = $vm.guest.ipAddress     

CreatedDate = $vm.Config.CreateDate     

Notes = $vm.Config.Annotation -replace '(?:\r|\n)',''      

Creator = ""      

}   

$vms.Creator = Get-VIEvent -Entity $vm.Name -MaxSamples ([int]::MaxValue) |      

where { $_ -is [VMware.Vim.VmDeployedEvent] } |     

Sort-Object -Property UserName -Descending | Select -First 1 | select -ExpandProperty UserName

                  

$Report.Add($vms) | Out-Null   

$cnt++

} }

[LucD]

Your code looks ok, but the problem could be that the vCenter doesn't keep the events long enough.

Did you also try the script with a VM that was created recently?

[SaqiChangx]

Oh, I didn't know that older events do not show up, actually, I see the VMs that were created recently. So How I should find out who created these VMs ? I want to extend this script with VmClonedEvent', 'VmRegisteredEvent', VmPoweredOffEvent ()CreatedTime would you please help me.

if i use double Get-VIEvent than it takes 3 hours.

[LucD]

I'm confused.
You stated earlier that nothing comes out, but now you seem to say that VMs do show up.

[SaqiChangx]

I didn't notice them, because out of thousands of VMs, only ten of them have a creator. When I turned the page down, I saw them.

[LucD]

Try something like this.
I collect all events in 1 call, to improve performance.

I also limited the Get-View to only return properties that are actually used.

I added the VmCreatedEvent.

$report = @()

$cnt = 1

Measure-Command {

    $view = Get-View -ViewType Virtualmachine -Property Name,Config,Guest

    Write-Host "Found $($view.count) virtual machines"

    $events = Get-VIEvent -Entity $view.Name -MaxSamples ([int]::MaxValue) |    

        Where-Object { $_ -is [VMware.Vim.VmDeployedEvent] -or

                       $_ -is [VMware.Vim.VmClonedEvent]-or

                       $_ -is [VMware.Vim.VmCreatedEvent]-or

                       $_ -is [VMware.Vim.VmRegisteredEvent]}

    foreach ($vm in $view ) { 

        if (!$vm.Config) { continue }

        Write-Host "Processing $($cnt) - $($vm.Name)" 

        $vms = [PSCustomObject]@{   

            VMName      = $vm.Name   

            Hostname    = $vm.guest.hostname   

            OS          = $vm.Config.GuestFullName   

            IPAddress   = $vm.guest.ipAddress   

            CreatedDate = $vm.Config.CreateDate   

            Notes       = $vm.Config.Annotation -replace '(?:\r|\n)', ''    

            Creator     = ($events | where{$_.VM.Name -eq $vm.Name} | Sort-Object -Property CreatedTime -Descending |

                Select -First 1).UserName

        }

                        

        $report += $vms

        $cnt++

    } }

$report

[SaqiChangx]

Thank you Sir, your script generates Report faster than mine.

[SaqiChangx]

Hi,

I did not notice that the formats of numbers, dates, and times depend on the language of Excelproduct. After the export, I am faced with the problem of mixing date and time formats (en-US and de-DE) 

is there any way to fix the formats of date and times. (we use Office Products in German and English) I have searched for some Culture functions. (GetCultureInfoByIetfLanguageTag).

I export with out-File:

.......

...

......

.....

$reportName = "C:\Users\Administrator\Downloads\VReport\VMInventory_$(Get-Date -Format 'yyyy_MM_dd').csv"

$Report | Export-Csv -Path $reportName -NoTypeInformation -Delimiter ";"

((Get-Content -Path $reportName |

ForEach-Object -Process {

     $_ -replace '"', '' })) |

Out-File -FilePath $reportName -Force -Encoding ascii

The output looks like this

Thanks in advance

[LucD]

Did you try using the -UseCulture switch on the Export-Csv cmdlet?

[SaqiChangx]

Yeah, I used both -Delimiter and -UseCulture but I got an error. I think it is not allowed to use both (Export-Csv -Path $reportName -NoTypeInformation -Delimiter ";" -UseCulture). I recently run the script without -Delimiter. I hope for a better result.

[LucD]

I normally only use -NoTypeInformation and -UseCulture.

And yes, you can't combine -Delimiter and -UseCulture

[SaqiChangx]

Without delimiter look the output in Excel(CSV) like this. Do you have English products? some of our colleagues have German products.  VMName,Responsible_Team,Unit,Hostname,VMState,IPAddress,Boottime,PoweredOff,UsedSpaceGB,Datastore,ProvisionedSpaceGB,MemoryUsage,CreatedDate,Backup_Strategy,To_Archive,Creator,OS,Notes pd-mango-esb,,HOLD,,poweredOff,,,,29.57,DS_ITCC_sound_sata,76.19,0,1/1/1970 12:00:00 AM,,,,Microsoft Windows Server 2012 (64-bit), build-fxp-esb-n,,HOLD,,poweredOff,,,,22.82,DS_hold_pd_ssd_1_strong,76.19,0,1/1/1970 12:00:00 AM,,,,Microsoft Windows Server 2012 (64-bit),

[LucD]

That looks pretty normal for a CSV (Comma Separated Values).

The first row contains the column headers, the other rows are the data.

Do you have a problem when you open this in Excel (configured for German)?

[SaqiChangx]

Not Serious problem, but it's not readable for others like it is not really in the right layout

Desired output:

[LucD]

Are you still talking only of the cells containing a datetime value?

When you open a CSV with Excel, it is Excel that will interpret the value in the cell and format it accordingly (and following the formatting used by the 'Culture').

You could write the file while PowerShell is configured for German, but then I suspect the en-US versions will have problems.

[SaqiChangx]

Yeah, i meant, that cells which have datetime and Numbers with Decimal. (current result: 28.34 and wanted result: 28,34). I have found an alternative solution of datetime, i just configured preferred language of system and its datetime formate. if any script could do these changes, it would be great.

[LucD]

I'm not sure if you can even make a file with content that is adapting to the Regional Settings of the station where the file is opened.

[SaqiChangx]

i have heard of parse, which helps with formating.

like this,

[datetime]::ParseExact( '12/30/2020 0:00', 'MM/dd/yyyy h:mm', [cultureinfo]::CurrentCulture )

if i use this, so my code looks like this and it is not working.

$reportName = "C:\Users\Administrator\Downloads\VMI_$(Get-Date -Format 'yyyy_MM_dd').csv"

$Report | Export-Csv -Path $reportName -NoTypeInformation -Delimiter ";"

import-csv $reportName | Select *, @{label = 'boottime/createdtime/Poweredoff';Expression= [datetime]::ParseExact( '12/30/2020 0:00', 'MM/dd/yyyy h:mm', [cultureinfo]::CurrentCulture ) |

((Get-Content -Path $reportName |

ForEach-Object -Process {

     $_ -replace '"', '' })) |

Out-File -FilePath $reportName -Force -Encoding ascii

I have figured out that i can resolve this from windows Settings.

[LucD]

I know the ParseExact method, but the problem is that this parses the value in the cultural settings of the one running the script.

When someone else later opens the file from a station that has different cultural settings, it will not re-format the values.

It's the difference in cultural settings between the 'creator' and the 'user' that causes the issues.

[SaqiChangx]

I see now I get it. Thank you, Sir.

I have a question: Today I saw in the Report that a VM which was deployed by a user the Script didn't find the User, who created this vm

What would be the cause?

this is the code:

Write-Host "Gathering VM statistics"

Measure-Command{

$view = Get-View -ViewType Virtualmachine

$cnt = 1

Write-Host "Found $($view.count) virtual machines"

$events = Get-VIEvent -Entity $view.Name -MaxSamples ([int]::MaxValue) | 

        Where-Object { $_ -is [VMware.Vim.VmDeployedEvent] -or

                      $_ -is [VMware.Vim.VmClonedEvent]-or

                       $_ -is [VMware.Vim.VmCreatedEvent]-or

                       $_ -is [VMware.Vim.VmPoweredOffEvent]-or

                       $_ -is [VMware.Vim.VmRegisteredEvent]}

Write-Host "Found $($view.count) virtual machines"

foreach($vm in $view ) {

    if (!$vm.Config) { continue }

Write-Host "Processing $($cnt) - $($vm.Name)"

    $vms = [PSCustomObject]@{

      VMName = $vm.Name

  Responsible_Team = ""

      Hostname = $vm.guest.hostname

      OS = $vm.Config.GuestFullName

      IPAddress = $vm.guest.ipAddress

      Boottime = $vm.Runtime.BootTime

  PoweredOff = ($events | where{$_.VM.Name -eq $vm.Name} | Sort-Object -Property CreatedTime -Descending | Select -First 1).CreatedTime

      VMState = $vm.summary.runtime.powerState

      UsedSpaceGB = [math]::Round($vm.Summary.Storage.Committed/1GB,2)

      ProvisionedSpaceGB = [math]::Round(($vm.Summary.Storage.Committed + $vm.Summary.Storage.UnCommitted)/1GB,2)

      MemoryUsage = $vm.summary.quickStats.guestMemoryUsage

      Datastore = $vm.Config.DatastoreUrl[0].Name

      CreatedDate = $vm.Config.CreateDate

      Unit = ""

  Backup_Strategy = ""

  To_Archive = ""

      Notes = $vm.Config.Annotation -replace '(?:\r|\n)',''

      Creator = ($events | where{$_.VM.Name -eq $vm.Name} | Sort-Object -Property CreatedTime -Descending | Select -First 1).UserName

    }

    foreach ($c in $vm.CustomValue) {

     switch ($c.Key) {

        "901" { $vms.Unit = $c.Value }

        "913" { $vms.To_Archive = $c.Value }

        "909" { $vms.Backup_Strategy = $c.Value }

        "904" { $vms.Responsible_Team = $c.Value }

      }

    }

 

    $Report.Add($vms) | Out-Null

    $cnt++

}

}

$reportName = "C:\Users\Administrator\Downloads\VReport\VMInventory_$(Get-Date -Format 'yyyy_MM_dd').csv"

$Report | Export-Csv -Path $reportName -NoTypeInformation -Delimiter ";"

((Get-Content -Path $reportName |

ForEach-Object -Process {

     $_ -replace '"', '' })) |

Out-File -FilePath $reportName -Force -Encoding ascii

Result: (the result is filtered)

see the first row Creator is not in the right cell, instead of user is VMWare Perl SDK 6.7.

and second VM has no creator because it was deployed as i saw in the Vsphere (events and Tasks)

can you please tell me what would be the cause?

[LucD]

That could be an issue with this line

Creator = ($events | where{$_.VM.Name -eq $vm.Name} | Sort-Object -Property CreatedTime -Descending | Select -First 1).UserName

You should probably also filter events on the VM's creation datetime (within eventually a margin) and exclude the VMPoweredOffEvent.

The timestamp on an event and the creation time you find on the VM do not always match exactly, there might be a skew between the two timestamps.

I would suggest creating such a VM again, and then take a look at all events around the time when the VM was created.

It should show if there is a time difference and how big it is.