How can I get uniform date and time output with get-stat on servers located in areas with different regional settings?

[LarsOliver]

Hello together,

I use a psh script on different vCenters worldwide to sample performance data. Unfortunetaly the regional settings of the vCenter Windows servers concerninbg time and date are different. For that reason I´ve got different date and time output to the csv. Here an example for reading the CPU performance of a VM.

Get-Stat -Entity $vm.name -Stat "cpu.usage.average" -IntervalMins 5 -Start (Get-Date).adddays(-1) -Finish (Get-Date).addminutes(-5)-MaxSamples 288 | Select-Object timestamp, value, unit | Export-Csv

vCenter Server located in the US with american Time (12 hours AM/PM) and Date (Month/Day/Year) settings:

"Timestamp","Value","Unit"

"6/27/2010 12:00:00 PM","2.75","%"

"

"

vCenter Server located in Europe with european regionalen Time (24 hours) and Date (Day.Month.Year) settings:

Timestamp,Value,Unit

"21.06.2010 00:20:00","3,06",%

"

"

For the import of this data to our central reporting database the formats must be uniform.

Has anyone a idea to get the format uniform?

Thanks in advance

Regards

Oliver

[LucD]

You could convert all Timestamp properties to UTC.

$stat.Timestamp.ToUniversalTime()

____________

Blog: LucD notes

Twitter: lucd22

[LarsOliver]

LucD,

thank you for your reply and suggestion. I´ve tryed to do that but I don´t know how I exactly script this convert.

Regards

LarsO

[LucD]

With the lines of code you gave in the binning of the thread you could do something like this

Get-Stat -Entity $vm.name -Stat "cpu.usage.average" -IntervalMins 5 -Start (Get-Date).adddays(-1) -Finish (Get-Date).addminutes(-5)-MaxSamples 288 | `
	Select-Object @{N="Timestamp";E={$_.timestamp.ToUniversalTime()}}, value, unit | Export-Csv

____________

Blog: LucD notes

Twitter: lucd22

[LarsOliver]

LucD,

I´ve tried this code, but the format in the csv file doesn´t change?

[LucD]

The time format doesn't change but your Timestamp will be in UTC.

You can check by running it this way

Get-Stat -Entity $vm.name -Stat "cpu.usage.average" -IntervalMins 5 -Start (Get-Date).adddays(-1) -Finish (Get-Date).addminutes(-5)-MaxSamples 288 | `
	Select-Object TimeStamp,@{N="TimestampUTC";E={$_.timestamp.ToUniversalTime()}}, value, unit | Export-Csv

____________

Blog: LucD notes

Twitter: lucd22

[RvdNieuwendijk]

I have created two functions Convert-EuDateTimeToDateTime and Convert-EuFloatToFloat that convert the EU datetime and float formats to PowerShell objects. You can use them for the European vCenter servers. Maybe you have to do something similar for the US vCenter servers:

Function Convert-EuDateTimeToDateTime {
  param ([parameter(Mandatory=$true)][string] $EUDateTime)
  
  Get-Date -Day $EUDateTime.Substring(0,2) -Month $EUDateTime.Substring(3,2) -Year $EUDateTime.Substring(6,4) `
    -Hour $EUDateTime.Substring(11,2) -Minute $EUDateTime.Substring(14,2) -Second $EUDateTime.Substring(17,2)
}

Function Convert-EuFloatToFloat {
  param ([parameter(Mandatory=$true)][string] $EUFloat)
  
  $int = $EUFloat.Split(",")[0]
  $fraction = $EUFloat.Split(",")[1]
  [float]"$int.$fraction"
}

With these two functions you can change your script for the European vCenter servers as follows:

Get-Stat -Entity $vm.name -Stat "cpu.usage.average" -IntervalMins 5 -Start (Get-Date).adddays(-1) -Finish (Get-Date).addminutes(-5) -MaxSamples 288 | `
  Select-Object @{N="timestamp";E={Convert-EuDateTimeToDateTime $_.timestamp}}, @{N="value";E={Convert-EuFloatToFloat $_.value}}, unit | Export-Csv

Regards, Robert

[LarsOliver]

Hi Robert,

sorry for my delay and thank you for your answer. I´ve tryed your Script but this is the output:

#TYPE Selected.VMware.VimAutomation.Client20.FloatSampleImpl

"timestamp","value","Unit"

,,"%"

,,"%"

,,"%"

,,"%"

,,"%"

,,"%"

The Output contains no values. Did I´ve done something wrong?

Regrads

LarsO

[LarsOliver]

Hi LucD,

thank you for your help and sorry for my delay.

I´ve tried your script, this is the output:

#TYPE Selected.VMware.VimAutomation.Client20.FloatSampleImpl

"Timestamp","TimestampUTC","Value","Unit"

"20.07.2010 09:35:00","20.07.2010 07:35:00","2,53","%"

"20.07.2010 09:30:00","20.07.2010 07:30:00","3,27","%"

"20.07.2010 09:25:00","20.07.2010 07:25:00","2,52","%"

The output contains the UTC time in addition. Thats great. But what I am looking for is a "universal cultura" output for the date and time format, independent if the script is running on an US Server or an European Server. Do you have an idea.

[LucD]

I get similar result with Robert's functions when the date and the value are not in the EU format (dd/mm/yy and xxx,yy).

If you only keep the UTC datetime, isn't that your "universal cultura" output ?

Get-Stat -Entity $vm.name -Stat "cpu.usage.average" -IntervalMins 5 -Start (Get-Date).adddays(-1) -Finish (Get-Date).addminutes(-5)-MaxSamples 288 | `
	Select-Object @{N="Timestamp";E={$_.timestamp.ToUniversalTime()}}, value, unit | Export-Csv

____________

Blog: LucD notes

Twitter: lucd22

[LarsOliver]

LucD,

unfortunately not, i think it is difficult to find a solution. The time difference is not realy the problem. It is the different output with . and / and the 12/24 hour format.

These are the outputs on two servers (US and Germany) with different windows cultural settings:

D:\> Get-Culture

LCID Name DisplayName

-

---

-

---

-

---

1031 de-DE German (Germany)

#TYPE Selected.VMware.VimAutomation.Client20.FloatSampleImpl

"Timestamp","Value","Unit"

"20.07.2010 08:00:00","2,5","%"

"20.07.2010 07:55:00","3,14","%"

D:\> Get-Culture

LCID Name DisplayName

-

---

-

---

-

---

1033 en-US English (United States)

#TYPE Selected.VMware.VimAutomation.Client20.FloatSampleImpl

"Timestamp","Value","Unit"

"7/20/2010 8:20:00 AM","3.66","%"

"7/20/2010 8:15:00 AM","3.35","%"

To import the Data into my central database I need a similar output of the date and time format. Do you have any idea?

Regrads

LarsO

[LucD · Accepted solution]

Why don't you use the solution offered in Using-Culture -Culture culture -Script {scriptblock} ?

You do all the writing to your database in 1 selected culture (for example en-US).

That way all your date and and number formats have the same layout.

____________

Blog: LucD notes

Twitter: lucd22

[LarsOliver]

LucD,

This is a realy great thing, thank you very much

I setup the function and put my whole script into the script block. Now my script work independent regarding the underlying culture settings of the windows vCenter servers.

Thanks a million!

LarsO