ranjithbabhu
Enthusiast
Enthusiast

Change Date Format in output CSV file

Jump to solution

I have below script to check the ESXi host disconnection time from VCenter. I am trying to change the Date format from DD/MM/YYYY 24HH:MM to MM/DD/YYYY 24HH:MM  using $date = Get-Date -format "mmddyyyy" but nothing useful.

$esx = Get-VMHost

Get-VIEvent -Entity $esx -MaxSamples 99999 -Start (Get-Date).AddDays(-7)| `

where {"HostConnectionLostEvent","HostConnectedEvent" -contains $_.GetType().Name} | `

Sort-Object -Property {$_.CreatedTime.DateTime} | `

Select @{N="Hostname";E={$_.Host.Name}},

       @{N="Time";E={$_.CreatedTime.ToShortDateString() + " " + $_.CreatedTime.ToShortTimeString()}},

       @{N="Status";E={if($_.GetType().Name -eq "HostConnectedEvent"){"Connected"}else{"Not connected"}}}|

Export-Csv Disconnected.csv -NoTypeInformation -UseCulture

Current output is

HostnameTimeStatus
Esxi1.vmware.local18/05/2020 10:20Not connected
Esxi1.vmware.local18/05/2020 10:29Connected

I need the output like below

HostnameDisconnected Connected Outage
Esxi1.vmware.local5/18/2020 10:205/18/2020 10:290:09

any one can help please.

1 Solution

Accepted Solutions
LucD
Leadership
Leadership

Did you try like this?

@{N='Time';E={$_.CreatedTime.ToString('MM/dd/yy HH:mm') }}


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

View solution in original post

6 Replies
LucD
Leadership
Leadership

Did you try like this?

@{N='Time';E={$_.CreatedTime.ToString('MM/dd/yy HH:mm') }}


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

View solution in original post

ranjithbabhu
Enthusiast
Enthusiast

Hi LucD,

Thank you very much. It gives the output as expected format. Is it possible to get the order . as like below or Just need to calculate the time difference between disconnected and connected time.

HostnameDisconnectedConnectedOutage
Esxi1.vmware.local5/18/2020 10:205/18/2020 10:290:09

Regards

Ranjithbabhu

0 Kudos
LucD
Leadership
Leadership

Try something like this

$esx = Get-VMHost

Get-VIEvent -Entity $esx -MaxSamples ([int]::MaxValue) -Start (Get-Date).AddDays(-7) |

Where-Object { "HostConnectionLostEvent", "HostConnectedEvent" -contains $_.GetType().Name } |

Group-Object -Property { $_.Host.Name } -PipelineVariable group |

ForEach-Object -Process {

    $events = $group.Group | Sort-Object -Property { $_.CreatedTime.DateTime }


    $i = 0


    while ($i -lt $events.Count) {

        while ($events[$i] -isnot [VMware.Vim.HostConnectionLostEvent]) {

            $i++

        }

        $disconnect = $events[$i]

        $i++


        while ($events[$i] -isnot [VMware.Vim.HostConnectedEvent]) {

            $i++

        }

        $connect = $events[$i]


        New-Object PSObject -Property ([ordered]@{

                HostName     = $group.Name

                Disconnected = $disconnect.CreatedTime.ToString('MM/dd/yy HH:mm')

                Connected    = $connect.CreatedTime.ToString('MM/dd/yy HH:mm')

                Outage       = (New-TimeSpan -Start $disconnect.CreatedTime -End $connect.CreatedTime).ToString("h':'m':'s")

            })

        $i++

    }

}


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

0 Kudos
ranjithbabhu
Enthusiast
Enthusiast

Hi LucD,  This works perfect for single ESXi server.

But if i run for entire VCenter (around 140 host) its keep on running. May be the while loop somewhere getting struck.

$esx = Get-VMHost

Get-VIEvent -Entity $esx -MaxSamples 99999 -Start (Get-Date).AddDays(-7) |

#Get-VIEvent -Entity $esx -MaxSamples ([int]::MaxValue) -Start (Get-Date).AddDays(-7) |

Where-Object { "HostConnectionLostEvent", "HostConnectedEvent" -contains $_.GetType().Name } |

Group-Object -Property { $_.Host.Name } -PipelineVariable group |

ForEach-Object -Process {

    $events = $group.Group | Sort-Object -Property { $_.CreatedTime.DateTime }

    $i = 0

    while ($i -lt $events.Count) {

       while ($events[$i] -isnot [VMware.Vim.HostConnectionLostEvent]) {

            $i++

        }

        $disconnect = $events[$i]

        $i++

        while ($events[$i] -isnot [VMware.Vim.HostConnectedEvent]) {

           $i++

        }

        $connect = $events[$i]

        New-Object PSObject -Property ([ordered]@{

                HostName     = $group.Name

                Disconnected = $disconnect.CreatedTime.ToString('MM/dd/yy HH:mm')

                Connected    = $connect.CreatedTime.ToString('MM/dd/yy HH:mm')

                Outage       = (New-TimeSpan -Start $disconnect.CreatedTime -End $connect.CreatedTime).ToString("h':'m':'s")

            })

        $i++

    }

}|

Export-Csv Disconnected_state_inVC.csv -NoTypeInformation -UseCulture

$global:DefaultVIServers | ForEach-Object { Disconnect-VIServer $_ -Confirm:$false }

0 Kudos
LucD
Leadership
Leadership

I suspect the Get-VIEvent call might take a lot of time to complete.

Add some Write-Host statements to see where it takes most of the time.


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

0 Kudos
ranjithbabhu
Enthusiast
Enthusiast

But in the below script  same Get-VIEvent command completed within 10 Minutes.  I suspect if there might be only multiple connected events  and there is no disconnected events. In below script  i can see sometimes host connected events  only.

$esx = Get-VMHost

Get-VIEvent -Entity $esx -MaxSamples 99999 -Start (Get-Date).AddDays(-30)| `

where {"HostConnectionLostEvent","HostConnectedEvent" -contains $_.GetType().Name} | `

Sort-Object -Property {$_.CreatedTime.DateTime} | `

Select @{N="Hostname";E={$_.Host.Name}},

       @{N='Time';E={$_.CreatedTime.ToString('MM/dd/yy HH:mm') }},

      # @{N="Time";E={$_.CreatedTime.ToShortDateString() + " " + $_.CreatedTime.ToShortTimeString()}},

       @{N="Status";E={if($_.GetType().Name -eq "HostConnectedEvent"){"Connected"}else{"Not connected"}}}|

Export-Csv Disconnected_state_inVC1.csv -NoTypeInformation -UseCulture

$global:DefaultVIServers | ForEach-Object { Disconnect-VIServer $_ -Confirm:$false }

   

HostnameTimeStatus
Host1.vmware.local7/1/2020 13:40Connected
Host1.vmware.local7/1/2020 13:41Connected
Host1.vmware.local7/1/2020 13:41Not connected
Host1.vmware.local7/1/2020 13:42Connected
Host1.vmware.local7/1/2020 13:44Not connected
Host1.vmware.local7/1/2020 13:49Connected

This host is recently joined on this Vcenter.

0 Kudos