Skip to content

A SQL series as a tag (Anomaly Watch)

A process value that lives only in a database table (an MES fill weight, a lab result, a shot record) can be screened by Anomaly Watch like a historized tag. Give the asset a tag with a source:

"tags": [ { "path": "[mes]Filler1/FillWeight", "role": "measurement", "unit": "g",
            "source": "query:MyProject/Artekis/Fill Weights" },
          { "path": "[mes]Filler1/CapTorque", "source": "query:MyProject/Artekis/Fill Weights",
            "name": "Cap torque" } ]
  • The source is written and checked exactly as a shape source (section 7.6): start/end only, read a day at a time, at most read.shapeRowCap rows. It returns the measurements columns (time, asset, name, value, …).
  • The tag reads the rows whose asset matches this asset (by id, then name) and whose name is the tag's name, or the last segment of its path when name is left out. One query can feed many tags across many assets.
  • Its path is the tag's name in findings, not a gateway tag: write it as [<name>]<path> (any bracketed name, such as [mes]). It is never read from a tag provider, and coverage counts it as keeping history.
  • Time zone. A zoneless date-time or date in a row's time (as a script: source may return: a LocalDate, a LocalDateTime, a java.sql.Date) is read in the plant's time zone (a date as midnight of that day); a timestamp the database driver returns, or any value with an offset, is taken as given. Before 1.5.17 a zoneless date-time was read in the gateway JVM's zone and a java.sql.Date failed; they agree wherever the gateway runs in the plant's zone. A cell that cannot be read counts as a bad row, never a failed source.
  • Rows are averaged into Anomaly Watch's five-minute steps (a step with no row is a gap, as in the historian), and carry no quality, so every value counts as Good. The tag is screened by all six checks against its own baseline, split by the asset's run, product and changeover signals as any other tag. A detector proposed from one of its findings computes its baseline and backtest from the same series.
  • A save is refused, naming the field, when the source is malformed, is a script: source while read.scriptSources is off, names a project outside read.projects, a project or named query that does not exist, or a query outside the contract; also when the tag's role is not measurement. (name may be set on any tag, section 7.)
  • If the source fails during a run (the database is down, a column was renamed), only the tags it feeds are left out: the run completes, and its record's kpis.sqlSeries.failed names each tag with the error. A source that reached the row cap is listed in kpis.sqlSeries.partial with the time its rows start from.