Splunk Search

Why does my search only give strptime output for one of three time values?

ppanchal
Path Finder

I have a field DATE_OF_BIRTH and the values are like
1962-09-30 00:00:00.0

1955-10-21 00:00:00.0

1988-10-31 00:00:00.0

I am firing the below query,

index=aimsprod source=accident splunk_server=alpputl018 *100172697* OR *100172679* | fieldformat dobtime=strptime(DATE_OF_BIRTH,"%Y-%m-%d %H:%M:%S")  |  table DATE_OF_BIRTH,dobtime

The dobtime field only gives output for the value 1988-10-31 00:00:00.0.

Why it does not give output for the other 2 above mentioned values?alt text

0 Karma

lquinn
Contributor

I believe it will be because epoch time is calculated as the number of seconds since 1st Jan 1970. The two dates that are not being converted occur before this time, so the value would be negative, which is not supported by Splunk. What are you trying to achieve by converting the timestamps to epoch?

Might be worth taking a look at this answer:
https://answers.splunk.com/answers/214742/can-splunk-strptime-work-with-the-date-before-1970-1.html

ppanchal
Path Finder

The issue that I am actually facing is when I export the data to a csv file, the date/time format is changed to something like 00:00:00...
Is there a way I an fix this?

0 Karma

lquinn
Contributor

Does this happen when you open the CSV in excel, or was the CSV saved from excel? Sometimes it does some weird auto-formatting stuff on dates ...
Do you just want to reformat it as YYYY-MM-DD?
If so, you could try just using the replace command instead of fieldformat:

| replace "* 00:00:00.0" with "*" in DATE_OF_BIRTH
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!