Augmenting Azure Advisor Cost Recommendations for Automated Continuous Optimization – Part 3

AnkitGarg, the query can be found in the respective recommendations runbook. However, as it is built dynamically it is maybe easier for you to try out this hard-coded example below:

let advisorInterval = 7d;
let perfInterval = 7d;
let perfTimeGrain = 1h;
let cpuPercentileValue = 99;
let memoryPercentileValue = 99;
let networkPercentileValue = 99;
let diskPercentileValue = 99;
let rightSizeRecommendationId = 'e10b1381-5f0a-47ff-8c7b-37bd13d7c974';
let billingInterval = 30d;
let etime = todatetime(toscalar(AzureOptimizationConsumptionV1_CL | summarize max(UsageDate_t))); 
let stime = etime-billingInterval; 
let RightSizeInstanceIds = materialize(AzureOptimizationAdvisorV1_CL 
| where todatetime(TimeGenerated) > ago(advisorInterval) and Category == 'Cost' and RecommendationTypeId_g == rightSizeRecommendationId
| distinct InstanceId_s);
let LinuxMemoryPerf = Perf 
| where TimeGenerated > ago(perfInterval) and _ResourceId in (RightSizeInstanceIds) 
| where CounterName == '% Used Memory' 
| summarize hint.strategy=shuffle PMemoryPercentage = percentile(CounterValue, memoryPercentileValue) by _ResourceId;
let WindowsMemoryPerf = Perf 
| where TimeGenerated > ago(perfInterval) and _ResourceId in (RightSizeInstanceIds) 
| where CounterName == 'Available MBytes' 
| project TimeGenerated, MemoryAvailableMBs = CounterValue, _ResourceId;
let MemoryPerf = AzureOptimizationVMsV1_CL 
| where TimeGenerated > ago(1d)
| distinct InstanceId_s, MemoryMB_s
| join kind=inner hint.strategy=broadcast (
	WindowsMemoryPerf
) on $left.InstanceId_s == $right._ResourceId
| extend MemoryPercentage = todouble(toint(MemoryMB_s) - toint(MemoryAvailableMBs)) / todouble(MemoryMB_s) * 100 
| summarize hint.strategy=shuffle PMemoryPercentage = percentile(MemoryPercentage, memoryPercentileValue) by _ResourceId
| union LinuxMemoryPerf;
let ProcessorPerf = Perf 
| where TimeGenerated > ago(perfInterval) and _ResourceId in (RightSizeInstanceIds) 
| where ObjectName == 'Processor' and CounterName == '% Processor Time' and InstanceName == '_Total' 
| summarize hint.strategy=shuffle PCPUPercentage = percentile(CounterValue, cpuPercentileValue) by _ResourceId;
let WindowsNetworkPerf = Perf 
| where TimeGenerated > ago(perfInterval) and _ResourceId in (RightSizeInstanceIds) 
| where CounterName == 'Bytes Total/sec' 
| summarize hint.strategy=shuffle PCounter = percentile(CounterValue, networkPercentileValue) by InstanceName, _ResourceId
| summarize PNetwork = sum(PCounter) by _ResourceId;
let DiskPerf = Perf
| where TimeGenerated > ago(perfInterval) and _ResourceId in (RightSizeInstanceIds) 
| where CounterName in ('Disk Reads/sec', 'Disk Writes/sec', 'Disk Read Bytes/sec', 'Disk Write Bytes/sec') and InstanceName !in ('_Total', 'D:', '/mnt/resource', '/mnt')
| summarize hint.strategy=shuffle PCounter = percentile(CounterValue, diskPercentileValue) by bin(TimeGenerated, perfTimeGrain), CounterName, InstanceName, _ResourceId
| summarize SumPCounter = sum(PCounter) by CounterName, TimeGenerated, _ResourceId
| summarize MaxPReadIOPS = maxif(SumPCounter, CounterName == 'Disk Reads/sec'), 
            MaxPWriteIOPS = maxif(SumPCounter, CounterName == 'Disk Writes/sec'), 
            MaxPReadMiBps = (maxif(SumPCounter, CounterName == 'Disk Read Bytes/sec') / 1024 / 1024), 
            MaxPWriteMiBps = (maxif(SumPCounter, CounterName == 'Disk Write Bytes/sec') / 1024 / 1024) by _ResourceId;
AzureOptimizationAdvisorV1_CL 
| where todatetime(TimeGenerated) > ago(advisorInterval) and Category == 'Cost'
| extend InstanceName_s = iif(isnotempty(InstanceName_s),InstanceName_s,InstanceName_g)
| distinct InstanceId_s, InstanceName_s, Description_s, SubscriptionGuid_g, TenantGuid_g, ResourceGroup, Cloud_s, AdditionalInfo_s, RecommendationText_s, ImpactedArea_s, Impact_s, RecommendationTypeId_g
| join kind=leftouter (
    AzureOptimizationConsumptionV1_CL
    | where UsageDate_t between (stime..etime)
    | extend VMConsumedQuantity = iif(InstanceId_s contains 'virtualmachines' and MeterCategory_s == 'Virtual Machines', todouble(Quantity_s), 0.0)
    | extend VMPrice = iif(InstanceId_s contains 'virtualmachines' and MeterCategory_s == 'Virtual Machines', todouble(UnitPrice_s), 0.0)
    | extend FinalCost = iif(InstanceId_s contains 'virtualmachines', VMPrice * VMConsumedQuantity, todouble(Cost_s))
    | summarize Last30DaysCost = sum(FinalCost), Last30DaysQuantity = sum(VMConsumedQuantity) by InstanceId_s
) on InstanceId_s
| join kind=leftouter (
    AzureOptimizationVMsV1_CL 
    | where TimeGenerated > ago(1d) 
    | distinct InstanceId_s, NicCount_s, DataDiskCount_s, Tags_s
) on InstanceId_s 
| where RecommendationTypeId_g != rightSizeRecommendationId or (RecommendationTypeId_g == rightSizeRecommendationId and toint(NicCount_s) >= 0 and toint(DataDiskCount_s) >= 0)
| join kind=leftouter hint.strategy=broadcast ( MemoryPerf ) on $left.InstanceId_s == $right._ResourceId
| join kind=leftouter hint.strategy=broadcast ( ProcessorPerf ) on $left.InstanceId_s == $right._ResourceId
| join kind=leftouter hint.strategy=broadcast ( WindowsNetworkPerf ) on $left.InstanceId_s == $right._ResourceId
| join kind=leftouter hint.strategy=broadcast ( DiskPerf ) on $left.InstanceId_s == $right._ResourceId
| extend MaxPIOPS = MaxPReadIOPS + MaxPWriteIOPS, MaxPMiBps = MaxPReadMiBps + MaxPWriteMiBps
| extend PNetworkMbps = PNetwork * 8 / 1000 / 1000
| distinct Last30DaysCost, Last30DaysQuantity, InstanceId_s, InstanceName_s, Description_s, SubscriptionGuid_g, TenantGuid_g, ResourceGroup, Cloud_s, AdditionalInfo_s, RecommendationText_s, ImpactedArea_s, Impact_s, RecommendationTypeId_g, NicCount_s, DataDiskCount_s, PMemoryPercentage, PCPUPercentage, PNetworkMbps, MaxPIOPS, MaxPMiBps, Tags_s
| join kind=leftouter ( 
    AzureOptimizationResourceContainersV1_CL
    | where TimeGenerated > ago(1d)
    | where ContainerType_s =~ 'microsoft.resources/subscriptions' 
    | project SubscriptionGuid_g, SubscriptionName = ContainerName_s 
) on SubscriptionGuid_g