Splunk Search

Convert timestamp at search time for report?

Builder

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
Tags (2)
0 Karma
1 Solution

Motivator

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

View solution in original post

Motivator

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

View solution in original post

Builder

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!

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!