Administration

• Introduction
• Site Management
  • Manage Data Sources
  • Manage Modules
• Audit Trail
• Admin Actions

Introduction

Site Management

As sites are managed centrally and the modules contain site specific records the Delete Event needs to be distributed to the modules, so they can perform corresponding housekeeping tasks (removing old records, etc.). This is achieved by publishing a message to the EventBusExchange the message has the following format:

{
    "Id":1,
    "CleanHistoryTables":true
}

Site configuration records

erDiagram

Site ||--o{ DataSources : "uses"
Site ||--o{ Modules : "has enabled"
DataSources ||--|{ DataTypes: "provides"
Modules ||--o{ DataTypes: "consumes"
ConnectionBridges o|--|| DataSources: "links"

DataTypes  {
 	int(4) ID "UniqueId"
	tinyint(1) DataType "A datatype, see the enumartion for translation"
	nvarchar ModuleName "Name of the microservice/module consuming this datatype"
	int(4) DataSourcesID "Relation to the datasource providing this data type for the module"
	nvarchar Reason "Change reason entered by the user"
	datetime2(8) SysStartTime 
	datetime2(8) SysEndTime 
	int(4) LastModifiedBy 
	int(4) OwnerUserId 
}

DataSources  {
 	int(4) ID "UniqueId"
	nvarchar(500) Name "User defined name of the datasource"
	tinyint(1) Type "Technical implementation type of the datasource e.g. PI used for interface resolution"
	nvarchar ConnectionString "Encrypted connection string to the datasource"
	int(4) SiteID "Relation to the site, where the datasource is assigned to"
	bit(1) IsValidated "Validation status of the datasource"
	datetime2(8) SysStartTime 
	datetime2(8) SysEndTime 
	nvarchar Reason "Change reason entered by the user"
	int(4) LastModifiedBy 
	int(4) OwnerUserId 
}

Site  {
 	int(4) ID "UniqueId"
	nvarchar(500) Name "User defined name of the site"
	nvarchar(500) ShortName "Short name of the site, used to link to RADA"
	nvarchar(500) TimeZone "Timezone of the site, needed for timezone conversion"
	bit(1) IsEnable "Flag if the site can be currently accessed by users/services"
	datetime2(8) SysStartTime 
	datetime2(8) SysEndTime 
	tinyint(1) SiteType "Enumeration of the site classification: DS, DP, etc."
	nvarchar Reason "Change reason entered by the user"
	int(4) LastModifiedBy 
	int(4) OwnerUserId 
}

Modules  {
 	int(4) ID "UniqueId"
	nvarchar(500) ModuleName "Name of the module/microservice"
	nvarchar RConnection "Encrypted connection string to the calculation engine"
	int(4) SiteID "Relation to the site, to enable site depended computation offloading"
	bit(1) IsEnable "Flag if the module is currently enabled at the site"
	bit(1) IsValidated "Flag indicating the qualification status of the calculation engine"
	datetime2(8) SysStartTime 
	datetime2(8) SysEndTime 
	nvarchar Reason "Change reason entered by the user"
	int(4) LastModifiedBy 
	int(4) OwnerUserId 
}

ConnectionBridges  {
 	int(4) ID "UniqueId"
	nvarchar Name 
	tinyint(1) PrimaryConnectionDataType "A datatype enumeration value, see the enumartion for translation"
	int(4) PrimaryConnectionDataSourcesId "Relation to the datasource providing this data type for the module"
	tinyint(1) SecondaryConnectionDataType "A datatype enumeration value, see the enumartion for translation"
	int(4) SecondaryConnectionDataSourcesId "Relation to the datasource providing this data type for the module"
	int(4) SiteId "Relation to the site where the connection bridge is assgined to"
	nvarchar KeyMapping 
	int(4) LastModifiedBy 
	int(4) OwnerUserId 
	datetime2(8) SysStartTime 
	datetime2(8) SysEndTime 
	nvarchar Reason "Change reason entered by the user"
}

Records classification and audit trail

Audit Trail

DCP implements various audit trail types, the following types are known:

• Configuration Audit trail contains all changes to the site/module and data source configuration performed by the instance admin
• Admin Action AuditTrail lists all performed admin actions with respective payloads and response statues
• Security Audit list all successful interactive logins (not successful logins are never reaching DCP as this is monitored inside SSO) and all login attempts performed via the API gateway.
• Module specific the different modules can implement an audit trail is required, the content is up to the module team, however it's recommended to use creating the audit trail on the database layer - utilizing the philosophy described below:

The audit trail philosophy of DCP is based on temporal tables features (also known as system-versioned temporal tables) on the database layer. If enabled all changes to records are tracked on the database level. This is done by adding a second "history" table, which contains the old versions of the records together with a validity range. This ensures that the database can be investigated as of a given time point. The feature is supported by SQL Server 2016 and later. As a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

The Audit trail is then constructed on runtime based on the change tracking in the database. All the records during the time range are queried and grouped by their Ids. Within each grouping the changes are detected and added as an additional row in the audit trail. If the database contains JSON documents - the DeepEquals method is used for comparison.

Audit Trail Records

erDiagram

SecurityAuditTrail  {
 	int(4) ID "UniqueId"
	nvarchar(300) UserName "Username by RADA"
	int(4) UserId "The userId who created the entity"
	int(4) Action 
	datetime(8) Timestamp "Timestamp in UTC"
}

AdminActionAuditTrail  {
 	int(4) ID "UniqueId"
	int(4) UserId "The userId who created the entity"
	int(4) Action 
	datetime(8) Timestamp "Timestamp in UTC"
	nvarchar ActionConfiguration 
	nvarchar Reason "Change reason entered by the user"
	nvarchar Module 
	nvarchar Exception 
}

Records classification and audit trail

Admin actions

External data source change uniqueID changes

The DCP system stores only references to external data sources, such as OSIsoft PI, instead of storing the complete datasets. It saves only the necessary information (unique IDs and ranges) to reconstruct the dataset from the data store. To accommodate potential changes, such as server upgrades that may involve ID changes, DCP implements two key functions:

The first function gathers all references and unique IDs from the source system and provides them as a downloadable JSON file. Users can then translate the IDs outside of DCP, either manually or using support scripts, and upload the updated mapping dictionary back into the system.

The second function iterates through the mapping directory and executes an SQL REPLACE statement for each mapping in the file. This replacement is performed column-wise across both tables of a system-versioned table (current and previous versions). The replacement occurs at the database level using SQL syntax, while the columns and tables to be considered are managed in C# code.

External data source rebuild the global filter

As previously mentioned, DCP only stores references to external data sources. Sometimes it is necessary to store the complete path of a user's selection to an element, referred to as the global filter. OSIsoft PI webIDs, stored in DCP using the IDonly format, do not change even if an element is moved within the external data source. To synchronize the IDs with the global filter after such moves, the rebuild global filter method is implemented. For each stored global filter of a site, the last unique ID in the filter is extracted. Using this element, a recursive call is made to extract the parent and read the details from the data source, building the filter level by level until the global filter root is reached.