0

I am struggling a bit with an issue I hope you can help me with.

I am using get-winevent to grab specific events from a computer and export the results to a csv.

I am setting a variable to hold all of the returned events and then using a foreach loop to step through all the events and export them to a csv file. When I export the timecreated object, the data is formatted as date-time and I can sort oldest to newest or newest to oldest. What I am running into is that the default format of "timecreated" is "MM/DD/YYYY hh:mm ". This causes a problem in that, unless I can see seconds and milliseconds, a lot of these events appear to happen at the same time.

To resolve this, I have been using the following to format the date:

$ticks = [datetime]$event.timecreated.Ticks

$date = $ticks.Tostring("MM/dd/yyyy hh:mm:ss:fff")

This works well when I just output to the screen, the date shows exactly like I want it to. However, since the variable is a string, when I export the variable to excel I can’t sort based on time.

Does anyone know of a way to convert ticks to the display format I want, but keep it as datetime rather than a string? Or any way to grab the timecreated object and format it the way I want, without having to convert it to a string?

I'm beginning to think I may have to create a PSObject and handle it that way, but I am not sure I can do it even then.

Thanks for reading.

Jason Boyd
  • 6,839
  • 4
  • 29
  • 47
jomoagogo
  • 1
  • 1
  • Try putting the string into excel and set the type in excel to date. – Marichyasana Jan 12 '19 at 00:22
  • Thanks, I have tried that, but t doesn't work. – jomoagogo Jan 12 '19 at 00:31
  • To start, it should be `ss.fff` instead of `ss:fff`. Though, honestly that is not really sufficient since you have no AM/PM indication, and have converted the hours to 12 hour standard, and excluded the AM/PM. You should use `HH` instead of `hh` or add `tt` to the end to show AM/PM. If that does not work in Excel, how are you exporting the variable to Excel? If you want a sortable date/time string you should use `'yyyy/MM/dd HH:mm:ss.fff'` instead. – TheMadTechnician Jan 12 '19 at 00:52
  • If you want to get the data into Excel, then save it as excel. Use this module, it's great: https://www.powershellgallery.com/packages/ImportExcel/5.4.0 I tested exporting winevents: the dates are exported in the right way including milliseconds. – Gert Jan Kraaijeveld Jan 12 '19 at 12:15
  • Thanks for both of your replies. Madtechnician, I was unaware of the importance of the case of the hours mask. I did not know that upper case indicated a 24 hour clock, so I will make adjustments accordingly. I will also change the “:” to a “.”. Gert, I will check out the module you linked to. Looks interesting. – jomoagogo Jan 12 '19 at 19:08
  • Hi, you have trying just to put the ticks on Excel i thinks they was automaticaly formated by Excel. – Sanpas Jan 13 '19 at 10:14

1 Answers1

0

if i understand your whole query and excepted result.

I have tested what your are looking for and result it to we doesn't have to add "fff" to string date format on excel because it's doesn't work.

Try this : 

$ticks = [datetime]$event.timecreated.Ticks

$date = $ticks.Tostring("MM/dd/yyyy hh:mm:ss")

The full example we have using for testing : 

$events =Get-WinEvent -ListLog * | Where {$_.IsClassicLog -eq ‘True’}

$events  | Select-Object LogMode, RecordCount, LogName, @{n="DateWrite";e={$_.LastWriteTime.ToString("dd/MM/yyyy hh:mm:ss")}} | Export-Csv -Path c:\fso\event_tester.csv -Encoding ascii -NoTypeInformation

At result have the excepted Date column :

enter image description here

Resources : How to represent a DateTime in Excel

pandas output timestamps to_excel with microseconds

Sanpas
  • 1,170
  • 10
  • 29