Now we can query from it and show the aggregate window timestamp alongside the result:
ksql> SELECT ROWTIME, TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd HH:mm:ss'), \ HOST, INVALID_LOGIN_COUNT \ FROM INVALID_USERS_LOGINS_PER_HOST; 1521644100000 | 2018-03-21 14:55:00 | rpi-03 | 1 1521646620000 | 2018-03-21 15:37:00 | rpi-03 | 2 1521649080000 | 2018-03-21 16:18:00 | rpi-03 | 1 1521649260000 | 2018-03-21 16:21:00 | rpi-03 | 4 1521649320000 | 2018-03-21 16:22:00 | rpi-03 | 2 1521649080000 | 2018-03-21 16:38:00 | rpi-03 | 2
In the above query I’m displaying the aggregate window start time,
ROWTIME(which is epoch), and converting it also to a display string, usingTIMESTAMPTOSTRING. We can use this to easily query the stream for a given window of interest. For example, for the window beginning at2018-03-21 16:21:00we can see there were four invalid user login attempts. We can easily check the source data for this, using theROWTIMEin the above output for the window (16:21 – 16:22) as the bounds for the predicate:
It’s a very interesting use case.