-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathactivity_cost.kql
67 lines (65 loc) · 5.15 KB
/
activity_cost.kql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
//ADF costs - summary
//https://azure.microsoft.com/en-gb/pricing/details/data-factory/data-pipeline/
let ADFCosts = datatable (computeType:string, Unit:string, cost:real, currency:string, region:string) //per 8 vCore hour costs or 8 DIUHours
["General", "coreHour", 0.231, "GBP", "UKSouth",
"Memory", "coreHour", 0.294, "GBP","UKSouth",
"AzureIR", "DIUHours", 0.2, "GBP","UKSouth",
"SelfhostedIR", "Hours", 0.08, "GBP","UKSouth"];
let DataFlows = ADFActivityRun
| where Status has_any ('Succeeded','Failed')
| where ActivityType == "ExecuteDataFlow"
| extend DataFactory = substring(reverse(split(reverse(ResourceId),"/",0)),2,strlen(split(reverse(ResourceId),"/",0))-4)
| extend input = parse_json(Input), output = parse_json(Output)
| extend coreCount = toint(input.compute.coreCount), computeType = tostring(input.compute.computeType)
| extend billing_data = parse_json(Output).billingReference
| extend bd = parse_xml(billing_data)
| extend activityType = bd.activityType
| extend meterType = tostring(bd.billableDuration[0].meterType), duration = toreal(bd.billableDuration[0].duration), unit = tostring(bd.billableDuration[0].unit), sessionType = tostring(bd.billableDuration[0].sessionType)
| project TimeGenerated, DataFactory, PipelineName, PipelineRunId, Status, computeType, coreCount, duration, TimeDur = End-Start, unit, Start, Starts=format_datetime(Start,'dd-MM-yyyy HH:mm:ss'), End=format_datetime(End,'dd-MM-yyyy HH:mm:ss');
let CopyActivity = ADFActivityRun
| where Status has_any ('Succeeded','Failed')
| where ActivityType == "Copy"
| extend DataFactory = substring(reverse(split(reverse(ResourceId),"/",0)),2,strlen(split(reverse(ResourceId),"/",0))-4)
| extend billing_data = parse_json(Output).billingReference
| extend bd = parse_xml(billing_data)
| extend activityType = bd.activityType, bdur = parse_xml(bd.billableDuration)
| extend meterType = tostring(bd.billableDuration[0].meterType), duration = toreal(bd.billableDuration[0].duration), unit = tostring(bd.billableDuration[0].unit), sessionType = tostring(bd.billableDuration[0].sessionType)
| project TimeGenerated, DataFactory, PipelineName, PipelineRunId, Status, computeType = meterType, coreCount = toint(1), duration, TimeDur = End-Start, unit,Start, Starts=format_datetime(Start,'dd-MM-yyyy HH:mm:ss'), End=format_datetime(End,'dd-MM-yyyy HH:mm:ss');
DataFlows | union CopyActivity
| join kind= leftouter ADFCosts on computeType
| extend runCost = cost*duration
//summarize by factory, day, computeType
| summarize TotalRunCost = sum(runCost) ,TotalTimeDur = sum(TimeDur) by DataFactory, bin(Start, 1d), computeType, PipelineName, PipelineRunId
//summarize by day for checking ball park costs against cost analysis info
//| summarize TotalRunCost = sum(runCost) by Runday = bin(Start, 1d)
| render timechart
//ADF costs - details
//https://azure.microsoft.com/en-gb/pricing/details/data-factory/data-pipeline/
let ADFCosts = datatable (computeType:string, Unit:string, cost:real, currency:string, region:string) //per 8 vCore hour costs or 8 DIUHours
["General", "coreHour", 0.231, "GBP", "UKSouth",
"Memory", "coreHour", 0.294, "GBP","UKSouth",
"AzureIR", "DIUHours", 0.2, "GBP","UKSouth",
"SelfhostedIR", "Hours", 0.08, "GBP","UKSouth"];
let DataFlows = ADFActivityRun
| where Status has_any ('Succeeded','Failed')
| where ActivityType == "ExecuteDataFlow"
| extend DataFactory = substring(reverse(split(reverse(ResourceId),"/",0)),2,strlen(split(reverse(ResourceId),"/",0))-4)
| extend input = parse_json(Input), output = parse_json(Output)
| extend coreCount = toint(input.compute.coreCount), computeType = tostring(input.compute.computeType)
| extend billing_data = parse_json(Output).billingReference
| extend bd = parse_xml(billing_data)
| extend activityType = bd.activityType
| extend meterType = tostring(bd.billableDuration[0].meterType), duration = toreal(bd.billableDuration[0].duration), unit = tostring(bd.billableDuration[0].unit), sessionType = tostring(bd.billableDuration[0].sessionType)
| project TimeGenerated, DataFactory, PipelineName, PipelineRunId, Status, computeType, coreCount, duration, TimeDur = End-Start, unit, Start=format_datetime(Start,'dd-MM-yyyy HH:mm:ss'), End=format_datetime(End,'dd-MM-yyyy HH:mm:ss');
let CopyActivity = ADFActivityRun
| where Status has_any ('Succeeded','Failed')
| where ActivityType == "Copy"
| extend DataFactory = substring(reverse(split(reverse(ResourceId),"/",0)),2,strlen(split(reverse(ResourceId),"/",0))-4)
| extend billing_data = parse_json(Output).billingReference
| extend bd = parse_xml(billing_data)
| extend activityType = bd.activityType, bdur = parse_xml(bd.billableDuration)
| extend meterType = tostring(bd.billableDuration[0].meterType), duration = toreal(bd.billableDuration[0].duration), unit = tostring(bd.billableDuration[0].unit), sessionType = tostring(bd.billableDuration[0].sessionType)
| project TimeGenerated, DataFactory, PipelineName, PipelineRunId, Status, computeType = meterType, coreCount = toint(1), duration, TimeDur = End-Start, unit, Start=format_datetime(Start,'dd-MM-yyyy HH:mm:ss'), End=format_datetime(End,'dd-MM-yyyy HH:mm:ss');
DataFlows | union CopyActivity
| join kind= leftouter ADFCosts on computeType
| extend runCost = cost*duration