Splunk Search

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

DPOIRE
Path Finder

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

Labels (1)
0 Karma
1 Solution

DPOIRE
Path Finder

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

View solution in original post

0 Karma

gcusello
Esteemed Legend

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

0 Karma

DPOIRE
Path Finder

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

0 Karma

gcusello
Esteemed Legend

Hi @DPOIRE,

could you share your full search?

Ciao.

Giuseppe

0 Karma

DPOIRE
Path Finder

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

0 Karma

DPOIRE
Path Finder

@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

0 Karma

gcusello
Esteemed Legend

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

0 Karma

DPOIRE
Path Finder

@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 fieldtimestamp field

0 Karma

gcusello
Esteemed Legend

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

0 Karma

gcusello
Esteemed Legend

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

0 Karma

DPOIRE
Path Finder

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.

0 Karma

gcusello
Esteemed Legend

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

0 Karma

DPOIRE
Path Finder

@gcusello 

newtime and timestamp have no values(blank)

timestamp1.jpg

0 Karma

DPOIRE
Path Finder

tried, makes no difference

0 Karma

DPOIRE
Path Finder

@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

0 Karma

DPOIRE
Path Finder

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

0 Karma

gcusello
Esteemed Legend

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 😉

Get Updates on the Splunk Community!

New Learning Videos on Topics Most Requested by You! Plus This Month’s New Splunk ...

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

How I Instrumented a Rust Application Without Knowing Rust

As a technical writer, I often have to edit or create code snippets for Splunk's distributions of ...

Splunk Community Platform Survey

Hey Splunk Community, Starting today, the community platform may prompt you to participate in a survey. The ...