# 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).

```kusto
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:

<figure><img src="/files/FiPpe1VZUz47lxMV2u5K" alt=""><figcaption><p>Measurement and Metadata is joined</p></figcaption></figure>

***

### 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.

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

Here is the result of running the Query:

<figure><img src="/files/P2bIG1mzeclxSdfYMgs8" alt=""><figcaption><p>Use summarize to count measurements per. Tag from a Line</p></figcaption></figure>

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.

```kusto
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:

<figure><img src="/files/9JYhzF7pNuJmjazt9j8u" alt=""><figcaption><p>The summarize operator can be used to aggregate bins of data using several operators</p></figcaption></figure>

***

### 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.*

```kusto
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:\\

<figure><img src="/files/4JEiNkfNbH5WkXswO95d" alt=""><figcaption><p>Make-series operator can be used to visualize data regardless of whether data exists in bins</p></figcaption></figure>

***


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tricloudnexus.io/management-portal/insights/queries/creating-a-query/intermediate-queries.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.