SOLVED

Calculating rate of change in Log Analytics

Brass Contributor

If I have a counter that increases over time and I want to display how much that counter is changing every minute, how would I do that.  In PromQL I would use the rate function but is there a simple equivalent KQL?

For example, 14:10:00 the total value since we collected data was 182077, at 14:11 it was 182083 and at 14:12 it was 182084.  I would like to render a graph showing 0 at 14:10, 6 at 14:11 and 1 at 14:12. 

Sounds simple but I can't see a way to do it.  Any help would be appreciated.

 

Regards

Pete

8 Replies

@Peter Hall 

 

Have you looked at bin? https://docs.microsoft.com/en-us/azure/kusto/query/binfunction

Event
| where TimeGenerated > ago(1h)
| summarize count(EventID) by bin(TimeGenerated, 1m)

 

This shows the count of EventIDs in the Events table every min in the past hour?

 

Go to Log Analytics and Run Query

clipboard_image_0.png

 

Adding as this as the last line will give you the graph, rather than a table.

 

| render barchart

 

 

 

@CliveWatson Thanks for the reply.  I have looked at that.  It's not the number of new entries per minute I am trying to ascertain, but the change in the sum of all previous entries per minute, if that makes sense.  

clipboard_image_0.png

ie in the above query, you'll see system mode cpu usage for computer aks-agentpool-31816283-2 goes from 264552.21 to 264560.83 in the minute, so i want the difference between those 2 on an on-going basis.  In fact, I actually want it for all modes but one step at a time.

@Peter Hall 

 

How about?

Event
| where TimeGenerated > ago(1h)
| summarize count() by bin(TimeGenerated, 1m)
| sort by TimeGenerated asc 
| extend accumulated =row_cumsum(count_)

 

Go to Log Analytics and Run Query

 

 

TimeGenerated count_ accumulated
2019-09-12T14:46:00Z 343 343
2019-09-12T14:47:00Z 57 400
2019-09-12T14:48:00Z 49 449
2019-09-12T14:49:00Z 488 937
2019-09-12T14:50:00Z 321 1258
2019-09-12T14:51:00Z 354 1612
2019-09-12T14:52:00Z 378 1990
2019-09-12T14:53:00Z 482 2472
2019-09-12T14:54:00Z 344 2816
2019-09-12T14:55:00Z 501 3317

 

best response confirmed by Peter Hall (Brass Contributor)
Solution

@CliveWatson you are a scholar and a gent.  That would appear to do the trick.  I'll adapt as necessary but thank you

You can also use the next or prev functions to get the rate
https://docs.microsoft.com/en-us/azure/kusto/query/prevfunction

@Ketan GhelaniThanks very much for the reply.  I'll take a look at that as well

@Peter Hall Did you find out prometheus "rate" equivalent function in kusto

@santhoshparepu For anyone still looking for a Prometheus equivalent to rate of change the link below defines a Kusto User Defined Function to calculate rate of change from a Prometheus style counter which can only go up, unless reset. series_rate_fl() - Azure Data Explorer | Microsoft Docs

1 best response

Accepted Solutions
best response confirmed by Peter Hall (Brass Contributor)
Solution

@CliveWatson you are a scholar and a gent.  That would appear to do the trick.  I'll adapt as necessary but thank you

View solution in original post