topic Re: Splitting a Field into multiple values - separating date from time in Splunk Search

Splitting a Field into multiple values - separating date from time

markthompson — Wed, 22 Oct 2014 08:44:14 GMT

Hi,
I have a field called Submit Date and it's format is like this: 10/21/2014 11:26:05 AM

I'm trying to separate this into 2 fields, to something like

Submit Date: 10/21/2014
Submit Time: 11:26:05 AM

I'm hoping to work out the average difference between the start and end of each event, so if I could also find a way to detect whether or not it's AM or PM and then convert to 24 hour clock, that would also be helpful.

If anyone has any suggestions as a way to deal with averaging the differences / response time, please let me know.

Re: Splitting a Field into multiple values - separating date from time

mathiask — Mon, 28 Sep 2020 18:00:01 GMT

If this is the timestamp of the event then on index time this should be correctly extracted
Everything else can be done later during search time
In the case this is the Event time then simply use the _time field and extract of it what you want using the eval function with strftime
http://www.wowhead.com/news=242815/preparing-for-6-0-guide-to-removed-mounts-pets-transmog-gear-titles-achievements

If it is not the event time, extract the strings during search time and process them accordingly using eval and strptime

To extract it use a regex like (?<submit_date>BLd{1,2}BL/BLd{1,2}BL/BLd{4})BLs+(?<submit_time>BLd{2}:BLd{2}:BLd{2}BLs+BLw{2})
not tested but should be close enough to figure out

PS i had the removal of < and > and backslash ...
BL stands for backslash since it wont be printed here ...

Good luck 🙂

Re: Splitting a Field into multiple values - separating date from time

markthompson — Wed, 22 Oct 2014 09:08:10 GMT

This is an actual field in the data as submitted date & also Closed Date

And I've never used regex command before, so Don't have a clue how to use it.

Re: Splitting a Field into multiple values - separating date from time

mathiask — Mon, 28 Sep 2020 18:00:04 GMT

You probably have to learn it ...
http://docs.splunk.com/Documentation/Splunk/6.1.4/SearchReference/rex
http://docs.splunk.com/Documentation/Splunk/6.1.4/Knowledge/AboutSplunkregularexpressions

To give you a short introduction about the string I wrote
- it will try to find the pattern defined in the expression
- the part within the round brackets defines one return value and the field/key it will be associated with is defined in the arrow brackets following the question mark.

so the first part will look for a pattern that starts with one or two digits (defined by the backslash d{1,2}, followed by a slash (defined by backslash slash) followed by again one or two digits and a slash followed by for digits. The result will be returned as a value and given to the key submit_date. The backslash s plus stands for one or more Whitespaces, this is not withing round brackets since we don't need the space
so it looks for something like
d(d)/d(d)/dddd which will match the 10/21/2014 so you get submit_date=10/21/2014
This pattern also allows that 9/9/2014 will be extracted.
The pattern is very specific and will not match a/bb/2014 since a and b are not numbers. I did this because there is not much context before or after the time string like "submit time 20/10/2014 12:05:15 AM"

Keep in mind this is now only a string. To do some math, difference etc you first have to convert it into a time value with strptime and then convert the result back using strftime