Splunk Search

How to find the difference of time between the 2 fields ?

Real_captain
Path Finder

Hi 

Can you please tell me how can i  extract the events for which the difference of current_time and timestampOfReception is greater that 4 hours for the below Splunk query : 

 

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001 (messageType= seev.047* OR messageType= SEEV.047*) status = SUCCESS targetPlatform = SRS_ESES
NOT [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Received Disclosure Response Command"
| spath input=Properties.appHdr
| rename bizMsgIdr as messageBusinessIdentifier
| fields messageBusinessIdentifier ]
| eval Current_time =strftime(now(),"%Y-%m-%d %H:%M:%S ")
| eval diff= Current_time-timestampOfReception
| fillnull timestampOfReception , messageOriginIdentifier, messageBusinessIdentifier, direction, messageType, currentPlatform, sAAUserReference value="-"
| sort -timestampOfReception
| table diff , Current_time, timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform,
| rename timestampOfReception AS "Timestamp of reception", originPlatform AS "Origin platform", sourcePlatform AS "Source platform", targetPlatform AS "Target platform", senderIdentifier AS "Sender identifier", receiverIdentifier AS "Receiver identifier",
messageOriginIdentifier AS "Origin identifier", messageBusinessIdentifier AS "Business identifier", direction AS Direction, currentPlatform AS "Current platform", sAAUserReference AS "SAA user reference", messageType AS "Message type"
Labels (1)
0 Karma

Real_captain
Path Finder

i am not able to find the difference of time using the below clause :

Can you please tell me what i should add to get the difference of the 2 timestamps. 

| eval diff= Current_time-timestampOfReception

 

Complete search : 

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001 (messageType= seev.047* OR messageType= SEEV.047*) status = SUCCESS targetPlatform = SRS_ESES
NOT [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Received Disclosure Response Command"
| spath input=Properties.appHdr
| rename bizMsgIdr as messageBusinessIdentifier
| fields messageBusinessIdentifier ]
| eval Current_time =strftime(now(),"%Y-%m-%d %H:%M:%S ")
| eval diff= Current_time-timestampOfReception
| fillnull timestampOfReception , messageOriginIdentifier, messageBusinessIdentifier, direction, messageType, currentPlatform, sAAUserReference value="-"
| sort -timestampOfReception
| table diff , Current_time, timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform,
| rename timestampOfReception AS "Timestamp of reception", originPlatform AS "Origin platform", sourcePlatform AS "Source platform", targetPlatform AS "Target platform", senderIdentifier AS "Sender identifier", receiverIdentifier AS "Receiver identifier",
messageOriginIdentifier AS "Origin identifier", messageBusinessIdentifier AS "Business identifier", direction AS Direction, currentPlatform AS "Current platform", sAAUserReference AS "SAA user reference", messageType AS "Message type"

 

Real_captain_0-1705400299019.png

 

 

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Apart from all the things we already said with @ITWhisperer your main problem here is this:

| eval Current_time =strftime(now(),"%Y-%m-%d %H:%M:%S ")
| eval diff= Current_time-timestampOfReception

with the fact that most probably your timestampOfReception is also a string field from your event (you're not strptime()-ing it anywhere in your search so I can safely assume that).

What you're trying to do is running a substraction operation on two strings. It won't fly. Strings are not substractable (also they are not additive in Splunk, you need to use concatenation operator).

So you won't get any value at all and that's normal in this case.

What you need to do is to use strptime() (not strftime()!) to parse the timestampOfReception field to the so-called unix-timestamp (which is the number of seconds from epoch which means it's a number) and substract it from the value of now() which is also returned as unix-timestamp. There is no need to formatting any of that into strings. Quite contrary - you want both of those timestamps as numbers because then you can easily manipulate them.

0 Karma

Real_captain
Path Finder

Hi 

Even with strptime , i am not able to find the difference of current time and timestampOfReception. i am using the below criteria. 

Can you please help me to extract the difference of current time and timestampOfReception in the diff field. 

And strptime is not working with the the function now (). 

`eoc_stp_events_indexes` host=p* OR host=azure_srt_prd_0001 (messageType= seev.047* OR messageType= SEEV.047*) status = SUCCESS targetPlatform = SRS_ESES
NOT [ search (index=events_prod_srt_shareholders_esa OR index=eoc_srt) seev.047 Name="Received Disclosure Response Command"
| spath input=Properties.appHdr
| rename bizMsgIdr as messageBusinessIdentifier
| fields messageBusinessIdentifier ]
| eval Current_time =strftime(now(),"%Y-%m-%d %H:%M:%S ")
| eval reception_time =strptime( timestampOfReception , "%Y-%m-%d%H:%M:%S.%N" )
| eval diff= current_time - reception_time
| fillnull timestampOfReception , messageOriginIdentifier, messageBusinessIdentifier, direction, messageType, currentPlatform, sAAUserReference value="-"
| sort -timestampOfReception
| table diff , reception_time, Current_time , timestampOfReception, messageOriginIdentifier, messageType, status, messageBusinessIdentifier, originPlatform, direction, sourcePlatform, currentPlatform, targetPlatform, senderIdentifier, receiverIdentifier, currentPlatform,
| rename timestampOfReception AS "Timestamp of reception", originPlatform AS "Origin platform", sourcePlatform AS "Source platform", targetPlatform AS "Target platform", senderIdentifier AS "Sender identifier", receiverIdentifier AS "Receiver identifier",
messageOriginIdentifier AS "Origin identifier", messageBusinessIdentifier AS "Business identifier", direction AS Direction, currentPlatform AS "Current platform", sAAUserReference AS "SAA user reference", messageType AS "Message type"

Real_captain_0-1705410311477.png

 



0 Karma

PickleRick
SplunkTrust
SplunkTrust

As @ITWhisperer already showed - there's no point of strftime'ing now() just to do strptime() on the result back to get the unix timestamp. Just use the value of now() directly.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eval diff= now() - reception_time
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Datetime calculations such as finding the difference should be done with epoch times so rather than formatting now() you should be parsing timestampOfReception using strptime() so you can subtract one from the other.

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Also if timestampOfReception is the main timestamp of the event, it should be properly parsed as _time field of the event. It makes searching the events much, much quicker.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...