I've got ifconfing reporting dropped packets every 10 minutes. Because that value never rolls over until the NIC goes down, I need to find the delta from one run to the other to get the current packet loss in said time frame. Easy enough:
index=servers sourcetype=ifconfig host=box1 | reverse | delta rx.dropped as current_dropped p=1 | table host current_dropped _time
box1 4643 2015-11-24 18:05:24
box1 4655 2015-11-24 17:55:09
The issue comes in when I do not want to specify a specific host and instead get the current_dropped from all hosts at once. Say, for a timechart. Each event compares itself to other random events from other hosts. This makes sense, but produces completely incorrect results for what I'm looking for:
index=servers sourcetype=ifconfig **host=***| reverse | delta rx.dropped as current_dropped p=1 | table host current_dropped _time
box1 11232891786 2015-11-24 17:55:09
box2 -11192819749 2015-11-24 17:55:09
Again, I'll get an ifconfig log on box1 comparing itself to the nearest ifconfig log from box2. Is there any way to restrict the comparison of events to each other only if they share the same host field? I do not want to make a search for each and every box if possible!
Through trial and error we found out how to find the deltas for the events scoped by host fields that match. The following works:
index=servers sourcetype=ifconfig host=*
| streamstats current=f last(rx.dropped) as last_dropped by host
| rename rx.dropped as current_dropped
| eval delta = last_dropped - current_dropped
| table host _time current_dropped last_dropped delta
| sort -_time
host _time current_dropped last_dropped delta
box1 2015-11-24 17:55:09 11235995641 11236000284 4643
box1 2015-11-24 17:50:09 11235990986 11235995641 4655
box1 2015-11-24 17:45:10 11235986362 11235990986 4624
box1 2015-11-24 17:40:09 11235981711 11235986362 4651
box1 2015-11-24 17:35:09 11235977068 11235981711 4643
box1 2015-11-24 17:30:09 11235972435 11235977068 4633
box2 2015-11-24 21:25:19 3108010
box2 2015-11-24 21:20:39 3107791 3108010 219
box2 2015-11-24 21:05:23 3107584 3107791 207
box2 2015-11-24 20:55:09 3107366 3107584 218
box2 2015-11-24 20:45:08 3107151 3107366 215
box2 2015-11-24 20:35:08 3106938 3107151 213
Things that you must follow for this to work (again, found through trial and error)
Finally, just do the subtraction to get the delta like normal. Why does it have to be done this way? No idea. Doesn't make sense to us. At this point though, you can graph away for packet loss by host.
Through trial and error we found out how to find the deltas for the events scoped by host fields that match. The following works:
index=servers sourcetype=ifconfig host=*
| streamstats current=f last(rx.dropped) as last_dropped by host
| rename rx.dropped as current_dropped
| eval delta = last_dropped - current_dropped
| table host _time current_dropped last_dropped delta
| sort -_time
host _time current_dropped last_dropped delta
box1 2015-11-24 17:55:09 11235995641 11236000284 4643
box1 2015-11-24 17:50:09 11235990986 11235995641 4655
box1 2015-11-24 17:45:10 11235986362 11235990986 4624
box1 2015-11-24 17:40:09 11235981711 11235986362 4651
box1 2015-11-24 17:35:09 11235977068 11235981711 4643
box1 2015-11-24 17:30:09 11235972435 11235977068 4633
box2 2015-11-24 21:25:19 3108010
box2 2015-11-24 21:20:39 3107791 3108010 219
box2 2015-11-24 21:05:23 3107584 3107791 207
box2 2015-11-24 20:55:09 3107366 3107584 218
box2 2015-11-24 20:45:08 3107151 3107366 215
box2 2015-11-24 20:35:08 3106938 3107151 213
Things that you must follow for this to work (again, found through trial and error)
Finally, just do the subtraction to get the delta like normal. Why does it have to be done this way? No idea. Doesn't make sense to us. At this point though, you can graph away for packet loss by host.
Have you looked at streamstats
command? http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/streamstats
Something like this may work (untested code)
.. | streamstats window=2 current=t global=f earliest(rx.dropped) as curr, latest(rx.dropped) as next by host | eval vaiance=next-curr
Update:
Tried out streamstats to get more granularity but it is still not working.
With just one host the intended data is returned:
index=servers sourcetype=ifconfig host=box1
| streamstats earliest(rx.dropped) as rx_new latest(.rx.dropped) as rx_old window=2 current=true by host
| eval difference = rx_old - rx_new
| table host rx.dropped rx_new rx_old difference _time
| sort -_time
Pay attention to "difference"
host rx.dropped rx_new rx_old difference _time
box1 11236139559 11236139559 11236139559 0 2015-11-24 20:35:08
box1 11236130264 11236130264 11236139559 9295 2015-11-24 20:25:20
box1 11236120957 11236120957 11236130264 9307 2015-11-24 20:20:35
box1 11236111680 11236111680 11236120957 9277 2015-11-24 20:05:25
With all hosts, it breaks down:
index=servers sourcetype=ifconfig box=*
| streamstats earliest(rx.dropped) as rx_new latest(.rx.dropped) as rx_old window=2 current=true by host
| eval difference = rx_old - rx_new
| table host rx.dropped rx_new rx_old difference _time
| sort -_time
Notice "difference" this time:
host rx.dropped rx_new rx_old difference _time
box1 11236148834 11236148834 11236148834 0 2015-11-24 20:45:08
box1 11236139559 11236139559 11236139559 0 2015-11-24 20:35:08
box1 11236130264 11236130264 11236130264 0 2015-11-24 20:25:20
box1 11236120957 11236120957 11236120957 0 2015-11-24 20:20:35
It seems as though earliest and latest isn't support, and first and last return the same result for some reason. Not sure how to get the last rx.dropped.
index=servers sourcetype=ifconfig host=*
| streamstats first(rx.dropped) as rx_new last(rx.dropped) as rx_old window=2 current=true by host
| table sensor rx.dropped rx_new rx_old
| sort -_time
sensor rx.dropped rx_new rx_old _time
box1 86 86 86 2015-11-24 20:05:26
box2 5066 5066 5066 2015-11-24 20:05:25
I googled and saw something similar (I think). I'll test it out!