Splunk Search

Why am I unable to change this datetime string to a time formatted field?

lukas_loder
Communicator

I have a date timestamp coming in as a string in this format
‎2015‎-‎10‎-‎07T19:49:34.676416100Z

With Regex, I get one Field called Date_old like this: 2015-10-07
and one called Time_old like this: 19:49:34.676416100

Now I want to be able to calculate with this time. So I also have a DateTime_old field like this: 2015-10-07 19:49:34.676416100
I want to compare this DateTime_old with another same formatted Timestamp in the same Log (DateTime_new extracted)

But I can't change this string to a Time-formatted field.
I already tried things like this:

eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S")

or

eval epochtime=strptime(DateTime_old, "%Y-%m-%d")

or

eval A_reformatted_date=strftime(strptime(Date_old,"%Y-%m-%d"),"%m/%d/%y")

and nothing worked. I never get anything in my new eval field. I can check with a table, that my Time_old, Date_old and DateTime_old Fields are correct, but I never get any value in my new field.
Do you have any idea what I can change or try?

1 Solution

lukas_loder
Communicator

SOLUTION:

I figured out, that there is somethingbetween "2015" and the "-". But I have no idea what.

Now I'm doing it with substr and it works fine.

DateTime_old = substr(Date_old,1,4) +"-"+ substr(Date_old,8,2) +"-"+ substr(Date_old,13,2) +" "+ Time_old

View solution in original post

matthewhaswell
Path Finder

I think I hit this weirdness as well - this is from Windows system event logs isn't it? I wanted to check how well (or not) our NTP system was working.

I used this search:

source="WinEventLog:System" "system time has changed" | rex field=Message ".*to (?<StartTime>[^.]+).*from (?<EndTime>[^.]+)\."  | eval StartUnix=strptime(StartTime, "%Y-%m-%dT%H:%M:%S") | eval EndUnix=strptime(EndTime, "%Y-%m-%dT%H:%M:%S") | table _time host StartTime EndTime StartUnix EndUnix

However, like Lukas, the strptime wasn't doing the conversion.

Copying and pasting the text from Splunk into Notepad++ then actually the Message line is:

Message=The system time has changed to ?2015?-?12?-?13T13:28:07.492000000Z from ?2015?-?12?-?13T13:18:04.893874600Z.

Notice the hidden control codes around the date fields? What on earth were Microsoft thinking? Anyway I have a solution using the rex command.

rex field=Message ".*to \D(?<StartYear>\d+)\D-\D(?<StartMonth>\d+)\D-\D(?<StartDay>\d+)T(?<StartTime>.*)Z from \D(?<EndYear>\d+)\D-\D(?<EndMonth>\d+)\D-\D(?<EndDay>\d+)T(?<EndTime>.*)Z"

The full search string I used is:

source="WinEventLog:System" "system time has changed" | rex field=Message ".*to \D(?<StartYear>\d+)\D-\D(?<StartMonth>\d+)\D-\D(?<StartDay>\d+)T(?<StartTime>.*)Z from \D(?<EndYear>\d+)\D-\D(?<EndMonth>\d+)\D-\D(?<EndDay>\d+)T(?<EndTime>.*)Z" | strcat StartYear "-" StartMonth "-" StartDay "T" StartTime StartTime | strcat EndYear "-" EndMonth "-" EndDay "T" EndTime EndTime | eval StartUnix=strptime(StartTime, "%Y-%m-%dT%H:%M:%S.%9N") | eval EndUnix=strptime(EndTime, "%Y-%m-%dT%H:%M:%S.%9N") | eval TotalTime=EndUnix - StartUnix | table _time host StartTime EndTime StartUnix EndUnix TotalTime

matthewhaswell
Path Finder

Ah! Looks like it was fixed in 2008 R2. So now there are 2 different timestamp formats in the logs.
e.g.

Message=The system time has changed to ?2015?-?12?-?13T13:28:07.492000000Z from ?2015?-?12?-?13T13:18:04.893874600Z.

and

Message=The system time has changed to 2015-12-12T09:09:14.198Z from 2015-12-12T09:09:14.198Z.

So here is my fixed rex (only to the second - decided not to bother with milliseconds):

.*to\D+(?<StartYear>\d+)\D+(?<StartMonth>\d+)\D+(?<StartDay>\d+)T(?<StartTime>[^.]+).* from\D+(?<EndYear>\d+)\D+(?<EndMonth>\d+)\D+(?<EndDay>\d+)T(?<EndTime>[^.]+)

Here is the full search:

source="WinEventLog:System" "system time has changed" | rex field=Message ".*to\D+(?<StartYear>\d+)\D+(?<StartMonth>\d+)\D+(?<StartDay>\d+)T(?<StartTime>[^.]+).* from\D+(?<EndYear>\d+)\D+(?<EndMonth>\d+)\D+(?<EndDay>\d+)T(?<EndTime>[^.]+)" | strcat StartYear "-" StartMonth "-" StartDay "T" StartTime StartTime | strcat EndYear "-" EndMonth "-" EndDay "T" EndTime EndTime | eval StartUnix=strptime(StartTime, "%Y-%m-%dT%H:%M:%S") | eval EndUnix=strptime(EndTime, "%Y-%m-%dT%H:%M:%S") | eval TotalTime=EndUnix - StartUnix | table _time host StartTime EndTime TotalTime
0 Karma

lukas_loder
Communicator

SOLUTION:

I figured out, that there is somethingbetween "2015" and the "-". But I have no idea what.

Now I'm doing it with substr and it works fine.

DateTime_old = substr(Date_old,1,4) +"-"+ substr(Date_old,8,2) +"-"+ substr(Date_old,13,2) +" "+ Time_old

srinathhh
New Member

eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S") eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S")

0 Karma

lukas_loder
Communicator

I figured out, that there is something between "2015" and the "-". But I have no idea what.

Now I'm doing it with substr and it works fine. Strange....

DateTime_old = substr(Date_old,1,4) +"-"+ substr(Date_old,8,2) +"-"+ substr(Date_old,13,2) +" "+ Time_old

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Your strptime format string has to match the value in the field. Try this

eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S.%9N")

Once you've converted the times into epoch format it's easy to compare them or make calculations.

---
If this reply helps you, Karma would be appreciated.
0 Karma

lukas_loder
Communicator

Thanks!

Unfortunately it doesn't work

my DateTime_old looks like this
2015‎-‎10‎-‎07 20:24:57.879337800

I check it with the table, the DateTime_old is always correct, but the createDt is empty

| eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S.%9N") | table DateTime_old createDt

Here is my complete search string:

EventCode=1 "Keywords=Time" | rex field=Message "The system time has changed to ‎(?P<Date_old>\\d+‎\\-‎\\d+‎\\-‎\\d+)T(?P<Time_old>\\d+:\\d+:\\d+\\.\\d+)\\w+\\s+\\w+\\s+‎(?P<Date_new>\\d+‎\\-‎\\d+‎\\-‎\\d+)T(?P<Time_new>\\d+:\\d+:\\d+\\.\\d+)" | eval DateTime_old = Date_old +" "+ Time_old | eval createDt = strptime(DateTime_old,"%Y-%m-%d %H:%M:%S.%9N") | table DateTime_old createDt

thanks for your help!

0 Karma

richgalloway
SplunkTrust
SplunkTrust

It definitely works as shown in this run-anywhere example.

index=_internal | head 1 | eval DateTime_old="2015-10-07 20:24:57.879337800" | eval createDt=strptime(DateTime_old,"%Y-%m-%d %H:%M:%S.%9N") | table DateTime_old createDt

DateTime_old    createDt
2015-10-07 20:24:57.879337800   1444263897.879337

Interestingly, the same search fails if I copy-and-paste your DateTime_old string, but works find if typed by hand.

---
If this reply helps you, Karma would be appreciated.
0 Karma

lukas_loder
Communicator

Thanks for your hint!

I also tried copy my value. And it didn't work. I figured out, that there is somethingbetween "2015" and the "-". But I have no idea what.

Now I'm doing it with substr and it works fine. Strange....

DateTime_old = substr(Date_old,1,4) +"-"+ substr(Date_old,8,2) +"-"+ substr(Date_old,13,2) +" "+ Time_old
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Glad to have helped. Please accept an answer.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...