You will learn
- Use a Derived Window to apply a filter to watch for events that should generate an alert
By Jeff Wootton
Create and use Derived Window to analyze events, filter data, and generate alerts.
In the steps below, you will add and configure a derived stream that will watch for events that need to generate an alert - by applying a filter.
However, rather than just using the standard Filter element, we will use the Derived Window element from the palette since it gives us more flexibility. With this, we can apply the filter and also change the structure of the output event.
At this point in the tutorial series, if you’d rather not step through the process of creating this window using the visual editor, you can simply switch to the CCL editor (F6 from the visual editor) and paste the CCL below into your project - and then skip to the last step to compile and test your Project.
CREATE OUTPUT WINDOW ALARM_TEMP PRIMARY KEY DEDUCED KEEP ALL AS SELECT AVG_TEMP.MACHINEID MACHINEID , AVG_TEMP.EVENT_TIME EVENT_TIME , AVG_TEMP.AVG_TEMP AVG_TEMP , AVG_TEMP.MAX_TEMP MAX_TEMP , AVG_TEMP.LOCATION LOCATION , 'TEMPERATURE' ALARM_TYPE , 'Machine not maintaining' ALARM_DESC FROM AVG_TEMP WHERE AVG_TEMP.AVG_TEMP > AVG_TEMP.MAX_TEMP ;
Click Derived Window in the Palette and drop it onto the canvas.
Add a connector from
AVG_TEMP to the new window.
Change the new window name to
ALARM_TEMP and press Enter when done.
Click Add Column Expression f(x) icon shown below.
Click the Copy Columns from Input menu item to open the column selection dialog.
Select all except for
TEMP_UNIT, then click OK.
Click Add Column Expression f(x) icon then Column Expression menu item to add a column to this window.
Repeat this to add a 2nd column (i.e. add 2 new columns total).
Rename the first new column to
ALARM_TYPE and press Enter key when done.
Rename the 2nd new column to
simpleResultExpression6 to edit the expression.
'TEMPERATURE' in the expression edit box for the
ALARM_TYPE column. This will set the “type” of all alarms emitted by this window to the string “TEMPERATURE”. Press Enter key to confirm entry.
Note that it’s important to enclose string values in single quotes in expressions
'Machine not maintaining temperature' in the expression box for the
ALARM_DESC column. Press Enter key to confirm entry.
Now click Add Query Clause to add a query clause.
We want to add a filter such that this window only contains rows for machines that have a current average temperature above the max specified for the machine.
Click the Filter menu item to execute it. You can also press f.
Double-click on 1 under the Other tab to edit the filter expression.
Change the filter expression to:
AVG_TEMP.AVG_TEMP > AVG_TEMP.MAX_TEMP. Use Ctrl+Space for completion assist.
Click Compile Project icon to check for errors.
You will get a compiler warning about the lack of a retention policy on the aggregate stream. You can ignore this warning. The reason for the warning is that windows default to KEEP ALL. If you explicitly state KEEP ALL, then the compiler accepts that you meant it. If you omit an explicit KEEP policy, the compiler warns you in case it’s not what you intended. Here, this is an aggregation where we are grouping by Machine ID - and we do indeed want to keep the aggregate value for every machine ID.