SOLVED
Home

Calculating rate of change in Log Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-851080%22%20slang%3D%22en-US%22%3ECalculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-851080%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20have%20a%20counter%20that%20increases%20over%20time%20and%20I%20want%20to%20display%20how%20much%20that%20counter%20is%20changing%20every%20minute%2C%20how%20would%20I%20do%20that.%26nbsp%3B%20In%20PromQL%20I%20would%20use%20the%20rate%20function%20but%20is%20there%20a%20simple%20equivalent%20KQL%3F%3C%2FP%3E%3CP%3EFor%20example%2C%2014%3A10%3A00%20the%20total%20value%20since%20we%20collected%20data%20was%20182077%2C%20at%2014%3A11%20it%20was%20182083%20and%20at%2014%3A12%20it%20was%20182084.%26nbsp%3B%20I%20would%20like%20to%20render%20a%20graph%20showing%200%20at%2014%3A10%2C%206%20at%2014%3A11%20and%201%20at%2014%3A12.%26nbsp%3B%3C%2FP%3E%3CP%3ESounds%20simple%20but%20I%20can't%20see%20a%20way%20to%20do%20it.%26nbsp%3B%20Any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EPete%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-851080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852272%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852272%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F115299%22%20target%3D%22_blank%22%3E%40Peter%20Hall%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHave%20you%20looked%20at%20bin%3F%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fbinfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fbinfunction%3C%2FA%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1h)%0A%7C%20summarize%20count(EventID)%20by%20bin(TimeGenerated%2C%201m)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20shows%20the%20count%20of%20%3CSTRONG%3EEventIDs%3C%2FSTRONG%3Ein%20the%20%3CSTRONG%3EEvents%3C%2FSTRONG%3Etable%20every%20min%20in%20the%20past%20hour%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA3MtS80r4eWqUSjPSC1KVQjJzE11T81LLUosSU1RsFNITM%252FXMMzQBCkoLs3NTSzKrEpVSM4vzSvRcAXp9HTRVEiqVEjKzNNA0aqjYJgL1lWUmpeSWqSQlFiUnJFYVKLABQB3z1SfcQAAAA%253D%253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131506iEF83D2D631A6ED75%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdding%26nbsp%3Bas%20this%20as%20the%20last%20line%20will%20give%20you%20the%20graph%2C%20rather%20than%20a%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSTRONG%3E%7C%20render%3C%2FSTRONG%3E%3CSPAN%3E%3CSTRONG%3Ebarchart%3C%2FSTRONG%3E%3C%2FSPAN%3E%20%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852448%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852448%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20reply.%26nbsp%3B%20I%20have%20looked%20at%20that.%26nbsp%3B%20It's%20not%20the%20number%20of%20new%20entries%20per%20minute%20I%20am%20trying%20to%20ascertain%2C%20but%20the%20change%20in%20the%20sum%20of%20all%20previous%20entries%20per%20minute%2C%20if%20that%20makes%20sense.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F131509i429E0177856B3C97%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eie%20in%20the%20above%20query%2C%20you'll%20see%20system%20mode%20cpu%20usage%20for%20computer%20aks-agentpool-31816283-2%20goes%20from%20264552.21%20to%20264560.83%20in%20the%20minute%2C%20so%20i%20want%20the%20difference%20between%20those%202%20on%20an%20on-going%20basis.%26nbsp%3B%20In%20fact%2C%20I%20actually%20want%20it%20for%20all%20modes%20but%20one%20step%20at%20a%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852502%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F115299%22%20target%3D%22_blank%22%3E%40Peter%20Hall%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3F%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EEvent%0A%7C%20where%20TimeGenerated%20%26gt%3B%20ago(1h)%0A%7C%20summarize%20count()%20by%20bin(TimeGenerated%2C%201m)%0A%7C%20sort%20by%20TimeGenerated%20asc%20%0A%7C%20extend%20accumulated%20%3Drow_cumsum(count_)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA1WNMQ7CMBAEeyT%252BcKUt0eQBoUN8gD5ynBWxxJ2ly5kA4vHYoUo52tndyxNix8OX1hkKuiXGFQINhonOFO7ZdbNvwlKYg6YPKOYi5jyNbxqTuF3nRB3%252F9azWjP1iWCK1FC%252BDVIyxcHlsUa95HSrWH7c9DP4HbfzUZZ0AAAA%253D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3ETimeGenerated%3C%2FTH%3E%0A%3CTH%3Ecount_%3C%2FTH%3E%0A%3CTH%3Eaccumulated%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A46%3A00Z%3C%2FTD%3E%0A%3CTD%3E343%3C%2FTD%3E%0A%3CTD%3E343%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A47%3A00Z%3C%2FTD%3E%0A%3CTD%3E57%3C%2FTD%3E%0A%3CTD%3E400%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A48%3A00Z%3C%2FTD%3E%0A%3CTD%3E49%3C%2FTD%3E%0A%3CTD%3E449%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A49%3A00Z%3C%2FTD%3E%0A%3CTD%3E488%3C%2FTD%3E%0A%3CTD%3E937%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A50%3A00Z%3C%2FTD%3E%0A%3CTD%3E321%3C%2FTD%3E%0A%3CTD%3E1258%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A51%3A00Z%3C%2FTD%3E%0A%3CTD%3E354%3C%2FTD%3E%0A%3CTD%3E1612%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A52%3A00Z%3C%2FTD%3E%0A%3CTD%3E378%3C%2FTD%3E%0A%3CTD%3E1990%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A53%3A00Z%3C%2FTD%3E%0A%3CTD%3E482%3C%2FTD%3E%0A%3CTD%3E2472%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A54%3A00Z%3C%2FTD%3E%0A%3CTD%3E344%3C%2FTD%3E%0A%3CTD%3E2816%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-12T14%3A55%3A00Z%3C%2FTD%3E%0A%3CTD%3E501%3C%2FTD%3E%0A%3CTD%3E3317%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853283%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3Byou%20are%20a%20scholar%20and%20a%20gent.%26nbsp%3B%20That%20would%20appear%20to%20do%20the%20trick.%26nbsp%3B%20I'll%20adapt%20as%20necessary%20but%20thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-856236%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-856236%22%20slang%3D%22en-US%22%3EYou%20can%20also%20use%20the%20next%20or%20prev%20functions%20to%20get%20the%20rate%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fprevfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fprevfunction%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857263%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20rate%20of%20change%20in%20Log%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857263%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F54749%22%20target%3D%22_blank%22%3E%40Ketan%20Ghelani%3C%2FA%3EThanks%20very%20much%20for%20the%20reply.%26nbsp%3B%20I'll%20take%20a%20look%20at%20that%20as%20well%3C%2FP%3E%3C%2FLINGO-BODY%3E
Peter Hall
New 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

6 Replies
Highlighted

@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

 

 

 

@Clive Watson 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

 

Solution

@Clive Watson 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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies