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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...