Intermediate Queries

Intermediate Queries

In this section, we'll cover some intermediate operators used in KQL queries, to help you begin exploring data in Tricloud Nexus.

Let, join operators

The query below retrieves data related to a specific asset hierarchy node ("DA.IM.P103.Line.OrderCompletion") from the AssetHierarchy and Measurements tables. It enriches the measurements data with metadata by joining it with the AssetHierarchyMetadata table and organizes the metadata into a structured bag format. The result of the first part of the query, is stored as a variable called metadata using the let operator. The second part then joins metadata with Measurements. The query filters measurements from the last 50 days, calculates the delivered items from dynamic properties, and selects relevant fields, limiting the output to 1,000 records sorted by the start timestamp. Using the todynamic operator lets you parse contents as json, and reference properties of the json, which is done in the query in this line toint(properties.deliverQuantity).

let metadata = AssetHierarchy
| where HierarchicalName has "DA.IM.P103.Line.OrderCompletion"
| join kind=leftouter AssetHierarchyMetadata on Id
| where HierarchicalName has "DA.IM.P103.Line.OrderCompletion"
| project Id, Name, HierarchicalName, MetadataKey = Key, MetadataValue =  Value, Type
| summarize Metadata = make_bag(pack(MetadataKey, MetadataValue)) by HierarchicalName, Type
| order by HierarchicalName asc
| project HierarchicalName, Type, Metadata;
Measurements
| where StartTimestamp > ago(50d)
| where HierarchicalName has "DA.IM.P103.Line.OrderCompletion"
| join kind=leftouter metadata on HierarchicalName
| extend properties = todynamic(ValueString)
| extend DeliveredItems = toint(properties.deliverQuantiy)
| project StartTimestamp, HierarchicalName, DeliveredItems, Quality, Metadata
| order by StartTimestamp asc
| take 1000

Here is the result of running the Query:

Summarize operator

The summarize operator can be used to produce a new table that aggregates certain values of the input table.

The following Query filters the Measurements to include all Measurements from the Line (Regardless of Tag name), it then uses the summarize operator to group the measurements by HierarchicalName and perform a Count() on each group.

Measurements
| where HierarchicalName startswith "DA.IM.P103.Line"
| summarize Count = count() by HierarchicalName

Here is the result of running the Query:

The following Query produces a time chart showing the average number of items delivered every 1 day (grouped by HierarchicalName) between December 1, 2024, and December 20, 2024. It first defines parameters for the query using the let operator. It then selects all measruments for the Tag called ("DA.IM.P103.Line.OrderCompletion") and filters its measurements in the timespan from startTime to endTime. It converts the payload of the Tag (ValueString) into a dynamic, to parse the value of the json property deliverQuantity into an integer. It uses the summarize operator, to group all measurements into bins of 1 day using the bin operator to specify the binning column StartTime, and the timespan of each group using the totimespan(resolution). Finally it projects the columns StartTimestamp and AverageItemsDelivered and rounds the AverageItemsDelivered to 2 decimals. Using the timechart operator, will draw the result as a timechart.

let startTime = datetime("2024-12-01 00:00:00");
let endTime = datetime("2024-12-20 00:00:00");
let resolution = 1d;
let tagName = "DA.IM.P103.Line.OrderCompletion";
Measurements
| where StartTimestamp > startTime and StartTimestamp < endTime
| where HierarchicalName has tagName
| extend properties = todynamic(ValueString)
| extend DeliveredItems = toint(properties.deliverQuantiy)
| summarize AverageItemsDelivered = avg(DeliveredItems) by bin(StartTimestamp, totimespan(resolution)), HierarchicalName
| project StartTimestamp, AverageItemsDelivered = round(AverageItemsDelivered, 2)
| render timechart

Here is the result of running the Query:

Make-series operator

The following query uses the make-series to aggregate the average number of delivered items per. day and the minimum/maximum orders delivered in an order each day between October 1, 2024 to February 1, 2025. The main difference between using the make-series operator instead of the summarize operator in the previous example above, lies in how they handle time-based grouping and gaps in data. The summarize operator does not assume or enforce continuity in time intervals, but the make-series operator always generate a continuous time series, and fills any missing values with user defined default value. In the example below it defaults any missing data in a bin to 0 using the statement default=0.

let startTime = datetime("2024-10-01 00:00:00");
let endTime = datetime("2025-02-01 00:00:00");
let resolution = 1d;
let tagName = "DA.IM.P103.Line.OrderCompletion";
Measurements
| where StartTimestamp > startTime and StartTimestamp < endTime
| where HierarchicalName has tagName
| extend properties = todynamic(ValueString)
| extend DeliveredItems = toint(properties.deliverQuantiy)
| make-series avg(DeliveredItems), min(DeliveredItems), max(DeliveredItems) default=0 on StartTimestamp from startTime to endTime step totimespan(resolution)
| project StartTimestamp, avg_DeliveredItems, min_DeliveredItems, max_DeliveredItems
| render timechart

Here is the result of running the Query: