Table Structure and Notes 
VTScada stores history using only it's own proprietary format, which is not a relational database.
The SQL interface allows a limited subset of the SQL language to be used as if querying SQL tables.
The SQL interface exposes the following information for you to query. Fields for each "table" are described within this topic.
For greater security, consider using one or more of the SQL View Tag, each of which can be assigned a custom privilege.
The Remote Tag Value / History Retrieve privilege is necessary in order to run custom SQL queries. Accounts with only the Remote Data Access privilege can see only their assigned SQL views or AlarmHistory, and cannot run their own SQL queries or browse the tag parameter tables.
| Alarms | The configuration of all alarms in your system |
| AlarmHistory | Provides a record of all alarm events at all priorities |
| ActiveAlarms | A table to retrieve active alarms |
| CurrentAlarms | A table to retrieve current alarms |
| DisabledAlarms | A table to retrieve disabled alarms |
| ShelvedAlarms | A table to retrieve shelved alarms |
| SuppressedAlarms | A table to retrieve suppressed alarms |
| UnackedAlarms | A table to retrieve unacked alarms |
| ControlLocks | A table to retrieve control locks |
| ControlTokens | A table to retrieve control tokens |
| ControlTokenRequests | A table to retrieve control token requests |
| History | All data collected from tags that have loggers built-in or attached. (Notebook values are retrieved in their encrypted form. There is no method to decrypt notes during retrieval. |
| History_TPPvalue | Summary values over a defined period of time. There can be multiple iterations of the defined time period within the bounds of the query. |
| TagTypes | A list of all VTScada tag types, noting which are present in this application. |
| Parm_TagTypeName | A list of all parameters defined for a given tag type. Substitute the name of the tag type for "TagTypeName". |
| Parm_AllTypes | Similar to Parms_TagTypeName but the fields of this table are the combined parameters of all tag types. Furthermore, the available fields for these tables have been extended to include not just tag parameters, but also extra information such as TagType, UniqueID, ShortName, Value (i.e. the current value of the tag), DisplayAddress (i.e. the address parm as it appears in the Tag Browser), NChildren (i.e. the number of immediate children under the tag). The extra non-parm columns are specified by the SQLTagParmExtraColumns setting |
| Parms_GroupName | Like Parms_AllTypes, you can also query on specific tag groups. For example, groups such as "Inputs" and "Container" can be accessed via tables such as Parms_Inputs and Parms_Container. The fields of these tables are the combined parameters of all tag types in the group. |
| TagGroups | A list of all the tag groups defined in VTScada |
| TagGroupMembers | A list of all tags that belong to a specified tag group. |
| TagLoggedFields | A list of all fields logged for a specified tag type. |
Since version 11.1, VTScada stores all data using UTC timestamps. This ensures that time zone and daylight savings time boundaries have no effect on data storage. Your queries will need to take this into account.
For queries against the table, History, you can query two available time stamp values:
- Timestamp - The time reported by the I/O device. If a time stamp is not provided with the value, then the time at which the value was read or calculated will be used.
This value is indexed, and should be used in all WHERE clauses. - StorageTimestamp - The time when the value was written by the Historian.
This value is not indexed and should not be used in a WHERE clause. Doing so may result in a very slow query response time. StorageTimestamp is not available to be queried in the derivative, data-aggregated :TPP tables.
Prior to release 11.1 of VTScada, tag data was stored in a separate table for each tag, using the server's time rather than UTC. These legacy tables may still be viewed if the application property SQLQueryHideLegacyTables is set to 0.
In applications with more than 10000 tags of any type, you may need to adjust the limits for ODBC query results. Large result sets may take time to retrieve.
To view tag parameter information, the account used to make the query must have the Tag Parameter View privilege. Any tags that are not available to that account due security privileges or Realm-Area Filtering will not be included in the query result. Disabled tags are not returned by the query.
You are strongly advised to include a time range in the where clause of every query. The BETWEEN clause is recommended.
Result sets are limited to 64 columns. Use care if attempting to do a SELECT * query.
Version 12 of VTScada introduced value attributes. These can be queried using "Value!" rather than "Value" for the field name or array of field names. Value! stores bitwise values according the following table.
Attributes flagged with a star (*) apply only if the I/O tag is attached to a DNP3 driver or a custom driver supporting this feature.
Refer to the constant, #NUM_DATA_ATTR_BITS, to discover the number of bits in data attributes for your version of VTScada.
| Bit Number | Defined Constant Name | Meaning |
|---|---|---|
| 0 | #DATA_ATTR_MANUAL_DATA | The value was from the tag's ManualData parameter |
| 1 | #DATA_ATTR_MANUAL_ENTRY | The value was manually entered |
| 2 | #DATA_ATTR_EDITED_DATA | The historical value was manually edited |
| 3 | #DATA_ATTR_QUESTIONABLE | The tag was marked as questionable |
| 4 | #DATA_ATTR_STALE_DATA | (*) The value was reported as stale by VTSDriver |
| 5 | #DATA_ATTR_IMPORTED | (*) The value was imported from a CSV file |
| 6 | #DATA_ATTR_COMMFAIL | (*) The driver experienced a comm failure |
| 7 | #DATA_ATTR_QUALITY | (*) The tag has marked the value as unreliable |
| 8 | #DATA_ATTR_DRIVER_COMMFAIL | (*) The PLC reported a downstream comm failure |
| 9 | #DATA_ATTR_DRIVER_QUALITY | (*) The PLC reported the value may be unreliable |
| 10 | #DATA_ATTR_DRIVER_OVERRIDDEN | (*) The PLC reported the value was overridden |
| 11 | #DATA_ATTR_DRIVER_NODATA | (*) The PLC reported there is no data available |
Contains all configured alarms.
|
Column name |
Data Type |
Description |
|---|---|---|
|
ID |
Text |
Unique ID of the alarm |
|
Name |
Text |
Name of the alarm |
|
Area |
Text |
The area the alarm belongs to |
| Description | Text | Description from the alarms table or the alarm |
|
Status |
Text |
Text description of alarm status |
|
Active |
Integer |
0 for inactive, 1 for active |
|
Disabled |
Integer |
0 if enabled, 1 if disabled |
| Shelved | Integer | 0 if unshelved, 1 if shelved |
| Suppressed | Integer | 0 if unsuppressed, 1 if suppressed |
|
Unacked |
Integer |
1 if unacknowledged, 0 if acknowledged |
| PriorityNum | Integer | Priority attached to alarm (numeric, not text) |
|
Priority |
Text |
Priority attached to alarm (text, not numeric) |
| Units | Text | Units attached to alarm |
| Setpoint | Double | Setpoint attached to alarm |
| SetpointLabel | Text | Setpoint label attached to alarm |
| Custom | Text | A customizable variable that is JSON encoded if it is not plain text |
| AlarmDatabase | Text | Name of the Alarm Database |
The following columns have been deprecated from the Alarms table: Message, SubName, and HookPointUnits.
Description may serve as an alternative to message.
The SubName can be found at the end of the ID after ":#:"
Units replaces HookPointUnits.
Alarm Status Tables
Tables containing records of configured alarms of a specific status. These records are retrieved from the alarms table.
- ActiveAlarms
- CurrentAlarms
- DisabledAlarms
- ShelvedAlarms
- SuppressedAlarms
- UnackedAlarms
The table will contain the following columns.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
Timestamp |
Date/Time |
Alarm timestamp (UTC) |
| DateTime | Text | Date and time |
| ExpiryTime | Date/Time | The expiry time of shelved alarms |
| ID | Text | unique ID for the event |
|
Name |
Text |
Alarm Name |
|
Area |
Text |
Alarm tag area |
| Description | Text | Alarm tag description |
| Status | Text | Alarm status |
|
Active |
Integer |
0 for inactive, 1 for active |
|
Disabled |
Integer |
0 if enabled, 1 if disabled |
| Shelved | Integer | 0 if unshelved, 1 if shelved |
| Suppressed | Integer | 0 if unsuppressed, 1 if suppressed |
|
Unacked |
Integer |
1 if unacknowledged, 0 if acknowledged |
| PriorityNum | Integer | Numeric value of the alarm priority. |
|
Priority |
Text |
Priority of the alarm (as text, not numeric) |
| Units | Text | Phrase key of setpoint units |
| Value | Double | Tag value |
| Setpoint | Double | Setpoint of alarm evaluation |
| SetpointLabel | Text | Label to display instead of setpoint |
| ValueLabel | Text | Label to display instead of value |
| Custom | Text | Information that can be retrieved by querying for the custom column. The response is JSON encoded if the data is not plain text. |
| AlarmDatabase | Text | Name of the Alarm Database |
The following columns have been deprecated from the AlarmStatus table: Message, SubName, HookPointValue, and HookPointUnits.
Description may serve as an adequate alternative to Message.
The Alarm SubName can be found at the end of the ID after ":#:".
HookPointValue has become ValueLabel.
HookPointUnits has become Units.
Please note:
- The ExpiryTime will only have a value for Shelved Alarms.
- Only the DisabledAlarms table can show the Active and Unacked status of Disabled alarms.
- Only the SuppressedAlarms table can show the Active and Unacked status of a suppressed alarm.
- Only ShelvedAlarms can show the Active and Unacked status of shelved alarms.
Contains a record of all alarms that have occurred in the application, including event alarms for operator actions.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
Timestamp |
Date/Time |
Alarm timestamp (UTC) |
| DateTime | Text | Date and time |
| ID | Text | unique ID for the event |
|
Name |
Text |
Alarm Name |
|
Area |
Text |
Alarm tag area |
| Description | Text | The alarm description |
|
Event |
Text |
Text description of the alarm event. (e.g. Enabled, Acknowledged, Cleared, Event, etc.) |
| PriorityNum | Integer | Numeric value of the alarm priority. |
|
Priority |
Text |
Priority of the alarm (as text, not numeric) |
| Units | Text | Unit associated with the value of the alarming or event tag |
| Value | Double | Tag value |
| Setpoint | Double | Setpoint of alarm evaluation |
| SetpointLabel | Text | Label to display instead of setpoint |
| ValueLabel | Text | Label to display instead of value |
| User | Text | (formerly Operator) account signed in when alarm originated |
| Workstation | Text | workstation from which alarm originated |
| Device | Text | Name of the client device |
| Custom | Text | Information that can be retrieved by querying for the custom column. The response is JSON encoded if the data is not plain text. |
| AlarmDatabase | Text | Name of the Alarm Database |
The following columns have been deprecated from the AlarmHistory table: Message, SubName, HookPointValue, HookPointUnits and Operator.
Description may serve as an adequate alternative to Message.
The Alarm SubName can be found at the end of the ID after ":#:".
HookPointValue has become ValueLabel.
HookPointUnits has become Units.
Operator has become User.
Locks And Tokens Tables
Tables containing a record of all configured locks, tokens and token requests can be queried as follows:
- ControlLocks
- ControlTokens
- ControlTokenRequests
Locks and Tokens tables will contain the following columns:
|
Column Name |
Data Type |
Description |
|---|---|---|
|
Timestamp (UTC) |
Date/Time |
Alarm timestamp (UTC) |
| DateTime | Text | Date and time |
| ID | Text | Unique ID for the event |
|
Name |
Text |
Tag name |
|
Area |
Text |
Tag area |
| Description | Text | Tag description |
|
Level |
Integer |
Lock/token level |
| User | Text | The account signed in when alarm originated |
| AlarmDatabase | Text | Name of the Alarm Database |
A list of all tag types that are defined in the application. Those for which instances exist are flagged with a 1 in the InUse column.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
TagType |
Text |
The name of a tag type. |
| Label | Text | The displayed label for the type. (Allen Bradley DF1 Compatible PLC/RTU) |
|
InUse |
Numeric |
Boolean indication of whether the application includes instances of this type |
Example:
Select * from TagTypes
Contains time stamps, values from all logged tags and recorded statistics from each driver tag in your application. Column names will vary according to tag type.
The history table supports pagination when using REST SQL queries if the number of rows to retrieve exceeds the setting SQLQueryMaxResultRows
Data column names will be the full name of each tag, with the name of the logged property appended. For most I/O tags, this suffix will be ":value".
|
Column Name |
Data Type |
Description |
|---|---|---|
|
Timestamp |
Date/Time |
The UTC time reported by the I/O device. If a time stamp is not provided with the value, then the time at which the value was read or calculated will be used. |
| StorageTimestamp |
Date/Time |
The UTC time when the value was written by the Historian. This value is not indexed and should not be used in a WHERE clause. Doing so may result in a very slow query response time. StorageTimestamp is not available to be queried in the derivative, data-aggregated :TPP tables. |
| Tagname:Value | Varies | Usually numeric, with exceptions such as for String I/O tags. |
|
Drivername:ErrorValue |
Numeric |
Only for driver tags. An error value or code associated with a driver's communication error. |
| Drivername:FailedCount | Numeric | Only for driver tags. Incrementd on each communication error. |
| Drivername:FailedRetryCount | Numeric | Only for driver tags. Incremented if failure occurs on a retry. Drivers will usually retry more than once before designating an error to be an "error on retry". Thus, this count will always be less than or equal to the FailedCount. |
| Drivername:SuccessCount | Numeric | Only for driver tags. Count for successful reads and writes, incremented on each successful operation. |
| Drivername:Quality | Numeric | Only for driver tags. A mathematically derived indication of communication error rates. Used to show the driver's overall "health". |
| Drivername:ResponseTime | Numeric | Only for driver tags. The time it takes the PLC/RTU to receive a command, process it and send a response. |
| Drivername:ErrorAddress | Text | Only for driver tags. If an address was associated with the error, it will be recorded here. |
| <<Other>> | Varies | Placeholder in this table for columns that may be available for some but not all tags. Query the TagLoggedFields table for the tag type in which you are interested. |
Example:
SELECT 'Station 1\PLC1\Level:value', Timestamp FROM History ORDER BY Timestamp DESC LIMIT 10
If your query includes two or more tag names, the result set will be sparse if viewed as a table. For any given timestamp, it is likely that there will be only one logged tag value. Other tag values are marked as "null" for that timestamp. In this case, there is no way to distinguish between values that were not logged at a given time, and values that were logged as null.
Tag value attributes cannot be queried.
Multiple Values per Time Stamp
In most situations, if there are multiple values from one tag with the same time stamp, it is because duplicates were recorded, or values edited ( Edit Data). VTScada will store all the values (each with its own sequence ID), but a query will return only the one with the largest sequence ID.
There are situations where different events can be logged to the same tag with an identical timestamp as provided by the I/O device. If this matches your situation, you can tell VTScada to return all the values for that timestamp by adding the property, SQLQueryHistoryNoOverridesTableName to your list of application properties and setting the value to a name you wish to use for the table in your queries.
If using this option, it usually will not make sense to query TPP values other than minimums or maximums. Add the property SQLQueryHistoryNoOverridesTableSupportsTPP as well, setting the value to FALSE to explicitly deny TTP value queries when retrieving duplicates.
The standard GetTagHistory Modes (Average, Minimum, Maximum, etc.) can be used to retrieve data aggregations over defined time periods referred to as TPP values.
Values that appear to come from a History_TPP table are calculated in response to your queries.
Use care to avoid querying a time span that extends into the future. The last known value will be projected for all time periods after the current date. Your query must provide both a start time and an end time.
Four default values for TPP are specified in the application property SQLQueryTableTPPs, but this is a legacy setting. Your query can be for any TPP value. For example, you may query from the History_15m even though it is not specified in the SQLQueryTableTPPs property.
Example table names: History_1M, History_1H
For each logged tag in the application, and for the recorded statistics of every driver, this table will contain a column named after the tag and having a suffix identifying the statistic calculated for each time period.
|
Column name showing suffix |
Data type |
Description |
|---|---|---|
|
Timestamp |
Date/Time |
UTC date and time at the beginning of each time span |
|
Tag Name:Value:Average |
Double |
Average value within each time span |
|
Tag Name:Value:Minimum |
Double |
Minimum value recorded in the time span |
|
Tag Name:Value:Maximum |
Double |
Maximum value recorded in the time span |
|
Tag Name:Value:Delta |
Double |
Change in value during the time span |
|
Tag Name:Value:ValueAtStart |
Double |
Value at the beginning of the time span |
|
Tag Name:Value:TimeOfMin |
Date/Time |
Time of the minimum value recorded in the range |
|
Tag Name:Value:TimeOfMax |
Date/Time |
Time of the maximum value recorded in the range |
|
Tag Name:Value:ZToNZCount |
Long |
Count of zero to non-zero transitions during the span |
|
Tag Name:Value:NonZeroTime |
Double |
Total time within the span that the value is not zero or invalid |
|
Tag Name:Value:Total |
Double |
Arithmetic sum of the recorded values in the time span |
|
Tag Name:Value:Interpolated |
Double |
A value interpolated across a range of time spans. |
| Tag Name:Value:BitwiseOR | Long | A summary of all bits set in any of the values of the range. |
| Tag Name:Value:BitwiseAND | Long | A summary of any bits set in every value of the range |
| Tag Name:Value:ValueAtEnd | Double | Value at the end of the time span |
There is one table for each tag type. Column names within the tables match the names of each configuration parameter and rows contain the current value of that parameter for each tag at the time of the query. In the case of parameters that are set by expression, the value at the time of the query is returned, not the text of the expression. Relative tag references will resolve to the tag name. Parameters that are phrases will be converted based on the language configured for the user making the request. Note that parameters marked as encrypted in custom tags will be revealed by the query.
The account used to make the query must have the Tag Parameter View privilege. Any tags that are not available to that account due security privileges or Realm-Area Filtering will not be included in the query result. Disabled tags are not returned by the query.
If a new type is created, it is immediately available to queries. Custom types that are removed will remain available to the query until the next application restart.
See Tag Names in Code or query the TagTypes table for a list of type names.
| Column Name | Data Type | Data Source | Description |
|---|---|---|---|
| Name | text | parameter | Tag name |
| Area | text | parameter | Tag area |
| Description | text | parameter | Tag description |
| ... | parameter | Other tag parameters specific to the tag type | |
| TagType | text | extra field | Name of tag type |
| TagTypeLabel | text | extra field | Friendly tag type label |
| UniqueID | text | extra field | Tag's unique identifier |
| <etc> | text | extra field | <fill in the rest of the extra fields defined in SQLTagParmExtraColumns |
Examples:
Select * from Parms_AnalogStatus
Select * from Parms_DigitalStatus
The following query will fail: Select * from Parms_IO. Result sets are limited to 64 columns and the parameter count of the IO tag exceeds this number. Use the following query instead: GET COLUMNS Parms_IO
The available GET queries are listed in SQL: Reference and Examples
All tags are members of one or more groups; Analogs, Digitals, Containers, etc.
This table contains a list of all tag types and their matching group name. TagGroup names repeat, with an entry for each matching TagType name.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
TagGroup |
Text |
The name of a tag group. (example: Analogs) |
|
TagType |
Text |
The name of a type that is a member of a matching group. |
All tags are members of one or more groups; Analogs, Digitals, Containers, etc.
This table contains a list of all the tag group names.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
TagGroup |
Text |
The name of a tag group. (example: Analogs) |
|
Label |
Text |
How that group name is displayed in the VTScada user interface. |
Example:
SELECT * FROM TagGroups
For most tags, the logged field is named Value, but this is not true of all tags. For example, every driver has seven logged fields as described in Communication Driver Log-Enabled Variables.
|
Column Name |
Data Type |
Description |
|---|---|---|
|
TagType |
Text |
The name (not the label) of a tag type. Repeats for types that have more than one logged field. See the TagTypes table or Tag Names in Code |
|
LoggedField |
Text |
The name of the field logged for that type. |
Example:
Select * from TagLoggedFields where TagType = 'IO'
