topic Re: Format Date in Splunk Search

Format Date

nikhilmehra79 — Mon, 28 Sep 2020 16:06:10 GMT

Hi -

I have a raw event which has raw event lines as
"11-Mar-14 9:38:58 PM",300,64.00000000
This was from today 11 March 2014

Now the event Date as figured by Splunk is
» 3/14/11
9:38:58.000 PM
Splunk is treating it as one event from year 2011

I read through time formatting document and made changes in props.conf with new event type
but still no luck.
My props.conf looks like:

[csv-2]
KV_MODE = none
REPORT-AutoHeader = AutoHeader-1
SHOULD_LINEMERGE = False
pulldown_type = true
TIME_FORMAT = %d-%b-%y %H:%M:%S.%3N %Z

Not sure why it is not working, any suggestion will be greatly appreciated. I also tried
TIME_FORMAT = %d-%b-%y %H:%M:%S with no luck. Please suggest/help

Re: Format Date

yannK — Wed, 12 Mar 2014 05:21:12 GMT

probably 3 issues :
- the %Z, there is not timezone in your timestamp
- and the %3N for the millisecongs, they are none in your timestamp
- and if you have PM/AM notation , it means that your hour is on a 12'clock not a 24h clock
check the documentation for the TIME_FORMAT

Re: Format Date

nikhilmehra79 — Wed, 12 Mar 2014 05:25:14 GMT

So i even tried
%d-%b-%y %H:%M:%S %p
%p for AM and PM

Re: Format Date

rakesh_498115 — Mon, 28 Sep 2020 16:06:15 GMT

Hi nikhil,

can u pls try the following ??

TIME_PREFIX = \"
TIME_FORMAT = %d-%b-%y %H:%M:%S %p

Re: Format Date

nikhilmehra79 — Mon, 28 Sep 2020 16:06:37 GMT

Rakesh thanks....actually i tried similar one :
Here is my props.conf
KV_MODE = none
REPORT-AutoHeader = AutoHeader-1
SHOULD_LINEMERGE = False
pulldown_type = true
TRANSFORMS-sortdate = resortdate
TIME_PREFIX=^\d+
TIME_FORMAT = %d-%b-%y %H:%M:%S %p

and my transforms.conf
[resortdate]
REGEX = ^(\d{2})-(.*)-(\d{2})\s([^/]+)
FORMAT = $2/$1/$3 $4
DEST_KEY = _raw

I am able to get date moved to current day with this but all the events in file inserted in splunk at one time - so say i have 10 events with time stamp 1:00, time stamp 2:00, time stamp 3:00 in log file

Re: Format Date

nikhilmehra79 — Wed, 12 Mar 2014 15:59:17 GMT

and say splunk read at 5:00 then splunk is showing 5:00 as time for all events instead of individual events as logged in log file

Re: Format Date

somesoni2 — Wed, 12 Mar 2014 18:09:47 GMT

Try this

[csv-2] 
KV_MODE = none 
REPORT-AutoHeader = AutoHeader-1 
SHOULD_LINEMERGE = False 
pulldown_type = true 
TIME_FORMAT = "%d-%b-%y %H:%M:%S %p

Re: Format Date

linu1988 — Wed, 12 Mar 2014 18:36:55 GMT

Hello,
Could you try this?

[csv-2] 
KV_MODE = none 
REPORT-AutoHeader = AutoHeader-1 
SHOULD_LINEMERGE = False 
TIME_FORMAT = %d-%b-%y %I:%M:%S %p
TIME_PREFIX="
pulldown_type = true

Thanks

Re: Format Date

nikhilmehra79 — Mon, 28 Sep 2020 16:07:18 GMT

i think this worked my props.conf looks as below,
i have a quick question though - does this mean the raw format in event is now changed and indexed like that and i do not need to modify muy transforms.conf as i pointed above, is props.conf entry for TIME_FORMAT and TIME_PREFIX is enough to make this change in raw events for future.
[csv-2]
KV_MODE = none
REPORT-AutoHeader = AutoHeader-1
SHOULD_LINEMERGE = False
TIME_FORMAT = %d-%b-%y %I:%M:%S %p
TIME_PREFIX="
pulldown_type = true

Re: Format Date

linu1988 — Thu, 13 Mar 2014 18:20:19 GMT

once this is recognized in splunk, the defualt _time field will be assigned. transforms is not affected by this change.

Feel free to accept as answer..