How to determine the delta between events based on a shared field value?

[thisissplunk]

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!

[thisissplunk · Accepted solution]

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)

1. You have to use streamstats so you can sort by host
2. You cannot use anything but window=0 (blank works too) in the streamstats command
3. You must do a rename to hold onto that event's current rx.dropped.
4. The rename must come after the streamstats command
5. You cannot use first() or earliest() in the streamstats command to get that value instead

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.

[sundareshr]

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

[thisissplunk]

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

[thisissplunk]

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

[thisissplunk]

I googled and saw something similar (I think). I'll test it out!