Using the following search, I'm able to obtain the most recent packet data
in my network.
index=generic router=ABC interface=FastEthernet packet_type="123" OR packet_type="456"
| transaction packet_type maxspan=15m
| stats latest(bytes) by packet_type,router,interface,_time
From that search I get the following output:
_time packet_type router interface latest(bytes)
1 11/15/13 2:58:37.000 PM 123 ABC FastEthernet 111887066
2 11/15/13 2:56:12.000 PM 456 ABC FastEthernet 2095092422
What I want to do is calculate the percentage from packet_type 123 and 456. Specifically
something like: | eval packet_loss(packet_type=456/packet_type=123)
How do I get the latest bytes from each packet_type into | eval to be calculated?
You can try following (assuming your only get two rows from your query)
index=generic router=ABC interface=FastEthernet packet_type="123" OR packet_type="456"
| transaction packet_type maxspan=15m
| stats latest(bytes) as bytes by packet_type,router,interface,_time | delta bytes as packet_loss p=1 | where isnotnull(packet_loss)
If you're getting one separate result row for each packet_type from following :-
index=generic router=ABC interface=FastEthernet packet_type="123" OR packet_type="456"
| transaction packet_type maxspan=15m
| stats latest(bytes) as bytes by packet_type,router,interface,_time
You can try adding following to your search:
|stats max(bytes) as byte1, min(bytes) as byte2 by router,interface | eval packet_loss=byte1-byte2 | fields router, interface,packet_loss
You can try following (assuming your only get two rows from your query)
index=generic router=ABC interface=FastEthernet packet_type="123" OR packet_type="456"
| transaction packet_type maxspan=15m
| stats latest(bytes) as bytes by packet_type,router,interface,_time | delta bytes as packet_loss p=1 | where isnotnull(packet_loss)
If you're getting one separate result row for each packet_type from following :-
index=generic router=ABC interface=FastEthernet packet_type="123" OR packet_type="456"
| transaction packet_type maxspan=15m
| stats latest(bytes) as bytes by packet_type,router,interface,_time
You can try adding following to your search:
|stats max(bytes) as byte1, min(bytes) as byte2 by router,interface | eval packet_loss=byte1-byte2 | fields router, interface,packet_loss
That didn't seem to do it. This string just populated the "latest" bytes from a single packet_type:
_time packet_type router interface bytes packet_loss
1 11/15/13 4:26:11.000 PM 456 ABC FastEthernet 1893916799 1893916799
Here is the raw data:
1
11/15/13
4:26:11.000 PM
ABC|FastEthernet|456|2013-11-15 16:26:11|1934589678
ABC|FastEthernet|456|2013-11-15 16:31:11|1893916799
2
11/15/13
4:23:37.000 PM
ABC|FastEthernet|123|2013-11-15 16:23:37|1870674
ABC|FastEthernet|123|2013-11-15 16:33:37|1761563