-
Notifications
You must be signed in to change notification settings - Fork 23
/
ContosoDairyDataHistoryQueries.kql
196 lines (185 loc) · 8.67 KB
/
ContosoDairyDataHistoryQueries.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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
// Copy and paste each query below into Azure Data Explorer. To run a query, click on the query to highlight it and press the Run button.
// In each query, replace <your_ADT_endpoint> with your instance's endpoint. Your endpoint should be in quotes, start with https://, and be followed by a semicolon.
// For example: let ADTendpoint = "https://MyExampleADTinstance.api.wcus.digitaltwins.azure.net";
// Also, replace <table-name> with the name of your data history table
// Get a sample of the data history table
<table-name>
| where Key == 'InFlow'
| take 100
// Get all factories
let ADTendpoint = "<ADT-instance>";
let ADTquery =
```SELECT t.$dtId as Factories FROM DIGITALTWINS t
WHERE IS_OF_MODEL(t , 'dtmi:assetGen:Factory;1')```;
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
// Get all the operators employed in the Oslo Factory
let factory = "OsloFactory";
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT person.$dtId as tid FROM DIGITALTWINS
MATCH (factory)-[:employs]->(person)
WHERE factory.$dtId ='```, factory,"'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
// Get all machines maintained by John in the OsloFactory
let factory = "OsloFactory";
let operator = "John";
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT machine.$dtId as tid FROM DIGITALTWINS
MATCH (factory)-[:employs]->(person)-[:maintains]->(machine)
WHERE person.$dtId = '```, operator,
"' AND factory.$dtId = '", factory, "'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
// Chart the 1-minute average OutFlow of each machine maintained by John in the OsloFactory over the past hour
let factory = "OsloFactory";
let operator = "John";
let sensor = "OutFlow";
let lookback = 1h;
let timeBin = 1m;
let ADTendpoint = "<ADT-instance>";
let chartTitle = strcat("1-Min Average ", sensor, " for Machines Maintained by ", operator, " in the ", factory);
let ADTquery = strcat(
```SELECT machine.$dtId as tid FROM DIGITALTWINS
MATCH (factory)-[:employs]->(person)-[:maintains]->(machine)
WHERE person.$dtId = '```, operator,
"' AND factory.$dtId = '", factory, "'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=rightsemi (<table-name>
| where TimeStamp > ago(lookback)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize avg(Value_double) by bin(TimeStamp, timeBin),Id
| render timechart with (title=chartTitle)
// Chart the maximum 1-minute Inflow of any machine maintained by John in the OsloFactory over the past hour
let factory = "OsloFactory";
let operator = "John";
let sensor = "InFlow";
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Maximum ", sensor, " of Any Machines Maintained by ", operator, " in the ", factory);
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT machine.$dtId as tid FROM DIGITALTWINS
MATCH (factory)-[:employs]->(person)-[:maintains]->(machine)
WHERE person.$dtId = '```, operator,
"' AND factory.$dtId = '", factory, "'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=rightsemi (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double=todouble(Value)
| summarize max(Value_double) by bin(TimeStamp, timeBin)
| render timechart with (title=chartTitle)
// Compare the 1-minute average "Outflow" of all machines that feed SaltMachine_C0 over the past hour
let downstreamMachine = "SaltMachine_C0";
let sensor = "OutFlow";
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Compare ",sensor, " from all Machines that feed into ", downstreamMachine);
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT upstreamMachine.$dtId as tid FROM DIGITALTWINS
MATCH (upstreamMachine)-[:feeds]->(downstreamMachine)
WHERE downstreamMachine.$dtId = '```, downstreamMachine, "'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=inner (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize avg(Value_double) by bin(TimeStamp, timeBin), tid_string
| render timechart with (title=chartTitle)
// Sum the"Outflow" from all machines that feed SaltMachine_C0
let downstreamMachine = "SaltMachine_C0";
let sensor = "OutFlow";
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Total ",sensor, " from all Machines that feed into ", downstreamMachine);
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT upstreamMachine.$dtId as tid FROM DIGITALTWINS
MATCH (upstreamMachine)-[:feeds]->(downstreamMachine)
WHERE downstreamMachine.$dtId = '```, downstreamMachine, "'");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=inner (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize avg(Value_double) by bin(TimeStamp, timeBin), tid_string
| summarize sum(avg_Value_double) by TimeStamp
| render timechart with (title=chartTitle)
// Compare the Outflow of all SaltMachines in the Oslo Factory
let machineType = "SaltMachine";
let sensor = "OutFlow";
let factory = "OsloFactory";
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Comparison of ",sensor, " from from all ", machineType,"s in the ", factory);
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT machine.$dtId as tid FROM DIGITALTWINS
MATCH (factory)-[:contains]->(machine)
WHERE factory.$dtId = '```, factory, "' and STARTSWITH(machine.$dtId, '", machineType, "')");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=rightsemi (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize avg(Value_double) by bin(TimeStamp, timeBin), Id
| render timechart with (title=chartTitle)
// Maximum 1-minute Outflow from any SaltMachine in any factories
let downstreamMachine = "SaltMachine_C0";
let sensor = "OutFlow";
let factoryIdList = "['OsloFactory', 'StockholmFactory']";
let machineType = 'SaltMachine';
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Maximum ", sensor, " from any ", machineType, " in the following factories:", factoryIdList);
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT machine.$dtId as tid FROM digitaltwins
MATCH (factory)-[:contains]->(machine)
WHERE factory.$dtId IN ```, factoryIdList,
" AND CONTAINS(machine.$dtId,'",machineType, "') ");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend tid_string = tostring(tid)
| join kind=rightsemi (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize max(Value_double) by bin(TimeStamp, timeBin)
| render timechart with (title=chartTitle)
// Compare the total Outflow from salt machines the OsloFactory vs those in the Stockholm factory
let sensor = "OutFlow";
let factoryList = "['OsloFactory', 'StockholmFactory']";
let machineType = 'SaltMachine';
let lookbackPeriod = 1h;
let timeBin = 1m;
let chartTitle = strcat("Comparison of total ", sensor, " (1-m avg) from ", machineType, "s in ", factoryList, " Factories");
let ADTendpoint = "<ADT-instance>";
let ADTquery = strcat(
```SELECT machine.$dtId as machine_tid, factory.$dtId as factory_tid FROM digitaltwins
MATCH (factory)-[:contains]->(machine)
WHERE factory.$dtId in ```, factoryList,
" AND CONTAINS(machine.$dtId,'",machineType, "') ");
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend machine_tid_string = tostring(machine_tid)
| extend factory_tid_string = tostring(factory_tid)
| join kind=inner (<table-name>
| where TimeStamp > ago(lookbackPeriod)
and Key == sensor)
on $left.machine_tid_string == $right.Id
| extend Value_double = todouble(Value)
| summarize avg(Value_double) by bin(TimeStamp, timeBin), Id, factory_tid_string
| summarize sum(avg_Value_double) by TimeStamp, factory_tid_string
| render timechart with (title=chartTitle)