topic Re: Why am I unable to change this datetime string to a time formatted field? in Splunk Search

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

lukas_loder — Wed, 07 Oct 2015 20:03:17 GMT

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

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

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?

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

richgalloway — Wed, 07 Oct 2015 20:20:41 GMT

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.

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

lukas_loder — Wed, 07 Oct 2015 20:32:36 GMT

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!

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

richgalloway — Wed, 07 Oct 2015 21:45:10 GMT

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.

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

lukas_loder — Thu, 08 Oct 2015 06:03:14 GMT

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

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

srinathhh — Tue, 29 Sep 2020 07:31:50 GMT

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

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

lukas_loder — Tue, 29 Sep 2020 07:31:56 GMT

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

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

richgalloway — Thu, 08 Oct 2015 11:37:33 GMT

Glad to have helped. Please accept an answer.

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

lukas_loder — Thu, 08 Oct 2015 12:20:01 GMT

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

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

matthewhaswell — Mon, 14 Dec 2015 10:37:06 GMT

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

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

matthewhaswell — Mon, 14 Dec 2015 17:00:46 GMT

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