Live Out-of-Range

The EQuIS Live Out-of-Range Agent will monitor data coming into EQuIS Live and notify a user of any data values that are outside of the expected range. Each out-of-range value will be reported only once. (The agent will not report out-of-range values multiple times on subsequent executions).

Input Parameters

Monitor these series – Shows a list of series that can be select to be monitored. Series will only be visible if the following fields are populated (i.e., not null):

•DT_LOGGER.TRANSMIT_PERIOD_S: The number of seconds that elapse between data collection events for this data logger.

•DT_LOGGER_SERIES.TYPICAL_MAX_VALUE: The maximum typical value for this series (used to identify out-of-range values).

•DT_LOGGER_SERIES.TYPICAL_MIN_VALUE: The minimum typical value for this series (used to identify out-of-range values).

Qualify out-of-range values with (for EIA use only) – (Optional) Shows a list of qualifiers from the RT_LOGGER_QUALIFIER table. If a qualifier is selected, that qualifier will be applied to out-of-range values (i.e., DT_LOGGER_DATUM.DATUM_QUALIFIER).

Date Range (ignored by EIA) – Specifies the date range for which data should be considered. This date range is ignored when run as an EIA.

Notification defeat interval (for EIA use only) – Defines the length of time, in minutes, following a notification being sent out, during which there will be no (further) notifications sent out. The EIA will still run, as scheduled, but notifications will not be sent out during this time. A value of 0 always sends out a notification (if there's an out-of-range value). The date and time that the last notification was sent out is saved in the user report's @last_notification (hidden) parameter (in the ST_USER_REPORT_PARAMETER table).

Value Status Expression – (Hidden parameter) Instructs the Out-of-Range agent how to determine what values are out-of-range. By default, it will compare the DT_LOGGER_DATUM.DATUM_VALUE to both DT_LOGGER_SERIES.TYPICAL_MAX_VALUE and DT_LOGGER_SERIES.TYPICAL_MIN_VALUE. If DATUM_VALUE is greater than TYPICAL_MAX_VALUE or if DATUM_VALUE is less than TYPICAL_MIN_VALUE, then the value is flagged, and if set up as an EIA, the alert is sent to the intended recipient(s). This default logic (stored in ST_REPORT_PARAMETER.VALUE for the "@value_status_expression" parameter) is the following SQL CASE expression:

case when datum_value > typical_max_value then '>' when datum_value < typical_min_value then '<' else null end

This @value_status_expression can be modified in ST_REPORT_PARAMETER to provide different out-of-range criteria, if desired. For example, a Live series may be tracking a cumulative sum over a time period (e.g., total permitted water extraction for a given year). The TYPICAL_MAX_VALUE stored for this series may apply to the total overall sum across the entire time period; however, it would be more useful to have the Out-of-Range agent give an early warning if usage on a given day is on pace to exceed the limit over the entire time period. The logic could be modified to accommodate this. For example, if the current date is 30-June (halfway through the calendar year), have the Agent notify the user of values in exceedance of half of the TYPICAL_MAX_VALUE (the total permitted value for the year).

If you wish to modify the logic used for this agent, consider creating copies of the agent report and modifying the @value_status_expression value on the copies. See Handling Multiple Copies of the Same Report for more information.

Monitoring Data

The Out-of-Range Agent can be run as a scheduled EQuIS Information Agent (EIA), at the desired frequency, to automatically monitor data as often as needed.

Note: An EIA of this Agent will ignore the date range parameters. It will save the newest out-of-range value's date and time, for each series, in hidden parameter series_info, and set the series' start date and time to this date and time the next time it runs. Therefore, the EIA does not report that the same value is out-of-range multiple times. The FIRST time it processes a series, it will set its start date and time equal to the date and time that it runs, minus the DT_LOGGER's TRANSMIT_PERIOD_S, or the ReportGenerate's ST_SERVICE_RESERVATION.SLEEP_SECONDS, whichever is greater.

The Out-of-Range Agent can also be run in EQuIS Professional or Enterprise (using the EZView widget) to load the data manually, without having to schedule an EIA. The following table highlights a few of the differences between the EIA and non-EIA functionality:

Item	EIA	Non-EIA
Uses date range parameters?	No	Yes
What values are included in the report output?	Out-of-range values only.	All values,both IN-range and OUT-of-range, but only the out-of-range values will have a non-empty VALUE_STATUS (e.g., > or <).
Does the report output include DATUM_UTC_DT?	No, only DATUM_DT, which is DT_LOGGER_DATUM.DATUM_UTC_DT + DT_LOGGER.UTC_OFFSET_HRS.	Yes, it includes DATUM_UTC_DT (i.e., DT_LOGGER_DATUM.DATUM_ UTC_DT), and DATUM_DT.
Sets DT_LOGGER_DATUM. DATUM_QUALIFIERs for out-of-range values?	Yes	No

If this agent does not set the DT_LOGGER_DATUM.DATUM_QUALIFIERs for out-of-range values, when running as an EIA, it may be because the data is old; it only processes newer data. The following SQL may be used to set the DT_LOGGER_DATUM.DATUM_QUALIFIERs and view out-of-range values.

-- @logger_series_ids is an array of DT_LOGGER_SERIES.LOGGER_SERIES_IDs.

DECLARE @logger_series_ids TABLE (id int);

INSERT @logger_series_ids (id) VALUES (1234), (1235), (1236);

-- @datum_qualifier is what to set the DT_LOGGER_DATUM.DATUM_QUALIFIERs to (MAX length is 4 characters).

DECLARE @datum_qualifier CHAR(4) = 'Q ';

-- @start_date and @end_date is the date range.

DECLARE @start_date DATETIME2(0) = '2000-01-01T00:00:00';

DECLARE @end_date DATETIME2(0) = '2030-01-01T00:00:00';

-- SET datum_qualifiers = @datum_qualifier for @logger_series_ids' out-of-range values.

UPDATE

SET

d.datum_qualifier = @datum_qualifier

FROM

dt_logger_datum d

INNER JOIN dt_logger_series s ON s.logger_series_id = d.logger_series_id

WHERE

(d.logger_series_id IN (SELECT id FROM @logger_series_ids))

AND (d.datum_utc_dt >= @start_date AND d.datum_utc_dt <= @end_date)

AND (d.datum_value IS NOT NULL)

AND ( (s.typical_min_value IS NOT NULL AND d.datum_value < s.typical_min_value)

OR (s.typical_max_value IS NOT NULL AND d.datum_value > s.typical_max_value));

-- VIEW @logger_series_ids' out-of-range values, dates and times, qualifiers, and min/max values.

SELECT

d.logger_series_id, COALESCE(series_desc, series_name) AS series_desc, s.typical_min_value, s.typical_max_value, d.datum_utc_dt, d.datum_value, d.datum_qualifier