If you're new to this article, I recommend reading the following previous material first:
Introduction
In the previous article, we discussed how to utilize Fast Fourier Transform, which can be most powerfully applied with incomparable performance by "Time Series DBMS". However, in the actual application stage, the ultimate goal is not only to view the FFT calculation results but also to detect anomalies in real-time or batch form for various conditions and notify the person in charge through multiple channels. Below are several imaginable alarm situations:
When a specific sensor exceeds the allowable Peak (maximum value)
When a specific sensor exceeds the allowable Trough (minimum value)
When a specific sensor exceeds the allowable RMS value (vibration intensity)
When the amplitude of a specific frequency range in the FFT result exceeds the allowable value (detection of specific fault patterns)
There could be more advanced cases besides the above, but in this document, we will implement and test two alarm generation methods for case 1.
Method of adding the alarm time and actual value to the log table
Method of sending alarm information in real-time to a web hook configured with a specific REST API
However, for real-time alarms like case 2, it would be useful to set them up for truly urgent types of alarms that should not actually occur in the field, or for very dangerous or important events, to prevent long-term significant risks.
Caution
This blog was tested and written based on Machbase Neo 8.0.18-rc3a or higher as of May 20, 2024. The reason is that from this version, a feature has been added to directly output TQL chart results to the dashboard, allowing TQL charts to be visualized immediately without using separate HTML files and JavaScript. Although it's not an official version, it's expected that this feature will be established in version 8.0.19 and later.
For those who may need to use earlier versions (8.0.18-rc2 or below) for various reasons, separate HTML files have been prepared for reference.
Practicing to Build an Alarm System
1. Video for Those in a Hurry
The video below is a capture of the constructed demo. You can check the various screens and content that have changed compared to the previous blog.
2. Installing Neo and Getting neo-apps Source Code
The installation content is completely identical to the previous blog. For the actual installation method, refer only to steps 1 (Neo installation) and 2 (neo-apps download) from the previous blog's link, and try to get the latest version of Machbase Neo after 8.0.18-rc3a if possible. Then, you can return here. (From step 3 onwards, follow the instructions below)
3. Explanation of Added Files in realtime-fft
As this blog focuses on alarm data generation and detection, data input, related TQL, and dashboards have been added separately as follows. Most prefixes are 'alarm-'.
Tutorial Worksheet:
alarm-data.wrk
Data Input:
alarm-insert-noise-x.tql
alarm-insert-noise-y.tql
alarm-insert-noise-z.tql
Improved FFT Charts:
alarm-fft-2d.tql: Creates 2D FFT charts for X, Y, Z axes through parameters
alarm-fft-3d.tql: Creates 3D FFT charts for X, Y, Z axes through parameters
Alarm Configuration and Processing TQL:
alarm-peak-publish-post.tql: Sends messages via webhook when an alarm occurs
alarm-peak-insert.tql: Inputs data into the log table when an alarm occurs
alarm-data-stat.tql: Generates statistical charts
Dashboards:
alarm-data.dsh: Statistical dashboard version (8.0.18-rc3a and above)
alarm-fft.dsh: Real-time FFT dashboard (8.0.18-rc3a and above)
alarm-data.html: Statistical dashboard HTML version (version independent)
alarm-peak-history.dsh: Real-time alarm chart (version independent)
4. Creating Schema and Loading Data
Table FFT Sensor Configuration
For this demo, actual vibration sensor data was simulated, and manipulated to draw various patterns to maximize the demo effect. We assumed one vibration sensor with X, Y, and Z axes, each generating 1000 data points per second. This generation is prepared using Machbase Neo's input TQL, and by calling it once per second using an internal timer, 1000 data points are input per second for each axis. From the database's perspective, 3000 sensor data points are input at high speed per second.
* alarm-insert-noise-x.tql (X-axis vibration data, amplitude 2.0 at 150Hz + amplitude 50 at 300Hz)
* alarm-insert-noise-y.tql (Y-axis vibration data, amplitude 1.0 at 100Hz + amplitude 10 at 0.1Hz)
* alarm-insert-noise-z.tql (Z-axis vibration data, amplitude 20 at 0.5Hz + amplitude 10 at 15Hz + amplitude 3 at 35Hz + amplitude 2 at 220Hz + amplitude 2 at 400Hz)
Noise Generation
Below is one of the three TQLs mentioned above. To randomly mix in noise, we've set it up to multiply the data by 100 with a probability of 1/1000, inputting a very large number. You can check this in the second line of the code.
alarm-insert-noise-x.tql
FAKE( oscillator( freq(150, 2.0), freq(300, 50), range("now", "1s", "1ms") ))
MAPVALUE(1, (random() < 0.0001) ? value(1) * 100: value(1))
PUSHVALUE(0, "vib-x2", "name")
APPEND(table("fft"))Creating FFT Table and Timer
Let's create the FFT table and timer using the worksheet alarm-fft.wrk as shown below. The added content is a log table called alarm_history, where the alarm details are entered. Also, using the retention feature, it has been improved to store data only for a certain period (one month). For detailed information about retention, refer to the related manual here.
Additionally, let's create a timer to automatically input data that includes noise. (The script for creating the timer is performed in the same way as in the previous blog.)
5. Checking Real-time FFT Results on the Dashboard (alarm-fft.dsh)
The difference from before is that there's no longer a need to create a separate HTML file to view multiple FFT charts on one screen. We're using the TQL chart feature in the dashboard, which was added in version 8.0.18-rc3a.
When you load the above chart, you'll see the FFT results for each of the three axes in both 2D and 3D, being output in real-time. You can observe the data being smoothly displayed.
Screen
As you can see above, the chart updates smoothly every 5 seconds.
For the 3D FFT, instead of using CHART_BAR3D(), we directly used CHART() to utilize various chart options such as spatial arrangement. Please refer to the source code below. Additionally, when creating charts, it has become very convenient to pass various parameters in key=value format for GET requests.
alarm-fft-3d.tql
SQL_SELECT(
'time', 'value',
from('fft', param('axis') ?? 'vib-y2'),
between('last-' + (param('range') ?? '10s'), 'last')
)
MAPKEY( roundTime(value(0), '500ms') )
GROUPBYKEY()
FFT(minHz(0), maxHz(1000))
FLATTEN()
PUSHKEY('fft')
PUSHVALUE(0, list(value(0), value(1), value(2)))
POPVALUE(1, 2, 3)
CHART(
plugins("gl"),
chartOption(strSprintf(`{
"xAxis3D": {
"name": "time",
"type": "time",
"show": true,
},
"yAxis3D": {
"name": "Hz",
"type": "value",
"show": true
},
"zAxis3D": {
"name": "Amp",
"type": "value",
"show": true
},
"grid3D": {
"boxWidth": 70,
"boxHeight":70,
"boxDepth": 70,
"left": "10%%",
"viewControl": {
"projection": "orthographic",
"autoRotate": false,
"speed": 0
}
},
"title": {
"text": "%s",
"left": 'center'
},
"visualMap": [{
"type": "continuous",
"calculable": true,
"min": 0,
"max": 10,
"inRange": {
"color": ["#313695", "#4575b4", "#74add1", "#abd9e9", "#e0f3f8", "#ffffbf", "#fee090", "#fdae61", "#f46d43", "#d73027", "#a50026"]
}
}],
"tooltip": {
"show": true,
"trigger": "axis"
},
"series": [{
"type": "bar3D",
"coordinateSystem": "cartesian3D",
"data":column(0),
"shading": "lambert",
"itemStyle": {
"opacity": 1
}
}]
}`, param('title') ?? "X axis FFT in 3D"))
)
Chart Creation Parameter Specification Screen
As shown below, when you set the chart type to TQL, you can now specify files and pass parameters. These parameters operate within the TQL by receiving values in the form of param('key').
6. Vibration Data Statistical Indicators Dashboard (alarm-data.dsh)
This statistical indicator can all be configured in the dashboard without a separate HTML.
The first row of three charts shows statistical figures, and the second row of three charts (X, Y, Z Alarm) displays alarms that have exceeded the given range. This dashboard runs alarm-data-stat.tql, alarm-peak-insert.tql, and alarm-peak-publish-post.tql periodically every 3 seconds.
In these alarm charts, normal conditions are output as 0, while alarm situations are output with their corresponding values. Statistically, an alarm will occur approximately once every 5-10 seconds. In this demo, alarms are set to trigger based on Peak values, and the detailed specifications for alarms on each axis are as follows:
Info | X Axis | Y Axis | Z Axis |
Data gen TQL | alarm-insert-noise-x.tql | alarm-insert-noise-y.tql | alarm-insert-noise-z.tql |
Alarm delivery methods | alarm_history Insert | alarm_history Insert | Web Hook |
Alarm Condition | When the Peak value of the X-axis is greater than 200 | When the Peak value of the Y-axis is greater than 300 | When the Peak value of the Z-axis is greater than 100 |
Alarm Detection TQL | alarm-peak-insert.tql | alarm-peak-insert.tql | alarm-peak-publish-post.tql |
Below is the operational screen of the dashboard, where you can see the moment when a peak alarm occurs on the Y-axis.
7. What Happens When an Alarm Occurs?
As mentioned above, when an alarm actually occurs, for the X and Y axes, it is entered into the alarm_history table, and for the Z axis, an event is sent via webhook. This is performed through the following code:
alarm-peak-insert.tql (X or Y Axis)
In the code below, the maximum value within a 1-second range is calculated using the GROUP() function. If this value is greater than the parameter value received through param('peak'), the corresponding data is inserted into the log table using the WHEN() + INSERT() statements.
SQL_SELECT(
'time', 'value',
from('fft', param('axis') ?? 'vib-y2'),
between(strSprintf('now-%s', param('range') ?? '20s') , 'now')
)
GROUP(
by( value(0),
timewindow(time(strSprintf('now-%s', param('range') ?? '20s')), time('now'), period(param('precision') ?? "1s")),
"TIME"
),
max( value(1),
"PEAK ALARM"
)
)
SET(PEAK, parseFloat(param('peak') ?? 100))
MAPVALUE(0, strTime(value(0), 'DEFAULT', tz('Local')))
WHEN(value(1) > parseFloat(param('peak') ?? 100),
do( value(0), param('axis'), value(1), {
ARGS()
MAPVALUE(3, strSprintf("%s peak alarm!..: more than %.2f", param('axis'), parseFloat(param('peak') ?? 100)))
INSERT("time", "name", "value", "desc", table("alarm_history"))
}
)
)
MAPVALUE(1, value(1) > $PEAK ? value(1) : 0)
CHART(
..생략
)alarm-ak-publish-post.tql (Z 축)
The code below is for sending an alarm message via POST to Google Space (the XXX key has been omitted in this blog for security reasons) when an alarm occurs on the Z-axis.
SQL_SELECT(
'time', 'value',
from('fft', param('axis') ?? 'vib-z2'),
between(strSprintf('now-%s', param('range') ?? '20s') , 'now')
)
GROUP(
by( value(0),
timewindow(time(strSprintf('now-%s', param('range') ?? '20s')), time('now'), period(param('precision') ?? "1s")),
"TIME"
),
max( value(1),
"PEAK ALARM"
)
)
MAPVALUE(0, strTime(value(0), 'DEFAULT', tz('Local')))
WHEN(value(1) > parseFloat(param('peak') ?? 100),
doHttp("POST",
"https://chat.googleapis.com/v1/spaces/AAAA6WEdswk/messages?key=XXXX",
strSprintf(`{"text": "%s peak alarm (%.2f)!..: more than %.2f"}`, param('axis') ?? "no tag", value(1), parseFloat(param('peak') ?? 100)),
"Content-Type: application/json",
"X-Custom-Header: notification" )
)
MAPVALUE(1, value(1) > parseFloat(param('peak') ?? 100) ? value(1) : 0)
CHART(
.. 생략
)
8. Visualizing Alarm Occurrence History (alarm-peak-history.dsh)
This dashboard utilizes a scatter chart to display in real-time the alarm history currently being stored in the alarm-history table. The Z-axis is not present in this dashboard because its history is not recorded in the log table.
Closing
In this blog post, as the second in the Fast Fourier Analysis series, we created a demo that sets up and processes real-time alarms. For the purpose of this article, we only set and processed alarms for Peak statistical values of specific axes. However, it's worth noting that you can easily add and expand this approach to other statistical values such as RMS or any other statistical measures using the same method.