AzCostMgmt.kql

AzCostMgmt.kql

// https://aka.ms/adx.blog

UsagePreliminary
| where Date == datetime(2023-08-01) 
| where MeterId == "2c57ed84-f939-4f5c-ba90-782349a367b8"
| where ResourceId == "/subscriptions/9ec51cfd-5ca7-4d76-8101-dd0a4abc5674/resourcegroups/mc_analyticsengine_analyticsengine_eastus/providers/microsoft.compute/virtualmachinescalesets/aks-secretagent-37798712-vmss"


UsagePreliminary
| summarize Quantity=sum(Quantity)


.show function IngestToUsage

{
    UsageIngest
    | extend Tags = todynamic(strcat('{', Tags, '}')), ResourceId = tolower(ResourceId)
}

.show function IngestToUsagePreliminary

{
    UsagePreliminaryIngest
    | extend Tags = todynamic(strcat('{', Tags, '}')), ResourceId = tolower(ResourceId)
}

.show table Usage policy update 

[
  {
    "IsEnabled": true,
    "Source": "UsagePreliminaryIngest",
    "Query": "IngestToUsagePreliminary()",
    "IsTransactional": true,
    "PropagateIngestionProperties": true,
    "ManagedIdentity": null
  }
]

costraw804
| summarize max(Date), min(Date)

costraw811
| summarize max(Date), min(Date)


costraw811
| extend Tags = todynamic(strcat('{', Tags, '}')), ResourceId = tolower(ResourceId)
| project  Tags, ResourceId

costraw811
| summarize q=sum(Quantity)
//1749.3155090599998

costraw811
| summarize q=sum(Quantity) by Date
// 2023-08-01T00:00:00Z	169.28342256000002
// 2023-08-02T00:00:00Z	169.28342256
// 2023-08-03T00:00:00Z	169.28342256000002
// 2023-08-04T00:00:00Z	169.28342256000008
// 2023-08-05T00:00:00Z	169.28342256000002
// 2023-08-06T00:00:00Z	169.28342256000002
// 2023-08-07T00:00:00Z	169.28342256000002
// 2023-08-08T00:00:00Z	169.28342256
// 2023-08-09T00:00:00Z	169.28342256000002
// 2023-08-10T00:00:00Z	169.28342256000005
// 2023-08-11T00:00:00Z	56.48128346

costraw810
| summarize q=sum(Quantity) by Date

costraw805
| summarize q=sum(Quantity) by Date


costraw811
| summarize sum(todecimal(Quantity)), sum(Quantity) 
// 1749.31550906	1749.3155090599998

UsagePreliminary
| summarize sum(Quantity), sum(toreal(Quantity))
// 1749.31550906	1749.3155090599998


UsagePreliminary
| getschema 
| where ColumnName == 'Quantity'

costraw811
| getschema 
| where ColumnName == 'Quantity'



costraw810 
| extend ingestion_time()
| union (costraw811 | extend ingestion_time())
// | summarize sum(Quantity) by Date
| summarize arg_min($IngestionTime,*) by Date

| summarize sum(Quantity), sum(toreal(Quantity)) by ['Date'], $IngestionTime


costraw810 
| extend ingestion_time()
| union (costraw811 | extend ingestion_time())
| summarize arg_min($IngestionTime,*) by ['Date']
| summarize sum(Quantity)


costraw811
| getschema | project strcat(ColumnName,', ')

costraw810
| getschema | project strcat(ColumnName,', ')


costraw811
| join kind=leftanti costraw810 on $left.['Date']==$right.['Date']
| summarize sum(Quantity) by ['Date']


costraw811 | summarize sum(Quantity) by ['Date'], ingestion_time()
| union (
costraw810 | summarize sum(Quantity) by ['Date'], ingestion_time()
)
| summarize arg_max(ingestion_time(),*) by ['Date']


let x= costraw811 | union costraw810;
x
| summarize sum(Quantity) by ['Date'], ingestion_time()
| summarize arg_min(ingestion_time(),*) by ['Date']
| summarize sum(sum_Quantity)

let x= costraw811 | union costraw810;
x
| summarize count() by ingestion_time()

let x= costraw811 | union costraw810 | union costraw711;
x
| where ingestion_time() in (function())  returns by lastDofAllMon + max(ingesitontime)
| count


let m='20230801-20230831';
let r='0002319c-26ff-4c01-beb7-3e7df86337bf';
let x= costraw811 | extend ctag = strcat('20230801-20230831 | 0002319c-26ff-4c01-beb7-3e7df86337bf');
let y= costraw810 | extend ctag = strcat('20230801-20230831 | 0007df1c-47a7-456a-880a-b47dc513b4e1');
x | union y
| distinct ctag
| extend s=split(ctag,'|')
| project trim(' ',tostring(s[0])), trim(' ',tostring(s[1]))
| where Column1 == m and Column2  != r 
//pipe to a control command ie. .delete or .drop extents with TABLE Admin only access.



union (costraw810 | extend ctag=strcat('20230801-20230831 | ', tostring(u2) ));
x
| project ctag
| distinct ctag



| where ['Date'] == Tags



prevload - .rename (secure req?)
todayload - current data

onetable - appendonly hist + current data