Custom date format extraction using datetime.xml

[dshakespeare_sp]

A colleague was tying to use Splunk to ingest a log file with a unusual date/time format.

• The DATE of the event is dd/mm/yyyy and always includes midnight 00:00:00 as part of the date.
• The actual TIME of the event is hhmm. There is no separator or seconds (ss).
• The Event Type is not a fixed value.
• The System Is not fixed value.

It does not seem possible to extract the Date and Time using TIME_PREFIX, TIME_FORMAT etc in props.conf as the Date Of Event and Time Of Event are separated by variable text and further compounded by the fact that Date Of Event is followed by something that could be taken as a time stamp.

The thought was that a regex could be key to a successful date/time extraction but it does not see possible use a regex with TIME_FORMAT as this only takes strptime

An example of the log file is shown below.

my_custom_log.log
Date Of Event: 01/10/2017 00:00:00 Event: Fan Fail System: az12 Time Of Event: 0150
Date Of Event: 02/10/2017 00:00:00 Event: CPU Fail System: az13 Time Of Event: 1400
Date Of Event: 03/10/2017 00:00:00 Event: Fan Fail System: az12 Time Of Event: 1300
Date Of Event: 04/10/2017 00:00:00 Event: Port Fail System: az14 Time Of Event: 0001
Date Of Event: 05/10/2017 00:00:00 Event: Fan Fail System: az13 Time Of Event: 1730
Date Of Event: 06/10/2017 00:00:00 Event: Disk Fail System: az09 Time Of Event: 2001
Date Of Event: 07/10/2017 00:00:00 Event: Interface Fail System: az12 Time Of Event: 0150
Date Of Event: 08/10/2017 00:00:00 Event: Interface Fail System: az12 Time Of Event: 1030
Date Of Event: 09/10/2017 00:00:00 Event: Port Fail System: az12 Time Of Event: 0800
Date Of Event: 10/10/2017 00:00:00 Event: Fan Fail System: az12 Time Of Event: 0720

[dshakespeare_sp]

For these scenarios I tend to favour a custom datetime.xml file to extract date and time.

Looking at the default datetime.xml in $SPLUNK_HOME/etc, the file format can look overwhelming at first glance. The Splunk supplied file is quite complex as it try to match text to a number of commonly known date and time formats ( 12hr, 24 Hour, AM/PM/Han Chinese Symbols, US Date Format, ISO Date Format, European Date Format) There are also some Maths functions. Unfortunately at the present time there is little information on datetime.xml in Splunk Documemtation.

The easiest way to solve this problem (and to understand the Splunk provided datetime.xml is to start off with a basic framework. Note there is no math function in this example, it is purely a simple custom date extraction.

<datetime>

<define name=“Your Custom Name Date" extract=“elements to extract">
        <text><![CDATA[Enter Your Date Regex And Extraction Capture Groups]]></text>
</define>

<define name="Your Custom Name Time" extract=“elements to extract,">
        <text><![CDATA[Enter Your Date Regex And Extraction Capture Groups]]></text>
</define>

<timePatterns>
      <use name="Your Custom Name Time"/>
</timePatterns>

<datePatterns>
      <use name="Your Custom Name Date"/> 
</datePatterns>

</datetime>

The date elements to extract are:month, day, year.
These are prefixed by Date Of Event: We will need to capture these elements with a regex and a capture group for each date element, so we need THREE capture groups ( )

The time elements to extract are hour and minute and these are prefixed by Time Of Event:
We need to capture these elements with a regex and a capture group for each date element
So we need TWO capture groups ( )

A working solution could be

<define name="my_custom_date" extract="day,month,year,">
        <text><![CDATA[Date Of Event:\s(\d{2})\/(\d{2})\/(\d{4})]]></text>
</define>

<define name="my_custom_time" extract="hour,minute,">
        <text><![CDATA[Time Of Event:\s+(\d{2})(\d{2})]]></text>
</define>

<timePatterns>
      <use name="my_custom_time"/>
</timePatterns>

<datePatterns>
      <use name="my_custom_date"/> 
</datePatterns>

</datetime>

• Save this file to say $SPLUNK_HOME/etc/system/local/datetime.xml
• add a DATETIME_CONFIG = /etc/system/local/datetime.xml to a relevant props.conf stanza. (note PATH is relative to $SPLUNK_HOME)
• restart Splunk

Ingest the file with

inputs.conf
[monitor:///var/log/my_custom_log.log]
sourcetype=my_custom_sourcetype


props.conf
[my_custom_sourcetype]
DATETIME_CONFIG=/etc/system/local/datetime.xml
SHOULD_LINEMERGE=false
NO_BINARY_CHECK=true