Splunk Search
Highlighted

DateTime Convertion

Engager

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

host="gmw8" OR host="gmw12" sourcetype="WinEventLog:System" EventCode=6008
|rex field=Message "at (?.[M])"
|rex field=Message "on (?.
)/(?.)/(?[^ ])"
|eval IDate=IMon+"-"+IDay+"-"+IYear
|eval IDateTime= IDate+" "+ITime
|eval IDateTimeepoch = strptime(IDateTime, "%m-%d-%Y %l:%M:%S %p")
|eval DateTime = strftime(IDateTime
epoch, "%Y-%m-%d %H:%M:%S")
| eval mytime = "2-14-2017 1:06:59 PM"
| eval my
timeepoch = strptime(mytime, "%m-%d-%Y %l:%M:%S %p")
| eval mytime = strftime(mytimeepoch, "%Y-%m-%d %H:%M:%S")
|table IDateTime,IDateTimeepoch,DateTime,mytime,mytimeepoch,mytime

alt text

0 Karma
Highlighted

Re: DateTime Convertion

Contributor

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.

View solution in original post

Highlighted

Re: DateTime Convertion

Engager

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.

0 Karma
Highlighted

Re: DateTime Convertion

SplunkTrust
SplunkTrust

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.

0 Karma
Highlighted

Re: DateTime Convertion

Legend

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).




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: DateTime Convertion

Engager

thankyou for helping

0 Karma
Highlighted

Re: DateTime Convertion

Legend

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




| eval message="Happy Splunking!!!"


0 Karma
Highlighted

Re: DateTime Convertion

SplunkTrust
SplunkTrust

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.

0 Karma