How to calculate time difference from 2022-07-14T09:05:08.21-04:00 format

[DPOIRE]

Good Day,
I need help to calculate the time difference for field "@timestamp" containing time format 2022-07-14T09:05:08.21-04:00
Example:
MYSearch | stats range(@timestamp) as Delay by "log_processed.logId" | stats max(Delay)

If I do the same with the Splunk _time field, it works perfectly

[gcusello]

Hi @DPOIRE,

_time is in epochtime even if is displayed in human readable, instead @timestamp is in a different format, so you have to convert using eval strptime:

| eval "@timestamp"=strptime("@timestamp","%Y-%m-%dT%H:%M:%S.%2N%:z")

In addition, I hint to avoid to use special chars in field names, 

Ciao.

Giuseppe

[DPOIRE]

@gcusello 
NOT working
The search find events but statistics as no value

[gcusello]

Hi @DPOIRE,

could you share your full search?

Ciao.

Giuseppe

[DPOIRE]

@gcusello 

index=indexname ("log_processed.destAppName"=app1 "log_processed.message"="Publish Take Message")
OR ("log_processed.destAppName"="app2" "log_processed.srcAppName"=app3)
| eval "newtime"=strptime("log_processed.@timestamp","%Y-%m-%dT%H:%M:%S.%2N%:z")
| stats range("newtime") as Delay by "log_processed.logId"
| stats max(Delay)

[DPOIRE]

@gcusello 

For each log_processed.logId it returns 2 events for which I want to calculate the time difference in the "log_processed.@timestamp" field

[gcusello]

Hi @DPOIRE,

at first avoid dots, spaces or special chars in field names, if you have rename them, to be more sure!

, so range function should run, but to debug your search use manuale calculation of range:

index=indexname ("log_processed.destAppName"=app1 "log_processed.message"="Publish Take Message")
OR ("log_processed.destAppName"="app2" "log_processed.srcAppName"=app3)
| rename "log_processed.@timestamp" AS timetamp
| eval newtime=strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%2N%:z")
| stats earliest(newtime) AS earliest latest(newtime) AS latest values(timestamp) AS timestamp BY "log_processed.logId"
| eval Delay=latest-earliest
| stats max(Delay) values(timestamp) AS timestamp 

In this way you manually calculate Delay and you display the values of starting and ending time.

Ciao.

Giuseppe

[DPOIRE]

@gcusello 
Hi,
Function is still not working, no values displayed in the Statistic window.
However, in the Events window, I see in the left pane the timestamp field.
Looks like the timestamp values are not recognized as epoch time format.
timestamp field

[gcusello]

Hi @DPOIRE,

analyzing your screenshot I see a difference in the time format of timestamp: there are three milliseconds and not two as the sample you shared, so please, use %3N insted of %2N in the eval command.

This explain the reading problem.

Ciao.

Giuseppe

[gcusello]

Hi @DPOIRE,

are you sure that the name of the field timestamp originally is "log_processed.@timestamp"?

You can see it using the main search without the other parts of the search.

Ciao.

Giuseppe

[DPOIRE]

Actually, from what I see (previous printscreen) the strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%2N%:z") function conversion does not seem to work.

[gcusello]

Hi @DPOIRE,

to be sure about the convertion in epochtine, please run this:

index=indexname ("log_processed.destAppName"=app1 "log_processed.message"="Publish Take Message")
OR ("log_processed.destAppName"="app2" "log_processed.srcAppName"=app3)
| rename "log_processed.@timestamp" AS timetamp
| eval newtime=strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%3N%:z")
| table _time newtime timestamp

each row should have all the three values if not the problem is in the convertion.

Ciao.

Giuseppe

[DPOIRE]

@gcusello 

newtime and timestamp have no values(blank)

[DPOIRE]

tried, makes no difference

[DPOIRE]

@gcusello 
Found something
Typo in the rename command for timestamp.
That why it is not working (I copied your stuff all the time)

Will redo tests without typo

[DPOIRE · Accepted solution]

ITS WORKING !
Final result:

MySearch
| rename "log_processed.@timestamp" AS timestamp
| eval epoch_timestamp=strptime(timestamp,"%Y-%m-%dT%H:%M:%S.%3N%:z")
| stats range(epoch_timestamp) as Delay by "log_processed.logId"
| stats max(Delay)

Note: rename command mandatory, else it was not working
Sorry, I should have seen the typo from the start.
Do you want to repost the solution so I can click on Accept Solution?

Thanks

[gcusello]

Hi @DPOIRE,

no, no problem, it's important that there's the correct solution for the other people of Community.

Ciao and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉