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

@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

Highlighted
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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies