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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...