Splunk Search

DateTime Convertion

duyanhtr
Engager

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

alt text

0 Karma
1 Solution

gvmorley
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

DalJeanis
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

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

duyanhtr
Engager

thankyou for helping

0 Karma

niketn
Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

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

duyanhtr
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

DalJeanis
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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...