topic Re: How do you compare 2 multivalued fields from different indices? in Splunk Search

How do you compare 2 multivalued fields from different indices?

maxzintel — Fri, 09 Nov 2018 21:42:04 GMT

I am attempting to correlate network latency fields from different indices. Basically, I would like to end up with a table with 2 columns, with each column being populated with every recorded value for network latency in each of the indices.

I have tried using subsearches like so:

index=indexA | eval "latencyFromIndexB"=[search index=indexB | return <count> $latencyB]+0 | fields "latencyFromIndexA" "latencyFromIndexB"

But, I have been unable to get multiple values to return from the subsearch in a useful way. Depending on the exact method, latencyFromIndexB either lists a single value or the same value over and over, presumably from the first event it finds.

End result will hopefully look something like this:

latencyA | latencyB
12312    |  545
324      |  2123
etc...

Thank you in advance if you are able to assist me!

Re: How do you compare 2 multivalued fields from different indices?

martin_mueller — Sat, 10 Nov 2018 06:10:36 GMT

How is one event in index A related to an event in index B? Is there a request ID?

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 13 Nov 2018 14:01:08 GMT

@martin_mueller Sorry for the delayed reply! Index A logs front-end events, Index B logs events from our API's. The latency reported by each should be different but directly related, with their difference being latency caused by the request traversing the internet.

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 13 Nov 2018 14:03:04 GMT

In other words, each front-end event that reports network latency should have a correlating event in the API index.

Re: How do you compare 2 multivalued fields from different indices?

martin_mueller — Tue, 13 Nov 2018 14:40:02 GMT

How does one correlate an event from A with an event from B on a conceptual level? Ignore Splunk/SPL for now.

Sample events would really help here.

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 29 Sep 2020 22:02:56 GMT

So, let's say the application front-end reports latency of 500ms for a given event. An API (with events being logged in B) takes that front-end request and processes it, Splunk logs this as an event and reports the API response_time=50ms. I believe this means that the request spent 450ms (the difference between the 2 values) traveling through proxies and gateways and whatnot. I am trying to run a Linear Regression as a proof of concept against these two values to show they are directly related.

Sample events (only showing relevant fields):

Index A - Front End
11/13/18
11:01:16.000 AM
{

latency: 1189

}

Index B - API
11/13/18
11:00:56.735 AM
{

method: GET

response_length: 9

response_status: 200

response_time: 0

}

So here, these events were produced via the same request. From this, I can tell that the API is not responsible for any of the latency experienced on the front-end (via response_time: 0). Thus, the latency is likely due to the request routing.

Re: How do you compare 2 multivalued fields from different indices?

martin_mueller — Tue, 13 Nov 2018 17:15:56 GMT

How can you tell they were generated by the same request? I see no request ID or something like that to link the two together.

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 13 Nov 2018 19:54:46 GMT

I currently do not have a request ID field that linking the two events together. My thinking was that if I am exclusively plotting the values of each over time, it would show their relationship just as well as creating some sort of link. That being said, it is possible I do not fully understand the value in adding in a request ID field.

How would a request ID field assist in getting the output I am looking for?

Re: How do you compare 2 multivalued fields from different indices?

martin_mueller — Tue, 13 Nov 2018 20:07:58 GMT

Without an ID you can chart general trends, e.g. this:

index=A OR index=B | timechart avg(latency) avg(response_time)

With an ID you could link which slow requests over here are related to which requests over there, potentially providing lots of value for troubleshooting.

Re: How do you compare 2 multivalued fields from different indices?

DalJeanis — Tue, 13 Nov 2018 20:17:06 GMT

The standard way to do this using the "Splunk Soup" method, if you had a matching request ID or similar, is this ...

 (  index=A  your first search ) OR ( index=B your second search) 
| fields ...list the fields you need to keep from either ...
| eval matchkey=case(index="A", key value from index A event, index="B", key value from index b event)
| eval latencyFromIndexA = case(index=A, latency) 
| eval latencyFromIndexB = case(index=B, response_time)
| fields matchkey latency* 
| stats values(latency*) as latency* by matchkey

The standard way to do this using time constraints, if you had NO request ID or similar, is this ...
(This assumes that, like in your example, the record in A will always be timestamped after the record in B, and will always arrive in less than 1s)

 (  index=A  your first search ) OR ( index=B your second search) 
| fields _time index response_time latency ...list any other fields you need to keep from either ...

| rename COMMENT as "sort into ascending order"
| sort 0 _time 

| rename COMMENT as "roll index b data onto the next index A record then throw away index B"
| streamstats time_window=1s last(case(index="B",response_time)) as lastResp  last(case(index="B",_time)) as prior_time
| where index="A"

| rename COMMENT as "now your record looks like this..."
| table _time prior_time latency lastResp

If you don't care about anything but the list of latencies of the two types, then do this

(search 1) OR (search 2) 
| fields _time latency response_time
| bin _time span=1m
| stats list(latency) as latency_A list(response_time) as response_timeB by _time

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 13 Nov 2018 20:44:56 GMT

Spot on. I will add in an ID to make the search more effective. Thank you very much for your time and assistance @martin_mueller !

Re: How do you compare 2 multivalued fields from different indices?

maxzintel — Tue, 13 Nov 2018 20:47:22 GMT

Thank you very much! This fits well with what Martin was explaining, and the detail, explanation, and example code is much appreciated!