topic Re: Calculating time difference in Splunk Search

How to calculate time difference?

EvansB — Wed, 07 Sep 2022 15:53:11 GMT

I'm looking to get a difference between both times and create a 3rd field for the results (Properties.actionedDate - _time). My current query is like this

index=* source=* | table Properties.actionedDate, _time

Here is a screenshot of my current result

Re: How to calculate time difference?

richgalloway — Wed, 07 Sep 2022 18:02:54 GMT

I probably should make this a macro since I give this answer a lot. Timestamps must be in epoch (integer) format to be compared or to find their difference. Use the strptime function for that.

index=* source=* | rename Properties.actionedDate as actionedDate | eval actionedTS = strptime(, "%Y-%m-%dT%H:%M:%S.%7N%:z") | eval diff = _time - actionedTS | table actionedDate, _time, diff

Re: Calculating time difference

EvansB — Wed, 07 Sep 2022 15:45:43 GMT

Thanks for your response.
This should work but I'm not getting results on the diff field - (I sorted with this field)
Does that mean there are no difference between timestamps and _time?

Re: Calculating time difference

richgalloway — Wed, 07 Sep 2022 16:24:55 GMT

I noticed a typo in the strptime format string in my reply, which I've corrected. I also added a rename command in case eval doesn't like the original field name.

Re: Calculating time difference

EvansB — Wed, 07 Sep 2022 16:45:45 GMT

did you edit your first comment.... I'm getting same results
can you post the corrected query?
Appreciate.

Re: Calculating time difference

richgalloway — Wed, 07 Sep 2022 18:03:27 GMT

I don't know what happened to my original edit, but I've re-posted it.

Re: Calculating time difference

EvansB — Wed, 07 Sep 2022 19:54:34 GMT

I eventually used this

index=* source=* | rename Properties.actionedDate as actionedDate | eval actionedTS = strptime(actionedDate, "%Y-%m-%dT%H:%M:%S.%7N%:z") | eval diff = _time - actionedTS | table actionedDate, _time, diff

Appreciate you @richgalloway

Re: How to calculate time difference?

vishenps — Wed, 31 Jan 2024 18:52:38 GMT

Hi @richgalloway, @ITWhisperer I have similar doubt but little tedious:

Use case:In my org primarily Mission Control events are investigated by SOC as soon as they pop up, if futher investigation is needed the incident is escalated to Enterprise security TEAM who is responsible to perform deeper/detailed investigation and update back in Mission Control.
USE CASE:
The enterprise security manger wants a DASHBOARD which will inform him about :
if the investigation is being performed by his team (ES)> how much average time his team member takes to resolve an incident (for now I'm only focusing on this)> averaged over a month.

jeff is ES resource & stephen is SOC resource
i want to pick end_time where resource is Stephen and notes is "Escalation to ES" and start_time where resource is jeff and subtract them in order to get claim_time_by_ES.

SO far the query I'm using but not successful yet is:

| mcincidents unwind_to=task | search incident_id="3e864839-xyzab" | eval is_es_team=if(IN(owner, "Jeff","Rama", "Mel"), 1, 0) | eval is_soc_team=if(IN(owner, "Stephen", "Crossman", "Ruby","Cole"), 1,0) | eval end_time_for_soc=if(is_soc_team==1 AND name=="Escalation to ES", end_time, null()) | eval start_time_for_ES=if(is_es_team==1, start_time, null()) | eval total_time_claimed=end_time_for_soc - start_time_for_ES

in the below snapshot of log the columns name are in sequence of: owner > start_time > end_time > total_time_taken> notes