topic Re: Calculate time difference between multivalue fields in Splunk Search

Calculate time difference between multivalue fields

ayush1234 — Wed, 15 Jul 2020 12:47:54 GMT

Hi , I have a data from search in below format
Name provider1IN provider1OUT provider2IN provider2OUT
ABC 13:00 14:00 15:00 16:00
17:00 18:00 19:00 20:00
BCD 21:00 22:00
23:00 23:30

here, for ABC, Intime at provider 1 is at 13:00 and 17:00 hours and out time is 14:00 and 18:00 hours. Similary at provider 2, intime is 15:00 and 19:00 hours and out time is 16:00 and 20:00 hours
For BCD, provider1 intime and outtime are null values and only provider2 intime and outtime has value as shown in above table.

Requirement : I need to calculate total time spent by ABC and BCD in provider1 and provider 2
which means what I want to achieve is

ABC provider1time provider2time
(14:00-13:00)+(18:00-17:00) (16:00-15:00)+(20:00-19:00)
BCD 0 (22:00-21:00)+(23:30-23:00)

Kindly help and suggest how can I achieve the above result
I am using stats list function to retrieve the multivalue intime and outtime.

Thanks for the help in advance.

Re: Calculate time difference between multivalue fields

richgalloway — Wed, 15 Jul 2020 15:48:54 GMT

I think stats list has painted you into a corner. Can you remove that and use eval to compute the difference between IN and OUT for each provider? Then use stats to sum the differences.

... | eval provider1diff = strptime(provider1OUT, "%H:%M") - strptime(provider1IN, "%H:%M"), provider2diff = strptime(provider1OUT, "%H:%M") - strptime(provider2IN, "%H:%M") | stats sum(provider1diff) as provider1time, sum(provider2diff) as provider2time by Name | fillnull value=0

Re: Calculate time difference between multivalue fields

ayush1234 — Wed, 15 Jul 2020 16:16:20 GMT

Thanks @richgalloway But since for each Name , provider1IN,provider1OUT,provider2IN,provider2OUT are multivalue fields, a direct subtraction is not working. I have to use list to get all the times when request was sent to provider and when response was received.

Re: Calculate time difference between multivalue fields

richgalloway — Wed, 15 Jul 2020 16:45:22 GMT

Don't use stats list. Then you won't have multi-value fields. That's the crux of my reply.

Re: Calculate time difference between multivalue fields

ayush1234 — Thu, 16 Jul 2020 03:28:46 GMT

Hi @richgalloway
I have to either use
stats list(eval(if(Source="Provider1",_time,null()))) as Provider1IN list(eval(if(Destination="Provider1",_time,null()))) as Provider1OUT

OR

stats values(eval(if(Source="Provider1",_time,null()))) as Provider1IN values(eval(if(Destination="Provider1",_time,null()))) as Provider1OUT

In either case, Provider1IN and Provider1OUT are multivalue field and similar for Provider2IN and Provider2OUT

Apologies if I am confusing here, I am new to this and trying to find my way out.

Re: Calculate time difference between multivalue fields

richgalloway — Thu, 16 Jul 2020 13:49:44 GMT

Where did the mandate to use list or values come from?
Can you share the full search in case there's another way to reach the same goal?