Basic Queries

Basic Queries

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

Where, order by, take operators

Here's a query example showing how to filter measurements using the Where operator on both timespan and HierarchicalName. The Order by operator sorts these measurements in ascending order based on StartTimestamp. The take operator limits the output to the first 1000 rows (alternatively, you can use the limit operator).

Measurements
| where StartTimestamp > ago(1d)
| where HierarchicalName has "DA.IM.CoolingStorage.Temp"
| order by StartTimestamp asc
| take 1000

Here is the result of running the query:

The query above filters data on time using the ago(1d) expression. The following examples are alternatives ways of filtering by time:

| where StartTimestamp > ago(1h) // Include measurements less than an hour old
| where StartTimestamp > ago(1m) // Include measurements less than a minute old
| where StartTimestamp > datetime('') // Include measurements less than an hour old
| where StartTimestamp > datetime('2025-01-16T12:00:00.000000Z') // Include measurements that is older than January 16. th 2025 12:00 UTC

Project, project-away operators

This query example shows you how to query Measurements, then using the project operator to only include the columns HierarchicalName, TagName, StartTimestamp, Value in the result.

Measurements
| where StartTimestamp > ago(1d)
| where HierarchicalName has "DA.IM.CoolingStorage.Temp"
| order by StartTimestamp asc
| project HierarchicalName, TagName, StartTimestamp, Value
| limit 1000

Here is the result of running the query:

The project-away operator can be used, to exclude columns from the result, instead of using the project operator to specifically include the columns.

Measurements
| where StartTimestamp > ago(1d)
| where HierarchicalName has "DA.IM.CoolingStorage.Temp"
| order by StartTimestamp asc
| project-away Id, TimeGenerated, EndTimestamp, Type, ValueDigital, ValueString
| limit 1000

Here is the result of running the query:

Extend operator

The extend operator is used below, to generate new columns in the result, and calculate row values for the extended columns.

Measurements
| where StartTimestamp > ago(1d)
| where HierarchicalName has "DA.IM.CoolingStorage.Temp"
| project StartTimestamp, HierarchicalName, Value
| extend AdxRowInsertTime = ingestion_time()
| extend DurationDifference = AdxRowInsertTime - StartTimestamp
| extend FormattedDuration = format_timespan(DurationDifference, 'ddd.h:mm:ss [fffffff]')
| take 1000

The query above starts by finding measurements less than a day old from the CoolingStorage.Temp sensor. It then projects only the columns StartTimestamp, HierarchicalName and Value columns. It extends the result with 3 new columns.

• AdxRowInsertTime will contain the exact UTC timestamp, that the row was created in the Database.

• DurationDifference Substracts the StartTimestamp from the AdxRowInsertTime and then stores the timespan in the column.

• FormattedDuration Formats the timespan datatype of the DurationDifference column and stores a strinng representation of the timespan.

Here is the result of running the query: