Scroll down to the bottom of the CCL file and paste in the following:
/**@SIMPLEQUERY=PATTERN*/
CREATE OUTPUT STREAM ALARM_POWER
AS SELECT
A.MACHINEID MACHINEID ,
A.EVENT_TIME EVENT_TIME ,
A.LOCATION LOCATION ,
'POWER' ALARM_TYPE ,
'POWER Out for more than 20 seconds' ALARM_DESC
FROM DEVICE_EVENTS A, DEVICE_EVENTS B
MATCHING [ 20 SEC : A , ! B ]
ON A.MACHINEID = B.MACHINEID
AND A.EVENT_VALUE = 'Power off'
AND B.EVENT_VALUE = 'Power on' ;
Now let’s take a look at the CCL above to understand it:
-
There’s nothing unusual about the SELECT clause: you’re simply defining the structure and content of the events that will be output by this stream whenever the pattern is detected.
Note that the alias assigned in the FROM clause is used to reference the input event.
-
In this particular example, we are watching for a pattern of events on a single stream. To do this, you list the input stream multiple times in the FROM clause, assigning multiple aliases (one for each event in the pattern).
- The MATCHING clause defines the pattern of events we are watching for, which always starts with a time interval. Every pattern must have a finite time boundary – in this example, we are watching for an “A” event that is not followed by a “B” event within 20 seconds. The “,” means “followed by”. You can also use “AND” or “OR”.
- The ON clause is used to qualify what incoming events qualify as an A event or a B event. Events that don’t qualify as an event in the pattern will be ignored. In this example, any “Power off” event will qualify as an “A” event, and any “Power on” event will qualify as a “B” event. However, since we are watching for the absence of a “Power on” event, then in this example, if you get a “B” event with the same MACHINEID as an “A” event, within 20 seconds of the “A” event, then the “A” event will be dropped.
See the Streaming Analytics CCL Reference guide for more information on using the MATCHING clause.