Database Tables

The schemas and tables Collection stores Devices, Observations, Assets, and Connection Status in.
MTConnect Server Docs Menu

Tables Profiles store the data of their Devices in SQLite, PostgreSQL, or SQL Server. The tables are created automatically, organized in schemas, so reporting and analytics tools can query them directly. In SQLite, which has no schemas, each schema is its own database file next to the database.

Schemas

SchemaContents
mtconnect_devicesThe Device models: Devices, Components, DataItems, and their Compositions, Descriptions, Configurations (ex. Coordinate Systems, Motion, Sensor Configuration, Specifications), Definitions, Constraints, and Relationships
mtconnect_observationsObservations by type, plus the latest value of each DataItem in the current_* tables
mtconnect_assetsMTConnect Assets
mtconnect_connectionsEach Device's connection status and status_history
mtconnect_statisticsObservation statistics by Device and DataItem, and Data Retention statistics
mtconnect_definitionsThe MTConnect Standard's Component, Composition, and DataItem Types, with DataItem SubTypes and Values, to describe the stored data

Observations

Each Observation is stored in the table for its DataItem's Category and Representation. Observations are written in batches, and the current_* tables are kept up to date, so the latest value of every DataItem can be read with a single query.

History TableCurrent TableDataItems
sample_valuecurrent_sample_valueNumeric SAMPLEs (ex. positions, temperatures, loads)
sample_time_seriescurrent_sample_time_seriesSAMPLEs with the TIME_SERIES Representation
event_valuecurrent_event_valueEVENTs (ex. Execution, Program, Part Count)
conditioncurrent_conditionCONDITIONs (alarms), with their state (level), native code, native severity, qualifier, and message (result)

Every Observation has its Device UUID, DataItem ID, timestamp, and the Agent's instance ID and sequence. Data Retention deletes old rows from the history tables, and never from the current_* tables.

Assets

Tables Profiles store each Device's MTConnect Assets (ex. Cutting Tools, Files, Raw Materials) unless Store Assets is turned off for the Profile. The mtconnect_assets.asset table keeps the latest version of each Asset (by Device UUID and Asset ID), with its Type, Timestamp, Removed flag, and the full Asset as MTConnect XML, so every Asset Type is supported.

Example Queries

Columns are named in snake case (ex. device_uuid, data_item_id). The latest Execution state of every Device, found by joining the DataItems of the Device models:

select o.device_uuid, o.data_item_id, o.result, o.timestamp
from mtconnect_observations.current_event_value o
join mtconnect_devices.data_item d on d.device_uuid = o.device_uuid and d.id = o.data_item_id
where d.type = 'EXECUTION';

The active alarms (Conditions whose state is WARNING or FAULT):

select device_uuid, data_item_id, state, native_code, result, timestamp
from mtconnect_observations.current_condition
where state in ('WARNING', 'FAULT');
To read the data from an application instead of a database connection, use the REST API, which reads the same tables.