Database views
Postgres schema
Every organizationorganization 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 4 views which are present from the start and are not dependant on the configured event types: v_events, v_assets, v_event_types and v_event_type_properties. Two helper functions, assets_by_parent and is_asset_child_of, are created alongside them.
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. The stored values are lowercase: open, incomplete, pending 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 (lowercase) 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: /
- metadata: The asset metadata in JSON form, as used by event configurations and calculations
- 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. The stored values are lowercase: open, incomplete, pending 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
This view only covers periodic properties with datatype number or boolean, and boolean values appear as 1 and 0 rather than true and false. Periodic properties of datatype string do not appear here; use v_{event_type_name}_event_samples_joined, which has no datatype restriction.
v_{event_type_name}_event_samples_joined:
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
- A column for every periodic property configured with the corresponding value at the sample time (if any)