KQL (azure monitor) I wanted a chart that dynamically tracks the 5 logs with the highest consumption and track that consumption by day

Question

We have had issue tracking and alerting if our logging starts to consume too much resource and in realty incurred to much cost

Now Ive got it working but I am sure there is a better solution

let TWmonthEnd = endofmonth(datetime(now),-2); 
let twoweek =ago(14d);

let histbase=Usage
| where TimeGenerated > TWmonthEnd 
| project TimeGenerated, DataType, Solution, Quantity, IsBillable
| where IsBillable =='True'
| summarize TWmonthEnd = sumif(Quantity, TimeGenerated >TWmonthEnd)
            by DataType;

let hisresults=histbase
|top 5 by TWmonthEnd
|project DataType;

let currentresult = Usage
| where TimeGenerated > twoweek 
| project TimeGenerated, DataType, Solution, Quantity, IsBillable
| where IsBillable =='True'
| summarize weekly = sum(Quantity) by bin(TimeGenerated,1d), DataType;

currentresult
| join kind = inner hisresults on DataType
|where TimeGenerated > twoweek 
| project TimeGenerated, DataType, weekly
| sort by TimeGenerated

PS Yes I know I could have just hardcoded the logs (as there are not that many) but I belive code should be dynamic and not need tinkering

Answer 1

You seem like you are very nearly there and looks sensible enough to me. One thing I would probably suggest though, is instead of looking at the top hitters over a long period I'd probably look at top hitters over more granular periods.

For instance show me everything which generates most cost per day over x days and report on all of those over the month. That way you should hopefully spot any outliers easier.

let TWmonthEnd = endofmonth(datetime(now),-2); 
let twoweek =ago(14d);
let AnyTop5Usage = Usage //Grab all top 5 hits per day and offer these for monitoring
| where TimeGenerated > (TWmonthEnd)
| where _IsBillable == true
| summarize sum(_BilledSize) by DataType, floor(TimeGenerated, 1d)
| partition by TimeGenerated (top 5 by sum__BilledSize)
| summarize make_set(DataType);
Usage //Show last two weeks of usage
| where TimeGenerated >= floor((twoweek), 1d)
| where _IsBillable == true
| where DataType in (AnyTop5Usage)
| summarize daily = sum(Quantity) by floor(TimeGenerated, 1d), DataType
| sort by TimeGenerated asc
//| render timechart 

TimeGenerated	daily	DataType
2023-09-26T00:00:00.0000000Z	0.004116	ProtectionStatus
2023-09-26T00:00:00.0000000Z	0.20706	Heartbeat
2023-09-26T00:00:00.0000000Z	0.0044800000000000009	Operation
2023-09-26T00:00:00.0000000Z	0.29616800000000007	AzureMetrics
2023-09-26T00:00:00.0000000Z	0.17425900000000003	StorageBlobLogs
2023-09-26T00:00:00.0000000Z	0.568918	webhost_test_CL
2023-09-27T00:00:00.0000000Z	1.117705	AzureMetrics
2023-09-27T00:00:00.0000000Z	0.842693	StorageBlobLogs
2023-09-27T00:00:00.0000000Z	0.014112	ProtectionStatus
2023-09-27T00:00:00.0000000Z	0.70992	Heartbeat

*All data, I don't generate enough Billed Data in my own Log Analytics to show anything meaningful as demo output.