Historian Database Tables

Important: You do not have the latest version of Historian! You are missing out on the newest capabilities and enhanced security. For information on all the latest features, see the Historian product page. For more information on upgrades, contact your GE Digital sales agent or e-mail GE Digital Sales Support. For the most up-to-date documentation, go here.

About the Historian Database Tables

The Historian database tables contain read-only data from the Historian archive. This chapter describes the columns contained in the Historian OLE DB Provider tables:

You cannot perform writes or updates to data in these tables. Almost all columns in Historian OLE DB Provider tables support comparison operators except for the following:

  • SamplingMode
  • Direction
  • NumberOfSamples
  • IntervalMilliseconds
  • CalculationMode
  • FilterTag
  • FilterMode
  • FilterComparisonMode
  • FilterValue
  • FilterExpression
  • TimeZone
  • DaylightSavingTime
  • RowCount

These columns only support the = comparison operator.

Nulls are not supported in any column. A blank space is returned when there is no value supplied by the Historian server, instead of a Null field.

Historian Security Groups and the Database Tables

A user with membership in the iH Readers security group can access any table in the Historian OLE DB Provider, even the ihArchives and ihCollectors tables. Members of the iH Readers group have read-only access to these tables.

Since the Historian OLE DB Provider only supports read-only access to data and does not allow INSERT or UPDATE operations, no users can make changes to the data in these tables. This includes members of the iH Readers security group and even security administrators in the iH Security Admins security group.

For more information on Historian group rights, refer to Chapter 5 in the Getting Started with Historian manual.

Input Data and Historian Archive Data in Table Columns

There are two types of column data in the Historian OLE DB Provider tables: input data and Historian archive data. Input data contains settings stored in the Historian OLE DB Provider and has nothing to do with the data stored in the Historian archives. Historian archive data is the data retrieved from the Historian server.

While most columns contain Historian archive data, there are a few columns that contain input data. The following columns, no matter what table they appear in, contain input data and do not originate from the Historian archives:

  • SamplingMode
  • Direction
  • NumberOfSamples
  • IntervalMilliseconds
  • CalculationMode
  • FilterTag
  • FilterMode
  • FilterComparisonMode
  • FilterValue
  • FilterExpression
  • TimeZone
  • DaylightSavingsTime
  • RowCount

The columns in the previous list are used in a WHERE clause to specify query parameters for retrieved data.

About the Table Descriptions

The following sections describe each table, list each column in the table, and list the data type and description for each column. The following table outlines the data types that are used throughout this chapter.

Table 1. Column Data Types
Data Type Format of Data
VT_BOOL Boolean
VT_BSTR String
VT_DBTimeStamp Date and Time
VT_I4 Integer
VT_R4 Float
VT_R8 Double Float
VT_UI1 Short Integer
VT_VARIANT Numeric or String

Also included after each table description are examples of SQL statements used with the specified database table. These examples are only provided to get you started with creating SQL statements with the Historian OLE DB Provider. For more detailed information on creating SQL queries, refer to your reporting software documentation.

ihTags Table

The ihTags table contains the set of tag names and the properties of each tag. This data is mostly the information that the administrator enters through the Tag Maintenance screen in the Historian Administrator application. Each row in the table represents one tag. The following table describes the columns of the ihTags table.

Table 2. ihTags Table
Column Name Data Type Description
Tagname VT_BSTR Tagname property of the tag.
Note: There is no length limit for Historian tag names in the Data Archiver. However, different client applications may have their own limits.
Description VT_BSTR User description of the tag.
EngUnits VT_BSTR Engineering units description of the tag.
Comment VT_BSTR User comment associated with the selected tag.
DataType VT_BSTR The data type of the tag:
  • Scaled
  • SingleFloat
  • DoubleFloat
  • SingleInteger
  • DoubleInteger
  • Quad Integer
  • Unsigned Single Integer
  • Unsigned Double Integer
  • Unsigned Quad Integer
  • Byte
  • Boolean
  • FixedString
  • VariableString
  • BLOB

The data type returned in this column is the data type that you defined in the Historian Administrator application.
FixedStringLength VT_UI1 Zero unless the data type is FixedString. If the data type is FixedString, this number represents the maximum length of the string value.
CollectorName VT_BSTR Name of the collector responsible for collecting data for the specified tag.
SourceAddress VT_BSTR Address used to identify the tag at the data source. For iFIX systems, this is the NTF (Node.Tag.Field).
CollectionType VT_BSTR Type of collection used to acquire data for the tag:
  • Unsolicited: The collector accepts data from the source whenever the source presents the data.
  • Polled: The collector acquires data from a source on a periodic schedule determined by the collector.
Note: Not all collectors support unsolicited collection.
CollectionInterval VT_I4 The time interval, in milliseconds, between readings of data from this tag.

For polled collection, this field represents the time between samples. For unsolicited collection, this field represents the minimum time allowed between samples.
CollectionOffset VT_I4 The time shift from midnight, in milliseconds, for collection of data from this tag.
LoadBalancing VT_BOOL Indicates whether the data collector should automatically shift the phase of sampling to distribute the activity of the processor evenly over the polling cycle. This is sometimes called phase shifting.
TimeStampType VT_BSTR The timestamp type applied to data samples at collection time:
  • Source: The source delivers the timestamp along with the data sample.
  • Collector: The collector delivers the timestamp along with the collected data.
HiEngineeringUnits VT_R8 The high end of the engineering units range. Used only for scaled data types and input scaled tags.
LoEngineeringUnits VT_R8 The low end of the engineering units range. Used only for scaled data types and input scaled tags.
InputScaling VT_BOOL Indicates whether the measurement should be converted to an engineering units value. When set to False, the measurement is interpreted as a raw measurement.

When set to True, the system converts the value to engineering units by scaling the value between the HiScale and LoScale columns. If not enabled, the system assumes the measurement is already converted into engineering units.
HiScale VT_R8 The high-end value of the input scaling range used for the tag.
LoScale VT_R8 The low-end value of the input scaling range used for the tag.
CollectorCompression VT_BOOL Indicates whether collector compression is enabled for the tag.

Collector compression applies a smoothing filter to incoming data by ignoring incremental changes in values that fall within a deadband centered around the last collected value. The collector passes (to the archiver) any new value that falls outside the deadband and then centers the deadband around the new value.
CollectorDeadbandPercentRange VT_R4 The current value of the compression deadband.
ArchiveCompression VT_BOOL Indicates whether archive collector compression is enabled for the tag.
ArchiveDeadbandPercentRange VT_R4 The current value of the archive compression deadband.
CollectorGeneral1 VT_BSTR The general (or spare) configuration fields for the tag.
CollectorGeneral2 VT_BSTR The general (or spare) configuration fields for the tag.
CollectorGeneral3 VT_BSTR The general (or spare) configuration fields for the tag.
CollectorGeneral4 VT_BSTR The general (or spare) configuration fields for the tag.
CollectorGeneral5 VT_BSTR The general (or spare) configuration fields for the tag.
ReadSecurityGroup VT_BSTR The name of the Windows security group that controls the reading of data for the tag.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.
WriteSecurityGroup VT_BSTR The name of the Windows security group that controls the writing of data for the tag.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.
AdministratorSecurityGroup VT_BSTR The name of the Windows security group responsible for controlling configuration changes for the tag.
Calculation VT_BSTR The equation for the calculation performed for the tag.
LastModified VT_DBTimeStamp The date and time that the tag configuration was last modified. The time structure includes milliseconds.
LastModifiedUser VT_BSTR The username of the Windows user who last modified the tag configuration.
CollectorType VT_BSTR The type of collector responsible for collecting data for the tag:
  • Undefined
  • iFIX
  • Simulation
  • OPC
  • File
  • iFIXLabData
  • ManualEntry
  • Simulation
  • Other
StoreMilliseconds VT_BOOL Indicates whether milliseconds are recorded in timestamps.

If not enabled, the time resolution is in seconds instead of milliseconds. Maximum data compression is achieved when this option is set to False. This is the optimum setting for most applications.

Note: StoreMilliseconds returns False in Historian v4.5 and later.
TimeResolution String Indicates the timestamp resolution in seconds, milliseconds, or microseconds.
UTCBias VT_I4 The time zone bias for the tag. Time zone bias is used to indicate the natural time zone of the tag expressed as an offset from UTC (Universal Time Coordinated) in minutes.

UTC is the international time standard, the current term for what was commonly referred to as Greenwich Mean Time (GMT).
AverageCollectionTime VT_I4 The average time it takes to execute the calculation tag since you started the Calculation Collector.
CollectionDisabled VT_I4 Indicates whether collection is enabled (0) or disabled (1) for the tag. The default setting is enabled (0).
CollectorCompressionTimeout VT_I4 Indicates the maximum amount of time the collector will wait between sending samples to the archiver. This time is kept per tag, as different tags report to the archiver at different times.

This value should be in increments of your collection interval, and not less.

Ideally, this field is used for polled data values. It can be used with unsolicited data, but when you do so, you are dependent on the data source for the value to change. With unsolicited data, since Historian only records the value when it changes, the actual time before the timeout might exceed the compression timeout.
ArchiveCompressionTimeout VT_I4 Indicates the maximum amount of time from the last stored point before another point is stored, if the value does not exceed the archive compression deadband.

The data archiver treats the incoming sample after the timeout occurs as if it exceeded compression. It then stores the pending sample.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates there is no limit to the number of rows returned.
InterfaceAbsoluteDeadbanding VT_BOOL Indicates whether absolute collector deadbanding is enabled for this tag.
InterfaceAbsoluteDeadband VT_R8 Indicates the value for absolute collector deadbanding.
ArchiveAbsoluteDeadbanding VT_BOOL Indicates whether absolute archive deadbanding is enabled for this tag.
ArchiveAbsoluteDeadband VT_R8 Indicates the value for absolute archive deadbanding.
SpikeLogic VT_BOOL Indicates whether Spike Logic is enabled for the tag.
SpikeLogicOverride VT_BOOL Indicates whether the Spike Logic setting for this tag overrides the collector.
StepValue VT_BOOL Indicates whether the StepValue property is enabled for the tag.
EnumeratedSetName VT_BSTR Indicates the enumerated set name associated with a tag. You can get more information about the set via the ihEnumeratedSet table.
DataStoreName VT_BSTR Indicates the name of the data store the tag belongs to.
NumberOfElements VT_I4 Indicates whether the tag is an array tag.

If set to -1, the tag is an array tag. If set to 0, the tag is not an array tag. Since the size of the array is dynamic, there is no single number of elements that can be returned.
CalcType Enum Indicates whether the tag is an analytical tag or a normal tag.
IsAlias VT_BOOL Indicates whether the tag has an alias or not.

ihTags Examples

Tasks that you might want to perform on the ihTags table are outlined in the following examples.

Example 1: Find All Tags That Belong to a Specific Collector

SELECT * FROM ihtags WHERE collectorname=MYCOMPUTER_Simulation ORDER BY tagname

Example 2: Find All Tags With a Specific Poll Rate, a Range of Poll Rates, or Polling Disabled

SELECT * FROM ihtags WHERE CollectionInterval=500
OR (CollectionInterval>=1000 AND CollectionInterval<=1200)
OR CollectionInterval=0

Example 3: Retrieve All Tags Collected by Each Collector

SELECT collectorname, tagname FROM ihTags ORDER BY collectorname

Example 4: Retrieve All Tags With a Specific Poll Rate

SELECT tagname FROM ihtags WHERE collectioninterval=1000

Example 5: Retrieve All Tags With Subsecond Collection

SELECT tagname FROM ihtags
WHERE collectioninterval BETWEEN 1 AND 999

Example 6: Retrieve All Tags with Polling Disabled

SELECT tagname, collectioninterval FROM ihtags
WHERE collectioninterval=0

Example 7: Count the Number of Tags and Group by Collector Name

SELECT collectorname, COUNT(*) FROM ihTags GROUP BY collectorname

Example 8: Count the Number of Tags and Group by Collector Type

SELECT ihCollectors.collectortype, COUNT(*)
FROM ihTags INNER JOIN ihCollectors
WHERE ihTags.collectorname=ihCollectors.collectorname
GROUP BY ihcollectors.collectortype

Example 9: Retrieve Tags Associated With a Specific Enumerated Set

SELECT * FROM ihtags
WHERE EnumeratedSetName='ExampleSet

ihArchives Table

Historian archives are stored as data files, each of which contains data gathered during a specific period of time.

The ihArchives table contains Historian archive configuration information and performance statistics for each archive. Each row in this table represents one archive. The following table describes the columns of the ihArchives table.
Table 3. ihArchives Table
Column Name Data Type Description
ArchiveName VT_BSTR Name of the archive for the current server if the authenticated user is a member of the Historian Administrators group.
ArchiveStatus VT_BSTR The status of the specified archive:
  • Undefined
  • Empty
  • NotEmpty
FileName VT_BSTR The file name for the specified archive. The file name must be specified in the context of the Historian server drives and directories.
IsCurrent VT_BOOL Indicates whether the specified archive is the newest archive that new data currently flows into.
IsReadOnly VT_BOOL Indicates whether the read-only status is set for the specified archive.
FileSizeCurrentDisk VT_I4 The actual space on the hard disk (in MB) for the specified archive.
FileSizeCurrent VT_I4 The size of the archive file that is currently being used (in MB) for the specified archive.
FileSizeTarget VT_I4 The target size of the specified archive file (in MB).
StartTime VT_DBTimeStamp The start time of the specified archive. This represents the earliest timestamp (including date and time) for any tag contained in the archive.
EndTime VT_DBTimeStamp The end time of the specified archive. This represents the latest timestamp (including date and time) for any tag contained in the archive.
LastBackup VT_DBTimeStamp The date and time the most recent online backup was performed on this archive.
LastBackupUser VT_BSTR The name of the user who performed the most recent online backup.
LastModified VT_DBTimeStamp The date and time that the archive was last modified. The time structure includes milliseconds.
LastModifiedUser VT_BSTR The username of the Windows user who last modified the archive.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT).
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates there is no limit to the number of rows returned.
DataStoreName VT_BSTR Indicates the name of the data store the tag belongs to.

ihArchives Examples

A task that you might want to perform on the ihArchives table is retrieving and recording the state of the archives and archive sizes when an event happens. Recording conditions when an event happens is useful in troubleshooting.

Sample SQL statements for the ihArchives table are outlined in the following examples.

Example 1: Retrieve the Archive List Sorted by StartTime

SELECT archivename, starttime, endtime
FROM iharchives ORDER BY starttime

Example 2: Retrieve All Properties of the Current Archive

SELECT * FROM iharchives WHERE iscurrent=true

ihCollectors Table

The ihCollectors table contains the configuration and status information for each collector connected to the Historian server. Each row in this table represents a collector that is connected to the archiver. The following table describes the columns of the ihCollectors table.

Table 4. ihCollectors Table
Column Name Data Type Description
CollectorName VT_BSTR The name of the collector. The collector name is unique in a specific Historian server.
CollectorDescription VT_BSTR The user description for the collector.
Comment VT_BSTR The user comment associated with the collector.
ComputerName VT_BSTR The name of the Windows computer on which the collector is running.
Status VT_BSTR The status of the specified collector:
  • Unknown
  • Starting
  • Running
  • Stopping
  • Stopped
CollectorType VT_BSTR The type of collector responsible for collecting data for the tag:
  • Undefined
  • iFIX
  • Simulation
  • OPC
  • OPC AE
  • File
  • iFIXLabData
  • ManualEntry
  • Simulation
  • Calculation
  • ServerToServer
  • Other
MaximumDiskFreeBufferSize VT_I4 The maximum size (in MB) of the disk buffer for outgoing data.
MaximumMemoryBufferSize VT_I4 The maximum size of the memory buffer (in MB) for outgoing data.

The memory buffer stores data during short-term or momentary interruptions of the server connection. The disk buffer handles long-duration outages.
ShouldAdjustTime VT_BOOL If the data source supplies the timestamps, this value is False. If the collector supplies the timestamps, this value is True.
Note: This column does not change collector times to match the server time. It indicates whether an increment of time is added or subtracted to compensate for the relative difference between the server and collector clocks, independent of time zone differences.
ShouldQueueWrites VT_BOOL Indicates whether queue writes are allowed.
CanBrowseSource VT_BOOL If True, this column indicates that the collector can browse its source for tags.
CanSourceTimestamp VT_BOOL Indicates whether the data source can provide timestamps along with the data.
StatusOutputAddress VT_BSTR An address or tagname in the data source to output current collector status.
RateOutputAddress VT_BSTR An address or tagname in the data source into which the collector writes the current value of the events per minute output.
HeartbeatOutputAddress VT_BSTR The address in the source database into which the collector writes the heartbeat signal output.
CollectorGeneral1 VT_BSTR The general (or spare) configuration fields for the collector. The CollectorGeneral1 column is not user-defined, and is different for each collector.
CollectorGeneral2 VT_BSTR The general (or spare) configuration fields for the collector. The CollectorGeneral2 column is not user-defined, and is different for each collector.
CollectorGeneral3 VT_BSTR The general (or spare) configuration fields for the collector. The CollectorGeneral3 column is not user-defined, and is different for each collector.
CollectorGeneral4 VT_BSTR The general (or spare) configuration fields for the collector. The CollectorGeneral4 column is not user-defined, and is different for each collector.
CollectorGeneral5 VT_BSTR The general (or spare) configuration fields for the collector. The CollectorGeneral5 column is not user-defined, and is different for each collector.
LastModified VT_DBTimeStamp The date and time that the collector configuration was last modified. The time structure includes milliseconds.
LastModifiedUser VT_BSTR The username of the Windows user who last modified the collector configuration.
SourceTimeInLocalTime VT_BOOL For data source timestamps only. Indicates whether the timestamps use local time. If the value is False, UTC time is used.
CollectionDelay VT_I4 The length of time, in seconds, that the collector should delay collection at startup (to allow the data source time to initialize).
DefaultTagPrefix VT_BSTR The prefix that is automatically applied to all tagnames added by the specified collector.
DefaultCollectionInterval VT_I4 The collection interval, in milliseconds, for tags added by the collector.
DefaultCollectionType VT_BSTR Type of collection used to acquire data for tags added by the collector:
  • Unsolicited: The collector accepts data from the source whenever the source presents the data.
  • Polled: The collector acquires data from a source on a periodic schedule determined by the collector.
Note: Not all collectors support unsolicited type collection.
DefaultTimeStampType VT_BSTR Type of timestamping applied to data samples at collection time for tags added by the collector:
  • Source: The source delivers the timestamp along with the data sample.
  • Collector: The collector delivers the timestamp along with the collected data.
DefaultCollectorCompression VT_BOOL Indicates whether default collector compression is enabled for tags added by the collector.
DefaultCollectorCompressionDeadband VT_R4 The default collector compression deadband for tags added by the collector.
DefaultCollectorCompressionTimeout VT_I4 The default collector compression timeout value.
DisableOnTheFlyChanges VT_BOOL Indicates whether a user can make on-the-fly changes to this tag. When enabled (True) you can make changes to this tag without having to restart the collector.

When disabled (False), any changes you make to this tag do not affect collection until you restart the collector.
DefaultSpikeLogic VT_BOOL Indicates whether Spike Logic is enabled.
DefaultSpikeMultiplier VT_R4 The default Spike Logic multiplier.
DefaultSpikeInterval VT_I4 The default Spike Logic interval.
RedundancyEnabled VT_BOOL Indicates whether collector redundancy is enabled.
PrincipalCollector VT_BSTR Indicates the primary collector.
IsActiveRedundantCollector VT_BOOL Indicates whether the current collector is active.
FailoverOnCollectorStatus VT_BOOL Indicates whether the collector is set to fail over on an unknown collector status.
FailoverOnBadQuality VT_BOOL Indicates whether the collector is set to fail over on bad data quality received from the watchdog tag.
FailoverOnValue VT_BOOL Indicates whether the collector is set to fail over on a change in value.
FailoverValueChangeType VT_I4 The value for the FailoverOnValue option.
WatchdogValueMaxUnchangedPeriod VT_I4 The maximum period for an unchanged value.
WatchdogTagName VT_BSTR The watchdog tag name.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 The maximum number of rows that can be returned. A value of 0 indicates there is no limit to the number of rows returned.

ihCollectors Examples

One task that you might want to perform on the ihCollectors table could be retrieving and recording the state of the collectors when an event happens. Recording conditions when an event happens is useful in troubleshooting.

Sample SQL statements for the ihCollectors table are outlined in the following examples.

Example 1: Retrieve All Collectors With Status Information

SELECT collectorname, collectordescription AS desc, status
FROM ihcollectors

Example 2: Retrieve All Collectors Not Running

SELECT collectorname, collectordescription AS desc, status
FROM ihcollectors WHERE status!=running

ihMessages Table

The ihMessages table contains Historian messages such as alerts, informational topics, and connection information contained in the audit log. Each row in this table represents a message. The following table describes the columns of the ihMessages table.

Table 5. ihMessages Table
Column Name Data Type Description
TimeStamp VT_DBTimeStamp The date and time that the message was created.
TimeStampSeconds VT_DBTimeStamp The date and time that the message was created.
Microseconds VT_I4 The microsecond portion of the date and time for the message.
Topic VT_BSTR The topic name of the message:
  • AlertTopics
  • AllTopics
  • ConfigurationAudit
  • Connections
  • General
  • MessageTopicMax
  • MessageTopics
  • Performance
  • ServiceControl
  • Security
  • Undefined
Username VT_BSTR Name of the Windows user who generated the message, or who the message is associated with.
MessageNumber VT_I4 Message number for the message. A message number is a unique identifier associated with the message template.
MessageString VT_BSTR Translated text of the message, including any substitutions. Messages generally include translated fixed text and variable substitutions such as timestamps, usernames, and tagnames.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
Row Count VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates there is no limit to the number of rows returned.

ihMessages Examples

One task that you might want to perform on the ihMessages table is retrieving a history of alerts and messages, with timestamps and user information. For instance, you might want to query the alerts for a day, or all messages associated with a particular username.

Sample SQL statements for the ihMessages table are outlined in the following examples.

Example 1: Retrieve All Messages and Alerts for Today

SELECT * FROM ihmessages WHERE timestamp>=today

Example 2: Retrieve All Alert Messages for a Specific User and Time

SELECT * FROM ihmessages
WHERE timestamp>'12-sep-2001 02:00:00'
AND topic=AlertTopics
AND username='DataArchiver' ORDER BY timestamp

Example 3: Retrieve All Messages in Your Archive

SELECT * FROM ihMessages WHERE timestamp <= Now

Example 4: Retrieve All Messages for a Specific User

SELECT * FROM ihMessages WHERE username=operator1
AND timestamp<=Now

Example 5: Count All Messages by a Specific User

SELECT username, COUNT(*) FROM ihMessages
WHERE timestamp <=Now GROUP BY username

ihRawData Table

The ihRawData table contains any collected data for each tag contained in the Historian server. It contains not just raw data, but also calculated data and interpolated data. This table is the one typically used for reporting.

There is one row in the ihRawData table for each combination of tagname and timestamp. For instance, you can have two rows for the same tag, each with different timestamps. You can retrieve data for more than one tag name in a simple query.

The following table describes the columns of the ihRawData table.

Table 6. ihRawData Table
Column Name Data Type Description
Tagname VT_BSTR Tagname property of the tag.
Note: There is no length limit for Historian tag names in the Data Archiver. However, different client applications may have their own limits.
TimeStamp VT_DBTimeStamp The date and time for the data sample.
TimeStampSeconds VT_DBTimeStamp The date and time for the data sample.
Microseconds VT_DBTimeStamp The microsecond interval for the data sample.
Value VT_VARIANT The value of the data.
Quality VT_VARIANT For non-raw sampled data, this column displays the percentage of good quality samples in the interval. For instance, a value of 100 means all samples in the interval are good.

For raw sampled data, data values are:

  • Good
  • Bad
  • Uncertain
  • Not Available
  • Really Unknown
This column also includes the subquality of the data value, if it exists:
  • NonSpecific
  • ConfigError
  • NotConnected
  • DeviceFail
  • SensorFail
  • LastKnownValue
  • CommFailure
  • OutOfService
  • ScaledOutOfRange
  • OffLine
  • NoValue
  • Really Unknown
OPCQualityValid VT_BSTR Indicates whether the OPCQuality column contains valid real OPC quality. A value of 0 indicates that you should ignore the OPCQuality field, and a value of 1 indicates that the OPCQuality column contains valid real OPC quality.
OPCQuality VT_I4 Indicates the OPC quality as delivered by the OPC server to the Historian OPC collector. The exact meaning of the bits depends on the OPC specification and the OPC server documentation. Typically, a value of 0 represents bad quality, and a value of 192 represents good quality.
SamplingMode VT_BSTR The mode used to sample data from the archive:
  • CurrentValue: Retrieves the current value. Time frame criteria are ignored.
  • Interpolated: Retrieves evenly spaced interpolated values based on interval or NumberOfSamples and the time frame criteria.
  • RawByTime: Retrieves raw archive values based on time frame criteria.
  • RawByNumber: Retrieves raw archive values based on the StartTime, NumberOfSamples, and Direction criteria.
    Note: EndTime criteria are ignored for this sampling mode.
  • RawByFilterToggle: Returns filtered time ranges. The values returned are 0 and 1. If the value is 1, then the condition is true and 0 means false. This sampling mode is used with the time range and filter tag conditions. The result starts with a starting timestamp and ends with an ending timestamp.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, the time frame criteria, and the CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns raw minimum and raw maximum values for each specified interval. Use this sampling mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian ignores any leftover values at the end, rather than putting them into a smaller interval.
  • Trend2: Returns raw minimum and raw maximum values for each specified interval. Use this sampling mode to maximize performance when retrieving data points for plotting. Also, if the sampling period does not evenly divide by the interval length, Historian creates as many intervals of the interval length as will fit into the sampling period, and then creates a remainder interval from whatever time is left. This sampling mode is more suitable than the Trend mode for analysis of minimums and maximums and for plotting programs that can handle unevenly spaced data.
  • InterpolatedtoRaw: Provides raw data in place of interpolated data when the number of samples is less than the available samples.
  • TrendtoRaw: This sampling mode almost always produces the same results as the Trend mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. TrendtoRaw is therefore used instead of Trend when the number of actual data samples is less than the requested number of samples.
  • TrendtoRaw2: This sampling mode almost always produces the same results as the Trend2 mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. TrendtoRaw2 is therefore used instead of Trend2 when the number of actual data samples is less than the requested number of samples.
  • LabtoRaw: Provides raw data for the selected calculated data when the number of samples is less than the available samples.
Direction VT_BSTR The direction (forward or backward from the start time) of data sampling from the archive.
NumberOfSamples VT_I4 Number of samples from the archive to retrieve.

Samples will be evenly spaced within the time range defined by the start and end times for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this value is ignored.

Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
IntervalMilliseconds VT_I4 For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
CalculationMode VT_BSTR

This column applies only if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:

  • Average
  • Count
  • Maximum
  • MaximumTime
  • Minimum
  • MinimumTime
  • StandardDeviation
  • Total
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
FilterTag VT_BSTR Tagname used to define the filter, if specified. Only a single tag can be specified, and wildcards are not supported.
FilterMode VT_BSTR

The type of time filter:

  • ExactTime: Retrieves data for the exact times that the filter condition is True.
  • BeforeTime: Retrieves data from the time of the last False filter condition to the time of the next True condition.
  • AfterTime: Retrieves data from the time of the last True filter condition to the next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the next False condition.

This mode defines how time periods before and after transitions in the filter condition should be handled.

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and leading up to the timestamp of the archive value that triggered the False condition.
FilterComparisonMode VT_BSTR The type of comparison to be made on the filter comparison value:
  • Equal: Filter condition is True when the FilterTag value is equal to the comparison value.
  • EqualFirst: Filter condition is True when the FilterTag value is equal to the first comparison value.
  • EqualLast: Filter condition is True when the FilterTag value is equal to the last comparison value.
  • NotEqual: Filter condition is True when the FilterTag value is not equal to the comparison value.
  • LessThan: Filter condition is True when the FilterTag value is less than the comparison value.
  • GreaterThan: Filter condition is True when the FilterTag value is greater than the comparison value.
  • LessThanEqual: Filter condition is True when the FilterTag value is less than or equal to the comparison value.
  • GreaterThanEqual: Filter condition is True when the FilterTag value is greater than or equal to the comparison value.
  • AllBitsSet: Filter condition is True when the binary FilterTag value is equal to all the bits in the condition. It is represented as ^ to be used in FilterExpression.
  • AnyBitSet: Filter condition is True when the binary FilterTag value is equal to any of the bits in the condition. It is represented as ~ to be used in FilterExpression.
  • AnyBitNotSet: Filter condition is True when the binary FilterTag value is not equal to any one of the bits in the condition. It is represented as !~ to be used in FilterExpression.
  • AllBitsNotSet: Filter condition is True when the binary FilterTag value is not equal to all the bits in the condition. It is represented as !^ to be used in FilterExpression.
This column defines how archive values for the FilterTag value should be compared to the FilterValue value to establish the state of the filter condition. If FilterTag and FilterComparisonValue values are specified, time periods are filtered from the results where the filter condition is False.
FilterValue VT_BSTR The value with which to compare the FilterTag value to determine appropriate filter times.
FilterExpression VT_BSTR An expression which includes one or more filter conditions. The type of conditions used are:
  • AND
  • OR
  • Combination of AND and OR
FilterExpression can be used instead of the FilterTag, FilterComparisonMode and FilterValue parameters. While using FilterExpression, the expression is passed within single quotes. For complex expressions, write the conditions within parentheses. There is no maximum length for the FilterExpression value, but if called using OLE DB or Excel, those tools may have their own limitations.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

The ihRawData table can generate a large number of rows if not used with caution. You can easily generate queries which take a very long time to complete and put stress on the archiver and generate network traffic.

ihRawData Examples

Tasks that you might want to perform on the ihRawData table are outlined in the following examples.

Example 1: Retrieve All Samples With a Value Outside the Query Supplied Values

SELECT * FROM ihRawData WHERE value<140000 OR value>150000

Example 2: Retrieve All Bad Samples (Raw Data)

SELECT * FROM ihRawData WHERE quality NOT LIKE good*
AND samplingmode=RawbyTime

Example 3: Count Bad Samples (Raw Data)

SELECT COUNT(*) FROM ihRawData WHERE quality NOT LIKE good*
AND samplingmode=RawbyTime

Example 4: Retrieve All Bad Samples Over the Last Day (Interpolated Data)

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND timestamp>=Now-24H

Example 5: Use an Explicit Time Zone

SELECT * FROM ihRawData WHERE timezone=300

Example 6: Perform a Simple Sequence of Events

SELECT timestamp, tagname, value, quality FROM ihrawdata
WHERE samplingmode=rawbytime ORDER BY timestamp

Example 7: Report the Busiest Tags

SELECT tagname, value FROM ihRawData
WHERE samplingmode=calculated
AND calculationmode=count
AND numberofsamples=1
AND timestamp>='07/30/2002 10:00:00'
AND timestamp<='07/30/2002 11:00:00' order by value descending

Example 8: Retrieve All Bad Samples Over the Last Day

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND timestamp>=Now-24H

Example 9: Retrieve All Bad Samples, Ignore End of Collection Markers

SELECT timestamp, tagname, value, quality FROM ihRawData
WHERE samplingmode=rawbytime
AND Quality NOT LIKE good*
AND quality NOT LIKE 'bad offline' AND timestamp>=Now-24H

Example 10: Count Bad Samples, Ignore End of Collection Markers

SELECT COUNT(*) FROM ihRawData WHERE samplingmode=rawbytime
AND Quality NOT LIKE good* and Quality NOT LIKE 'bad offline'
AND timestamp>=Now-24H

Example 11: Obtain All Raw Samples With Comments From Yesterday

SELECT ihRawData.Tagname, ihRawData.TimeStamp, ihRawData.Value
FROM ihRawData
INNER JOIN ihComments ON ihComments.Tagname = ihRawData.Tagname
AND ihComments.Timestamp = ihRawData.Timestamp
AND ihComments.Comment = "The comment" WHERE samplingmode=rawbytime
AND ihComments.Timestamp > Yesterday
AND ihComments.Timestamp < Today

Example 12: Determine the Number of Milliseconds Per Interval With Good Data

SELECT timestamp, tagname, value as TimeGood, quality, intervalmilliseconds FROM ihRawData 
WHERE tagname=Denali.Simulation00001
AND samplingmode=calculated
AND calculationmode=timegood
AND intervalmilliseconds=10s
AND timestamp>='1/20/2003 13:18:00'
AND timestamp<='1/20/2003 13:20:00'

Example 13: Retrieve Raw Minimum and Maximum Values Per Interval

In this example, you use the data retrieved from the query (with the Trend sampling mode) to plot points.

SELECT timestamp, tagname, value, quality
FROM ihRawData
WHERE tagname=dFloatTag5
AND samplingmode=trend
AND intervalmilliseconds=24h
AND timestamp>='1/01/2003 07:00:00'
AND timestamp<='1/10/2003 12:00:00'

Example 14: Retrieve Data with Native Values and Tags Associated With Enumerated Sets

If enumsetrawvalue is set to False, the data is retrieved with string values by default. If enumsetrawvalue is set to True, the raw values are retrieved. Once set, these values are retrieved by default for the current session and will only change when you open a new session.

SELECT * from ihrawdata
WHERE samplingmode='rawbytime' and tagname=mytag
AND enumsetrawvalue=TRUE

SELECT timestamp,value,quality from ihrawdata WHERE tagname = MyTag
AND samplingmode=Interpolated and numberofsamples=6 and criteriastring='#enumnativevalue'

SET enumsetrawvalue=TRUE
SELECT * from ihrawdata
WHERE samplingmode='rawbytime' and tagname=mytag

Example 15: Retrieve Average Values for Enumerated Sets

SET enumsetrawvalue=FALSE

SELECT * from ihrawdata
WHERE tagname LIKE Call AND samplingmode=calculated
AND calculationmode=average

ihComments Table

The ihComments table contains the annotations associated with the collected data. There is a separate row of data in the ihComments table for each comment associated with a tag. For instance, you can have five rows that contain the same tag and timestamp, but each contain a different comment value.

It is possible to have different data types of annotations. Comments are most often strings, but can be binary numbers or BLOBs. Only string comments are returned in the ihComments table.

The following table describes the columns of the ihComments table.
Table 7. ihComments Table
Column Name Data Type Description
Tagname VT_BSTR Tagname property of the tag.
Note: There is no length limit for Historian tag names in the Data Archiver. However, different client applications may have their own limits.
TimeStamp VT_DBTimeStamp The date and time that the data was generated.
TimeStampSeconds VT_DBTimeStamp The date and time that the data was generated.
Microseconds VT_I4 The microsecond portion of the date and time.
StoredOnTimeStamp VT_DBTimeStamp The date and time that the comment was generated.
StoredOnTimeStamp VT_DBTimeStamp The time that the comment was added to the archive.
SuppliedUsername VT_BSTR The username of the currently logged-in Windows user at the time that the comment was entered.
Username VT_BSTR Username provided along with the comment.
Comment VT_BSTR The actual comment.
DataTypeHint VT_BSTR Name of the data type for the comment:
  • String
  • Read-only
  • Optional
SamplingMode VT_BSTR The mode used to sample data from the archive:
  • CurrentValue: Retrieves the current value. Time frame criteria are ignored.
  • Interpolated: Retrieves evenly spaced interpolated values based on interval or NumberOfSamples and time frame criteria.
  • RawByTime: Retrieves raw archive values based on time frame criteria.
  • RawByNumber: Retrieves raw archive values based on the StartTime, NumberOfSamples, and Direction criteria.
    Note: EndTime criteria are ignored for this sampling mode.
  • RawByFilterToggle: Returns filtered time ranges. The values returned are 0 and 1. If the value is 1, then the condition is true and 0 means false. This sampling mode is used with the time range and FilterTag conditions. Results have starting and ending timestamps.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, time frame, and CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian ignores any leftover values at the end instead of putting them into a smaller interval.
  • Trend2: Returns raw minimum and maximum values for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian creates as many intervals of the interval length as will fit into the sampling period, and then creates a remainder interval from whatever time is left. This sampling mode is more suitable than the Trend mode for analysis of minimums and maximums and for plotting programs that can handle unevenly spaced data.
  • InterpolatedtoRaw: Provides raw data in place of interpolated data when the number of samples requested is less than the number of available samples.
  • TrendtoRaw: This mode almost always produces the same results as the Trend mode. The exception is that when a greater number of samples are requested than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend when the number of actual data samples is less than the requested number of samples.
  • TrendtoRaw2: This sampling mode almost always produces the same results as the Trend2 mode. The exception is that when a greater number of samples are requested than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend2 when the number of actual data samples is less than the requested number of samples.
  • LabtoRaw: Provides raw data for the selected calculated data when the number of samples is less than the number of available samples.
Direction VT_BSTR The direction (forward or backward from the start time) of data sampling from the archive.
NumberOfSamples VT_I4 Number of samples from the archive to retrieve.

Samples will be evenly spaced within the time range defined by start and end times for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this value is ignored.

Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
IntervalMilliseconds VT_I4 For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
CalculationMode VT_BSTR The calculation mode, if used.
FilterTag VT_BSTR Tagname used to define the filter, if specified. Only a single tag can be specified, and wildcards are not supported.
FilterMode VT_BSTR The type of time filter:
  • ExactTime: Retrieves data for the exact times that the filter condition is True.
  • BeforeTime: Retrieves data from the time of the last False filter condition to the time of the next True condition.
  • AfterTime: Retrieves data from the time of the last True filter condition to the time of the next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the time of the next False condition.

The FilterMode defines how time periods before and after transitions in the filter condition should be handled.

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and ending at the timestamp of the archive value that triggered the False condition.
FilterComparisonMode VT_BSTR The type of comparison to be made on the filter comparison value:
  • Equal: Filter condition is True when the FilterTag value is equal to the comparison value.
  • EqualFirst: Filter condition is True when the FilterTag value is equal to the first comparison value.
  • EqualLast: Filter condition is True when the FilterTag value is equal to the last comparison value.
  • NotEqual: Filter condition is True when the FilterTag value is not equal to the comparison value.
  • LessThan: Filter condition is True when the FilterTag value is less than the comparison value.
  • GreaterThan: Filter condition is True when the FilterTag value is greater than the comparison value.
  • LessThanEqual: Filter condition is True when the FilterTag value is less than or equal to the comparison value.
  • GreaterThanEqual: Filter condition is True when the FilterTag value is greater than or equal to the comparison value.
  • AllBitsSet: Filter condition is True when the binary FilterTag value is equal to all the bits in the condition. It is represented as ^ to be used in FilterExpression.
  • AnyBitSet: Filter condition is True when the binary FilterTag value is equal to any of the bits in the condition. It is represented as ~ to be used in FilterExpression.
  • AnyBitNotSet: Filter condition is True when the binary FilterTag value is not equal to any one of the bits in the condition. It is represented as !~ to be used in FilterExpression.
  • AllBitsNotSet: Filter condition is True when the binary FilterTag value is not equal to all the bits in the condition. It is represented as !^ to be used in FilterExpression.
This column defines how archive FilterTag values should be compared to FilterValue values to establish the state of the filter condition. If FilterTag and FilterComparisonValue values are specified, time periods are filtered from the results where the filter condition is False.
FilterValue VT_BSTR The value with which to compare the FilterTag value to determine appropriate filter times.
FilterExpression VT_BSTR An expression which includes one or more filter conditions. The type of conditions used are:
  • AND
  • OR
  • Combination of AND and OR
This column can be used instead of the FilterTag, FilterComparisonMode, and FilterValue columns. While using FilterExpression, the expression is passed within single quotes, and for complex expressions you write the conditions within parentheses. There is no maximum length for FilterExpression, but if called using OLE DB or Excel, these tools may have their own limitations.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihComments Examples

Example SQL statements for the ihComments table are outlined in the following examples.

Example 1: Retrieve All Comments for a Specific Tag for This Month

SELECT * FROM ihcomments WHERE tagname LIKE '*001'
AND timestamp>bom

Example 2: Retrieve Comments That Contain a Substring

SELECT * FROM ihcomments WHERE comment LIKE '*abc*'

Example 3: Retrieve All Comments in an Archive

SELECT * FROM ihComments WHERE timestamp<=Now
AND samplingmode=rawbytime

ihTrend Table

The ihTrend table allows you to compare multiple tags for the same timestamp. It contains a row of data for each unique timestamp, but with columns from one or more tags. The column names are dynamic and determined by the returned tag names. The ihTrend table is similar to a pivot table or, for instance, a cross-tab report that you can create in Crystal Reports.

The ihTrend table can store up to 100 columns in a returned set. This allows you to compare Value columns with up to 99 tags for a single timestamp, or Value and Quality columns with up to 49 tags.

Note: Currently, you cannot analyze the ihTrend table in Crystal Reports or the Microsoft SQL Server DTS application.

The following table describes the columns of the ihTrend table, including all possible tag columns. Different queries on this table can produce different column results.

Note: In all column names in the following table, TagID is used as a placeholder for the actual tag name.
Table 8. IhTrend Table
Column Name Data Type Description
TimeStamp VT_DBTimeStamp The date and time that the trend was generated.
TimeStampSeconds VT_DBTimeStamp The date and time for the data sample.
Microseconds VT_I4 The microsecond interval for the data sample.
SamplingMode VT_BSTR The mode of sampling data from the archive:
  • CurrentValue: Retrieves the current value. Time frame criteria are ignored.
  • Interpolated: Retrieves evenly spaced interpolated values based on interval or NumberOfSamples and time frame criteria.
  • RawByTime: Retrieves raw archive values based on time frame criteria.
  • RawByNumber: Retrieves raw archive values based on the StartTime, NumberOfSamples, and Direction criteria.
    Note: EndTime criteria are ignored for this mode.
  • RawByFilterToggle: Returns filtered time ranges. The values returned are 0 and 1. If the value is 1, then the condition is true and 0 means false. This mode is used with the time range and FilterTag conditions. Results start and end with timestamps.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, time frame, and CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian ignores any leftover values at the end instead of putting them into a smaller interval.
  • Trend2: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian puts leftover values into a remainder interval. This mode is more suitable than the Trend mode for analysis of minimums and maximums and for plotting programs that can handle unevenly spaced data.
  • InterpolatedtoRaw: Provides raw data in place of interpolated data when the number of samples is less than the number of available samples.
  • TrendtoRaw: This mode almost always produces the same results as the Trend mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend when the number of actual data samples is less than the requested number of samples.
  • TrendtoRaw2: This mode almost always produces the same results as the Trend2 mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend2 when the number of actual data samples is less than the requested number of samples.
  • LabtoRaw: Provides raw data for the selected calculated data when the number of requested samples is less than the number of available samples.
Direction VT_BSTR The direction (forward or backward from the start time) of data sampling from the archive.
NumberOfSamples VT_I4 Number of samples to retrieve from the archive.

Samples will be evenly spaced within the start and end times defined for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this column is ignored.

Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
IntervalMilliseconds VT_I4 For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
CalculationMode VT_BSTR This column applies only if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:
  • Average
  • Count
  • Maximum
  • MaximumTime
  • Minimum
  • MinimumTime
  • StandardDeviation
  • Total
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
FilterTag VT_BSTR Tagname used to define the filter, if specified. Only a single tag can be specified. Wildcards are not supported.
FilterMode VT_BSTR The type of time filter:
  • ExactTime: Retrieves data for the exact times that the filter condition is True.
  • BeforeTime: Retrieves data from the time of the last False filter condition to the time of the next True condition.
  • AfterTime: Retrieves data from the time of the last True filter condition to the time of the next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the time of the next False condition.

This value defines how time periods before and after transitions in the filter condition should be handled.

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and ending at the timestamp of the archive value that triggered the False condition.
FilterComparisonMode VT_BSTR The type of comparison to be made on the filter comparison value:
  • Equal: Filter condition is True when the FilterTag value is equal to the comparison value.
  • EqualFirst: Filter condition is True when the FilterTag value is equal to the first comparison value.
  • EqualLast: Filter condition is True when the FilterTag value is equal to the last comparison value.
  • NotEqual: Filter condition is True when the FilterTag value is not equal to the comparison value.
  • LessThan: Filter condition is True when the FilterTag value is less than the comparison value.
  • GreaterThan: Filter condition is True when the FilterTag value is greater than the comparison value.
  • LessThanEqual: Filter condition is True when the FilterTag value is less than or equal to the comparison value.
  • GreaterThanEqual: Filter condition is True when the FilterTag value is greater than or equal to the comparison value.
  • AllBitsSet: Filter condition is True when the binary FilterTag value is equal to all the bits in the condition. It is represented as ^ to be used in FilterExpression.
  • AnyBitSet: Filter condition is True when the binary FilterTag value is equal to any of the bits in the condition. It is represented as ~ to be used in FilterExpression.
  • AnyBitNotSet: Filter condition is True when the binary FilterTag value is not equal to any one of the bits in the condition. It is represented as !~ to be used in FilterExpression.
  • AllBitsNotSet: Filter condition is True when the binary FilterTag value is not equal to all the bits in the condition. It is represented as !^ to be used in FilterExpression.
This column defines how archive values for the FilterTag value should be compared to the FilterValue value to establish the state of the filter condition. If FilterTag and FilterComparisonValue values are specified, time periods are filtered from the results where the filter condition is False.
FilterValue VT_BSTR The value with which to compare the FilterTag value to determine appropriate filter times.
FilterExpression VT_BSTR An expression which includes one or more filter conditions. The type of conditions used are:
  • AND
  • OR
  • Combination of AND and OR
This column can be used instead of the FilterTag, FilterComparisonMode, and FilterValue columns. While using FilterExpression, the expression is passed within single quotes. For complex expressions, you write the conditions within parentheses. There is no maximum length for FilterExpression, but if called using OLE DB or Excel, these tools may have their own limitations.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.
TagID.Value VT_VARIANT The value of the data for the specified tag ID.
TagID.Quality VT_VARIANT For non-raw sampled data, this column displays the percentage of good quality samples in the interval. For instance, a value of 100 means all samples in the interval are good.

For raw sampled data, data values are:

  • Good
  • Bad
  • Uncertain
  • Not Available
  • Really Unknown
This column also includes the subquality of the data value, if it exists:
  • NonSpecific
  • ConfigError
  • NotConnected
  • DeviceFail
  • SensorFail
  • LastKnownValue
  • CommFailure
  • OutOfService
  • ScaledOutOfRange
  • OffLine
  • NoValue
  • Really Unknown
TagID.Tagname VT_BSTR Tagname property of the specified tag ID.
TagID.Description VT_BSTR User description for the specified tag ID.
TagID.EngUnits VT_BSTR Engineering unit description for the specified tag ID.
TagID.Comment VT_BSTR User comment associated with the specified tag ID.
TagID.DataType VT_BSTR The data type for the specified tag ID:
  • Scaled
  • SingleFloat
  • DoubleFloat
  • SingleInteger
  • DoubleInteger
  • QuadInteger
  • UnsignedSingleInteger
  • UnsignedDoubleInteger
  • UnsignedQuadInteger
  • FixedString
  • VariableString
  • Byte
  • Boolean
  • BLOB
  • Time
  • Undefined
The data type returned in this column is the data type that you defined in the Historian Administrator application.
TagID.FixedStringLength VT_UI1 This value is 0 unless the data type is FixedString. If the data type is FixedString, this number represents the maximum length of the string value.
TagID.CollectorName VT_BSTR The name of the collector responsible for collecting data for the specified tag ID.
TagID.SourceAddress VT_BSTR The address used to identify the specified tag ID at the data source. For iFIX systems, this is the NTF (Node.Tag.Field).
TagID.CollectionType VT_BSTR Type of collection used to acquire data for the tag:
  • Unsolicited: The collector accepts data from the source whenever the source presents the data.
  • Polled: The collector acquires data from a source on a periodic schedule determined by the collector.
Note: Not all collectors support unsolicited collection.
TagID.CollectionInterval VT_I4 The time interval, in milliseconds, between readings of data from this tag.

For polled collection, this field represents the time between samples. For unsolicited collection, this field represents the minimum time allowed between samples.
TagID.CollectionOffset VT_I4 The time shift from midnight, in milliseconds, for collection of data from this tag.
TagID.LoadBalancing VT_BOOL Indicates whether the data collector should automatically shift the phase of sampling to distribute the activity of the processor evenly over the polling cycle for the specified tag ID. This is sometimes called phase shifting.
TagID.TimeStampType VT_BSTR The timestamp type applied to data samples at collection time:
  • Source: The source delivers the timestamp along with the data sample.
  • Collector: The collector delivers the timestamp along with the collected data.
TagID.HiEngineeringUnits VT_R8 The high end of the engineering units range. Used only for scaled data types and input scaled tags.
TagID.LoEngineeringUnits VT_R8 The low end of the engineering units range. Used only for scaled data types and input scaled tags.
TagID.InputScaling VT_BOOL Indicates whether the measurement should be converted to an engineering units value. When set to False, the measurement is interpreted as a raw measurement.

When set to True, the system converts the value to engineering units by scaling the value between the HiScale and LoScale values. If not enabled, the system assumes the measurement is already converted into engineering units.
TagID.HiScale VT_R8 The high-end value of the input scaling range used for the tag.
TagID.LoScale VT_R8 The low-end value of the input scaling range used for the tag.
TagID.CollectorCompression VT_BOOL Indicates whether collector compression is enabled for the specified tag ID.

Collector compression applies a smoothing filter to incoming data by ignoring incremental changes in values that fall within a deadband centered around the last collected value. The collector passes (to the archiver) any new value that falls outside the deadband and then centers the deadband around the new value.
TagID.CollectorDeadbandPercentRange VT_R4 The current value of the compression deadband.
TagID.ArchiveCompression VT_BOOL Indicates whether archive collector compression is enabled for the tag.
TagID.ArchiveDeadbandPercentRange VT_R4 The current value of the archive compression deadband.
TagID.CollectorGeneral1 VT_BSTR The general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral2 VT_BSTR The general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral3 VT_BSTR The general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral4 VT_BSTR The general (or spare) configuration fields for the specified tag ID.
TagID.CollectorGeneral5 VT_BSTR The general (or spare) configuration fields for the specified tag ID.
TagID.ReadSecurityGroup VT_BSTR The name of the Windows security group that controls the reading of data for the specified tag ID.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.
TagID.WriteSecurityGroup VT_BSTR The name of the Windows security group that controls the writing of data for the specified tag ID.

Refer to "Implementing Historian Security" in the Getting Started with Historian manual for definitions of the various security levels and groups.
TagID.AdministratorSecurityGroup VT_BSTR The name of the Windows security group responsible for controlling configuration changes for the specified tag ID.
TagID.Calculation VT_BSTR The equation for the calculation performed for the specified tag ID.
TagID.LastModified VT_DBTimeStamp The date and time that the tag configuration was last modified. The time structure includes milliseconds.
TagID.LastModifiedUser VT_BSTR The username of the Windows user who last modified the tag configuration.
TagID.CollectorType VT_BSTR The type of collector responsible for collecting data for the specified tag ID:
  • Undefined
  • iFIX
  • Simulation
  • OPC
  • File
  • iFIXLabData
  • ManualEntry
  • Simulation
  • Other
TagID.StoreMilliseconds VT_BOOL Indicates whether time resolution in milliseconds is enabled for the specified tag ID.

If not enabled, time resolution is in seconds instead of milliseconds. Maximum data compression is achieved when this value is set to False. This is the optimum setting for most applications.
TagID.UTCBias VT_I4 The time zone bias for the specified tag ID. Time zone bias is used to indicate the natural time zone of the tag expressed as an offset from UTC (Universal Time Coordinated) in minutes.

UTC is the international time standard, the current term for what was commonly referred to as Greenwich Mean Time (GMT).
TagID.AverageCollectionTime VT_I4 The average time it takes to execute the calculation tag since you started the Calculation Collector for the specified tag ID.
TagID.CollectionDisabled VT_I4 Indicates whether collection is enabled (0) or disabled (1) for the specified tag ID. The default setting is enabled (0).
TagID.CollectorCompressionTimeout VT_I4 Indicates the maximum amount of time the collector will wait between sending samples to the archiver. This time is kept per tag, as different tags report to the archiver at different times.

This value should be in increments of your collection interval, and not less.

Ideally, this field is used for polled data values. It can be used with unsolicited data, but when you do so, you are dependent on the data source for the value to change. With unsolicited data, since Historian only records the value when it changes, the actual time before the timeout might exceed the compression timeout.
TagID.ArchiveCompressionTimeout VT_I4 Indicates the maximum amount of time from the last stored point before another point is stored, if the value does not exceed the archive compression deadband for the specified tag ID.
TagID.InterfaceAbsoluteDeadbanding VT_BOOL Indicates whether absolute collector deadbanding is enabled for the specified tag ID.
TagID.InterfaceAbsoluteDeadband VT_R8 Indicates the value for absolute collector deadbanding.
TagID.ArchiveAbsoluteDeadbanding VT_BOOL Indicates whether absolute archive deadbanding is enabled for the specified tag ID.
TagID.ArchiveAbsoluteDeadband VT_R8 Indicates the value for absolute archive deadbanding.
TagID.SpikeLogic VT_BOOL Indicates whether Spike Logic is enabled on the collector.
TagID.SpikeLogicOverride VT_BOOL Indicates whether the Spike Logic setting for the specified tag ID overrides the collector setting (True) or the collector setting is used (False).

Use care when building queries against the ihTrend table. Because a query to this table compares multiple tags at the same time, it takes longer to query the ihTrend table than it does the ihRawData table. The ihTrend table can be quite large, so be sure to either use the default start and end times, or define a specific time interval. See Query Performance Optimization for more ideas on how to optimize your query of the ihTrend table.

ihTrend Examples

Example SQL statements for the ihTrend table are outlined in the following examples.

Example 1: Retrieve Value and Quality of the First 50 Tags

SELECT timestamp, *.value, *.quality FROM ihtrend

Example 2: Retrieve Value of the First 100 Tags

SELECT timestamp, *.value FROM ihTrend

Example 3: Retrieve Values of All Tags That Match a Specific Pattern

SELECT timestamp,*0001.value FROM ihtrend ORDER BY MY_SERVER.Simulation00001.Value

Example 4: Retrieve Hourly Interpolated Values of TagNames That Match *0001

SET samplingmode=interp, intervalmilliseconds=1h
SELECT timestamp, *0001.value FROM ihtrend
ORDER BY Simulation00001.value DESC, timestamp DESC

Example 5: Retrieve Maximum Values of All TagNames That Match *0001

The following example shows how to use a TagName (simulation.00001.Value) in a WHERE clause.

SELECT timestamp, *0001.value FROM ihtrend
WHERE timestamp>='28-nov-2001 00:00'
AND timestamp<='29-nov-2001 00:00:00'
AND samplingmode=calc
AND intervalmilliseconds=1h
AND calculationmode=max
AND simulation00001.Value > 1000 ORDER BY timestamp

Example 6: Select Interpolated Values for All Single Float Tags

The following example shows how to select interpolated values for all single float tags, without doing a JOIN with the ihTags table to retrieve the DataType property.

SELECT timestamp, *.value,*.description FROM ihtrend
WHERE timestamp>>='28-nov-2001 00:00'
AND timestamp<='29-nov-2001 00:00:00'
AND samplingmode=calculated
AND intervalmilliseconds=2h
AND *.datatype = singlefloat ORDER BY timestamp

Example 7: Select Interpolated Data for TagNames That Match sim*

The following example shows how to sort the returned rows by a TagName , simulation.00001.Value.

SET starttime='28-nov-2001 00:00', endtime='29-nov-2001 00:00:00', samplingmode=interp, intervalmilliseconds=1h
SELECT timestamp, sim*.*, sim*.description, sim*.lastmodifieduser FROM ihtrend
WHERE sim*.description LIKE '*sim*'
AND sim*.description like '*first*'
AND *.datatype = singlefloat
ORDER BY simulation00001.value DESC, timestamp

ihQuerySettings Table

The ihQuerySettings table contains the current session settings. These settings are applied to all queries you make in a session, unless overridden with a WHERE clause. This table displays settings stored in the provider, and has nothing to do with the data stored in the archives.

The ihQuerySettings table provides a convenient way to display all your session settings. You cannot, however, write or update settings in this table. This table contains only one row with the settings for the current session. The only way to change these parameters is by using the SET statement.

The following table describes the columns of the ihQuerySettings table.
Table 9. ihQuerySettings Table
Column Name Data Type Description
StartTime VT_DBTimeStamp The start time of the query. This represents the earliest timestamp for any tag contained in the query.

If no StartTime value is specified, the start time is two hours prior to execution of the query.
EndTime VT_DBTimeStamp The end time of the query. This represents the latest timestamp for any tag contained in the query.

If no EndTime value is specified, the end time is the time that you execute the query.
SamplingMode VT_BSTR The mode of sampling data from the archive:
  • CurrentValue: Retrieves the current value. Time frame criteria are ignored.
  • Interpolated: Retrieves evenly spaced interpolated values based on interval or NumberOfSamples and time frame criteria.
  • RawByTime: Retrieves raw archive values based on time frame criteria.
  • RawByNumber: Retrieves raw archive values based on the StartTime, NumberOfSamples, and Direction criteria.
    Note: EndTime criteria are ignored for this mode.
  • RawByFilterToggle: Returns filtered time ranges. The values returned are 0 and 1. If the value is 1, then the condition is true and 0 means false. This mode is used with the time range and FilterTag conditions. Results start and end with timestamps.
  • Calculated: Retrieves evenly spaced calculated values based on NumberOfSamples, interval, time frame, and CalculationMode criteria.
  • Lab: Returns actual collected values without interpolation.
  • Trend: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian ignores any leftover values at the end instead of putting them into a smaller interval.
  • Trend2: Returns the raw minimums and maximums for each specified interval. Use this mode to maximize performance when retrieving data points for plotting. If the sampling period does not evenly divide by the interval length, Historian puts leftover values into a remainder interval. This mode is more suitable than the Trend mode for analysis of minimums and maximums and for plotting programs that can handle unevenly spaced data.
  • InterpolatedtoRaw: Provides raw data in place of interpolated data when the number of samples is less than the number of available samples.
  • TrendtoRaw: This mode almost always produces the same results as the Trend mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend when the number of actual data samples is less than the requested number of samples.
  • TrendtoRaw2: This mode almost always produces the same results as the Trend2 mode. The exception is that when the number of samples requested is greater than the number of raw data points, this mode returns all available raw data points with no further processing. This mode is therefore used instead of Trend2 when the number of actual data samples is less than the requested number of samples.
  • LabtoRaw: Provides raw data for the selected calculated data when the number of requested samples is less than the number of available samples.
Calculated is the default setting.
Direction VT_BSTR The direction (Forward or Backward from the start time) of data sampling from the archive. The default value is Forward.
NumberOfSamples VT_I4 Number of samples to retrieve from the archive.

Samples will be evenly spaced within the specified start and end times defined for most sampling modes. For the RawByNumber mode, this column determines the maximum number of values to retrieve. For the RawByTime mode, this column is ignored.

Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If NumberofSamples is used, IntervalMilliseconds is not used.
IntervalMilliseconds VT_I4 For non-raw sampled data, this column represents a positive integer for the time interval (in milliseconds) between returned samples.
Note: The NumberofSamples and IntervalMilliseconds columns are mutually exclusive. If IntervalMilliseconds is used, NumberofSamples is not used.
CalculationMode VT_BSTR This column applies only if the SamplingMode is set to Calculated. It represents the type of calculation to perform on archive data:
  • Average
  • Count
  • Maximum
  • MaximumTime
  • Minimum
  • MinimumTime
  • OPCQOr and OPCQAnd
  • StandardDeviation
  • StateCount
  • StateTime
  • Total
  • RawAverage
  • RawStandardDeviation
  • RawTotal
  • TimeGood
  • FirstRawValue
  • FirstRawTime
  • LastRawValue
  • LastRawTime
  • TagStats
The default value is Average.
FilterTag VT_BSTR Tagname used to define the filter, if specified. Only a single tag can be specified. Wildcards are not supported.
FilterMode VT_BSTR The type of time filter:
  • ExactTime: Retrieves data for the exact times that the filter condition is True.
  • BeforeTime: Retrieves data from the time of the last False filter condition to the time of the next True condition.
  • AfterTime: Retrieves data from the time of the last True filter condition to the time of the next False condition.
  • BeforeAndAfterTime: Retrieves data from the time of the last False filter condition to the time of the next False condition.

This value defines how time periods before and after transitions in the filter condition should be handled.

For example, AfterTime indicates that the filter condition should be True starting at the timestamp of the archive value that triggered the True condition and ending at the timestamp of the archive value that triggered the False condition.
FilterComparisonMode VT_BSTR The type of comparison to be made on the filter comparison value:
  • Equal: Filter condition is True when the FilterTag value is equal to the comparison value.
  • EqualFirst: Filter condition is True when the FilterTag value is equal to the first comparison value.
  • EqualLast: Filter condition is True when the FilterTag value is equal to the last comparison value.
  • NotEqual: Filter condition is True when the FilterTag value is not equal to the comparison value.
  • LessThan: Filter condition is True when the FilterTag value is less than the comparison value.
  • GreaterThan: Filter condition is True when the FilterTag value is greater than the comparison value.
  • LessThanEqual: Filter condition is True when the FilterTag value is less than or equal to the comparison value.
  • GreaterThanEqual: Filter condition is True when the FilterTag value is greater than or equal to the comparison value.
  • AllBitsSet: Filter condition is True when the binary FilterTag value is equal to all the bits in the condition. It is represented as ^ to be used in FilterExpression.
  • AnyBitSet: Filter condition is True when the binary FilterTag value is equal to any of the bits in the condition. It is represented as ~ to be used in FilterExpression.
  • AnyBitNotSet: Filter condition is True when the binary FilterTag value is not equal to any one of the bits in the condition. It is represented as !~ to be used in FilterExpression.
  • AllBitsNotSet: Filter condition is True when the binary FilterTag value is not equal to all the bits in the condition. It is represented as !^ to be used in FilterExpression.
This option defines how archive values for the FilterTag value should be compared to the FilterValue value to establish the state of the filter condition. If FilterTag and FilterComparisonValue values are specified, time periods are filtered from the results where the filter condition is False.
FilterValue VT_BSTR The value with which to compare the FilterTag value to determine appropriate filter times.
FilterExpression VT_BSTR An expression which includes one or more filter conditions. The type of conditions used are:
  • AND
  • OR
  • Combination of AND and OR
This column can be used instead of the FilterTag, FilterComparisonMode, and FilterValue columns. While using FilterExpression, the expression is passed within single quotes. For complex expressions, you write the conditions within parentheses. There is no maximum length for this value, but if called using OLE DB or Excel, these tools may have their own limitations.
TimeZone VT_BSTR The type of time zone used:
  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

If the query result contains more rows than the RowCount value, the Historian OLE DB Provider truncates the extra rows. The truncation is performed last. For instance, if you use ORDER BY in your SELECT statement, the truncation occurs after the rows are ordered.
AlarmType VT_BSTR Indicates the alarm type:
  • Alarms
  • Alarm_History
  • Events

ihQuerySettings Examples

Example SQL statements for the ihQuerySettings table are outlined in the following examples.

Example 1: Show All Settings for the Current Session

SELECT * FROM ihquerysettings

Example 2: Show the Selected Session Settings

SELECT starttime, endtime FROM ihquerysettings

ihCalculationDependencies Table

The ihCalculationDependencies table contains the calculation and server-to-server tags and their triggers. The following table describes the columns of the ihCalculationDependencies table.

Table 10. ihCalculationDependencies Table
Column Name Data Type Description
Tagname VT_BSTR A calculation or server-to-server tag with unsolicited collection and at least one dependent tag.
DependentTagname VT_BSTR A dependent tagname. If a tag has multiple dependent tags, there are multiple rows in the table for that tagname.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihCalculationDependencies Examples

Example SQL statements for the ihCalculationDependencies table are outlined in the following examples.

Example 1: Show the Dependencies for a Specific Tag

SELECT * FROM ihcalculationdependencies WHERE tagname = c1

Example 2: Show the Dependencies for a Specific Dependent Tag

SELECT * FROM ihcalculationdependencies
WHERE dependenttagname=brahms.ai1.f_cv

ihAlarms Table

The ihAlarms table contains collected alarm and event data. The following table describes the columns of the ihAlarms table.

CAUTION:
When you perform joins of the ihRawData and ihAlarms tables, you can easily construct queries that temporarily consume all your system resources. Although this scenario typically does not affect data collection, it can interfere with data analysis. To avoid this issue, always define a start and end time for the query to limit the number of rows returned.
Table 11. ihAlarms Table
Column Name Data Type Description
AlarmID VT_I4 The unique ID of the alarm or event in the Historian alarm database.
ItemID VT_BSTR The OPC ItemID of the alarm. This contains the source address of the data access tag with which the alarm is associated. This can contain a NULL value if an alarm is not associated with a tag.
Source VT_BSTR The unique identifier used by the OPC AE Collector for the alarm or event.
DataSource VT_BSTR The collector interface name associated with the alarm or event.
Tagname VT_BSTR The Historian tag name associated with the alarm. This value is NULL unless the tag is also collected by Historian.
AlarmType VT_BSTR The alarm type:
  • Alarms: In Historian, the full life cycle of an alarm is stored as a single record in the alarm archive.
  • Alarm_History: The separate transitions for all alarms. One row per transition is returned.
  • Events: The simple and tracking events.
EventCategory VT_BSTR The OPC event category of the alarm or event.
Condition VT_BSTR The OPC condition of the alarm. Does not apply to event data. This value combined with the Source value comprises an alarm.
SubCondition VT_BSTR The OPC subcondition of the alarm. Does not apply to event data. This value represents the state of the alarm.
StartTime VT_DBTimeStamp The start time or timestamp of the alarm or event.
EndTime VT_DBTimeStamp The end time of the alarm. Does not apply to event data.
AckTime VT_DBTimeStamp The time the alarm was acknowledged. Does not apply to event data.
Microseconds VT_I4 The microsecond portion of the date and time.
Message VT_BSTR The message attached to the alarm or event.
Acked VT_BOOL Stores the acknowledgement status of the alarm. If the alarm is acknowledged, this is set to TRUE.
Severity VT_I4 The severity of the alarm or event. Stored as an integer value with a range of 11000.
Actor VT_BSTR The operator who acknowledged the alarm, or caused the tracking event.
Quality VT_VARIANT The quality of the alarm or event. Stored as a string, with values of GOOD or BAD.
TimeZone VT_BSTR

The type of time zone used:

  • Client
  • Server
  • Explicit bias number (number of minutes from GMT)
DaylightSavingTime VT_BOOL Indicates whether Daylight Saving Time logic should be applied to timestamps.
RowCount VT_I4 The maximum number of rows returned by the current query.
User-Defined Variable #X VT_VARIANT User-defined variables. This is a dynamic list of columns that varies based on the collectors running on the Historian system.
Note: Additional fields may be added by third-party products such as iFIX. Please consult the relevant product documentation for further information.

ihAlarms Examples

Example 1: Show All Alarms for the Last Two Hours, Including Vendor Attributes

SELECT * FROM ihAlarms
SELECT * FROM ihAlarms WHERE alarmtype = alarms //same as above

Example 2: Show Alarm History

SELECT * FROM ihAlarms WHERE alarmtype = alarm_history

Example 3: Show Tracking and System Events

SELECT * FROM ihAlarms WHERE alarmtype = events

Example 4: Return All Closed Events and Associated Tag Data

SELECT
alarmid, ihalarms.tagname, ihalarms.starttime, ihalarms.endTime, ihrawdata.timestamp, ihrawdata.value
FROM ihalarms, ihrawdata
WHERE ihalarms.tagname=ihrawdata.tagname
AND ihalarms.starttime <= ihrawdata.timestamp
AND ihalarms.endtime >= ihRawdata.timestamp
AND ihalarms.subcondition == "OK"
OR ihalarms.quality = "Bad"
ORDER BY ihalarms.starttime
Note: When you join data from the ihRawData and ihAlarms tables, be sure to specify a timestamp range.

Example 5: Return All Open Alarms and Associated Tag Data

SELECT
alarmid, ihalarms.tagname, ihalarms.starttime, ihalarms.endTime, ihrawdata.timestamp, ihrawdata.value
FROM ihalarms, ihrawdata
WHERE ihalarms.tagname=ihrawdata.tagname
AND ihalarms.starttime <= ihrawdata.timestamp
AND ihalarms.endtime >= ihRawdata.timestamp
AND ihalarms.subcondition <> "OK"
AND ihalarms.quality = "Good"
ORDER BY ihalarms.starttime
Note: When you join data from the ihRawData and ihAlarms tables, be sure to specify a timestamp range.

ihEnumeratedSets Table

The ihEnumeratedSets table contains information about enumerated sets that are defined in the system. The following table describes the columns of the ihEnumeratedSets table.

Table 12. ihEnumeratedSets Table
Column Name Data Type Description
SetName VT_BSTR The name of the set.
Description VT_BSTR The description of the set.
NumberofStates VT_I4 The number of states a set contains.
NumberofTagReferences VT_I4 The number of tags with which a set is associated.
SetDataType VT_BSTR The data type of the set.
AdministratorSecurityGroup VT_BSTR The security group to which the set belongs.
LastModifiedUser VT_BSTR Indicates which user last modified the set.
LastModifiedTime VT_DBTimeStamp Indicates the last time the set was modified.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihEnumeratedSets Examples

Sample SQL statements for the ihEnumeratedSets table are outlined in the following examples.

Example 1: Retrieve All Sets By Using Integer States

SELECT * FROM ihEnumeratedSets
WHERE SetDataType=integer

Example 2: Retrieve a Set By Name From Sets

SELECT * FROM ihEnumeratedSets
WHERE setname like PLC1

ihEnumeratedStates Table

The ihEnumeratedStates table contains information about enumerated sets that are defined in the system. The following table describes the columns of the ihEnumeratedStates table.

Table 13. ihEnumeratedStates Table
Column Name Data Type Description
SetName VT_BSTR The name of the set.
Description VT_BSTR The description of the set.
NumberofStates VT_I4 The number of states a set contains.
NumberofTagReferences VT_I4 The number of tags with which a set is associated.
SetDataType VT_BSTR The data type of the set.
AdministratorSecurityGroup VT_BSTR The security group to which the set belongs.
LastModifiedUser VT_BSTR Indicates which user last modified the set.
LastModifiedTime VT_DBTimeStamp Indicates the last time the set was modified.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihEnumeratedStates Examples

Sample SQL statements for the ihEnumeratedStates table are outlined in the following examples.

Example 1: Retrieve All States That Belong to a Specific Set

SELECT * FROM ihEnumeratedStates
WHERE setname=plcset1 order by statelowvalue ascending

Example 2: Retrieve All States From a Specific Set

SELECT * FROM ihEnumeratedStates
WHERE setname = 'setname'

ihUserDefinedTypes Table

The ihUserDefinedTypes table contains information about user-defined data types in the system.

Use this table to see the set of types and get information about each field in the data type.

The following table describes the columns of the ihUserDefinedTypes table.

Table 14. ihUserDefinedTypes Table
Column Name Data Type Description
TypeName VT_BSTR The name of the user-defined type.
DataType VT_BSTR The data type of the user-defined type.
Description VT_BSTR The description of the user-defined type.
StoreFieldQuality VT_BOOL Indicates whether the field-level quality is stored.
NumberofFields VT_I4 The number of fields a user-defined type contains.
NumberofTagReferences VT_I4 The number of tags with which a user-defined type is associated.
AdministratorSecurityGroup VT_BSTR The security group to which the user-defined type belongs.
LastModifiedUser VT_BSTR Indicates which user last modified the user-defined type.
LastModifiedTime VT_DBTimeStamp Indicates the last time the user-defined type was modified.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihUserDefinedTypes Examples

Sample SQL statements for the ihUserDefinedType table are outlined in the following examples.

Example 1: Retrieve All User-Defined Types

SELECT * FROM ihuserdefinedtypes

Example 2: Retrieve a User-Defined Type By Name

SELECT * FROM ihuserdefinedtypes WHERE typename LIKE New

ihFields Table

The ihFields table contains information about field elements that are specified in user-defined data types. The following table describes the columns of the ihFields table.

Table 15. ihFields Table
Column Name Data Type Description
TypeName VT_BSTR The name of the user-defined type.
FieldName VT_BSTR The name of the field.
Description VT_BSTR The description of the field.
FieldValueDataType VT_BSTR The data type of the field.
MasterField VT_BOOL Indicates whether the field is a master field.
RowCount VT_I4 Indicates the maximum number of rows that can be returned. A value of 0 indicates that there is no limit to the number of rows returned.

ihFields Examples

Sample SQL statements for the ihFields table are outlined in the following examples.

Example: Retrieve All Fields for a Specific Type

SELECT * FROM ihfields WHERE typename='MyUserDefinedType'