Comparing data sets from two types of host

[drwilliams]

I'm using the webping app from SplunkBase to calculate latency from each of my forwarders to a central server. I'm collecting latency data from multiple devices on each location's wireless LAN as well as from a single device on the physical LAN closer to the WAN interface. Currently, I can use two different search strings to show me average latency (time_in_ms) by type of device grouped by location, which allows me to visualize LAN performance separate from WAN performance.

The next step, which is where I'm struggling, is that I'd like to come up with single search string that would compare the avg(time_in_ms) of TypeA against the avg(time_in_ms) of TypeB for each BusinessUnit (extracted field) and show me any resulting locations where the difference between LAN and WAN latency is greater than 100ms.

Here are my search strings:

Returns average latency grouped by a field extraction that denotes location:

 sourcetype=webping time_in_ms < 2000 |stats avg(time_in_ms) by BusinessUnit | sort -avg(time_in_ms)

Returns average latency grouped by host TypeA

 sourcetype=webping time_in_ms < 2000 NOT host="ssa*" |stats avg(time_in_ms) by host | sort -avg(time_in_ms)

Returns average latency groupd by host TypeB

 sourcetype=webping time_in_ms < 2000 host="ssa*" |stats avg(time_in_ms) by host | sort -avg(time_in_ms)

Any suggestions would be greatly appreciated.

[woodcock]

This should do it:

sourcetype=webping time_in_ms < 2000 | eva type=if(like(host,"ssa%"), "TypeB", "TypeA") |stats avg(time_in_ms) AS  Avg_ms BY BusinessUnit,type | xyseries BusinessUnit type Avg_ms | eval diff=abs(TypeB-TypeA) | where diff>100