DYNAMICTHRESHOLDS Configuration Syntax


The DynamicThreshold Configuration is used to setup the connection to the SQL Database and also the data series collection.

SUBSYS DYNAMICTHRESHOLDS
CONFIGURE CONNECTIONSTRING ('Driver={ODBC Driver 17 for SQL Server}; Server=<hostname>\<database_instance_name>,<portnum>; Database=<db_name>; {trusted_connection=yes | UID=<UID>; PWD=<PWD>}; sslmode=disabled; Encrypt=yes; TrustServerCertificate={yes | no}')
ADD SERIES (RECORD=<record_name>, PROFILE={DayOfWeek3Month | DayOfWeek6Month | TimeOfDay1Week}, FIELD=<field_name>[, REMASSOC=<assoc_name>][, KEYS=<key_list>][, AGGREGATE={NONE | SUM | AVERAGE}])

Syntax Elements

CONFIGURE CONNECTIONSTRING

To use Windows Authentication to connect to SQL Server, provide a ConnectionString where the following values must be provided:

<hostname>

SQL Server host name

<database_instance_name>

The SQL Server instance name containing the database

<db_name>

SQL Server database name containing the DT Predictions

<portnum>

SQL TCP port number, default is 1433

'trusted_connection=yes'To use Windows Authentication to connect to SQL Server
'UID=<UID>; PWD=<PWD>'

To use SQL Server Authentication to connect to SQL Server, specify this string literally instead of the 'trusted_connection=yes'. i.e do not replace the <UID> <PWD> with any other text.

The SQL Server credentials will be retrieved from the PASSWORDS Configuration, with entry 'DT:SQL', refer to User Permissions for Dynamic Thresholds for further details.

TrustServerCertificate={yes | no}

To enable authentication of the connected Server, set the 'TrustServerCertificate' option in the connection string to 'yes'.

ADD SERIES

The series definitions tell Prognosis Dynamic Thresholds exactly what to collect. There are several elements to the definition of a series:

RECORD=<record_name>

Prognosis Short Record name and must be the same record for every series defined

For further details see Record

PROFILE={DayOfWeek3Month | DayOfWeek6Month | TimeOfDay1Week}

DayOfWeek3Month, DayOfWeek6Month or TimeOfDay1Week  

For further details see Profile

FIELD=<field_name>

Prognosis Short Field name

REMASSOC=<assoc_name>

An associate name for collector request, if not specified will default to '#AllNames'

KEYS

List of the active key fields in the source record, separated by the '+' symbol, if not supplied, all keys within the Record are used

AGGREGATE={NONE | SUM | AVERAGE}

A switch that, if set, aggregates fields in the source collections if they are keyed identically but for their source nodes.

Optional, if not supplied then default is NONE.

Record

One record. By default it is DTTXNSUM. It is not permitted to have series definitions that are not distinct.  A deployment of Dynamic Thresholds has exactly:

  • One interval. It is five minutes.

  • One where clause. It is "WHERE ALL".

A Prognosis Record other than DTTXNSUM can be selected, but it must be the same for every series.

The attributes that differentiate series (since the three core attributes are the same for every series) are:

  • Field name

  • Profile

  • Remote Associate

Series also have properties that are not used to differentiate between them. These are;

  • Keys

  • Aggregate

This means that there can not be two series that are the same except for the KEYS attribute or the AGGREGATE attribute.

Profile

The Dynamic Thresholds solution assumes that monitored data is periodic and that the value at this time today is similar to the value either:

  • At this time every day; or

  • At this time of this day of the week, every week.

The selection between these options is achieved by specifying one of the 3 choices in the 'Profile' configuration. The Profile also selects how much history will be used to determine the expected value.

Profile

Period

History

First Expected Value

Suggested 1st Use After

Forecasts Value

DayOfWeek3Month

Weekly

13 Weeks

~8 days

29 days (4 samples)

Yes

DayOfWeek6Month

Weekly

26 Weeks

~8 days

29 days (4 samples)

Yes

DayOfWeekLastWeek

Weekly

1 Week

7 days

1 Week (1 Sample)

No*

TimeOfDay1Week  

Daily

7 Days

~2 days

8 days (7 samples)

Yes

TimeOfYesterday

Daily

1 Day

1 day

1 Day (1 Sample)

No*

*For these profiles with single samples, the built-in dynamic thresholds will not work, due to missing upper and lower limits. However, these can be used in Prognosis Displays showing the DTVALUE Record for historical comparison only.

The following series definition serves to illustrate the essential elements of a series definition.

Example Series

ADD SERIES(RECORD=DTTXNSUM, PROFILE=TimeOfDay1Week, FIELD=APRV%, REMASSOC=TOTAL, KEYS=, AGGREGATE=AVERAGE)

Which means that:

  • From the field 'APRV%' from the DTTXNSUM record,

  • Comparing against this time of day for every day of the last week,

  • Selecting the 'TOTAL' associate from the source of DTTXNSUM,

  • With NO KEY FIELDS active (KEYS=,) - as required for the TOTAL associate,

  • Aggregating matching rows from all monitoring nodes under the Dynamic Thresholds node as their average.

Provide feedback on this article