How to create a query to track received and sent bytes transferred daily through a VPN tunnel?

[Mantic]

I am trying to write a query to calculate the amount of bytes  received and sent per day from one of our firewalls at our site to a firewall at another site. This is to create a series of daily metrics for management. I've come up with a query that succeeds most of the time.

Current query:

index=syslogindex device=firewall vpn=site1-to-site2 | bin span=1d _time | stats range(rcvdbyte) as rcvdbyte range(sentbyte) as sentbyte by _time

However, this query fails on days when the vpn tunnel is reset.  The rcvdbyte and sentbyte fields that come from the firewall are summed values from the moment the VPN tunnel is started. When the tunnel is reset, it creates a new tunnelid and resets the rcvdbyte, sentbyte, and duration counts to zero. And the current query I am using calculates a massive spike for those days since the range of the rcvdbyte field is now zero minus whatever the previous summed amount of the rcvdbyte field was.

There are a few ways I can think of changing the query to account for when the tunnel is reset. One of my ideas is to track tunnelid over time while still calculating daily rcvdbyte and sentbyte ranges. Another is to somehow track when rcvdbyte or sentbyte or even duration get reset to zero and do a different calculation for that day. Another solution is to just disregard the days when it is reset. However, I haven't been able to implement any of the solutions I have thought of.

Does anyone have any different ideas or know how I can implement one of my ideas?

An example event:

date=2021-06-01 time=23:50:43 device=firewall serialid=1234567891 loggingid=123456789 type=event subtype=vpn loggingdesc="tunnel statistics" loggingmsg="tunnel statistics" action=tunnel-stats remoteip=192.168.1.2 localip=192.168.2.2 remoteport=60000 localport=60000 vpn="site1-to-site2" tunnelid=1234567891 tunneltype="vpn" duration=10170 sentbyte=120 rcvdbyte=360

[johnhuang]

Try this:

index=syslogindex device=firewall vpn=site1-to-site2 
| bucket _time span=1d
| stats max(rcvdbyte) AS rcvdbyte max(sentbyte) AS sentbyte BY _time tunnelid
| stats sum(rcvdbyte) AS rcvdbyte sum(sentbyte) AS sentbyte BY _time

[Mantic]

I tried it and the results aren't quite what I'm expecting, but it may be on the right track.

This query gives rcvdbyte and sentbyte as zero every day except when a tunnel is reset. On the days a tunnel is reset, it gives the range of the max rcvdbyte of the new tunnel minus the max rcvdbyte of the old tunnel. For example:

Date	Tunnel ID	max rcvdbyte
11-30	200000001	39555510895
12-01	200000001	41880638036
12-01	200000002	10506504914
12-02	200000002	16556551565

Then this query gives:

Date	rcvdbyte
11-30	0
12-01	31374133122
12-02	0

[johnhuang]

I'm not sure how the query would have given you those results. Could you run this and let me know the output?

index=syslogindex device=firewall vpn=site1-to-site2 
| bucket _time span=1d
| stats max(rcvdbyte) AS rcvdbyte max(sentbyte) AS sentbyte BY _time tunnelid

[Mantic]

Oh, I see what I did. Copy and paste error on my part. This is the query I ran:

index=syslogindex device=firewall vpn=site1-to-site2 
| bucket _time span=1d
| stats max(rcvdbyte) AS rcvdbyte max(sentbyte) AS sentbyte BY _time tunnelid
| stats range(rcvdbyte) as rcvdbyte range(sentbyte) as sentbyte by _time

Instead of:

| stats sum(rcvdbyte) AS rcvdbyte sum(sentbyte) AS sentbyte BY _time

I ran the corrected query. It sums the max values between tunnels correctly, and I get the data as seen below:

date	rcvdbyte	sentbyte
2021-11-30	41878632024	97284896
2021-12-01	52387142950	112851721
2021-12-02	10506764478	15057101

But that doesn't quite capture data sent per day. It does sum rcvdbytes and sentbytes between tunnelids at least.

[johnhuang · Accepted solution]

Ok try this:

index=syslogindex device=firewall vpn=site1-to-site2 
| bucket _time span=1d
| stats range(rcvdbyte) AS rcvdbyte range(sentbyte) AS sentbyte BY _time tunnelid
| stats sum(rcvdbyte) AS rcvdbyte sum(sentbyte) AS sentbyte BY _time

[Mantic]

That's exactly what I'm looking for. Thank you for your help.