Why does field becomes null when attempting to subtract?

[iomega311]

I am feeling puzzled. I am trying to take a date, convert it to epoch time, and then subtract a number of seconds from that time... then reconstruct it back to a human readable format.

I have a field called "eventTime" that comes in looking like this:
2023-02-20T22:33:00.000Z

I am converting it to epoch time like so:
| eval eventTime=strptime(eventTime,"%Y-%m-%dT%H:%M:%S.%3QZ")

I have then converted to the server time, like so:
| eval eventTime=strftime(eventTime, "%+")

After those steps, the value in "eventTime" looks like so:
Mon Feb 20 22:33:03 MST 2023

I am then attempting to convert to epoch like so:
| eval event_etime=strptime(eventTime, "%a %b %e %H:%M:%S %Z %Y")

This works, and converts it to this value:
1677034564.000000

Everything works as I would expect thus far... it is when I attempt to do any sort of math, that it turns the value to null.

So, with this statement:
| eval event_etime=tonumber(event_etime)-25200

I am attempting to subtract 23,200 seconds off the time... but when I do this step, the value goes null.
I have tried with and without the "tonumber" function... it doesn't do a thing.
Any ideas on how I can subtract "25200" from the epoch time, and retain a value that is not null?

[bowesmana]

OK, so your logic

| makeresults
| fields - _time
| eval eventTime="2023-02-20T22:33:00.000Z"
| eval eventTime1=strptime(eventTime,"%Y-%m-%dT%H:%M:%S.%3QZ")
| eval eventTime2=strftime(eventTime1, "%+")
| eval event_etime=strptime(eventTime2, "%a %b %e %H:%M:%S %Z %Y")
| eval event_etime=tonumber(event_etime)-25200

works fine in the above - I have used variants of eventTimeX to retain each step.

Note that the value parsed in strptime in both cases is the same, as strptime will not recognise this directly as a UTC timestamp.

If you do this in your first strptime

| eval eventTime1=strptime(eventTime." UTC","%Y-%m-%dT%H:%M:%S.%3QZ %Z")

it will force it to be parsed as UTC.

Not sure why you are getting null - does the above work for you?

[iomega311]

So, when I try the suggested query, using the generated makeresults.... it works like a charm, as expected.
When I then pull from the index/sourcetype for the same data, but leave the eval statement that generates the "eventTime" field... it still works.

However, once I remove this line:

| eval eventTime="2023-02-20T22:33:00.000Z"

And just use whatever value already comes in the eventTime field... it breaks again.
This makes me feel like it is not coming in the exact format expected, maybe an extra space or tab somewhere in there... so I put together a very tight regex and used SED to replace whatever it found with the expected pattern, and it still broke.

I then built a query that dumps out each of the stages... and everything works right up until I attempt to do some sort of math function. So in this example:

| makeresults
| fields - _time
| eval eventTime="2023-02-20T22:33:00.000Z"
| rex field=eventTime mode=sed "s/.*(\d{4}\x2d\d{2}\x2d\d+T(?:\d{2}\x3a){2}\d{2}\x2e\d+Z).*/\1/g"
| eval eventTime1=strptime(eventTime,"%Y-%m-%dT%H:%M:%S.%3QZ")
| eval eventTime2=strftime(eventTime1, "%+")
| eval event_etime=strptime(eventTime2, "%a %b %e %H:%M:%S %Z %Y")
| rex field=event_etime "^(?P<event_etime2>\d+)"
| eval event_etime3=tonumber(event_etime2)+1
| eval event_etime3=if(isnull(event_etime3),"null",event_etime3)
| table eventTime eventTime1 eventTime2 event_etime event_etime2 event_etime3

It works great.

However once I remove the first 3 lines, and replace it with my index search... "event_etime3" shows up with "null" as the value (because of the "if" statement to add in "null" when null):

| rex field=eventTime mode=sed "s/.*(\d{4}\x2d\d{2}\x2d\d+T(?:\d{2}\x3a){2}\d{2}\x2e\d+Z).*/\1/g"
| eval eventTime1=strptime(eventTime,"%Y-%m-%dT%H:%M:%S.%3QZ")
| eval eventTime2=strftime(eventTime1, "%+")
| eval event_etime=strptime(eventTime2, "%a %b %e %H:%M:%S %Z %Y")
| rex field=event_etime "^(?P<event_etime2>\d+)"
| eval event_etime3=tonumber(event_etime2)+1
| eval event_etime3=if(isnull(event_etime3),"null",event_etime3)
| table eventTime eventTime1 eventTime2 event_etime event_etime2 event_etime3

Any sort of math function seems to break it, and that field has a value up until the attempted math.

[bowesmana]

I wonder what you are actually trying to achieve with this original date...

Is event_etime3 null when event_etime2 a number?

However, you have a UTC timestamp - what do you want to end up with - I suspect you're trying to convert it to local time.

If you parse the original eventTime and tell Splunk that the Z is a timezone identifier, i.e. UTC, then the epoch will be UTC. If you then format that into a date it will be formatted as local time

By using %Z to parse the 'Z' it will recognise it as UTC

| eval eventTime1=strptime(eventTime,"%Y-%m-%dT%H:%M:%S.%3Q%Z")