topic Re: How to find the difference of time between the 2 fields ? in Splunk Search

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

Real_captain — Mon, 15 Jan 2024 16:54:07 GMT

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"

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

ITWhisperer — Mon, 15 Jan 2024 17:32:17 GMT

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.

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

PickleRick — Mon, 15 Jan 2024 22:05:21 GMT

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.

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

Real_captain — Tue, 16 Jan 2024 10:18:39 GMT

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"

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

PickleRick — Tue, 16 Jan 2024 10:26:36 GMT

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.

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

Real_captain — Tue, 16 Jan 2024 13:05:18 GMT

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"

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

ITWhisperer — Tue, 16 Jan 2024 13:22:19 GMT

| eval diff= now() - reception_time

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

PickleRick — Tue, 16 Jan 2024 13:36:19 GMT

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.