Skip to content

Query API Documentation

The Query API provides access to telemetry data, device information, and plant topology from Bühler Insights. Built on the powerful Kusto Query Language (KQL), it enables both raw data retrieval and real-time aggregation capabilities.

Performance Recommendation

For optimal performance, downsample data to the finest granularity needed for your use case. For continuous raw data export, consider using the Data Export Engine instead.

📊 Available Tables

The Query API provides access to several key data tables:

  • States - Real-time and historical state data from devices
  • Events - Events, alarms, and notifications
  • Devices - Device registry
  • DataPoints - Data point definitions and metadata
  • Machines - Machine specifications and details
  • Plants - Plant locations and configuration

📈 States Table

The states table contains information about the state of data points from devices within the system. Each row in the table represents a single state of a data point, and includes information such as the device ID, the agent ID, the timestamp of the state, and the value of the state. The table also includes columns for the plant ID, the name ID, the name of the data point, and any associated data metadata.

ColumnName ColumnOrdinal DataType ColumnType Description Example
Uid 0 System.String string Unique identifier of the device 963e86fc-eed2-4236-bcd4-b5b251d0a54a_qms-milb_flour1
DeviceId 1 System.String string Identifier for the device given by the source of the data QMS-MILB_Flour1
AgentId 2 System.String string Identifier for the agent 963e86fa-eed2-4236-bcd4-b5b251d0a54a
AgentTimestamp 3 System.DateTime datetime Timestamp when processed by the agent 2022-01-01T00:00:00Z
IngestionId 4 System.String string Identifier for the ingestion Telemetry
IngestionTimestamp 5 System.DateTime datetime Timestamp of the ingestion 2022-01-01T00:00:00Z
MessageId 6 System.String string Identifier for the message message-001
MessageType 7 System.String string Type of the message state
MessageVersion 8 System.String string Version of the message 1.0
MessageMetadata 9 System.Object dynamic Denormalized dimensions stored as JSON key-value pairs. Contains contextual metadata about the message, including both static attributes (topology-reference) and dynamic operational context (recipe, job) {"recipe": "Recipe_A1", "topology-reference": "Line1/Zone2", "operator": "John.Doe"}
PlantId 10 System.String string Identifier for the plant 1123333
NameId 11 System.String string Unique and opaque identifier for the data point name eb786a6eab0ec0cd2e3a23930fcaac3bd0aa26167079b4bac431bcd95ce1c75d
Name 12 System.String string Name of the data point temperature
DataMetadata 13 System.Object dynamic Denormalized dimensions stored as JSON key-value pairs. Contains data-specific contextual metadata such as job identifiers, quality grades, and operational parameters {"job": "Job_2024_001", "quality-grade": "A", "batch": "B240613"}
Unit 15 System.String string Unit of the data point rpm
Value 16 System.Object dynamic Value of the data point 25.0
DeviceTimestamp 17 System.DateTime datetime Timestamp of the device 2022-01-01T00:00:00Z
Timestamp 18 System.DateTime datetime Timestamp which is a fallback of device -> agent -> ingestion timestamp 2022-01-01T00:00:00Z
Source 19 System.String string Source of the data point Edge
LocalAgentTimestamp 20 System.DateTime datetime Local timestamp of the agent 2022-01-01T00:00:00-05:00
LocalIngestionTimestamp 21 System.DateTime datetime Local timestamp of the ingestion 2022-01-01T00:00:00-05:00
LocalDeviceTimestamp 22 System.DateTime datetime Local timestamp of the device 2022-01-01T00:00:00-05:00
LocalTimestamp 23 System.DateTime datetime Local timestamp which is a fallback of device -> agent -> ingestion timestamp 2022-01-01T00:00:00-05:00

🚨 Events Table

The events table contains information about events that have occurred within the system. Each row in the table represents a single event, and includes information such as the device ID, the agent ID, the timestamp of the event, and the severity of the event. The table also includes columns for the state of the event, whether it has been acknowledged, and any associated alarm ID or reason.

ColumnName ColumnOrdinal DataType ColumnType Description Example
Uid 0 System.String string Unique identifier of the device 963e86fc-eed2-4236-bcd4-b5b251d0a54a_nos_0011790040_00001_pcs
DeviceId 1 System.String string Identifier for the device given by the source of the data nos_0011790040_00001_pcs
AgentId 2 System.String string Identifier for the agent 963e86fc-eed2-4236-bcd4-c3b251d0a54a
AgentTimestamp 3 System.DateTime datetime Timestamp of the agent 2022-01-01T00:00:00Z
IngestionId 4 System.String string Identifier for the ingestion Telemetry
IngestionTimestamp 5 System.DateTime datetime Timestamp of the ingestion 2022-01-01T00:00:00Z
MessageId 6 System.String string Identifier for the message 863b68ba-6247-44eb-b6a5-a54699da410c
MessageType 7 System.String string Type of the message state
MessageVersion 8 System.String string Version of the message 1.0
MessageMetadata 9 System.Object dynamic Denormalized dimensions stored as JSON key-value pairs. Contains contextual metadata about the message, including both static attributes (topology-reference) and dynamic operational context (recipe, job) {"recipe": "Recipe_A1", "topology-reference": "Line1/Zone2", "operator": "John.Doe"}
PlantId 10 System.String string Identifier for the plant 1123333
NameId 11 System.String string Identifier for the name 626ad8b8a910125da3e82283f0b362967c0fc97783dfde1be3d152ce265ff92b
Name 12 System.String string Name of the data point temperature
DataMetadata 13 System.Object dynamic Denormalized dimensions stored as JSON key-value pairs. Contains data-specific contextual metadata such as job identifiers, quality grades, and operational parameters {"job": "Job_2024_001", "quality-grade": "A", "batch": "B240613"}
Unit 15 System.String string Unit of the data point rpm
Value 16 System.Object dynamic Value of the data point 25.0
DeviceTimestamp 17 System.DateTime datetime Timestamp of the device 2022-01-01T00:00:00Z
Timestamp 18 System.DateTime datetime Timestamp which is a fallback of device -> agent -> ingestion timestamp 2022-01-01T00:00:00Z
Source 19 System.String string Source of the data point Edge
LocalAgentTimestamp 20 System.DateTime datetime Local timestamp of the agent 2022-01-01T00:00:00-05:00
LocalIngestionTimestamp 21 System.DateTime datetime Local timestamp of the ingestion 2022-01-01T00:00:00-05:00
LocalDeviceTimestamp 22 System.DateTime datetime Local timestamp of the device 2022-01-01T00:00:00-05:00
LocalTimestamp 23 System.DateTime datetime Local timestamp which is a fallback of device -> agent -> ingestion timestamp 2022-01-01T00:00:00-05:00

🖥️ Devices Table

Definition: Device

Devices are physical or virtual entities that collect data and connect to sensors within the Bühler Insights ecosystem. These can include:

  • Plant automation systems - Industrial control systems managing production processes
  • OPC UA servers - Communication servers providing standardized data access
  • Software applications - Programs running behind message brokers or communication middleware
  • Edge devices - Local computing units collecting and preprocessing sensor data
  • Virtual devices - Software representations of physical systems or aggregated data sources

Each device serves as a data collection point, gathering telemetry from sensors and equipment to provide visibility into plant operations and machine performance.

The devices table contains the registry of all connected devices within the Bühler Insights ecosystem. This table serves as the master catalog for device identification across all plants.

ColumnName ColumnOrdinal DataType ColumnType Description Example
PlantId 0 System.String string Unique identifier for the plant where the device is located PLANT_001
DeviceId 1 System.String string Unique identifier for the device within the plant DEVICE_QMS_001
DeviceFriendlyName 2 System.String string Human-readable name for the device Quality Management System - Line 1
Uid 3 System.String string Universal unique identifier for the device 963e86fc-eed2-4236-bcd4-b5b251d0a54a
DeviceIsActive 4 System.SByte bool Indicates whether the device is currently active and operational true

📊 DataPoints Table

The datapoints table defines all available data points across devices, providing metadata and configuration information for each measurable datapoint in the system.

ColumnName ColumnOrdinal DataType ColumnType Description Example
PlantId 0 System.String string Unique identifier for the plant 0022990050
DeviceId 1 System.String string Unique identifier for the device DEVICE_QMS_001
MachineNumber 2 System.String string Machine number associated with the data point 000000000700162711
Uid 3 System.String string Universal unique identifier for the data point c6ee764a-e826-45de-b368-a2547128b603_b-4110
NameId 4 System.String string Unique identifier for the data point name d9c602cad6ac2712184043eb579c092b1118749344d734447032a8f119867df7
Name 5 System.String string Technical name of the data point motor_temperature
FriendlyName 6 System.String string Human-readable name for the data point Motor Temperature Sensor
Address 7 System.String string Physical or logical address of the data point PLC.DB1.DBD100
LogicalPath 8 System.String string Hierarchical path representing the data point's location /Plant/Line1/Motor/Temperature
LogicalName 9 System.String string Logical name within the system hierarchy Line1.Motor.Temperature
Unit 10 System.String string Unit of measurement for the data point °C
DataPointFirstTimestamp 11 System.DateTime datetime Timestamp when the data point first reported data 2022-01-01T00:00:00Z

⚙️ Machines Table

The machines table contains detailed information about all machines within the production facilities.

ColumnName ColumnOrdinal DataType ColumnType Description Example
PlantId 0 System.String string Unique identifier for the plant 0022990050
MachineNumber 1 System.String string Combination of numbers which designates a machine 000000000700162711
MachineFriendlyName 2 System.String string Human-readable name for the machine My Machine
ProductId 3 System.String string Product identifier associated with the machine CUBE-00023-387
Chiffre 4 System.String string Bühler-internal code, which is made up of minimum three and maximum four uppercase letters, and used to identify machines, control systems, documents, procedures etc. MRRA
Type 5 System.String string Machine type or category 4-1250/250
BuildYear 6 System.Int32 int Year when the machine was manufactured 2020

🏭 Plants Table

The plants table contains comprehensive information about all production facilities.

Definition: Plant

Assembly of different systems on a specific site. Represents a complete production process on a customer site for a specific product. May comprise one or more production lines.

ColumnName ColumnOrdinal DataType ColumnType Description Example
PlantId 0 System.String string Unique identifier for the plant 0022990050
PlantName 1 System.String string Official name of the production facility Bühler Mill
PlantTimeZoneId 2 System.String string Time zone identifier for the plant location Europe/Zurich
PlantLatitude 3 System.Double real Geographic latitude coordinate of the plant 47.3769
PlantLongitude 4 System.Double real Geographic longitude coordinate of the plant 8.5417
PlantStreet 5 System.String string Street address of the plant Gupfenstrasse 5
PlantHouseNumber 6 System.String string House number of the plant address 5
PlantZip 7 System.String string Postal code of the plant location 9240
PlantCity 8 System.String string City where the plant is located Uzwil
PlantCountryCode 9 System.String string ISO country code for the plant location CH
PlantIsActive 10 System.SByte bool Indicates whether the plant is currently operational true

🔍 Query Examples

Here are some common query patterns to help you get started:

Basic Data Retrieval

// Get latest states for all devices in a specific plant
States
| where PlantId == "0022990050"
| where Timestamp > ago(1h)
| summarize arg_max(Timestamp, *) by DeviceId, Name

Device Information

// List all active devices with their friendly names
Devices
| where DeviceIsActive == true
| join kind=inner Plants on PlantId
| project DeviceId, DeviceFriendlyName, PlantName, PlantCity

Machine Analytics

// Get machine information with data point counts
Machines
| join kind=leftouter (
    DataPoints
    | summarize DataPointCount = count() by MachineNumber, PlantId
) on MachineNumber, PlantId
| project MachineNumber, MachineFriendlyName, Type, BuildYear, DataPointCount

🏗️ Data Architecture & Usage Patterns

Metadata as Denormalized Dimensions

The telemetry data in States and Events tables includes metadata fields that function as denormalized dimensions. These metadata fields are stored directly with each telemetry record, enabling efficient filtering and aggregation without requiring joins to separate dimension tables.

Understanding Metadata Types

MessageMetadata and DataMetadata are JSON objects containing key-value pairs that provide contextual information about each telemetry record. Based on real plant deployments, metadata can be categorized as:

  • Static/Infrastructure Metadata - Well-known, relatively unchanging attributes:
    • topologyReference: References to plant topology elements (machineNumber)
    • machineNumber, machineId: Machine identification and specifications
    • lineIdent, lineName: Production line identifiers
    • sectionIdent, sectionName: Equipment section references
    • deviceId, deviceIdent: Device identification
    • datapointAddress: Physical address of the data point
    • plantPart.en, plantPart.local: Plant area classifications
  • Operational/Dynamic Metadata - Context that changes frequently during operations:
    • jobIdent, jobName: Active job or batch identifiers
    • recipeIdent, recipeName, recipeVersion: Production recipe information
    • batchNr, batchNumber: Batch tracking identifiers
    • productID, productIdent, productName: Product being manufactured
    • orderNumber, salesOrder: Order management references
    • jobStart, jobEnd, jobDuration: Job timing information
  • Quality & Process Metadata - Production quality and process control context:
    • qDataState, qDataAccepted, qDataClosed: Quality data status
    • qualityDataLogIdent: Quality tracking references
    • totalWeight, netWeightWeighbridge: Weight measurements
    • classification: Product classification results
    • target, upper_sd, lower_sd: Quality control limits
    • outOfLimit, threshold_exceeded: Quality deviation flags
  • Equipment State Metadata - Real-time equipment and sensor status:
    • activeState, objectState: Equipment operational state
    • rollSpeed, rollNumber: Mill roll operational parameters
    • temperaturePosition, temperatureAmplitude: Sensor readings
    • CalibrationState, CalibrationInProgress: Equipment calibration status
    • statusCode, statusName, statusDescription, stCode: Equipment status information

Usage Examples

// Filter by recipe and job (operational metadata)
States
| where MessageMetadata.recipeIdent == "Recipe_Mill_001"
| where DataMetadata.jobIdent == "Job_2024_0613_001"
| where Timestamp > ago(1h)

// Aggregate temperature data by machine and batch
States
| where Name contains "temperature"
| where DataMetadata.batchNr != ""
| summarize 
    AvgTemp = avg(todouble(Value)),
    MaxTemp = max(todouble(Value))
by 
    tostring(MessageMetadata.machineNumber),
    tostring(DataMetadata.batchNr),
    bin(Timestamp, 15m)

// Analyze quality events by product and line
Events
| where MessageMetadata.qDataState != ""
| where DataMetadata.productIdent != ""
| summarize 
    EventCount = count(),
    QualityAccepted = countif(tostring(MessageMetadata.qDataAccepted) == "true")
by 
    tostring(DataMetadata.productIdent),
    tostring(MessageMetadata.lineIdent),
    tostring(MessageMetadata.qDataState)

// Monitor equipment status across topology
States
| where MessageMetadata.topologyReference != ""
| where DataMetadata.activeState != ""
| summarize arg_max(Timestamp, *) by 
    tostring(MessageMetadata.topologyReference),
    tostring(MessageMetadata.machineId)
| project 
    TopologyRef = tostring(MessageMetadata.topologyReference),
    MachineId = tostring(MessageMetadata.machineId),
    ActiveState = tostring(DataMetadata.activeState),
    StatusCode = tostring(DataMetadata.statusCode),
    LastUpdate = Timestamp

// Track production efficiency by recipe and order
States
| where MessageMetadata.recipeIdent != ""
| where DataMetadata.orderNumber != ""
| where Name == "production_rate"
| summarize 
    AvgProductionRate = avg(todouble(Value)),
    TotalDuration = max(Timestamp) - min(Timestamp)
by 
    tostring(MessageMetadata.recipeIdent),
    tostring(DataMetadata.orderNumber),
    tostring(MessageMetadata.lineIdent)

Dimensional vs Fact Tables

The Query API follows a dimensional modeling approach:

  • Fact Tables (Primary): States and Events contain the high-volume telemetry data and should be your primary data source for analytics
  • Dimension Tables: Devices, DataPoints, Machines, and Plants serve as lookup/reference tables to enrich fact data with contextual information
  • Flexible Usage: While dimension tables are primarily for lookups, they can also serve as fact tables when analyzing configuration changes or device lifecycle events

Performance Best Practices

Use lookup Operator for Dimension Enrichment

When enriching telemetry data with dimensional information, prefer the lookup operator over join for better performance: 

States
| where Timestamp > ago(1h)
| lookup kind=leftouter Devices on DeviceId
| lookup kind=leftouter Plants on PlantId

Query Performance Tips

  • Always filter by time range when querying States or Events tables
  • Use PlantId filters early in your queries for better performance
  • Prefer lookup over join when enriching with dimension tables
  • Consider using summarize operations to reduce data volume
  • Use dimension tables for filtering and grouping operations
  • Cache frequently used dimension data in your applications
{"environments":[],"pdf-download":true}