topic Re: Convert timestamp at search time for report? in Splunk Search

Convert timestamp at search time for report?

msarro — Thu, 18 Nov 2010 05:04:55 GMT

Hey everyone! I am working on files right now that contain numerous timestamps. The timestamps are presented in this fashion: 20101117201236.368 (YYYYMMDDHHMMSS.SSSS). I have managed to get time stamp extraction working properly, so the events are getting the correct timestamp. However when I attempt to generate the date and time in a report I still get the original format for the data. How can I do this in a search string? I've tried using this format but its not working. Any ideas?

eval Call_Start_Time = strptime(Start_Time, "%Y%m%d%H%M%S.%q") |stats list(Call_Start_Time) by Calling_Number

Re: Convert timestamp at search time for report?

southeringtonp — Thu, 18 Nov 2010 06:54:15 GMT

Are you really sure it's still the same format?

Your sample looks like it would convert to unix epoch time, but would still give you a numeric result. It would look very similar (especially because of the milliseconds at the end) but it would start with a number around '1290' instead of '2010'.

You should be able to just do:

...
| eval Call_Start_Time = strptime(Start_Time, "%Y%m%d%H%M%S.%q")
| convert mktime(Start_Time)
| stats list(Call_Start_Time) by Calling_Number

If you want another test search, try this:

* | head 1 | eval Start_Time="20101117201236.368"
| eval Call_Start_Time = strptime(Start_Time, "%Y%m%d%H%M%S.%q")
| eval Text_Start_Time=Call_Start_Time
| convert ctime(Text_Start_Time)
| table Start_Time, Call_Start_Time, Text_Start_Time

This should produce the following table, with Start_Time containing the sample string you provided, Call_Start_Time containing the equivalent time in unix epoch format, and Text_Start_Time containing a human-readable version:

Start_Time             Call_Start_Time       Text_Start_Time 
20101117201236.368     1290042756.368000     11/17/2010 20:12:36.368000

Re: Convert timestamp at search time for report?

msarro — Mon, 28 Sep 2020 09:21:11 GMT

I got it working based on your reply! Thanks so much. Here's what I ended up doing:
|eval Call_Start_Time_Text = strptime(Start_Time,"%Y%m%d%H%M%S.%q")
|convert ctime(Call_Start_Time_Text)
|eval Call_Release_Time_Text= strptime(Release_Time,"%Y%m%d%H%M%S.%q")
|convert ctime(Call_Release_Time_Text)
|eval Call_Answer_Time_Text= strptime(Answer_Time,"%Y%m%d%H%M%S.%q")
|convert ctime(Call_Answer_Time_Text)

If it would be possible to skip the convert operation that would be awesome, but for now its working and I can worry about cleaning it up later. Much appreciated!