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.
To prevent free browsing of the main history tables, consider using one or more of the SQL View Tag, each of which can be assigned a custom privilege. In addition, deny access to the Remote Tag Value / History Retrieve privilege so that accounts with Remote Data Access can view only their assigned SQL views.
The current status and the configuration of all alarms in your system
Provides a record of all alarm events at all priorities
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.)
Summary values over a defined period of time. There can be multiple iterations of the defined time period within the bounds of the query.
A list of all VTScada tag types, noting which are present in this application.
A list of all parameters defined for a given tag type. Substitute the name of the tag type for "TagTypeName".
Parms_AllTypes table
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 table
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.
A list of all the tag groups defined in VTScada
A list of all tags that belong to a specified tag group.
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 |
|
SubName |
Text |
Sub-name of the alarm (e.g. LoAlarm, HiAlarm) |
|
Status |
Text |
Text description of alarm status |
|
Active |
Integer |
0 for inactive, 1 for active |
|
Unacked |
Integer |
0 if unacknowledged, 1 if acknowledged |
|
Disabled |
Integer |
0 if enabled, 1 if disabled |
|
Timestamp |
Date/Time |
Time of the last event for this alarm. May be null. This is the last time the alarm was commissioned. |
|
Message |
Text |
Message associated with last event |
|
Priority |
Text |
Priority attached to alarm (text, not numeric) |
|
Type |
Integer |
May either denote whether the alarm was reported, or may be used to differentiate between different alarm types. |
|
HookPointValue |
Double |
Numeric value of the tag when commissioning the alarm |
|
Area |
Text |
The area the alarm belongs to |
|
HookPointUnits |
Text |
The engineering units matching the HookPointValue |
|
Operator |
Text |
The operator signed in at the time of the last event. |
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 |
UTC date and time of each alarm event |
|
Name |
Text |
Name of the alarm |
|
SubName |
Text |
Sub-name of the alarm (e.g. HiAlarm, LoAlarm) |
|
Event |
Text |
Text description of the alarm event. (e.g. Enabled, Acknowledged, Cleared, Event, etc.) |
| Disabled | Integer | 1 or 0 according to the alarm disabled status |
|
Message |
Text |
Message associated with the event |
| PriorityNum | Integer | Numeric value of the alarm priority. |
|
Priority |
Text |
Priority of the alarm (as text, not numeric) |
|
Type |
Integer |
Obsolete |
|
HookPointValue |
Double |
The value of the tag triggering the alarm at the time of the event. |
|
Area |
Text |
The area the alarm belongs to. |
|
HookPointUnits |
Text |
Engineering units associated with the HookPointValue. |
|
Operator |
Text |
The operator signed in at the time of the event. |
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.
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 |
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 for 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'
