Splunk Search

Calculate time difference between multivalue fields

ayush1234
New Member

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.

Labels (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

ayush1234
New Member
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.
0 Karma

richgalloway
SplunkTrust
SplunkTrust
Don't use stats list. Then you won't have multi-value fields. That's the crux of my reply.
---
If this reply helps you, Karma would be appreciated.
0 Karma

ayush1234
New Member

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust
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?
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

New Splunk Observability innovations: Deeper visibility and smarter alerting to ...

You asked, we delivered. Splunk Observability Cloud has several new innovations giving you deeper visibility ...

Synthetic Monitoring: Not your Grandma’s Polyester! Tech Talk: DevOps Edition

Register today and join TekStream on Tuesday, February 28 at 11am PT/2pm ET for a demonstration of Splunk ...

Instrumenting Java Websocket Messaging

Instrumenting Java Websocket MessagingThis article is a code-based discussion of passing OpenTelemetry trace ...