hrlinuxdt0312
Contributor
Contributor

Unable to Export a list of VMs which are created in the past 7, 30, 120 & 180 days from an Imported csv file which contains VM-Created date

Jump to solution

Unable to Export a list of VMs which are created in the past 7, 30, 120 & 180 days from an Imported csv file which contains VM-Created date. My issues is getting the correct statement for variable: $VmCreated7DaysAgo: $_CreatedOn "-lt" $CDate7.

####SCRIPT_START

$file = "C:\Users\Admin\Documents\WindowsPowerShell\08-18-2014\VM-Repo.csv"

$Import = import-csv $file

$VMCreatedLast7RDayRepoFile = "C:\Users\Admin\Documents\WindowsPowerShell\08-18-2014\Last7Days.csv"

$start7 = (Get-Date).AddMonths(-1)

$CDate7 = $start7.ToString('MM/dd/yyyy')

$VmCreated7DaysAgo = $Import | select-object -property Name, Powerstate, vCenter, VMHost, Cluster, Folder, Application, CreatedBy, CreatedOn, NumCpu, MemoryGB | Where-Object {$_.CreatedOn -lt $CDate7} | Sort-Object CreatedOn

$TotalVmCreated7DaysAgo = $VmCreated7DaysAgo.count

$VmCreated7DaysAgo | Export-Csv -Path $VMCreatedLast7RDayRepoFile -NoTypeInformation -UseCulture

Write-Host "$TotalVmCreated7DaysAgo VMs Created in the Past 7 Days" -BackgroundColor Magenta

Invoke-Item $VMCreatedLast7RDayRepoFile

####SCRIPT_END

0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership

You could use the New-Timespan cmdlet in the Where-clause, it returns the time difference between 2 DateTime objects.

As an example of that cmdley

New-TimeSpan -Start (Get-Date).AddDays(-7) -End (Get-Date) | Select -ExpandProperty Days

In your case you could do

Where{(New-Timespan -Start ([DateTime]$_.CreatedOn) -End $start7).Days -gt 7}

But watch out for negative numbers.


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

View solution in original post

0 Kudos
5 Replies
JohnMcKeown
Enthusiast
Enthusiast

There is a thread about this topic here:

Get VM's creation date

I myself pulled and tailored this from the thread,

$vms = get-vm

   $vmevts = @()

   $vmevt = new-object PSObject

   foreach ($vm in $vms) {

      Progress bar:

      $foundString = "       Found: "+$vmevt.name+"   "+$vmevt.createdTime+"   "+$vmevt.IPAddress+"   "+$vmevt.createdBy

      $searchString = "Searching: "+$vm.name

      $percentComplete = $vmevts.count / $vms.count * 100

      write-progress -activity $foundString -status $searchString -percentcomplete $percentComplete

      $evt = get-vievent $vm | where {$_.Gettype().Name-eq "VmCreatedEvent" -or $_.Gettype().Name-eq "VmBeingClonedEvent" -or $_.Gettype().Name-eq "VmBeingDeployedEvent"}

      $vmevt = new-object PSObject

      $vmevt | add-member -type NoteProperty -Name createdTime -Value $evt.createdTime

      $vmevt | add-member -type NoteProperty -Name name -Value $vm.name

      $vmevt | add-member -type NoteProperty -Name IPAddress -Value $vm.Guest.IPAddress.ToString

      $vmevt | add-member -type NoteProperty -Name createdBy -Value $evt.UserName

   $vmevt | add-member -type NoteProperty -Name Vlan -Value $vm.NetworkAdapters.NetworkName

   $vmevt | add-member -type NoteProperty -Name NICType -Value $vm.NetworkAdapters.Type

   $vmevt | add-member -type NoteProperty -Name PowerState -Value $vm.PowerState

   $vmevt | add-member -type NoteProperty -Name ResourcePool -Value $vm.ResourcePool

   $vmevt | add-member -type NoteProperty -Name HardwareVersion -Value $vm.Version

   $vmevt | add-member -type NoteProperty -Name MemoryGB -Value $vm.MemoryGB

   $vmevt | add-member -type NoteProperty -Name CPU -Value $vm.CPU

   $vmevt | add-member -type NoteProperty -Name OperatingSystem -Value $vm.ExtensionData.Summary.Guest.GuestFullName

   $vmevts += $vmevt

   }

$vmevts | sort createdTime

$vmsname7days = $vmevts | where {$_.createdTime -gt ((Get-Date).AddDays(-7))}

$vmsname7days

Which will give VMs and some nice properties for the last 7 days.

hrlinuxdt0312
Contributor
Contributor

Appreciate your response, however, I do not want to have to execute a cmd to retrieve the VM's Creation-Date which I already have using this script: "Script to retrieve VMs Created-On Date & Created-By date & add to existing CustomFields" and it inputs the CreatedOn and Created by as a CustomField within the VM's Summary, within the "Annotations" box.

Then, I export all the CustomFields with the VM names to a CSV file and now I want to manipulate that csv to provide me the VMs within the CSV which are dated for the last 7 days, 30 days, 120 days & 180 days.

This will be part of an automated script which will run every week an will provide the number of VMs created within the past week.

The issue from my script that I am facing is that, the variable: "$start7 = (Get-Date).AddMonths(-1)" is able to get the present date and subtracts by ONE month, which is then inputted into a 'Where-object" pipeline that is not able to obtain a list of VM's created within that past month. I know I have to use a <int:32> but not sure how it would be stated.

Please advise.

Thanks,

H.R.

0 Kudos
LucD
Leadership
Leadership

You could use the New-Timespan cmdlet in the Where-clause, it returns the time difference between 2 DateTime objects.

As an example of that cmdley

New-TimeSpan -Start (Get-Date).AddDays(-7) -End (Get-Date) | Select -ExpandProperty Days

In your case you could do

Where{(New-Timespan -Start ([DateTime]$_.CreatedOn) -End $start7).Days -gt 7}

But watch out for negative numbers.


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

View solution in original post

0 Kudos
hrlinuxdt0312
Contributor
Contributor

Worked Perfectly! Thanks LucD!

0 Kudos
hrlinuxdt0312
Contributor
Contributor

LucD,

One issue I am facing is if the results for VMs created within the past 7 days is equal to '1", .Count does not provide the value '1', instead it states "0", which was only provided after inputting [int32] within the .Count variable. I have restated the script being executed based off the changes made. Also, if there are 2 VMs or more, it is able to provide a successful value of '2' or more. If you can please assist in resolving this would be really helpful.

###SCRIPT_Begin

$file = "C:\Users\Admin\Documents\WindowsPowerShell\VM-Repo.csv"

$IMp = import-csv $file

$VMCreatedLast7RDayRepoFile = "C:\Users\Admin\Documents\WindowsPowerShell\2-Last7Days_VMList.csv"

$start7 = (Get-Date).AddDays(-2).ToString('MM/dd/yyyy')

$VmCreated7DaysAgo = $IMp | select-object -property Name, CreatedOn | Where{(New-Timespan -Start $today -End ([DateTime]$_.CreatedOn) ).Days -ge "1"}

$TotalVmCreated7DaysAgo = [Int32]$VmCreated7DaysAgo.count

$VmCreated7DaysAgo | Export-Csv -Path $VMCreatedLast7RDayRepoFile -Force -NoTypeInformation -UseCulture

Write-Host "$TotalVmCreated7DaysAgo VMs Created in the Past 7 Days" -BackgroundColor Magenta

Invoke-Item $VMCreatedLast7RDayRepoFile

##Script_End

Thanks,

Hasan R.

0 Kudos