How do you compare 2 multivalued fields from different indices?

[maxzintel]

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!

[DalJeanis · Accepted solution]

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

[maxzintel]

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

[martin_mueller]

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.

[maxzintel]

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 !

[martin_mueller]

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.

[maxzintel]

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?

[martin_mueller]

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.

[maxzintel]

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.

[martin_mueller]

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

[maxzintel]

@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.

[maxzintel]

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