forked from bwatts64/PatientMonitoringDemo
-
Notifications
You must be signed in to change notification settings - Fork 2
/
Sample.kql
268 lines (223 loc) · 10.3 KB
/
Sample.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
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
//Data lands in the TelemetryRaw table so lets look at that first
.show table TelemetryRaw
TelemetryRaw
| limit 10
//Telemetry is being generated by two devices VirtualPatches and KneeBraces
TelemetryRaw
| distinct templateId
//We use an update policy to flatten this data and append it to the appropriate table
.show table KneeBrace
.show table VirtualPatch
.show table VirtualPatchFall
//Here is the update policy. Expand the Policy column to see the details
.show table KneeBrace policy update
.show table VirtualPatch policy update
.show table VirtualPatchFall policy update
//Here is the function that is used for the update policy
.show function Update_KneeBrace
//Below is the query for this function
TelemetryRaw
| where templateId == 'dtmi:j71gm4wvkse:q2hnw2dwt'
| project enqueuedTime,deviceId, todynamic(telemetry.Acceleration), toint(telemetry.RangeOfMotion), toint(telemetry.KneeBend), toint(telemetry.BatteryLevel), todouble(telemetry.DeviceTemperature)
.show function Update_VirtualPatch
//Below is the query for that function
TelemetryRaw
| where templateId == 'dtmi:hpzy1kfcbt2:umua7dplmbd'
| where telemetry !has 'FallDetection'
| project enqueuedTime, deviceId, toint(telemetry.BatteryLevel), todouble(telemetry.DeviceTemperature), toint(telemetry.HeartRate), toint(telemetry.RespiratoryRate), toint(telemetry.HeartRateVariability), todouble(telemetry.BodyTemperature), todynamic(telemetry.BloodPressure), tostring(telemetry.Activity)
.show function Update_VirtualPatchFall
//And our last update function
TelemetryRaw
| where templateId == 'dtmi:hpzy1kfcbt2:umua7dplmbd'
| where telemetry has 'FallDetection'
| project enqueuedTime, deviceId, tostring(telemetry.FallDetection)
//Lets do some exploration of the KneeBrace telemetry first
KneeBrace
| limit 10
//Lets look at the avg range of motion over time
let start=datetime(2022-1-10);
let end=datetime(2022-1-20);
KneeBrace
| where TimeStamp between (start .. end)
| summarize avg(RangeofMotion) by DeviceId, bin(TimeStamp,15m)
| render timechart
//Lets filter to the device that has the largest knee bend over that time and chart it
let start=datetime(2022-1-10);
let end=datetime(2022-1-20);
let Device=toscalar(KneeBrace
| where TimeStamp between (start .. end)
| top 1 by KneeBend
| project DeviceId);
KneeBrace
| where TimeStamp between (start .. end)
| where DeviceId == Device
| summarize avg(RangeofMotion) by DeviceId, bin(TimeStamp,15m)
| render timechart
//We can also explore the data to see time range we've colleced
KneeBrace
| summarize MinDate=min(TimeStamp), MaxDate=max(TimeStamp), MinIngest=min(ingestion_time())
//But we have a gap in data between our historical data and new data coming in
KneeBrace
| summarize count() by bin(TimeStamp, 1d)
| render columnchart
// Lets focus on the data coming from VirtualPatches now
VirtualPatch
| limit 10
// Lets examine the body temp over time
let start=datetime(2022-1-10);
let end=datetime(2022-1-20);
VirtualPatch
| where TimeStamp between (start .. end)
| summarize avg(BodyTemperature) by DeviceId, bin(TimeStamp, 1m)
| render timechart
//Lets focus on 1 Device and see if we are missing any data
//33jdqaeuff
//make-series
//Create series of specified aggregated values along specified axis.
let start=datetime(2022-1-18);
let end=datetime(2022-1-20);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) on TimeStamp from start to end step 1m
| render timechart
//How can I fill the missing values?
//series_fill_linear()
//Performs linear interpolation of missing values in a series.
let start=datetime(2022-1-18);
let end=datetime(2022-1-20);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end step 1m
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| render timechart
//What will be the temprature for next one hour? Note that we are using historical data so we will use 1-31 as present
let start = datetime(2022-01-29);
let end = datetime(2022-01-30);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end+60m step 1m
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| extend forecast = series_decompose_forecast(NoGapsTemp, 60)
| render timechart with(title='Forecasting the next 60min by Time Series Decmposition')
//Lets forcast out 12 hours
let start = datetime(2022-01-29);
let end = datetime(2022-01-30);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end+12h step 1m
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| extend forecast = series_decompose_forecast(NoGapsTemp, 720)
| render timechart with(title='Forecasting the next 12 hours by Time Series Decmposition')
//Are there any anomalies for this device?
let start = datetime(2022-01-29);
let end = datetime(2022-01-31);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end step 1m
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| extend anomalies = series_decompose_anomalies(NoGapsTemp,1)
| render anomalychart with(anomalycolumns=anomalies)
//Lets make it less sensative
let start = datetime(2022-01-29);
let end = datetime(2022-01-31);
VirtualPatch
| where TimeStamp between (start .. end)
| where DeviceId == '33jdqaeuff'
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end step 1m
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| extend anomalies = series_decompose_anomalies(NoGapsTemp,1.5)
| render anomalychart with(anomalycolumns=anomalies)
//What the anomalies I shoudl focus on across all devices?
let start = datetime(2022-01-29);
let end = datetime(2022-01-31);
VirtualPatch
| where TimeStamp between (start .. end)
| make-series AvgBodyTemp=avg(BodyTemperature) default=real(null) on TimeStamp from start to end step 1m by DeviceId
| extend NoGapsTemp=series_fill_linear(AvgBodyTemp)
| project TimeStamp, NoGapsTemp
| extend anomalies = series_decompose_anomalies(NoGapsTemp,1.5)
| mv-expand TimeStamp, anomalies, NoGapsTemp
| where anomalies == 1
//All the devices have a battery level so lets look at the battery level across
//all devices using a join
let start = datetime(2022-01-29);
let end = datetime(2022-01-31);
KneeBrace
| where TimeStamp between (start .. end)
| project TimeStamp, DeviceId, BatteryLevel
| union (VirtualPatch
| where TimeStamp between (start .. end)
| project TimeStamp, DeviceId, BatteryLevel)
| summarize avg(BatteryLevel) by DeviceId, bin(TimeStamp, 30m)
| render timechart
//Lets shift focus to the current data being ingested into ADX
//We have the ability to query out to blob, sql, cosmos, adt (azure digital twins), etc..
//The provisioning process created a function that will query out to ADT so lets look at that
//This function allows us to get the thermostats at certain facilities (Arkham)
// and departments (Rehabilitation and Phsycology).
.show function GetTwinsbyFacilityDepartment
//Here is the query
//{
// let ADTendpoint = "https://<your adt endpoint>";
// let ADTquery = strcat(```Select T.$dtId as Facility,Department.$dtId as Department,Device.$dtId as deviceId, Device.PatientId as PatientId, Device.StartDate as StartDate FROM DIGITALTWINS T
// JOIN Department RELATED T.facilitycontainsdepartment
// JOIN Device RELATED Department.departmentownsdevice
// where T.$dtId = '``` , facilityName,
// "' AND Department.$dtId = '", departmentName, "'");
// evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
// | project Facility=tostring(Facility), Department=tostring(Department),DeviceId=tostring(deviceId), PatientId=tostring(PatientId), StartDate=todatetime(StartDate)
//}
//Lets look at devices assigned to the Rehab department
GetTwinsbyFacilityDepartment('Arkham', 'Rehabilitation')
//Now we can join that to the telemetry data and chart by the patient name
GetTwinsbyFacilityDepartment('Arkham', 'Rehabilitation')
| join kind=inner (KneeBrace
| where TimeStamp >= ago(1h)
| summarize AvgKneeBend=avg(KneeBend) by DeviceId, bin(TimeStamp, 5m)
) on DeviceId
| where isnotempty(PatientId)
| project AvgKneeBend, TimeStamp, PatientId
| render timechart with (ycolumns=AvgKneeBend, series=PatientId)
//Lets circle back to VirtualPatch. This has blood pressure in dynamic field
//Below we'll look at a couple way to show this
VirtualPatch
| where TimeStamp >= ago(2h)
| project BloodPressure, Option1=strcat(BloodPressure.Systolic, '/', BloodPressure.Diastolic), Option2=strcat(BloodPressure.Systolic, ' over ', BloodPressure.Diastolic)
//What about any reading where the Diastolic is over 80
//Lets circle back to VirtualPatch. This has blood pressure in dynamic field
//Below we'll look at a couple way to show this
VirtualPatch
| where TimeStamp >= ago(2h)
| where BloodPressure.Diastolic>80
| project DeviceId, TimeStamp, BloodPressure=strcat(BloodPressure.Systolic, '/', BloodPressure.Diastolic)
//Materialized views are commonly used to
// a. Store aggregates of the raw data
// b. Store last know values
// c. Remove duplicate records
// Lets create two materialized views. One for hourly aggregates and one for last known values
// We'll also backfill the records. You will typically use async but for this sample we'll wait for it to complete
.create materialized-view with (backfill=true) VirtualPatch_Hourly_Average_Mview on table VirtualPatch
{
VirtualPatch
| summarize DeviceTemp=avg(DeviceTemp), BatteryLevel=avg(BatteryLevel), HeartRate=avg(HeartRate), RespiratoryRate=avg(RespiratoryRate), HeartRateVariability=avg(HeartRateVariability), BodyTemp=avg(BodyTemperature) by DeviceId, TimeStamp=bin(TimeStamp, 1h)
}
VirtualPatch_Hourly_Average_Mview
| where TimeStamp > ago(1h)
| take 1000
// Now lets create a current view
.create materialized-view with (backfill=true) VirtualPatch_Current_Mview on table VirtualPatch
{
VirtualPatch
| summarize arg_max(TimeStamp, *) by DeviceId
}
VirtualPatch_Current_Mview