topic Re: DateTime Convertion in Splunk Search

DateTime Convertion

duyanhtr — Tue, 29 Sep 2020 13:00:27 GMT

Hi, I don't understand why my datetime extracted can't convert when same format has no issue

host="gm*w8*" OR host="gm*w12*" sourcetype="WinEventLog:System" EventCode=6008
|rex field=Message "at (?.[M])"
|rex field=Message "on (?.)/(?.)/(?[^ ])"
|eval IDate=IMon+"-"+IDay+"-"+IYear
|eval IDateTime= IDate+" "+ITime
|eval IDateTime_epoch = strptime(IDateTime, "%m-%d-%Y %l:%M:%S %p")
|eval DateTime = strftime(IDateTime_epoch, "%Y-%m-%d %H:%M:%S")
| eval my_time = "2-14-2017 1:06:59 PM"
| eval my_time_epoch = strptime(my_time, "%m-%d-%Y %l:%M:%S %p")
| eval mytime = strftime(my_time_epoch, "%Y-%m-%d %H:%M:%S")
|table IDateTime,IDateTime_epoch,DateTime,my_time,my_time_epoch,mytime

Re: DateTime Convertion

gvmorley — Wed, 22 Feb 2017 03:38:35 GMT

Hi,

It's possible that maybe you've picked up an extract character (space, newline, carriage return) in your rex commands.

The code in you're question for the rex statements doesn't quite look complete, as it doesn't have any named capture groups. But I'm assuming that you're extracting IMon, IDay, IYear and ITime, as you use these later in your eval.

One thing you could try would be to check the length of your field IDateTime, to make sure that it is what you'd expect. Just eval another field, such as:

eval IDateTime_Len=len(IDateTime)

Not sure if that will be it, but it's worth a look.

You could also check that each of the fields is being extracted as a String, so that you're concatenation works. Check these with the typeof() function.

The final one is try using the period (.) instead of the plus (+) for the concatenation. This one shouldn't make a difference, but it's what I'd normally do, just from a readability perspective, to know that I'm not trying to do addition on the fields.

If you could also add some example data and the full rex commands, there might be some pointers there too.

Re: DateTime Convertion

niketn — Wed, 22 Feb 2017 06:40:28 GMT

Not sure what is wrong based on IDateTime that you have in your screenshot. Can you try a different command for the same?

<YourBaseSearch>
| convert timeformat="%m-%d-%Y %I:%M:%S %p" mktime(IDateTime)
| fieldformat IDateTime=strftime(date,"%m-%d-%Y %I:%M:%S %p")

PS: Use of fieldformat instead of eval will keep underlying field as it is (in our case epochtime) and just display the changed value (human readable date).

Re: DateTime Convertion

duyanhtr — Thu, 23 Feb 2017 22:51:28 GMT

thankyou for helping

Re: DateTime Convertion

duyanhtr — Thu, 23 Feb 2017 22:54:55 GMT

i spent countless hours then find out my raw data has "invisible character" known as Left-to-right mark (\u200e)

FML ... thankyou for suggesting look at the len.

Re: DateTime Convertion

DalJeanis — Fri, 24 Feb 2017 04:02:43 GMT

When you post a question, answer or comment, make sure to mark the code as code (that's the button with the 101 010 on it) so that the interface will not strip out your tags or anything in angle brackets < >.

It's especially important if you want feedback on your regexs.

Re: DateTime Convertion

DalJeanis — Fri, 24 Feb 2017 04:11:32 GMT

Glad that you solved your problem. Please accept an answer so that everyone knows the problem was solved.

gvmorley's seems to have pointed you to the solution.

Re: DateTime Convertion

niketn — Fri, 24 Feb 2017 16:53:39 GMT

@duyanhtr... Anytime, did mktime work for you despite the spooky character?