InfluxDB® Schema

Database & Retention Policies

Database: The default database is "winccoa".

Retention Policies: Each configured archive group for the InfluxDB® backend maps 1:1 to a retention policy. The name of the retention policy is the "Group Name" as configured in the GUI.

The retention period of all retention policies in the database is infinite. This is to prevent InfluxDB® from deleting the retention policies on its own. Instead, the user-specific retention period is used in the backend to check if a shard in such a retention period has expired. If so, the backend backups the shard before it is deleted.

Valuetype

The valuetype is used to convert the read value back to the original WinCC OA value and indicates which column should be read.

valuetype WinCC OA Element Type InfluxDB® Column Suffix and Type
0 bool bool
1 int int
2 unsigned int int
3 float float or float_special
4 string string
5 blob N/A
6 lang string string
7 char int
8 bit32 int
9 bit64 int
10 time int
11 dpid string
12 long int
13 unsigned long int
all dyn dyn

Notes

  • If the dyn column is not null, it has priority
  • if float_special is not null, it has priority over float
  • float_special is stored as string

The column with suffix _float_special contains the special float values values #inf and nan since InfluxDB® cannot map them to float values.

Storage of DYNs

If the element type is dyn, it is always stored in the column with the "dyn_" suffix.

To fit all elements into one column, a custom format is used.

This format is a repeated sequence of:

NumberOfBytes:<Bytes>

Example

5:Hello6:World!

The langString data type uses a format derived from the sequence above.

A langString is an array of strings. However, by default, it is saved in the column with the "string_" suffix but using the dyn storage format.

This makes a dyn_LangString an array of an array, which then layers this format.

Example

14:5:Hello5:Hallo9:3:You2:Du

14 bytes are the first entry in the array, which is 5:Hello5:Hallo.

Followed by 9 bytes of the second entry, which is 3:You2:Du.

Each of these entries is a single LangString, which again is a string array itself.

Events/Values

This is the schema of the value history measurement ("table") in InfluxDB®.

Column Name Type Datatype Description
time - - Time when the value was changed
name tag - Name of the DPE
variabletype tag - WinCC OA Type
alias tag -
Important:
Line breaks (\n) in the alias attribute of a DP element are not supported in the Influx database® and must be avoided!
valuetype field int See value type table
original_value_suffix field - Suffix depending on the valuetype
_status64 field int
_manager field int
_user field int
_unit field string Attribute of the _common config of a DPE.
_comment field langString Attribute of the _common config of a DPE.

Alarms/Alerts

This is the schema of the alert history measurement ("table") in InfluxDB®.

Langstrings in Alarms/Alerts

Langstring in alarms or alerts are different than the langStrings in events/values. For alarms and alerts each lang string entry gets its own column instead of using the dyn storage format. This is necessary since a direct and simple comparison is needed for filtering within WinCC OA.

The format for this is:


columnName_LangIdSuffix

Example


_abbr_3

Would indicate that this column holds the 4th (0-based index) language as defined in the project.

Column Name Type Data type Description
time - - Time the alarm occurred
name tag string Name of the DPE
variabletype tag string WinCC OA Type
alias tag string
_dpeid_dp tag string Datapoint ID
_dpeid_el tag string Element ID
alarmvalue_type field int See value type table
alarmvalue_suffix field * Suffix depends on the alarmvalue_type
_abbr_ID field string Is a langString
_ack_state field int
_ack_time field float
_ack_type field int
_ackable field bool
_alert_color field string
_alert_font_style field string
_alert_fore_color field string
_alert_id field string
_class field string
_comment field string
_dest field int
_dest_text_ID field string Is a langString
_direction field bool
_inact_ack field bool
_panel field string
_partn_idx field int
_partner field string
_prior field int
_single_ack field bool
_state field int
_system_time field string
_text_ID field string Is a langString
_value_status64 field int
_visible field bool
countOfValidAddValues field int Number of add values
_add_value_n_type field int See value type table
_add_value_n_suffix field * Suffix depends on _add_value_n_type

General Notes

  • A query must at least select one field, otherwise no result will be returned.
  • Unsigned integers are not natively supported for the database. Therefore, a WHERE for variables of this type might provide unexpected query results.
  • Requesting an "unsigned int" from the database, requires to manually cast the result value to an unsigned type.
  • The time column is always in UTC time and it is always retrieved as the first column.
  • If a dpQuery with SORT BY 0 GROUP BY SECS(<num>) is used to get downsampling data, a result is not returned in an InfluxDB® backend where correction values are set.

Quoting rules

When querying the InfluxDB® directly and not using WinCC OA query functions (dbQuery, dpGetPeriod), the following rules apply.

For Use Example
SELECT double-qoutes SELECT "_visible"
FROM no qoutes FROM Events.Event
WHERE column name double-qoutes WHERE "_state"
WHERE string or tag value single-qoutes WHERE ... = 'text'
WHERE numeric value no qoutes WHERE ... > 15
Regular Expressions forward-slash WHERE ... =~ /^[0-9]?/

Example Queries

These queries can be executed using external tools to access the InfluxDB® natively (InfluxDB® command line interface, telegraf, etc.)
Note:
These queries are only relevant for directly accessing InfluxDB® (not for using WinCC OAfunctions)

Checking if the database generally works:

SELECT * FROM Event.Event LIMIT 10

Requesting float values:

SELECT "name", "original_value_float", "original_value_float_special" FROM Event.Event WHERE "valuetype" = 3

Requesting all values from a specific type:

SELECT * FROM Event.Event WHERE "variabletype" = 'ExampleDP_Float'

Using a regular expression to request all "_text" columns for alerts:

SELECT "name", /^_text.*/ FROM Alert.Alert