How to combine two values from separate searches to get an average?

[jmonroe516]

I have 2 searches

index=test field1=abc field2=xyc | stats dc(field3) as Devices

and

index=test field1=abc field2=xyc (field6=1111 OR field6=2222) | stats dc(field4) as Connections

I'm trying to take the number from the first search and the number from the second search, which is more filtered, and get an average, specifically an average of the specific connections per device. The device may not have the specific connections, but I need an average even on devices that may not have that specific connection.

I have tried using something like return, but when I combine it, it doesn't seem to like to return anything or use it in an eval like if I do | eval Average=Connections/Devices

Is there any way to combine these 2 into one search and get the values I need?

[stephanefotso · Accepted solution]

with the join command, test this: index=test field1=abc field2=xyc | stats dc(field3) as Devices|join [search index=test field1=abc field2=xyc (field6=1111 OR field6=2222)|stats dc(field4) as Connections] | eval Average=Connections/Devices

here is an example in the _internal index, that you can take as a template.

index=_internal source=*metrics.log group=search_concurrency|stats dc(active_hist_searches) as ActiveHitSearch|join  [search index=_internal source=*metrics.log group=search_concurrency (component=Metrics OR component=view)|stats dc(active_realtime_searches) as ActiveRealtimeSearch] |eval average=ActiveHitSearch/ActiveRealtimeSearch

[jmonroe516]

That worked perfectly, thank you for the help!