topic Re: trouble using regex to find a timestamp in Splunk Search

trouble using regex to find a timestamp

jgautreau — Wed, 30 Oct 2013 18:47:22 GMT

Hi,

I'm trying to use regex to define where my stamp stamp is in the data below. I have it working for some of the data but there are instances where it does not work, and I'm struggling to understand why... the data looks the same.

Any ideas?

Regex:

,(?=\d{1}/\d{2}/\d{4} \d{2}:\d{2})

Format:

%m/%d/%Y %H:%M

Works here:

38112544113,mmehltretter@legendequities.com,gabriela.fuselli@lfd.com,38112,AML Wholesaler NMG Template,9/29/2013 19:30,...

Timestamp = 9/29/13 7:30:00.000 PM

Does not work here:

36277629151,arnold.cottrell@lfg.com,nicole.hartnett@lfg.com,36277,Welcome: Extended Leadership Team,8/16/2013 8:56,...,,,9/30/2013 23:00,

TimeStamp = 9/30/13 11:00:00.000 PM (picking up another time later in event string)
OR Here:

37866185650,dconoway@americanportfolios.com,gabriela.fuselli@lfd.com,37866,AML Wholesaler NMG Template,9/24/2013 7:39,....,,,9/30/2013 22:59,

Timestamp = 9/30/13 10:59:00.000 PM (picking up another time later in event string)

Re: trouble using regex to find a timestamp

lukejadamec — Wed, 30 Oct 2013 18:53:43 GMT

Can you repost your regex as code? Highlight and select the 101010 format button.

Re: trouble using regex to find a timestamp

richgalloway — Wed, 30 Oct 2013 18:54:09 GMT

Your regex expects only two-digit hours so the examples occurring before noon are not recognized.

Re: trouble using regex to find a timestamp

rgcurry — Wed, 30 Oct 2013 19:00:07 GMT

Here is what I'd put into the props.conf if this were my data:

TIME_PREFIX = TimeStamp = 
TIME_FORMAT = %m/%d/%Y %H:%M:5S.%3N %p
MAX_TIMESTAMP_LOOKAHEAD = 165

But if you are wanting a field extraction for the date/time, try this:

[sS]tamp\s+\=\s+(?<dateTime>\d+\/\d+\/\d+\s+\d+\:\d+\:\d+\.\d+\s+\w+)\s+

You mention "picking up another time later in event string" but there is no way for any of us to know what impact that might have. Try one of these and let us know if it works. There may be a better answer if you provide us with more info.

Re: trouble using regex to find a timestamp

jgautreau — Wed, 30 Oct 2013 19:10:06 GMT

,(?=d{1}/d{2}/d{4} d{2}:d{2})

Re: trouble using regex to find a timestamp

jgautreau — Wed, 30 Oct 2013 19:14:45 GMT

okay - got it! thanks! JG

Re: trouble using regex to find a timestamp

jgautreau — Wed, 30 Oct 2013 19:15:12 GMT

got it - thanks Rich!

Re: trouble using regex to find a timestamp

jgautreau — Wed, 30 Oct 2013 19:16:02 GMT

,(?=\d{1,2}/\d{1,2}/\d{4} \d{1,2}:\d{1,2})

Re: trouble using regex to find a timestamp

kristian_kolb — Wed, 30 Oct 2013 20:48:51 GMT

Sorry, but I think the 'Timestamp=xx/yy/zz' part of the question is actually the timestamp as Splunk parses it, and NOT part of the event.

Re: trouble using regex to find a timestamp

kristian_kolb — Wed, 30 Oct 2013 21:21:27 GMT

Does Splunk parse the time in a nice way, i.e. is it "the same" time in Splunk as in the actual log message? In that case, you can access the time information through the built-in field _time (which is epoch), instead of doing an extra field extraction of the timestamp.

If everything is basically OK with the timestamp parsing, then don't bother with the TIME_FORMAT, and definitely not with the TIME_PREFIX. Have a look at the convert search command, or the strftime() function of the eval search command to understand on how to convert and use the information stored in _time. Stop reading here.

But if you mean that your events get the "wrong" time in Splunk, e.g. if there is more than one timestamp, then you need to write the correct TIME_FORMAT, and also possibly the TIME_PREFIX and MAX_TIMESTAMP_LOOKAHEAD.

TIME_FORMAT should reflect on how the timestamp is formatted in your event, e.g.

%e for days 1-31.
%m for days 01-31.
%y for two-digit years.
%Y for four-digit years.

TIME_PREFIX shall match upto where the time information is present in the event. If your messages are as static as your samples, that could be;

TIME_PREFIX = (?:[^,]+,){5}

i.e. the time information is in the sixth element in a comma-separated list, so skipping over non-comma characters followed by a comma 5 times would get you there.

Makes any sense?

Re: trouble using regex to find a timestamp

kristian_kolb — Wed, 30 Oct 2013 21:23:06 GMT

something is weird with the formatting here...is there only a fixed amount of code elements that can be put into a single post? fixed now.