Historian Database Tables

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 page in 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 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 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 the enumnativevalue query modifier is not set, the data is retrieved with string values by default. If it is set, the raw values are retrieved. These values are then 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 criteriastring='#enumnativevalue'

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

SET criteriastring='#enumnativevalue'
SELECT * from ihrawdata
WHERE samplingmode='rawbytime' and tagname=mytag

Example 15: Retrieve Average Values for Enumerated Sets

SET criteriastring='#enumnativevalue'

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 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 alarms and events 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 1???1000.
`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'