Azure Data Explorer functions for data querying

บทความ
11/18/2024

Important

Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.

Important

This document includes several references to the ISA95 semantic model. Visit the ISA95 website and store to purchase the ISA95 standards.

The following custom functions are registered in the deployed Azure Data Explorer (ADX) instance to help querying data using KQL. These functions are also used by Factory Operations Agent for querying the underlying data.

Kusto Graph

The Kusto Graph function uses the data in AdtPropertyEvents, AdtTwinEvents, and AdtRelations tables. It internally uses make-graph operator to build the graph from the tabular data and returns a graph expression. The graph function has to be followed by graph-match operator.

Graph(
    interestingEntities: dynamic,
    interestingNodeProperties: dynamic
)

Param	Type	Required	Description
interestingEntities	dynamic (list)	Optional	Filters out unnecessary entities from the graph, potentially enhancing query performance. Use with caution when employing Variable length edge in your query, as it may inadvertently exclude some paths.
interestingNodeProperties	dynamic (list)	Optional	Filters out unnecessary columns from the graph, potentially enhancing query performance. This list contains the column names from all entities to be used later in the graph query.

Return type - Kusto Graph query

A property is referenced by the variable name followed by a dot (.) and the attribute name. For a pattern like (e1)-[rel]-e2 in graph-match, the following Node and Edge properties are available.

Node Property

Property Name	Description	Sample	Comment
ModelId	Type of entity	`dtmi:digitaltwins:isa95:Equipment;1`	To filter use `e1.ModeldId has 'Equipment'`. This is a must have filter in "where" clause.
Properties	Property bag of all the columns of the entity	`{ "equipmentLevel": "workCell", "id": "BREADBLAST06" }`	Should be used to filter on column values. Filters can be applied like `e1.Properties.id has 'BREADBLAST06'`
TimeStamp	Ingestion time stamp
SourceTimeStamp	Source time stamp
TwinId	Twin ID of the node

Edge Property

Note

If a Variable length edge is used, values in edge properties will be listed (values from all the hops) instead of single value.

Property Name	Description	Sample
Name	The name of the relationship	`correspondsToEquipment` or `["correspondsToEquipment"]`
RelationshipId	The unique identifier of the relationship
Source	The source entity TwinId of the relationship
SourceModelId	The model ID of the source entity	`dtmi:digitaltwins:isa95:Equipment;1` or `["dtmi:digitaltwins:isa95:Equipment;1"]`
SourceTimeStamp	The timestamp from the source system
Target	The target entity TwinId of the relationship
TargetModelId	The model ID of the target entity	`dtmi:digitaltwins:isa95:EquipmentActual;1` or `["dtmi:digitaltwins:isa95:EquipmentActual;1"]`
TimeStamp	The timestamp of relationship ingestion

Example - Kusto Graph

Graph() 
| graph-match (mlot)--(mact)--(sres)--(eact)--(eqp) 
    where mlot.ModelId has 'MaterialLot' 
        and mact.ModelId has 'MaterialActual' 
        and sres.ModelId has 'SegmentResponse' 
        and eact.ModelId has 'EquipmentActual' 
        and eqp.ModelId has 'Equipment' 
        and mlot.Properties.id contains 'HYDL3300412' 
    project equipment_id = tostring(eqp.Properties.id), equipment_description = tostring(eqp.Properties.description)

The query can also be written as shown with entity and column filters.

let columnsFilter = dynamic(['id', 'description']);
let entitiesFilter = dynamic(['MaterialLot', 'MaterialActual','SegmentResponse','EquipmentActual','Equipment']);
Graph(interestingEntities=entitiesFilter, interestingNodeProperties=columnsFilter) 
| graph-match (mlot)--(mact)--(sres)--(eact)--(eqp) 
    where mlot.ModelId has 'MaterialLot' 
        and mact.ModelId has 'MaterialActual' 
        and sres.ModelId has 'SegmentResponse' 
        and eact.ModelId has 'EquipmentActual' 
        and eqp.ModelId has 'Equipment' 
        and mlot.Properties.id contains 'HYDL3300412' 
    project equipment_id = tostring(eqp.Properties.id), equipment_description = tostring(eqp.Properties.description)

Kusto GetEquipmentPropertyHistory

The GetEquipmentPropertyHistory function fetches the historical / telemetry values for the given equipment.

GetEquipmentPropertyHistory (
    equipmentId:string,
    startDateTime:datetime,
    endDateTime:datetime
    )

Param	Type	Required	Description
equipmentId	string	Optional	The ID of the equipment for which the telemetry property is to be looked for. If not specified, it retrieves records of all the equipment.
startDateTime	datetime	Optional	Start of the lookup period. If not specified, it defaults to 30 days back.
endDateTime	datetime	Optional	End of the lookup period. If not specified, it defaults to the current date.

Return type - GetEquipmentPropertyHistory

It returns a tabular result with the following columns:

Column	Type	Description
EquipmentId	string	The ID of the equipment.
SourceTimeStamp	datetime	Record time stamp.
ID	string	The ID of the property.
value	string	The value of the property.
valueUnitOfMeasure	string	The unit of measure of the property.
description	string	The description of the property.

Examples - GetEquipmentPropertyHistory

Refer to the following examples for querying the historical data of equipment properties.

Example 1

Query

GetEquipmentPropertyHistory(equipmentId="BREADBLAST07")

Result

EquipmentId	SourceTimeStamp	ID	value	valueUnitOfMeasure	description
BREADBLAST07	2024-10-04T05:46:36.7346756Z	BREADBLAST07_Temperature	212.93	Celsius	Temperature
BREADBLAST07	2024-10-04T05:46:00.814936Z	BREADBLAST07_Temperature	210.57	Celsius	Temperature
BREADBLAST07	2024-10-04T05:45:58.8361173Z	BREADBLAST07_Temperature	212.65	Celsius	Temperature
BREADBLAST07	2024-10-04T05:45:56.0540144Z	BREADBLAST07_Temperature	209.49	Celsius	Temperature
BREADBLAST07	2024-10-04T05:45:53.0300008Z	BREADBLAST07_Temperature	210	Celsius	Temperature

Example 2