Splunk Search

Date not parsed if the hour is 24

reggie_123
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)

tpflicke
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

reggie_123
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

yannK
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

tpflicke
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

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

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...