# Gain Insights from Queries This guide will help you quickly get started with using the Query Editor of Tricloud Nexus to analyze and gain insights from data efficiently. *** ## Introduction The Tricloud Nexus Query Editor is a powerful tool designed to help users query and analyze data effortlessly. Built on the Azure Data Explorer Query UI, it uses KQL (Kusto Query Language) for efficient data access and exploration. ### Learning Kusto Query Language (KQL) Here are some links to help you get started with the learning the KQL language. * Kusto Query documentation: * Quick Reference Guide: * Kusto Cheat Sheet; *** ### Accessing the Query Editor 1. **Login to Tricloud Nexus**: * Navigate to the Tricloud Nexus platform using your browser. * Enter your credentials and click **Login**. 2. **Open the Query Editor**: * Click on the **Query Editor** tab located in the navigation menu.

3. **Select the Database:** * In the Query Editor, your workspace is pre-configured to access the Azure Data Explorer database. * Ensure you have the necessary permissions to query the database. * Select the database as seen in the example screenshot above, and take notice that the database is selected above the query editor using the convention *cluster/databasename.* * In the example above the selection is: tciotadxcluster.westeurope/TimeSeriesSandbox02 which may differ depending on your installation. *** ### Creating your first Query 1. **Familiarize yourself with KQL**: * The Query Editor uses KQL (Kusto Query Language) to query data. If you're new to KQL, refer to the [#learning-kusto-query-language-kql](#learning-kusto-query-language-kql "mention") 2. **Build Your Query**: * Use the query editor interface to type your KQL commands. * Example: * This Query will show the name all Asset Hierarchies that has been deployed to a device, and sort them alphabetically by the name of the Asset Hierarchy: ```kusto AssetHierarchy | distinct HierarchyName | order by HierarchyName asc ``` 3. **Preview your Query**: * Click **Run** to execute the query and preview the results in the **Results** panel.

*** ## Database Tables The database hosts several tables that are crucial for exploring and analyzing your data. This section provides an overview of the most commonly used tables to help you get started. You can view the database structure by expanding the nodes in the **Database Explorer**. Among the available tables, the most relevant ones include:

The most relevant Tables for gaining Insights can be found in the Database Explorer

* **AssetHierarchy**: This Table contains information from Asset Hierarchies, that has been deployed to a Device. The Table details the organizational structure of deployed assets, featuring the latest version of each node— whether it is an Area or an Asset within any Asset Hierarchy. * **AssetHierarchyMetadata**: This Table contains metadata information from Asset Hierarchies, that has been deployed to a device. The Table provides metadata for all Area/Assets and Tags that has been configured for Asset hierarchies. * **Measurements**: Stores time-series measurements and events associated with your assets. The Table contains the actual metrics that has been collected from your devices. {% hint style="info" %} All timestamps in any Timestamp column is always represented in the Date format ISO8601 as UTC unless otherwise specified {% endhint %} *** ### AssetHierarchy Table This Table contains information from Asset Hierarchies, that has been deployed to a Device. The Table details the organizational structure of deployed assets, featuring the latest version of each node— whether it is an Area or an Asset within any Asset Hierarchy. Running this query, lets you get a list of latest available AssetHierarchies in the Database. ```kusto AssetHierarchy | distinct HierarchyName, HierarchyId, HierarchyVersion | order by HierarchyName asc ``` Beneath is an example result of running the query

Example of Query output showing current Hierarchies in the Database

Now that we can see all available Asset Hierarchies in the Table, we can now refine our search, to only show all Nodes from a specific Asset Hierarchy. Running the following query, will show the Asset Hierarchy nodes for the Asset Hierarchy called "*Odense Factory*". The Query only includes some of the available columns using the *project* operator. ```kusto AssetHierarchy | where HierarchyName == "Odense Factory" | project HierarchicalName, Type, Description, IsDeployed, DeploymentTimestamp, DeviceId | order by HierarchicalName asc ``` Here is an example result of running the query

Showing all Asset Hierarchy Nodes from the Odense Factory Hierarchy

Notice that by ordering the query result by HierarchicalName displays the result exactly as it was Modelled in Tricloud Nexus in that version. Also notice that you can see whether a Node has been deployed, when it was deployed and the device that was targeted for the deployment.

*** ### AssetHierarchyMetadata Table This Table contains metadata information from Asset Hierarchies, that has been deployed to a device. The Table provides metadata for all Area/Assets and Tags that has been configured for Asset hierarchies. All metadata for an Area/Asset or Tag that has been deployed to a device, can found by running the query beneath. The query displays all metadata Key/Value objects for all available Asset Hierarchies. It removes some less important columns from the result (Id, IngestionTime, DataType). It then orders the result by the Type of metadata. ```kusto AssetHierarchyMetadata | project-away Id, IngestionTime, DataType | order by Type asc ``` Here is an example result of running the query

Notice that the first 2 rows are metadata about an Area that seemingly sets a GPS coordinate for the Area. The rest of the rows are metadata about a Tag such as uom (Unit Of Measure), description, ranges etc. You can combine an entire AssetHierarchy with the available metadata for all Area/Assets or Tags into a single query by joining the *AssetHierarchy* and *AssetHierarchyMetadata* Tables. The query combines all metadata available for a specific node into a json document (Key/Value) and stores this in the Metadata column. ```kusto AssetHierarchy | join kind=leftouter AssetHierarchyMetadata on Id | where HierarchyName == "Dallas Factory" | project Id, Name, HierarchicalName, MetadataKey = Key, MetadataValue = Value, DeploymentTimestamp | summarize Metadata = make_bag(pack(MetadataKey, MetadataValue)) by HierarchicalName | order by HierarchicalName asc ``` Result of running Query

Showing the Asset Hierarchy Structure along with all available Metadata for each Area/Asset or Tag

*** ### Measurement Table The Measurement Table stores time-series measurements and events associated with your assets. The Table contains the actual metrics that has been collected from the devices. The following Query, will find all available metrics/measurements that has a StartTimestamp between 17. the dec to 20. the dec. (UTC) for the Tag with the HierarchicalName *OD.Printing.Line.Temperature,* then order the result by StartTimestamp descending: ```kusto Measurements | where StartTimestamp between (datetime('2024-12-17T00:00:00') .. datetime('2024-12-20T00:00:00')) | where HierarchicalName contains "OD.Printing.Line.Temperature" | order by StartTimestamp desc ``` Result of running the Query

The result shows the structure of the Measurement Table, the Table has the following Columns: * **Id -** The Id of the Tag that governs measurements * **HierarcicalName -** ISA95 name for the Tag that governs the measurements * **TagName -** Shorthanded name of the Measurements * **TimeGenerated -** The UTC time the Measurement was generated at the Data Collector * **StartTimestamp -** The UTC start time of the Measurement * **EndTimestamp -** The UTC end time of the Measurement. Many measurements does not have an endtime specifed, denoting that the measurement does not have timespan. * **Type -** Measurement type. Can be either Analog, Digital or String. The Type depends on Tag type that was configured in the Asset Hierarchy. * **Value -** The value of the measurement as a real data type. This will likely only be set for measurements of type Analog or Digital. * **ValueDigital -** The value of the measurement as a boolean data type. This will likely only be set for measurements of type Digital and Analog. The database will automatically try and convert the value of the measurement to a boolean, by converting 1 to true and 0 to false. * **ValueString -** The value of the measurement as a string data type. This will likely only be set for measurements of type String. This is a very flexible data type and can be used for metrics that carries a complex data type such as Json. * **Quality -** The quality of the measurement. The quality of each measurement is set by the data collector on the device, that is used to collect the measurement from the destination data source. If a given measurement has a quality other than the value *good,* it is not recommended to use the measurements for training an AI model. {% hint style="info" %} The Measurement Table is indexed on the StartTimestamp column, which significantly improves Query performance for queries that starts by filtering data on this column. Example: `Measurements` `| where StartTimestamp > ago(2d)` {% endhint %} *** ## Queries Please refer to the KQL query help section for guidance and examples on using the KQL language to explore data in Tricloud Nexus. [queries](https://docs.tricloudnexus.io/management-portal/insights/queries "mention") *** --- # 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/introduction/quick-start-guide/gain-insights-from-queries.md?ask= ``` 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.