Database views

Postgres schema

Every organization in Factry Historian will have a corresponding schema in the postgres database with the same name. A postgres user which can only access the schema is also created when creating an organization. This user can only access the views and functions present in this schema, by default its search path is set to this schema. Renaming the organization will also rename this schema.

Views

There are 2 views which are present from the start and are not dependant on the configured event types .

Events view

This view can be used to get basic information of all the events in the organization.

It contains the following columns:

event_uuid: The UUID of the event.
asset_uuid: The UUID of the asset of the event
event_parent_uuid: The UUID of the parent event, may be null if no parent is configured
event_type: The name of the event type of the event
event_type_uuid: The UUID of the event type of the event
asset_path: The asset path of the asset of the event
asset_name: The name of the asset of the event
asset_description: The description of the asset of the event
start_time: The start time of the event
stop_time: The stop time of the event
start_time_epoch: The unix milliseconds of the start time of the event
stop_time_epoch: The unix milliseconds of the stop time of the event
status: The status of the event (Open, Incomplete or Processed)
source: The source of the event (auto or manual)
properties: The properties of the event in JSON form
duration: The duration of the event in seconds

Assets view

This view returns all assets in the organization.

It contains the following columns:

uuid: The UUID of the asset
name: The name of the asset
description: The description of the asset
status: Will be Deleted if the asset is deleted
parent_uuid: The UUID of the parent asset, NULL if the asset has no parent
asset_path: The full asset path
created_by: The UUID of the user that created the asset
created_at: The timestamp when the asset was created
updated_by: The UUID of the user that last updated the asset
updated_at: The timestamp when the asset was last updated
attributes: /
organization_uuid: The UUID of the organization the asset is part of

Generated views

All event types will have up to 3 views generated depending on the configured properties.

All these views will have the following columns:

event_uuid: The UUID of the event
event_parent_uuid: The parent UUID of the event
asset_uuid: The UUID of the asset of the event
asset_path: The full asset path of the asset of the event
asset_name: The name of the asset of the event
asset_description: The description of the asset of the event
start_time: The start time of the event
stop_time: The stop time of the event
start_time_epoch: The unix milliseconds of the start time of the event
stop_time_epoch: The unix milliseconds of the stop time of the event
status: The status of the event (Open, Incomplete or Processed)
source: The source of the event (auto or manual)
duration: The duration of the event in seconds

All event types will have the following corresponding view.

v_{event_type_name}_event_properties:

This view has an extra column for every configured simple event type property.

v_{event_type_name}_event_samples:

This view has the following extra columns:

time_offset: The offset in seconds from the start time of the sample time of the periodic value
sample_time: The sample time of the periodic value
sample_time_epoch: The unix milliseconds of the sample time
name: The name of the corresponding periodic property
value: The value of the corresponding periodic property at the sample time

v_{event_type_name}_event_samples_joined: