Example for monotonic counters?

[rcarmo]

I'm trying to plot "Network Total Bytes" (which is a monotonic counter), and need a way to compute the deltas inside time bins and extrapolate total traffic per bin (or per second).

Any simple way to do that?

[noakup]

Hi,
not sure what exactly you mean, perhaps that will help:
WireData | summarize avg(TotalBytes) by bin(TimeGenerated, 1h) | render timechart

You're welcome to raise more questions on our community space where similar Qs have been raised and answered.

Thanks

[rcarmo]

Nope, doesn't help. What I'm saying is that I want to chart things that increase continuously (like packet counters) on a per-interval basis (i.e., plot only differences between successive values over time, and not the absolute value).

[rs38]

@rcarmo isn't there always a counter based a certain timespan like total xy per second?!

[rcarmo]

@rs38 No, per-interval counters aren't always available. And when dealing with networking, it is often critical to be able to be able to bin the data for cross-referencing with other event types, and avoid averages (which is why I opened this ticket in the first place).

To this date, I have not yet been able to do this solely inside Log Analytics - I've had to export the data and post-process it.

[rs38]

@rcarmo this should work for you. I'm curious if there are some more elegant ways….?!

let t1 = Perf 
| where CounterName == "Requests Failed Total"
| project Tg1 = TimeGenerated , C1 = CounterValue 
| sort by Tg1 desc 
| extend rn = row_number(); 
let t2 = Perf
| where CounterName == "Requests Failed Total"
| project Tg2 = TimeGenerated , C2 = CounterValue 
| sort by Tg2 desc 
| extend rn = row_number() + 1;
t1
| join (t2) on rn
| project  Tg1, C2-C1

nesting the let function is also supported:

let t1 = Perf 
| where Computer == "weazu-srv1100" 
| where CounterName == "Requests Failed Total"
| project Tg = TimeGenerated , C = CounterValue 
| sort by Tg desc 
| extend rn = row_number(); 
let t2 = t1
| extend rn = row_number() + 1;
t2
| join (t1) on rn
| project  Tg,  C - C1

[jruales]

@rcarmo if the time bins are regular, you can try a make-series to turn the data points into an array. Then, you can apply a finite impulse response filter with series-fir to compute the differences between immediate rows. For this, your finite impulse response filter should be something like dynamic([-1, 1]) or dynamic([1, -1]). Finally, if you would like to turn the resulting array into a table, you can use mvexpand. If you do end up using mvexpand, make sure that in the in the mvexpand statement you set the "limit" clause to a high number, since by default it truncates at 128 rows.

That was one option. Another option is to try an approach similar to what @rs38 recommended, or you can experiment with the prev or next functions.

[rs38]

@jruales Wow! I guess only knowing about the exitence of the function prev() did the trick!

Perf
| where CounterName == "Requests Failed Total"
| project Tg1 = TimeGenerated , C1 = CounterValue
| sort by Tg1 desc
| extend diff = prev(C1,1) - C1
| project Tg1, diff

it would be very helpful to have something that helps finding these by translating SQL command. in this case LAG()....