Query Modifiers

Query Modifiers are used for retrieving data that has been stored in the archive. They are used along with sampling and calculation modes to get a specific set of data. The following sections describe the Query Modifiers in Historian.
  • ONLYGOOD

    The ONLYGOOD modifier excludes bad and uncertain data quality values from retrieval and calculations. Use this modifier with any sampling or calculation mode but it is most useful with Raw and CurrentValue queries.

    All the calculation modes such as minimum or average exclude bad values by default, so this modifier is not required with those.

    Example 1:Demonstrating the Behavior

    Import the following data to demonstrate the behavior of ONLYGOOD

    [Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
BADDQTAG,SingleFloat,60,0
[Data]
Tagname,TimeStamp,Value, DataQuality
BADDQTAG,12-Jul-2012 8:59:00.000,22.7,Good
BADDQTAG,12-Jul-2012 9:08:00.000,12.5,Bad
BADDQTAG,12-Jul-2012 9:14:00.000,7.0,Bad
BADDQTAG,12-Jul-2012 9:22:00.000,4.8,Good
    Example 2: Excluding bad data from raw data query

    Without any query modifier, all raw samples are returned from a RawByTime query.

    select timestamp,value,quality 
from ihrawdata
where tagname = BADDQTAG and samplingmode=Rawbytime and timestamp < now
    Time Stamp Value Quality
    7/12/2012 08:59:00 22.7000000 Good, NonSpecific
    7/12/201209:08:00 12.5000000 Bad, NonSpecific
    7/12/201209:14:00 7.0000000 Bad, NonSpecific
    7/12/201209:22:00 4.8000000 Good, NonSpecific
    Note: The above results have both good and bad samples:

    Now by using the ONLYGOOD modifier, you can exclude the bad quality values:

    select timestamp,value,quality 
from ihrawdata
where tagname = BADDQTAG and samplingmode=Rawbytime and timestamp < now and criteriastring="#ONLYGOOD" 
timestamp             value              quality
    Time Stamp Value Quality
    7/12/2012 08:59:00 22.7000000 Good, NonSpecific
    7/12/201209:22:00 4.8000000 Good, NonSpecific
    Note: Only the good samples have been retrieved.
    Example 3: Retrieving the last known value
    Value

    You can use the ONLYGOOD query modifier to show the last known good value for a tag. If the collector loses communication with the data source or has shut down, you can ignore the bad data that is logged.

    The following examples demonstrate the ways to retrieve the last known values: 

    [Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnit
CURRENTLYBAD,SingleInteger,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
CURRENTLYBAD,06-Aug-2012 8:59:00.000,2,Good
CURRENTLYBAD,06-Aug-2012 9:02:00.000,0,Bad

    Without any query modifier, the newest raw sample is returned in a current value query as retrieved with the following query. 

    select timestamp,value,quality 
from ihrawdata
where tagname = CURRENTLYBAD and samplingmode=CurrentValue
    Time Stamp Value Quality
    8/6/201209:02:00 Bad NonSpecific

    The bad data could be a communication error or collector shutdown marker

    When the ONLYGOOD modifier is used, the bad quality value is ignored and last known good value is returned as per the query here. 

    select timestamp,value,quality 
from ihrawdata
    where tagname = CURRENTLYBAD and samplingmode=CurrentValue and criteriastring="#ONLYGOOD"
    Note: only the Good value has been retrieved as following. timestamp value quality.
    Time Stamp Value Quality
    8/6/201208:59:00 2 Good NonSpecific
    Anticipated Usage

    You can use the ONLYGOOD modifier to exclude end of collection markers but understand that it excludes all bad data, even communication errors, and out of range errors.

    If you want to bring data into Microsoft Excel for further analysis, you can use ONLYGOOD so that good values are brought into a spreadsheet.

  • INCLUDEREPLACED

    Normally, when you query raw data from Historian, any values that have been replaced with a different value for the same timestamp are not returned. The INCLUDEREPLACED modifier helps you to indicate that you want replaced values to be returned, in addition to the currently retrievable data. However, you cannot query only the replaced data and the retrievable values that have replaced. You can query all currently visible data and get the data that has been replaced.

    This modifier is only useful with rawbytime or rawbynumber retrieval. Do not use it with any other sampling or calculation mode.

    Example

    Import this data to demonstrate the behavior of the INCLUDEDELETED query modifier. 

    [Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
DELETEDDATA,SingleInteger,60,0 
[Data] 
Tagname,TimeStamp,Value,DataQuality
DELETEDDATA,06-Aug-2012 9:01:00.000,1,Good 
DELETEDDATA,06-Aug-2012 9:02:00.000,2,Good 
DELETEDDATA,06-Aug-2012 9:04:00.000,4,Good

    Delete the raw sample at 9:02 and query the raw data without any modifier and you will get only the non-deleted values.

    Run the following query: 

    select timestamp,value,quality 
from ihrawdata
where tagname = DELETEDDATA and samplingmode=RawByTime and timestamp < now
    Time Stamp Value Quality
    8/6/2012 09:01:00 1 Good NonSpecific
    8/6/2012 09:04:00 4 Good NonSpecific

    Query with the INCLUDEDELETED modifier and you will get the deleted sample together with the non-deleted data. 

    select timestamp,value,quality 
from ihrawdata
where tagname = DELETEDDATA and samplingmode=RawByTime and timestamp < now and criteriastring="#INCLUDEDELETED"
    Time Stamp Value Quality
    8/6/2012 09:01:00 1 Good NonSpecific
    8/6/2012 09:02:00 2 Good NonSpecific
    8/6/2012 09:04:00 4 Good NonSpecific
    Anticipated Usage

    The INCLUDEDELETED modifier can be used to detect and recover deleted data. Perform a query without the modifier and with the modifier and compare the results. You will detect the deleted samples. You can also determine the deleted samples with a User API program.

ONLYIFCONNECTED and ONLYIFUPTODATE

The ONLYIFCONNECTED and ONLYIFUPTODATE modifiers can be used on any sampling or calculation mode to retrieve bad data if the collector is not currently connected and sending data to the archiver. The bad data is not stored in the IHA file but is only returned in the query. If the collector reconnects and flushes data and you run the query again, the actual stored data is returned in the following situations:

  • Collector loses connection to the Archiver
  • Collector crashes
  • Collector compression is used and no value exceeds the deadband

Any data query will return the last known value repeated till the current time with the quality of data as good. But the information could have changed in the real world and has yet to reach the archiver.

Repeating the last known good value data can be misleading. Data should be returned as bad quality if no data is coming in from a collector.

The Data Archiver keeps track of the newest raw sample received for any tag for each collector. If no data is received for any tag, the collector is considered to be idle. If the collector is idle for more than 270 seconds, then either the data is heavily compressed or the collector is crashed or has lost connection. The collector idle time defaults to 270 seconds and this current setting appears in the dataarchiver.shw file. You can change the value using an SDK program. The setting applies to all collectors.

Use the ONLYIFUPTODATE modifier to return bad data from the time of the newest raw sample to the current time. However, if there is an unlikely chance that all tags are heavily compressed, then use the ONLYIFCONNECTED modifier. The difference in the behavior of the two modifiers is given in the following examples:

When you add an ONLYIFCONNECTED or ONLYIFUPTODATE modifier to the query, and the collector is disconnected from the archiver, bad values are returned from the time of the disconnect until the current time. Queries of data before the disconnect time are unaffected.

Note:
  • The ONLYIFCONNECTED and ONLYIFUPTODATE modifiers are applicable to tags that are collected by data collectors.
  • For raw by number, if the number of samples collected are greater or equal to the number of samples, bad data quality is not added.
  • For raw by time, if the endtime is less than maximum data received time, bad data quality is not added.
  • For raw by number backward, bad data quality is added at the beginning.
Example 1: Using ONLYIFCONNECTED to detect connection loss

To demonstrate the behavior of ONLYIFCONNECTED, you need to query data currently being collected.

  1. Configure the Simulation collector to collect any tag once per second with no compression. For example, collect the simulation RAMP tag.
  2. Let the collector run for at least 5 minutes of collection.
  3. Disconnect the collector but leave it running. In this test, the collector was disconnected at 20:55:00.
  4. After about 5 minutes, query the data without ONLYIFCONNECTED and the last known value repeated with good quality to the current time, even though the collector is not connected. 
    set starttime='22-Aug-2012 20:53:00',endtime='now 
select timestamp,value,quality
from ihrawdata
where tagname = RAMP and samplingmode=Interpolated and intervalmilliseconds=5s order by timestamp asc
    Time Stamp Value Quality
    8/22/2012 20:54:55 166.666666666667 100.0000000
    8/22/2012 20:55:00 0.000000000000 100.0000000
    8/22/2012 20:55:05 166.666666666667 100.0000000
    8/22/2012 20:55:10 333.333333333333 100.0000000
    8/22/2012 20:55:15 500.000000000000 100.0000000
    8/22/2012 20:55:20 533.333333333333 100.0000000
    8/22/2012 20:55:25 533.333333333333 100.0000000
    8/22/2012 20:55:30 533.333333333333 100.0000000
    8/22/2012 20:55:35 533.333333333333 100.0000000
  5. Run the query again with ONLYIFCONNECTED and the data is marked bad at the time of the collector disconnect:
    set starttime='22-Aug-2012 20:53:00',endtime='now 
select timestamp,value,quality
from ihrawdata
where tagname = RAMP and samplingmode=Interpolated and intervalmilliseconds=5s and criteriastring=#onlyifconnect
    Time Stamp Value Quality
    8/22/2012 20:54:55 166.666666666667 100.0000000
    8/22/2012 20:55:00 0.000000000000 100.0000000
    8/22/2012 20:55:05 166.666666666667 100.0000000
    8/22/2012 20:55:10 333.333333333333 100.0000000
    8/22/2012 20:55:15 500.000000000000 100.0000000
    8/22/2012 20:55:20 0.000000000000 0.0000000
    8/22/2012 20:55:25 0.000000000000 0.0000000
    8/22/2012 20:55:30 0.000000000000 0.0000000
    8/22/2012 20:55:35 0.000000000000 0.0000000
  6. Reconnect the collector and once the collector reconnects and flushes its buffered data run the query again with ONLYIFCONNECTED and the period of bad data is filled in with ramping values:
    Time Stamp Value Quality
    8/22/2012 20:54:55 166.666666666667 100.0000000
    8/22/2012 20:55:00 0.000000000000 100.0000000
    8/22/2012 20:55:05 166.666666666667 100.0000000
    8/22/2012 20:55:10 333.333333333333 100.0000000
    8/22/2012 20:55:15 500.000000000000 100.0000000
    8/22/2012 20:55:20 569.696969985962 100.0000000
    8/22/2012 20:55:25 615.151515960693 100.0000000
    8/22/2012 20:55:30 660.606061935425 100.0000000
    8/22/2012 20:55:35 706.060606002808 100.0000000
Example 2: Querying Compressed Data

If all tags for a collector are compressed, then the newest raw sample across all tags can easily be older than 270 seconds even when the collector is connected to archiver. It is unlikely in a real system that a collector will send 0 raw samples for 270 seconds, but it is possible.

  1. Use the simulation collector and collect the constant tag as 1 second polled with a small deadband such as 1. In the example below, the newest raw sample is at 17:27:31 and the current time is 5 minutes or more.
  2. Query the data as interpolated with a 5 second interval and no modifier. 
    set starttime='23-Aug-2012 17:00:30',endtime='now,rowcount=0 
select timestamp,value,quality
from ihrawdata
where tagname = CONSTANT and samplingmode=Interpolated and intervalmilliseconds=5s order by timestamp asc
    Time Stamp Value Quality
    8/23/20121 7:27:20 500.000000000000 100.0000000
    8/23/20121 7:27:25 500.000000000000 100.0000000
    8/23/20121 7:27:30 500.000000000000 100.0000000
    8/23/20121 7:27:35 0.000000000000 100.0000000
    8/23/20121 7:27:40 0.000000000000 100.0000000
    Note: The newest sample is repeated to the current time
  3. Query with ONLYIFCONNECTED and you get the same results even when the newest raw sample is more than 270 seconds old. The data is old but the collector is currently connected. 
    set starttime='23-Aug-2012 17:00:30',endtime='now,rowcount=0 
select timestamp,value,quality
from ihrawdata
where tagname = CONSTANT and samplingmode=Interpolated and intervalmilliseconds=5s and criteriastring=#onlyifcon
    Time Stamp Value Quality
    8/23/20121 7:27:20 500.000000000000 100.0000000
    8/23/20121 7:27:25 500.000000000000 100.0000000
    8/23/20121 7:27:30 500.000000000000 100.0000000
    8/23/20121 7:27:35 500.000000000000 100.0000000
    8/23/20121 7:27:40 500.000000000000 100.0000000
  4. Query with ONLYIUPTODATE and the data is considered bad quality after the newest raw sample.
    set starttime='23-Aug-2012 17:00:30',endtime='now,rowcount=0 
select timestamp,value,quality
from ihrawdata
where tagname = CONSTANT and samplingmode=Interpolated and intervalmilliseconds=5s and criteriastring=#onlyifupt
    Time Stamp Value Quality
    8/23/20121 7:27:20 500.000000000000 100.0000000
    8/23/20121 7:27:25 500.000000000000 100.0000000
    8/23/20121 7:27:30 500.000000000000 100.0000000
    8/23/20121 7:27:35 0.000000000000 0.0000000
    8/23/20121 7:27:40 0.000000000000 0.0000000
Note: If your collector can possibly have no data for any tag due to compression, use ONLYIFCONNECTED. Otherwise, if you want to detect data being old due to collector crash or disconnect, then use ONLYIFUPTODATE and optionally adjust the collector idle time.
Anticipated Usage

Use the ONLYIFCONNECTED and ONLYIFUPTODATE modifiers so that your trend lines stop plotting when the collector loses connection.

Use the ONLYIFCONNECTED and ONLYIFUPTODATE modifiers with CurrentValue retrieval so that the current value turns to bad quality if the collector is disconnected. This way you are not misled by looking at an outdated value that does not match the real world.

ONLYRAW

The ONLYRAW modifier retrieves only the raw stored samples. It does not add interpolated or lab sampled values at the beginning of each interval during calculated retrieval such as average or minimum or maximum.

Normally, a data query for minimum value will interpolate a value at the start of each interval and use that together with any raw samples to determine the minimum value in the interval. Interpolation is necessary because some intervals may not have any raw samples stored.

Note: Use the ONLYRAW modifier with Calculation modes only, not with raw or sampled retrieval like interpolated modes.
Example

Import this data to demonstrate the behavior of the ONLYRAW query modifier.

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
RAMPUP,SingleFloat,100,0
[Data]
Tagname,TimeStamp,Value,DataQuality
RAMPUP,06-Aug-2012 9:01:00.000,1,Good
RAMPUP,06-Aug-2012 9:02:00.000,2,Good
RAMPUP,06-Aug-2012 9:03:00.000,3,Good
RAMPUP,06-Aug-2012 9:04:00.000,4,Good
RAMPUP,06-Aug-2012 9:05:00.000,5,Good
RAMPUP,06-Aug-2012 9:06:00.000,6,Good

When you query the minimum without any modifier, you see that the minimum value may not be one of the stored values. 

set starttime='06-Aug-2012 09:02:30',endtime='06-Aug-2012 09:05:30' 
select timestamp,value,quality
from ihrawdata
where tagname = RAMPUP and samplingmode=Calculated and CalculationMode=minimum and numberofsamples=3
Time Stamp Value Quality
8/6/2012 09:03:30 2.500000000000 100.0000000
8/6/2012 09:04:30 3.500000000000 100.0000000
8/6/2012 09:05:30 4,500000000000 100.0000000 
set starttime='06-Aug-2012 09:02:30',endtime='06-Aug-2012 09:05:30' 
select timestamp,value,quality
from ihrawdata
where tagname = RAMPUP and samplingmode=Calculated and CalculationMode=minimum and numberofsamples=3 
and criteriastring='#onlyraw'
Time Stamp Value Quality
8/6/2012 09:03:30 3.000000000000 100.0000000
8/6/2012 09:04:30 4.000000000000 100.0000000
8/6/2012 09:05:30 5.000000000000 100.0000000
Anticipated Usage

Use the ONLYRAW modifier to query the minimum and maximum values of stored data samples, similar to the RawAverage Calculation mode. A minimum or maximum of raw samples is more like doing a MIN() or MAX () in an Excel spreadsheet. Realize that if you use the ONLYRAW modifier, there may be intervals with no raw samples. The ONLYRAW modifier is useful for Calculation modes and not the Sampling modes.

LABSAMPLING

The LABSAMPLING modifier affects the calculation modes that interpolate a value at the start of each interval. Instead of using interpolation, lab sampling is used. When querying highly compressed data you may have intervals with no raw samples stored. An average from 2 P.M to 6 P.M on a one hour interval will interpolate a value at 2 P.M., 3 P.M., 4 P.M, and 5 P.M and use those in addition to any stored samples to compute averages. When you specify LABSAMPLING, then lab sampling mode is used instead of interpolated sampling mode to determine the 2 P.M., 3 P.M., 4 P.M., and 5 P.M., values.

A lab sampled average would be used when querying a tag that never ramps but changes in a step pattern such as a state value or setpoint.

Note: Use the LABSAMPLING modifier with calculation modes only, not raw or sampled retrieval like interpolated modes.
Example

Import this data to demonstrate the behavior of the LABSAMPLING query modifier. 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
RAMPUP,SingleFloat,100,0
[Data]
Tagname,TimeStamp, Value,DataQuality
RAMPUP,06-Aug-2012 9:01:00.000,1,Good
RAMPUP,06-Aug-2012 9:02:00.000,2,Good
RAMPUP,06-Aug-2012 9:03:00.000,3,Good
RAMPUP,06-Aug-2012 9:04:00.000,4,Good
RAMPUP,06-Aug-2012 9:05:00.000,5,Good
RAMPUP,06-Aug-2012 9:06:00.000,6,Good

Run this query without a modifier to see the minimum values using the interpolated values: 

set starttime='06-Aug-2012 09:02:30',endtime='06-Aug-2012 09:05:30' 
select timestamp,value,quality
from ihrawdata
where tagname = RAMPUP and samplingmode=Calculated and CalculationMode=minimum and numberofsamples=3
Time Stamp Value Quality
8/6/2012 09:03:30 2.500000000000 100.0000000
8/6/2012 09:04:30 3.500000000000 100.0000000
8/6/2012 09:05:30 4.500000000000 100.0000000
The returned minimum values are stored values but sampled forward to each interval timestamp. This is the behavior of lab sampling and is applied here to calculated values.
Anticipated Usage

Use the LABSAMPLING modifier to query the minimum, maximum, and average values of tags that change in a step fashion and never ramp. For example, you may want to retrieve the minimum of a set point. This tag would change from one value directly to another without ramping. And the value may not change in a long period. A minimum should not return a value that ramps over a long period of time from one set point value to the next. The LABSAMPLING modifier is useful for Calculation modes and not the Sampling modes.

ENUMNATIVEVALUE

The ENUMNATIVEVALUE modifier retrieves the native, numeric values such as 1 or 2 instead of string values such as on/off for the data that has enumerated states associated with it.

Note: You can use the ENUMNATIVEVALUE modifier with any sampling or calculation mode.
Example

Import this data to demonstrate the use of ENUMNATIVEVALUE: 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
STATETAG,SingleInteger,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
STATETAG,06-Aug-2012 9:08:00.000,4,Good
STATETAG,06-Aug-2012 9:14:00.000,4,Good
STATETAG,06-Aug-2012 9:22:00.000,2,Good

Assume the tag has an enumerated set associated where 2=Stopped and 4=Running. When you do the interpolated query you get the string value:

set starttime='06-Aug-2012 09:10:00',endtime='06-Aug-2012 09:30:00' 
select timestamp,value,quality
from ihrawdata
where tagname = STATETAG and samplingmode=Interpolated and numberofsamples=6
Time Stamp Value Quality
8/6/2012 09:13:20 running 100.0000000
8/6/2012 09:16:40 running 100.0000000
8/6/2012 09:20:00 running 100.0000000
8/6/2012 09:23:20 stopped 100.0000000
8/6/2012 09:26:40 stopped 100.0000000
8/6/2012 09:30:00 stopped 100.0000000

Using the ENUMNATIVEVALUE query modifier, you can get the numeric value suitable for plotting

set starttime='06-Aug-2012 09:10:00',endtime='06-Aug-2012 09:30:00' 
select timestamp,value,quality
from ihrawdata
where tagname = STATETAG and samplingmode=Interpolated and numberofsamples=6 and criteriastring='#enumnativevalue'
Time Stamp Value Quality
8/6/2012 09:13:20 4 100.0000000
8/6/2012 09:16:40 4 100.0000000
8/6/2012 09:20:00 4 100.0000000
8/6/2012 09:23:20 2 100.0000000
8/6/2012 09:26:40 2 100.0000000
8/6/2012 09:30:00 2 100.0000000
Note: For bad data, the values are returned as string values based on the Enumerated State table though the enumerative value is set to FALSE.
Anticipated Usage

Use the ENUMNATIVEVALUE query modifier to plot tags that use enumerated values. You can put the string value in a data link and put the native value in a chart.

INCLUDEBAD

The INCLUDEBAD modifier directs the Data Archiver to consider raw samples of bad data quality when computing calculation modes. Use INCLUDEBAD modifier to consider both good and bad quality values.

You can use the INCLUDEBAD modifier with any Sampling or Calculation mode only if you want to include bad quality data.

Use the INCLUDEBAD modifier only if you believe the bad quality data has meaningful values and are useful as input to calculations. Most bad data quality values do not have meaningful values; they show 0 or unpredictable numbers. But in some cases, if the data is being written using a user program instead of a collector, you can use this query modifier.

Bad data always has a sub quality such as Comm Error or Configuration Error. When you use the INCLUDEBAD modifier any end of collection raw samples or calculation error raw samples are still ignored because they are not process data, just data markers that are inserted by collectors.

Example

Import this data to demonstrate the behavior of the INCLUDEBAD query modifier: 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
Tag1,SingleFloat,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
Tag1,07-05-2011 17:24:00,29.72,Bad
Tag1,07-05-2011 17:25:00,29.6,Good
Tag1,07-05-2011 17:26:00,29.55,Good 
Tag1,07-05-2011 17:27:00,29.49,Bad
Tag1,07-05-2011 17:28:00,29.53,Bad
Note: The given sample contains three bad quality data.

Run the following query

set starttime='07-05-2011 16:00:00', endtime='07-05-2011 21:00:00'
select timestamp,value,quality from ihrawdata where tagname like 'Tag1' and samplingMode=calculated and 
 CalculationMode=count and Numberofsamples=1
Time Stamp Value Quality
7/5/201121:00:00 2.000000000000 100.0000000

The count is 2 because only good quality data is considered. If you want to consider bad quality use the INLCUDEBAD query modifier as given in the following example. 

set starttime='07-05-2011 16:00:00', endtime='07-05-2011 21:00:00'
select timestamp,value,quality from ihrawdata where tagname like 'Tag1' and samplingMode=calculated and 
CalculationMode=count and criteriastring="#INCLUDEBAD" and  Numberofsamples=1
Time Stamp Value Quality
7/5/201121:00:00 5.000000000000 100.0000000
Note: When we use INCLUDEBAD query modifier all the values are considered and the count is 5.
Anticipated Usage

The INCLUDEBAD modifier can be used to force the Data Archiver to consider every raw sample collected from a field device while still excluding Proficy Historian collection markers or calculation errors and timeouts.

The INCLUDEBAD modifier is usually used if you are writing data with a custom program and not a collector and your program stores meaningful values with bad quality.

FILTERINCLUDEBAD

The FILTERINCLUDEBAD modifier directs the Data Archiver to consider the values of bad quality data when determining the time ranges that match the filter condition. This modifier is similar to the INCLUDEBAD but that modifier applies to the data tag and this modifier applies to the FilterTag.

You can use the FILTERINCLUDEBAD modifier if you are also using INCLUDEBAD because your application data of bad quality has meaningful values then you can also consider this modifier but, you do not need to use both modifiers at the same time.

Example: Filtered Data Query containing Bad Quality Filter Values

Import this data to demonstrate the behavior of the FILTERINCLUDEBAD query modifier: 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
ExcelTag1,SingleFloat,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
ExcelTag1,07-05-2011 17:24:00,29.72,Bad
ExcelTag1,07-05-2011 17:25:00,29.6,Good
ExcelTag1,07-05-2011 17:26:00,29.55,Good 
ExcelTag1,07-05-2011 17:27:00,29.49,Bad
ExcelTag1,07-05-2011 17:28:00,29.53,Bad

The given sample contains three bad quality data samples. Run the following query: 

set starttime='07-05-2011 16:00:00', endtime='07-05-2011 21:00:00'
select timestamp,value,quality from ihrawdata where tagname=ExcelTag1 and samplingMode=Calculated and 
calculationmode=rawtotal and FilterExpression ='ExcelTag1>29.5' and numberofsamples=1

In this query, we use a filtered expression where the filter condition is ExcelTag1>29.5, and the result is as follows because it adds the two good values to compute the RawTotal:

Time Stamp Value Quality
7/5/201121:00:00 59.149999618530 100.0000000

Bad quality data is not considered while filtering. If you want to consider bad quality data then use the FILTERINLCUDEBAD query modifier together with the INCLUDEBAD query modifier as in the following query: 

set starttime='07-05-2011 16:00:00', endtime='07-05-2011 21:00:00'
select timestamp,value,quality from ihrawdata where tagname=ExcelTag1 and samplingMode=Calculated and 
calculationmode=rawtotal and FilterExpression ='ExcelTag1>29.5' and numberofsamples=1 and CriteriaString='#FilterIncludeBad#IncludeBad'

The result is as follows

Time Stamp Value Quality
7/5/201121:00:00 118.399999618530 100.0000000
Note: The value is 118 because all the values that are greater than 29.5 are added together, not just the good quality values.
Anticipated Usage

The FILTERINCLUDEBAD modifier can be used to force the Data Archiver to consider every raw sample collected from a field device when determining the time ranges while still excluding Historian injected end of collection markers or calculation errors and timeouts.

USEMASTERFIELDTIME

The USEMASTERFIELDTIME query modifier is used only for the MultiField tags. It returns the value of all the fields at the same timestamp of the master field time, in each interval returned.

The following are the points to remember while using the USEMASTERFIELDTIME query modifier:
  1. In your user defined data type, you have to indicate which field is the master field. You can define a master field when you define the type.
  2. When you use the USEMASTERFIELDTIME query modifier, the query returns raw values of all the field elements at the timestamp determined by the MasterField.
  3. When you use the USEMASTERFIELDTIME query modifier in Excel Add-in, the percentage good value displayed will be incorrect. It is recommended to use this query modifier using APIs.
  4. Only a few calculation modes are supported by the USEMASTERFIELDTIME query modifier. The supported calculation modes are:
    • Minimum Value
    • Maximum Value
    • Minimum Time
    • Maximum Time
    • FirstRawValue
    • FirstRawTime
    • LastRawValue
    • LastRawTime

The supported modes will examine the raw samples for the master field of a Multi Field tag. For each raw sample in the interval, the minimum or maximum or first or last sample is determined depending on the mode. The timestamp of that raw sample is the master field time.

For example you have a multi-field tag called mytag with 3 fields and field3 is the master field.
  1. You do a LastRawValue query on mytag and pass the USEMASTERFIELDTIME query modifier.
  2. The Data Archiver determines the last raw sample for mytag.field3 between 3pm and 4pm is at 3:42pm. That is the master field time for this interval. Each interval has a master field time.
  3. The Data Archiver gets the values for field1 and field2 at 3:42 so now you have a value for all 3 fields at 3:42.
Note: When there is no raw sample for a field in the given interval then there is no master time for that interval. Most of the calculation modes will then return a 0 Value and Quality Bad for that interval.
Example

Import this data to demonstrate the behavior of the USEMASTERFIELDTIME query modifier.

[Data]
Tagname,TimeStamp,Value,DataQuality
MUser1.F1,05-22-2013 14:15:00,4,Good
MUser1.F1,05-22-2013 14:15:01,7,Good
MUser1.F1,05-22-2013 14:15:02,9,Good
MUser1.F2,05-22-2013 14:15:00,241,Good
MUser1.F2,05-22-2013 14:15:01,171,Good
MUser1.F2,05-22-2013 14:15:02,191,Good
Note: In this sample the MUser1 tag has two fields F1 and F2 and F2 is marked as the MasterField.
Run the following query:
set starttime = '5/22/2013 14:15:00', endtime = '5/22/2013 14:15:02'
select tagname, timestamp, value, quality from ihrawdata where tagname = 'MUser1' and 
samplingmode = calculated and calculationmode = minimum and 
criteriastring = '#USEMASTERFIELDTIME' and numberofsamples = 1
The output is as follows:
Tag Name Time Stamp Value Quality
MUser1.F1 05-22-201314:15:02 7 0.0000000
MUser1.F2 05-22-201314:15:02 171.000000000000 100.0000000

Here the minimum value for the Master Field tag F2 is 171 at 14:15:01 timestamp. That is the master time. Then the master time is used to get the value of F1 at the same timestamp which is 7 and this is returned even as the minimum value of F1 is 4.

In a multi field tag it is possible that some fields may be NULL at a given timestamp. In this case if F1 was a NULL value at 14:15:01 you would get a value of null and bad quality.

HONORENDTIME

Normally, a query keeps searching through archives until the desired number of samples has been located, or until it gets to the first or last archive. However, there are cases where you would want to specify a time limit as well. For example, you may want to output the returned data for a RawByNumber query in a trend page, in which case there is no need to return data that would be off page.

In cases where you want to specify a time limit, you can do this by specifying an end time in your RawByNumber query and including the HONORENDTIME query modifier. Since RawByNumber has direction (backwards or forwards), the end time must be older than the start time for a backwards direction or newer than the start time for a forwards direction.
Note: Use the HONORENDTIME modifier only with the RawByNumber sampling mode.
Example using HONORENDTIME with RawByNumber Sampling Mode

Import this data to Historian:

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
TAG1,SingleInteger,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
TAG1,09/18/2015 14:00:00.000,00,Good
TAG1,09/18/2015 14:05:00.000,5,Good
TAG1,09/18/2015 14:10:00.000,10,Good
TAG1,09/18/2015 14:15:00.000,15,Good
TAG1,09/18/2015 14:20:00.000,20,Good
TAG1,09/18/2015 14:25:00.000,25,Good
TAG1,09/18/2015 14:30:00.000,30,Good
TAG1,09/18/2015 14:35:00.000,35,Good
TAG1,09/18/2015 14:40:00.000,40,Good
TAG1,09/18/2015 14:45:00.000,45,Good
TAG1,09/18/2015 14:50:00.000,50,Good
TAG1,09/18/2015 14:55:00.000,55,Good
TAG1,09/18/2015 15:00:00.000,60,Good
Without HONORENDTIME Query Modifier 
set starttime='9/18/2015 14:00:00',endtime='9/18/2015 14:15:00'
select Timestamp,Value,Quality from ihrawdata where tagname like TAG1 and samplingmode=rawbynumber and 
direction=forwardand numberofsamples=6

The output is as follows:

Time Stamp Value Quality
9/18/2015 14:00:00 0 Good NonSpecific
9/18/2015 14:05:00 5 Good NonSpecific
9/18/2015 14:10:00 10 Good NonSpecific
9/18/2015 14:15:0 15 Good NonSpecific
9/18/2015 14:20:00 20 Good NonSpecific
9/18/2015 14:25:00 25 Good NonSpecific

In the above query, the endtime specified is ignored and 6 values are returned.

With HONORENDTIME Query Modifier 
set starttime='9/18/2015 14:00:00',endtime='9/18/2015 14:15:00'
select TagName,Timestamp,Value,Quality from ihrawdata where tagname like TAG1 and samplingmode=rawbynumber and 
direction=forward and numberofsamples=6 and criteriastring=#honorendtime

The output is as follows:

Time Stamp Value Quality
9/18/2015 14:00:00 0 Good NonSpecific
9/18/2015 14:05:00 5 Good NonSpecific
9/18/2015 14:10:00 10 Good NonSpecific
9/18/2015 14:15:0 15 Good NonSpecific

In the above query, the endtime specified is used and only 4 values are returned.

Anticipated Usage

Use the HONORENDTIME modifier when you would want to specify a time limit to a query. For example, you may want to output the returned data for a RawByNumber query in a trend page, in which case there is no need to return data that would be offpage.

EXAMINEFEW

Queries using calculation modes normally loop through every raw sample, between the given start time and end time, to compute the calculated values.

When using FirstRawValue, FirstRawTime, LastRawValue, and LastRawTime calculation modes, we can use only the raw sample near each interval boundary and achieve the same result. The EXAMINEFEW query modifier enables this. If you are using one of these calculation modes you may experience better read performance using the EXAMINEFEW query modifier.
Note: Use the EXAMINEFEW query modifier only with FirstRawValue, FirstRawTime, LastRawValue, and LastRawTime calculation modes.
Examples using EXAMINEFEW with FirstRawValue and FirstRawTime Calculation Modes

Import this data to Historian: 

[Tags]
Tagname,DataType,HiEngineeringUnits,LoEngineeringUnits
Tag1,SingleFloat,60,0
[Data]
Tagname,TimeStamp,Value,DataQuality
Tag1,07-05-2011 17:24:00,29.72,Bad
Tag1,07-05-2011 17:25:00,29.6,Good
Tag1,07-05-2011 17:26:00,29.55,Good 
Tag1,07-05-2011 17:27:00,29.49,Bad
Tag1,07-05-2011 17:28:00,29.53,Bad
Tag1,07-05-2011 17:29:00,29.58,Good
Tag1,07-05-2011 17:30:00,29.61,Bad
Tag1,07-05-2011 17:31:00,29.63,Bad
Tag1,07-05-2011 18:19:00,30,Good
Tag1,07-05-2011 18:20:00,29.96,Good
Tag1,07-05-2011 18:21:00,29.89,Good
Tag1,07-05-2011 18:22:00,29.84,Good
Tag1,07-05-2011 18:23:00,29.81,Bad
Using FirstRawValue Calculation Mode 
set starttime='07-05-2011 16:00:00', endtime='07-05-2011 19:00:00'
select timestamp,value,quality from ihrawdata where tagname like 'Tag1' and samplingMode=Calculated and 
CalculationMode=FirstRawValue and criteriastring="#EXAMINEFEW" and intervalmilliseconds=1h

The output is as follows:

Time Stamp Value Quality
07-05-2011 17:00:00 00.0000000 0.0000000
07-05-2011 18:00:00 29.6000000 100.0000000
07-05-2011 19:00:00 30.0 100.0000000
Note: The EXAMINEFEW query modifier does not affect query results, but may improve read performance.
Using FirstRawTime Calculation Mode 
set starttime='07-05-2011 16:00:00', endtime='07-05-2011 19:00:00'
select timestamp,value,quality from ihrawdata where tagname like 'Tag1' and samplingMode=Calculated and 
CalculationMode=FirstRawTime and criteriastring="#EXAMINEFEW" and intervalmilliseconds=1h

The output is as follows:

Time Stamp Value Quality
07-05-2011 17:00:00 01-01-1970 05:30:00 0.0000000
07-05-2011 18:00:00 07-05-2011 17:25:00 100.0000000
07-05-2011 19:00:00 07-05-2011 18:20:00 100.0000000
Note: The EXAMINEFEW query modifier does not affect query results, but may improve read performance.
Anticipated Usage

Using the EXAMINEFEW modifier is recommended when:

  • The time interval is greater than 1 minute.
  • The collection interval is greater than 1 second.
  • The data node size is greater than the default 1400 bytes.
  • The data type of the tags is String or Blob.
Note: Query performance varies depending on all of the above factors.

EXCLUDESTALE

Stale tags are tags that have no new data samples within a specified period of time, and which have the potential to add to system overhead and slow down user queries.

The EXCLUDESTALE query modifier allows for exclusion of stale tags in data queries.

Unless permanently deleted, stale tags from the archiver are not removed but are simply marked as stale. Use the query without the EXCLUDESTALE query modifier to retrieve the sample values.
Note: Data is not returned for stale tags. An ihSTATUS_STALED_TAG error is returned instead.
Example
Data

In this example, the data below is for the last raw samples for Tag1 to Tag7: 

Tag, Timestamp, Value
Tag1, 9/25/2015 10:00:00, 10
Tag2, 9/18/2015 10:00:00, 20
Tag3, 9/25/2015 10:00:00, 30
Tag4, 9/25/2015 10:00:00, 40
Tag5, 9/18/2015 10:00:00, 50Tag6, 9/25/2015 10:00:00, 60
Tag7, 9/18/2015 10:00:00, 70
Further Assumptions
  • Current System Time: 9/26/2015 11:00:00
  • Server configuration
    • Stale Period: 7 Days
    • Stale Period Check: 1 Day

In this case, Tag2, Tag5, and Tag7 were logged more than 7 days ago. They are therefore considered stale.

Query without EXCLUDESTALE

The following query is run at 9/26/2015 11:00:00: 

set StartTime='9/17/2015 10:00:00',EndTime='9/26/2015 11:00:00'
select TagName,Timestamp,Value from ihrawdata where tagname like Tag* and Samplingmode=RawByTime and 
CriteriaString="#ExcludeStale"

Output is returned for the following tags: 

Tag, Timestamp, Value
Tag1, 9/25/2015 10:00:00, 10
Tag3, 9/25/2015 10:00:00, 30
Tag4, 9/25/2015 10:00:00, 40
Tag6, 9/25/2015 10:00:00, 60

In the above query, the stale tags (Tag 2, Tag5, and Tag7) are excluded from the results.

Query with EXCLUDESTALE:

The following query is run at 9/26/2015 11:00:00: 

set StartTime='9/17/2015 10:00:00',EndTime='9/26/2015 11:00:00'
select TagName,Timestamp,Value from ihrawdata where tagname like Tag* and Samplingmode=RawByTime and 
CriteriaString="#ExcludeStale"

Output is returned for the following tags: 

Tag, Timestamp, Value
Tag1, 9/25/2015 10:00:00, 10
Tag3, 9/25/2015 10:00:00, 30
Tag4, 9/25/2015 10:00:00, 40
Tag6, 9/25/2015 10:00:00, 60

In the above query, the stale tags (Tag 2, Tag5, and Tag7) are excluded from the results.

Anticipated Usage

Stale tags have the potential to add to system overhead and slow down user queries, without adding new data. The EXCLUDESTALE modifier can be used to exclude such tags, thereby speeding up query time.