Averaged data: A<TimeSpanId?>P<ParameterId>
time: [datetime2](2)
value: float
Allow NULL:  
min: float  
max: float
avg: float
std: float
mark: smallint

SQL Database Setup

Setting up the database for CsLog2020 is simple - the ConSys code creates all parameter tables as needed. The only table that needs to be set up manually is the identification table ‘CsLog2020’. As mentioned above this table has two fields, ‘logDbId’ and ‘note’. One and only one row should be added to this table – with the ‘logDbId’ id set to the unique database id defined in the configuration database CsLog2020 table.

Creating a new database for CsLog2020

Example of creating a new log database:

Database server: ISASVR13
Log Database name: CsLog2020Phys
Initial Size: 100 MByte
Grow by: 100 Mbyte
Log: Initial size: 10 Mbyte
Default Options - except:
Recovery model: Simple

Also set up a backup schema - could be a weekly full backup and a daily differential backup - and a cleanup of backup files older than 2 weeks.

Script for creating the CsLog2020 table:

CREATE TABLE [dbo].[CsLog2020]( 
    [logDbId] [int] NOT NULL, 
    [note] [varchar](255) NULL 
) ON [PRIMARY] 
GO 

Setting security on the CsLog2020 database

The security settings on the database must be set for the different kind of user groups of the database. It is recommended to use dedicated Windows domain groups for the different log server user groups:

SQL Administrators: role
db_owner

Users that should log to database, (like ‘UNI\svc_isa-consys’), member of roles:
db_datareader
db_datawriter
db_ddladmin (needed to be able to create new log tables)

Users that should access the log database for retrieval only – member of role
db_datareader

ConSys configuration database tables

The log source/destination, log options and other CsLog2020 related configurations are stored in the ConSys configuration database. Each ConSys parameter to be logged must be assigned to a log group defining the log source (device/application) and the log database to find the loggings in. The id of the log group that a parameter is assigned to is given by ‘logGroupId’ in the ‘Parameter’ table. The log group options for this group are defined in the ‘LogGroupTable’. One of the fields in the ‘LogGroupTable’ is ‘LogDatabaseId’ – defining the database on which to find the logged parameters for use in applications that needs to retrieve the logged data. Remark that the ‘LogDatabaseId’ in the ‘LogGroupTable’ is not used to define were log data is written to. Log data writings go to the log database that is defined by ‘CsLog2020LogDbId’ in the computer table. This splitting has been made for two reasons: First, the implementation of writing/logging should be to the same log database for all devices on a ConSysLoader instance. Secondly, this choice makes it possible and easy to let ‘operational’ computers, like the FEC’s, write to the operational log database, while debug copies of devices running on development computers can write to other ‘Test’ log databases. Typical. a development computer will be set up to write all loggings to a test log database. Applications, like the Console and CsPlot will by default get log data from the operational log database defined with the log group. It is however possible to select another source log database fro retrival in CsPlot and Console.In this way test and development can be made quite safe by never letting a development computer be set up to write to an operational log database.

LogGroup table (full def.):

Log source types:
  0: CSLOG20_LOGTYPE_NOT_LOGGED - The parameter is not logged.
  1: CSLOG20_LOGTYPE_DEVICE - The parameter is logged by a device.
  2: CSLOG20_LOGTYPE_DATALOGGER - he parameter is logged by the DataLogger.
  --- All other values than the values above is also threaded as 0, 'Not Logged'.

CsLog2020Server table (full def.):

Log database server types:
  0: CSLOG20_SERVER_TYPE_UNDEF - Server type 0 and all other values apart from
      the values below are treaded as undefined server type.
  1: CSLOG20_SERVER_TYPE_OPERATIONAL - This is a operational (work) log database.
      Loggings of parameter to this type of database should only be made if log source conditions are fullfilled.
  2: CSLOG20_SERVER_TYPE_TEST - This is a test database used for development etc.
      Log conditions are relaxed for this type, typical all parameters can be logged to this database type independend on source conditions.
  3: CSLOG20_SERVER_TYPE_TEST_OPERATIONAL - This is a test database used for development etc.
      Treat log conditions as operational database.

Computer table (CsLog2020 related fields):

Parameter table (CsLog2020 related field):

ConSys classes

CLog20Buffer
  CLog20Buffer10
  CLog20Buffer100
  CLog20Buffer1000

The CLog20Buffer derived classes are buffer classes that are used to store logged values until they are written to the ODBC database. Each CLog20Parameter has a CLog20Buffer to store its logged values. When the buffer becomes full - and/or at regular intervals - the CLog20Parameter class sends the CLog20Buffer to the write que in CLog20HistoryManager and allocates a new CLog20Buffer.

CLog20Parameter

The CLog20Parameter class handles logging for a single ConSys parameter. The logged data are added to an active CLog20Buffer. If the buffer becomes full, the buffer is added to the CLog20HistoryManager write que for writing to the ODBC log database – and a new active buffer is created. The CLog20Parameter class also handles time marks and standard history marks.

CLog20HistoryManager

Handles SQL write histories for a set of parameters - either belonging to a device or an application. The main purpose of this class is managing a set of log histories stored in an array. Works together with the CLog20DatabaseManager, that is the class doing the actual writing to the SQL database, and CLog20Parameter that handles logging for a single ConSys parameter.

CLog20DatabaseManager

This class exist in a single instance and handles all writing to the CsLog2020 database for an application or a ConSysLoader service/program. CLog20DatabaseManager works together with the CLog20HistoryManager class holding histories collecting data for a given part of the code - a device, an application or part of application. This class contains the thread actual connecting and writing to the log database.

CLog20SQLWrite

CLog20SQLWrite is based on CSQLMultiRowWrite, that implements common code for writing multiple rows at once to SQL server. Any number of rows up to the max number of supported rows in the supplied buffer can be written in one operation.

Logging to CsLog2020 - general

Logging to CsLog2020 - Datalogger

The existing DataLogger program has been upgraded to support logging to the CsLog2020 in parallel with logging to the old log database. Logging by the DataLogger is used to continue logging of parameters set up for the old log system to the CsLog2020 log database. As ConSys devices implements CsLog2020 logging by devices is the preferred method. But some older devices will probably not end up having support for CsLog2020 logging – for these devices the DataLogger will still be used. Parameters that should be logged by the DataLogger should be in a log group with log type set to CSLOG20_LOGTYPE_DATALOGGER. See also the Datalogger documentation.

Logging to CsLog2020 - Devices

Implementation of CsLog2020 support in ConSys devices require the folllowing to be added:

CLog20HistoryManager: An attribute with an instance of this class must be defined in the device class. This class handles and stores the log histories fro the device.

Retrieving Loggings

Loggings can be retrieved directly from the CsLog2020 database from any programming language or tool that supports ODBC access. As described in the ‘Log database tables’ section loggings for a given ConSys parameter is stored in a single table: P<parameterId>. A SQL select statement to retrieve values should include time limits, optional mark conditions and a limit on the number of rows to return (to avoid returning to many rows if the request is to ‘wide’. Examples of request for ASTRID2 beam current, A2BeamCurrent.normal, parameterId: 51716:

Mark byte filter 0x23: Bit 0: Start, Bit 1: Event mark 1, Bit 5: Time mark 10 minute
Maximum 5000 rows returned
in time the time interval from 3/1 8:00 to 29/3 18:00 in 2021:

SELECT TOP 50000 * FROM [P51716] 
WHERE time>='2021-03-01 08:00:00' AND time<='2021-03-29 18:00:00' 
AND (marks & 0x23)>0
ORDER BY time ASC

Select all A2BeamCurrent.normal loggings starting from 1/1-2021:

SELECT * FROM [P51716] 
WHERE time>='2021-01-01 00:00:00' 
ORDER BY time ASC

Interpretation Log Options

The CsLog2020 log options for most parameters are specified trough the interpretation. For a few devices the logging is coded (ASTRID2 BeamCurrent device and DMI weather device). The configurable options are:

Log Mode: Select the log mode to be used for a parameter. The standard/default log mode logs parameters when the value is changed enough to log or/and when the time conditions for the logging is fullfilled. All other modes are based on averaging of parameters, optional logging minimum, maximum and average for the selected average periods. Standard average periods are 1 minute, 10 minutes, 1 hour and 12 hours. All longer periods than the lowest selected are also calculated and logged, ie. selecting 1 minut average loggings will log values for all the four periods above. The time stamp for the average values are set to the time at the end of the average period. Minimum and Maximum loggings are logged with the actual timestamp for the minimum/maximum value respectively.

Available log modes are:

• 0: default: The default log mode logs parameters when they are changed enough according to the selected 'Update Mode' and 'Max' and 'Min' lg time limits as specified below. Empty field/selectiom is threaded as default.
• 1: min in period: Log minumun values only in time intervals given by 'Min allowed log time diff'.
• 2: max in period: Log maximum values only in time intervals given by 'Min allowed log time diff'
• 3: average 1 min: Log averaged values for 1 minute and longer periods.
• 4: average 10 min: Log averaged values for 10 minutes and longer periods.
• 5: average 1 hour: Log averaged values for 1 hour and longer periods.
• 6: average 1 m AvgMinMax 10 min: Log 1 minute averaged values, 10 minutes and above min, max and averaged values.
• 7: avg Min Max 1 min: Log averaged, minimum and maximum values for 1 minute and longer periods.
• 8: avg Min Max 10 min; Log averaged, minimum and maximum values for 10 minutes and longer periods.
• 9: avg Min Max 1 hour: Log averaged, minimum and maximum values for 1 hour and longer periods.

In default log mode:

Max no log time: The maximum time with no loggings being added to the log database. (for read parameters). When no loggings has been made for the specified amount of minutes, the next value send to the log system will be added independent on the update mode check condition. Default value '12 hours' used for 0 or empty field.

Max no log time write: As 'Max no log time' for write parameters. If empty or 0, 'Max no log time' is used instead.

Min allowed log time diff. Values will not be logged with times differences less than this value. Used to limit the number of loggings made. If empty field or less than 10 seconds, the value is set to 10 seconds. In Minimum and Maximum mode, used for period.

Available update (Scale difference) modes are:

• 0: default. log with default hardcoded log options - in most cases equal to 'decimals normal'. Empty field/selection is threaded as default.
• 1: decimals normal Logs changes greather than the normal digit resolution times specified scale difference - or coded scale 2.1 if no scale defined.
• 2: decimals high As decimals normal, using high decimals.
• 3: procent of max scale Procent change of max scale - specified in Diff/Scale/Value - must be defined.
• 4: absolute Absolute change - specified in Diff/Scale/Value - must be defined.
• 5: procent change from last Procent change of max scale specified in Diff/Scale/Value - must be defined.

Diff/Scale/Value: The use of this field depends on the update scale mode. See notes under the different update modes above.

Warning limit: The warning limit is the number of loggings per day before before the log status is marked with a warning of to many loggings. The warning limit is only for information, does not influence the logging rate. The default value for the warning limit used if the field is empty is 100. 0 disables the warning limit. Note the count is each individual log point, so a Avg/Min/Max log mode counts for 3 points.

Quarantine limit: The warning limit is the number of loggings per day before before the logging for the parameter is set in quarantine. Parameters in quarantine is logged with reduced rate, the default way is the. The default value for the quarantine limit used if the field is empty is 1000. 0 disables quarantine method, ie. no limit. Note the count is each individual log point, so a Avg/Min/Max log mode counts for 3 points.

Warning limits and quarantine limits are evaluted starting for each day, and as a total average/day since start of logging. The average/day limit updates/checks starts when at least 50 loggings for the given paramereter has been added to the log since device start.
Warning and quarantine status can be seen in ReSto, where a special 'Upload CsLog2020 Status' can be done, and looked at.

ConSys Health device - CsLog2020 status

The status of the loggings made by devices can be seen for each ConSysLoader instance through the CComputerHealthDevice. The overall status of the device loggings is collected from the individual computer health devices and summed up in the CCsLog20HealthDevice. The status of the individual parameter loggings can be seen in the console by right clicking on the parameter or in Resto by uploading the CsLog2020 log status for selected groups of parameters.

Health device for collected status:

Loader instance log status (computer health device):

Resto upload of CsLog2020 status

Last Modified 24 May 2024