Splunk Search
Highlighted

Date not parsed if the hour is 24

Explorer

Splunk doesn't parse the date in the beginning of an event, when it has a hour of 24 (JODA time), like in 03.02.2015 24:05:03:100. Such a row is not appearing as a separate event, but rather as a continuation of an event which has the time of 23:59 for example. How can this be fixed?
Thanks,

Tags (2)
Highlighted

Re: Date not parsed if the hour is 24

Splunk Employee
Splunk Employee

According to the JODA specification,
http://www.joda.org/joda-time/key_format.html
H hour of day (0~23) number 0
k clockhour of day (1~24) number 24

a timestamp like "24:05:03:100. " is not an hour ,but a clockhour [1;24] therefore it cannot be parsed by splunk defaults, as a hour range is [0;23]
Please change your log format to use 24hour "H" instead.

0 Karma
Highlighted

Re: Date not parsed if the hour is 24

Path Finder

From yannK's answer I understand the problem is that you've got clock hour [1;24] rather than hour [0;23] so you need to shift everything back by 1 hour from clock hour to hour as Splunk has no support for clock hour.
The query below shows how this can be done using rex mode=sed and a timezone offset with the 24 clock hour being handled separately.

Essentially what happens is:
"28.02.2015 24:05:03:100" => "28.02.2015 23:05:03:100-0000" => "28.02.2015 23:05:03.100"
"28.02.2015 20:32:45:123" => "28.02.2015 20:32:45:123+0100" => "28.02.2015 19:32:45.123"

| gentimes start=-1 
| eval testdata="something 28.02.2015 24:05:03:100 else#something 28.02.2015 20:32:45:123 else" 
| table testdata | makemv testdata delim="#" | mvexpand testdata | eval _raw = testdata
| rex mode=sed field=_raw "s/(\d\d\.\d\d\.\d{4}) 24(:\d\d:\d\d:\d+) /\1 23\2-0000 /g"
| rex mode=sed field=_raw "s/(\d\d\.\d\d\.\d{4}\s+\d+:\d\d:\d\d:\d+) /\1+0100 /g" 
| rex field=_raw "(?<date_str>\d\d\.\d\d\.\d{4}\s+\d+:\d\d:\d\d:\d+[+-]\d+)"
| eval n = strftime(strptime(date_str, "%d.%m.%Y %k:%M:%S:%3N%z"), "%d.%m.%Y %H:%M:%S.%Q") 
| table testdata date_str n

You should also be able to specify the manipulations in props.conf using SEDCMD which would be applied at indexing time:

SEDCMD-clockhour24 = s/(\d\d\.\d\d\.\d{4}) 24(:\d\d:\d\d:\d+) /\1 23\2-0000 /g
SEDCMD-clockhourXX = s/(\d\d\.\d\d\.\d{4}\s+\d+:\d\d:\d\d:\d+) /\1+0100 /g
0 Karma
Highlighted

Re: Date not parsed if the hour is 24

Explorer

Thanks.
I understand that Splunk cannot parse this date out of the box.
Is there anything I can do meanwhile to parse the date, e.g using TIME_FORMAT in the props.conf or any other trick?

0 Karma
Highlighted

Re: Date not parsed if the hour is 24

Splunk Employee
Splunk Employee

It is not possible to change the timestamp at index time with a regex, because on the indexing pipeline, the date is extracted before the regex are applied.

The example from tpflicke is to search all time then create at search time a new timestamp from the events, but this require to search each time over all time, and lose the benefit os search speed.

About the TIME_FORMAT, it relies son a the python strftime function, that does not recognize the 1-24h hour format.

0 Karma
Highlighted

Re: Date not parsed if the hour is 24

Path Finder

While a far better solution would be fixing the log format, I wonder if a heavy forwarder could be employed to modify the date string before sending it to the indexing tier. If necessary, the forwarder could be configured to ignore the timestamp, it should simply apply the transformation, e.g. via SEDCMD, then send the data on.

0 Karma