How to change time in the index from 2 different date/time strings in the log?

[claudiocruz]

Forgive me if this question has been asked before but I couldn't find the answer and I'm a little confused.

I have the following TXT log file line, from which I need to compose a solid timestamp for Splunk:
Date opened: 12/02/2015 12:00:00 AM, Time opened: 1600

How can I combine into a timestamp transform the data in the index like the following:

12/02/2015 4:00 PM

I've tried the following REGEX:

REGEX = ^[\n\r].Date opened:\s((\d{2})\/(\d{2})\/(\d{4})\s)| Time opened:\s(\d{4})
FORMAT = $2/$3/$4 $5
DEST_KEY = _raw   <----------  Would change the original log file

Any help is appreciated,

Thanks,

Claudio

[somesoni2]

The Timestamp extractions happens before a TRANSFORMS (SEDCMD) is applied, so your change in the raw data would not affect the timestamp extraction. Assuming every event have same 12:00:00 AM, Time opened: after the date, you could configure your timestamp extraction configuration for your sourcetype (props.conf in Indexer/heavy forwarder whichever comes first) as follows

props.conf

[YourSourceType]
...put your line breaking configuration here..
TIME_PREFIX = ^Date opened:\s+
TIME_FORMAT = %d/%m/%Y 12:00:00 AM, Time opened: %H%M
MAX_TIMESTAMP_LOOKAHEAD =42

[cpetterborg]

I don't think this will work if the 12:00:00 AM is not a fixed time. If it is anything but that exact string the parsing will not work. Perhaps Rich's is the better one to use.

[richgalloway · Accepted solution]

Try this slightly more efficient regex string.

REGEX = ^Date opened:\s(\d{2})\/(\d{2})\/(\d{4})\s[^,]+,\sTime opened:\s*(\d{4})
FORMAT = $1/$2/$3 $4

You may still end up with a non-standard timestamp. If so, try this:

REGEX = ^Date opened:\s(\d{2})\/(\d{2})\/(\d{4})\s[^,]+,\sTime opened:\s*(\d{2})(\d{2})
FORMAT = $1/$2/$3 $4:$5