Skip to content

Instantly share code, notes, and snippets.

@cosh
Created November 12, 2021 15:55
Show Gist options
  • Save cosh/dd6af2d36e05c71fbf071b3c920f7931 to your computer and use it in GitHub Desktop.
Save cosh/dd6af2d36e05c71fbf071b3c920f7931 to your computer and use it in GitHub Desktop.
KQL queries to contextualize timeseries data
//The top level request
GetRelevantTimeseriesData('3000000004')
//Creates a function to joins data from SQL, ADT and ADX
.create-or-alter function with (folder = "Analytics/IoT", skipvalidation = "true") GetRelevantTimeseriesData(purchaseOrder:string) {
let productionOrder = GetProductionOrderData(purchaseOrder);
let plantID = toscalar (productionOrder | project tostring(Plant_Code));
let lineID = toscalar (productionOrder | project tostring(Mfg_Line));
let productionDate = toscalar (productionOrder | project todatetime(Date));
let startDate = productionDate - 24h;
let endDate = productionDate + 24h;
// Get twins of interest based on plantID and lineId
GetTwins(plantID, lineID)
| join kind=inner
(
GetTelemetryData(startDate, endDate)
) on Id
| project-away tid, Id1
}
//Query a Synapse dedicated SQL pool
.create-or-alter function with (folder = "Analytics/IoT", skipvalidation = "true") GetProductionOrderData(purchaseOrder:string) {
let SQLquery = strcat(
```
declare @plantID VARCHAR(10)
declare @plantProductionRecordViewName VARCHAR(50)
declare @plantProductionRecordQuery nvarchar(100)
declare @PO VARCHAR(20)
set @PO = '```,purchaseOrder,```'
-- get the plantID where the PO was fufilled
set @plantID = (
Select top (1) WERKS from PO
Where PO.EBELN = @PO)
-- set the view name based on the plantID
set @plantProductionRecordViewName = CONCAT('Plant',@plantID,'ProductionScheduleView')
-- create query string to retrieve the production record of interest
set @plantProductionRecordQuery = CONCAT('select * from ',@plantProductionRecordViewName, ' where Purchase_Order = ', @PO)
EXEC sp_executesql @plantProductionRecordQuery```);
let productionOrder = materialize(
(evaluate sql_request(
'Server=tcp:synapseadxdemo.sql.azuresynapse.net,1433;'
'Authentication="Active Directory Integrated";'
'Initial Catalog=SAPPOs;', SQLquery) ));
productionOrder
}
//Query Azure Digital Twins
.create-or-alter function with (folder = "Analytics/IoT", skipvalidation = "true") GetTwins(plantID:string, lineID:string) {
let ADTendpoint = "https://<name>.api.eus.digitaltwins.azure.net";
let plantDtId = strcat('Plant', plantID);
let ADTquery = strcat(```SELECT station.$dtId as tid FROM DIGITALTWINS plant
JOIN prodLine RELATED plant.contains
JOIN station related prodLine.contains
WHERE plant.$dtId = '``` , plantDtId,
"' AND prodLine.productionLineNumber = ", lineID);
evaluate azure_digital_twins_query_request(ADTendpoint, ADTquery)
| extend Id = tostring(tid)
}
//Get the timeseries data from iot devices
.create-or-alter function with (folder = "Analytics/IoT", skipvalidation = "true") GetTelemetryData(startDate:datetime, endDate:datetime) {
TimeseriesData
| where ts between (startDate..endDate)
| project TimeStamp = ts, Id, ModelId, Key, Value = value, RelationshipTarget = "", RelationshipId = ""
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment