Zero-Length Capsule Configuration

Overview

Zero-length capsules are used when you have data that is modelled as follows:

Timestamp	Value	Property1	Property…	PropertyN

This could be utilized whenever a discrete event needs to be trended along with other data. For example, alert or lab sample data is often recorded as a discrete event. Seeq accesses this data as a zero-length capsule. When visualized, this data is shown as a diamond in Workbench.

Configuration

Our SQL connector and our BigQuery connectors support zero-length capsules. To configure a zero-length condition, the timestamp column would be used for both the start and the end timestamp, and the Maximum Duration property would be set to 0 minutes.

For example, for the following alerts dataset stored in a SQL table:

ts	value	status	asset	val2	operator
2023-05-15T08:52:51Z	43	green	p789	45	Vallie Tarrier
2023-12-21T16:41:57Z	96	red	p456	35	Erskine Kenwood
2023-11-16T15:56:15Z	63	green	p456	18	Sergio Belham

We would define a query definition as follows:

CODE

[ {
      "Name" : "MyAlerts",
      "Type" : "CONDITION",
      "Sql" : "SELECT ts,ts as ts2,value,val2,operator,asset FROM  SEEQDB.DBO.ZEROLENGTH where asset = '${asset}'",
      "Enabled" : true,
      "TestMode" : false,
      "Variables" : [ {
        "Names" : [ "asset" ],
        "Values" : [ [ "${result}" ] ],
        "Sql" : "select distinct asset from SEEQDB.DBO.ZEROLENGTH"
       } ],
      "Properties" : [ {
        "Name" : "Name",
        "Value" : "${asset} Alert",
        "Uom" : "string"
      }, {
        "Name" : "Description",
        "Value" : "This is an alert from ${asset}",
        "Uom" : "string"
      }, {
        "Name" : "Maximum Duration",
        "Value" : "0min",
        "Uom" : "string"
      } ],
      "CapsuleProperties" : [ {
        "Name" : "Asset",
        "Value" : "${columnResult}",
        "Column" : "asset",
        "Uom" : "string"
      }, {
        "Name" : "Value",
        "Value" : "${columnResult}",
        "Column" : "value",
        "Uom" : "string"
      }, {
        "Name" : "Value2",
        "Value" : "${columnResult}",
        "Column" : "val2",
        "Uom" : "string"
      }, {
        "Name" : "Operator",
        "Value" : "${columnResult}",
        "Column" : "operator",
        "Uom" : "string"
      } ]
    } ]

There are three key elements to the query definition above.

The Variable Query

CODE

"Variables" : [ {
        "Names" : [ "asset" ],
        "Values" : [ [ "${result}" ] ],
        "Sql" : "select distinct asset from SEEQDB.DBO.ZEROLENGTH"
       } ]

The variable query is run during indexing. It is responsible for querying for the distinct assets. Each asset will represent a condition. The condition will contextualize the events into events sourced from the asset.

The Maximum Duration Property

CODE

{
    "Name" : "Maximum Duration",
    "Value" : "0min",
    "Uom" : "string"
}

Setting the maximum duration property to 0min ensures that no unnecessary query expansion will occur.

The SQL Query

CODE

"Sql" : "SELECT ts,ts as ts2,value,val2,operator,asset FROM  SEEQDB.DBO.ZEROLENGTH where asset = '${asset}'",

The SQL query selects the ts column twice to follow the rules as defined on the Creating a Query Definition article. Because most SQL engines require unique column names to avoid ambiguous queries, the example above shows selecting the second instance of the timestamp column as ts2. This allows for the query engine to remove the ambiguity while querying. If you were to attempt to select ts,ts you will likely end up with the error SQL compilation error: ambiguous column name 'TS' Exception: java.sql.SQLException.

Troubleshooting

If you encounter an error like this one

Invalid column name 'ts2'. Exception: java.sql.SQLException To retry, use the button to the right.

Wrap the SELECT Statement like this

"Sql" : "SELECT * from ( SELECT Timestamp, Timestamp AS ts2, ....... ) as foo",