Building a System Monitoring Service with Machbase Neo
Overview
Machbase Neo is an integrated time-series database that covers data collection, storage, retrieval, and visualization. Its time-series architecture is particularly well suited to storing and managing periodically sampled sensor values and system-resource measurements, commonly called metrics in IT.
Many monitoring systems use Grafana for visualization, and that approach has its advantages. However, large data volumes can introduce performance constraints, while managing the database and visualization tools separately adds operational complexity. Building an integrated system with Machbase Neo offers a useful way to compare the tradeoffs.
This guide uses a dashboard-enabled version of Machbase Neo to collect computer-resource usage in real time and process alerts. If you want to set up monitoring without following the development walkthrough, jump to the Quick Setup section near the end.
<A simple system-monitoring dashboard built with Neo>
Collecting Metrics
Typical resources collected from a server or personal computer include:
CPU utilization (%)
Disk space used (GB)
Disk space available (GB)
Inbound network traffic (bytes/sec)
Outbound network traffic (bytes/sec)
A data center might also measure temperature, humidity, noise, power consumption, and peak power demand. For this demonstration, a small set of standard server metrics is enough.
The next question is how to collect these metrics. Dedicated open-source tools such as Telegraf and many other collection solutions are available. Here, we use a simple Python program that works across operating systems.
Designing the Collection Agent
A collection agent can be written with a small amount of Python code.
For learning purposes, we provide two agents, referred to here as the JSON and TQL versions.
JSON version
Sends collected metrics directly to the Machbase Neo database engine.
Uses APPEND rather than INSERT for fast ingestion.
Cannot inspect or act on the data before storage; processing requires reading it back afterward.
Alerts could be implemented by rereading stored data and checking conditions. This option is not implemented in the example.
TQL version
Sends collected metrics to a Machbase Neo TQL script in CSV format.
The TQL script can process the incoming data before storing it.
The example checks whether a metric is a CPU measurement above 10% utilization, then writes an alert to a separate log table. This functionality is implemented.
The metric is then stored using high-speed APPEND ingestion.
Both agents share the same core collection code. The source files are organized as follows and are available on GitHub:
The following metrics are collected. Disk and network values represent aggregated devices in this example. Modify the source code if you want to monitor individual devices.
Tag name (prefix) | Description |
CPU-{server name} | CPU utilization (%) |
DT-{server name} | Total disk capacity (bytes) |
DU-{server name} | Disk space used (bytes) |
DR-{server name} | Disk space remaining (bytes) |
NI-{server name} | Inbound network data (bytes) |
NO-{server name} | Outbound network data (bytes) |
Six metrics are generated for each monitored machine and sent to the database at the configured interval.
Running the Agent
JSON Agent
Command-line options
$ python3 run-agent-json.py -h
usage: run-agent-json.py [-h] [--ip_address IP_ADDRESS] [--interval INTERVAL] [--servername SERVERNAME]
[--debug DEBUG]
url tablename
System Statistics Collector
positional arguments:
url NEO-URL (e.g., http://host:port)
tablename Table name for data storage
options:
-h, --help show this help message and exit
--ip_address IP_ADDRESS
IP Address prefix to filter (default: "")
--interval INTERVAL Interval between posts in seconds (default: 3)
--servername SERVERNAME
servername
--debug DEBUG debuggingurl (required): the URL of the Neo database, typically http://127.0.0.1:5654 when testing locally.
tablename (required): the tag table used to store metrics, named monitoring in this example.
--ip_address: sets the IP-address prefix used to identify the machine. Without an explicit setting, the agent uses the first matching device address beginning with 192.
--interval: collection interval in seconds; the default is 3.
--servername: overrides the machine name. Use it when you prefer an easily recognizable label.
--debug: enables additional diagnostic output.
Running on Linux
On Linux, output similar to the following indicates that the agent is working.
This example uses Neo at 127.0.0.1 on port 5654, the monitoring table, and the server name linux.
$ python3 run-agent-json.py http://127.0.0.1:5654 monitoring --servername linux
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713667837, 0.0], ['DU-linux', 1713667837, 154169008128], ['DT-linux', 1713667837, 538854957056], ['DR-linux', 1713667837, 384685948928], ['NI-linux', 1713667837, 0], ['NO-linux', 1713667837, 0]]}}
{'success': True, 'reason': 'success, 6 record(s) appended', 'elapse': '2.630386ms'}
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713667841, 0.0], ['DU-linux', 1713667841, 154169008128], ['DT-linux', 1713667841, 538854957056], ['DR-linux', 1713667841, 384685948928], ['NI-linux', 1713667841, 1245], ['NO-linux', 1713667841, 1245]]}}
{'success': True, 'reason': 'success, 6 record(s) appended', 'elapse': '7.586805ms'}
......This output confirms that data collection has started. Next, check the stored data.
Run the following query:
select * from monitoring order by name, time;
The result shows that the metrics are being collected successfully.
Running on Windows
C:\Temp> python3 run-agent-json.py http://127.0.0.1:5654 monitoring --servername windowsThe same source code works on Windows; only the machine name differs in this example.
Detecting Abnormal Conditions and Creating Alerts
Overview
Monitoring helps anticipate abnormal conditions or respond quickly when they occur. A common approach is to trigger an alert when a metric exceeds or falls below a threshold. AI-based methods can also detect more complex anomalies.
This guide implements threshold-based detection. There are two main approaches:
Post-processing
How it works
Store incoming data first, then periodically poll the stored data to check for abnormal conditions.
Advantages and tradeoffs
Shorter polling intervals detect problems sooner but increase system load. Longer intervals reduce load but can delay detection.
Pre-processing
How it works: inspect the data before storage. If an abnormal condition is found, take action and then store the data; otherwise, store it normally.
Advantages and tradeoffs
No polling is needed, so anomalies can be detected as data arrives. However, overall throughput can fall if the incoming volume exceeds the processing capacity.
This example uses pre-processing to take advantage of Machbase Neo’s TQL capabilities.
append.tql
Code walkthrough
The run-agent-tql.py agent sends data with an HTTP POST request. The append.tql script receives it, checks alert conditions, and writes the data to the database.
CSV(payload())
MAPVALUE(1, parseFloat(value(1)) * 1000000000)
MAPVALUE(2, parseFloat(value(2)))
WHEN(glob("CPU-*", value(0)) && value(2) > 10,
do( value(0), value(1), value(2), {
ARGS()
MAPVALUE(3, strSprintf("CPU usage above %.1f%%", 10))
INSERT("name", "time", "value", "desc", table("alarm_history"))
}
)
)
APPEND(table(param('table') ?? 'monitoring'))CSV(payload())
Reads the CSV data received over HTTP and passes each row into the pipeline. The three columns are name, time, and value, accessible as value(index).
MAPVALUE(1, ...): multiplies the timestamp in seconds by one billion to convert it to the nanoseconds used by Neo.
MAPVALUE(2, ...): explicitly converts the incoming value, which is a string, to a 64-bit floating-point number.
WHEN(...): this is the key conditional step.
glob(): checks whether the tag name in value(0) begins with "CPU-". In this example, CPU utilization above 10 triggers an alert. Change 10 to the desired threshold. To detect values outside a range, use a condition such as value(2) < 10 || value(2) > 90.
do(): runs when the condition is true and defines what happens when an alert is triggered.
Here, an alert inserts a record into the alarm_history log table. The fourth field, value(3), is generated by strSprintf() to add a descriptive message.
APPEND(): writes the CSV data to the table at high speed.
param(): retrieves a named URL parameter supplied in the form ?name=value.
This script reads the table parameter. If it is omitted, the default destination is the monitoring table.
Further Options
The following extensions illustrate how append.tql can be adapted to other requirements.
Discard detected anomalies instead of storing them in the tag table
Some abnormal values are unwanted noise. If you do not want to retain them in the tag table, use TQL’s FILTER() function.
FILTER() passes through rows that satisfy its condition and discards the others.
The following version records the alert but filters out the CPU readings that triggered it before appending metrics:
CSV(payload())
MAPVALUE(1, parseFloat(value(1)) * 1000000000)
MAPVALUE(2, parseFloat(value(2)))
WHEN(glob("CPU-*", value(0)) && value(2) > 10,
do( value(0), value(1), value(2), {
ARGS()
MAPVALUE(3, strSprintf("CPU usage above %.1f%%", 10))
INSERT("name", "time", "value", "desc", table("alarm_history"))
}
)
)
FILTER(!(glob("CPU-*", value(0)) && value(2) > 10))
APPEND(table(param('table') ?? 'monitoring'))Send an alert to a REST API instead of storing it in a table
Use doHttp() to send data to an HTTP endpoint. The following examples show GET and POST requests. See the linked training material for further details.
HTTP calls
doHttp("GET", strSprintf("http://127.0.0.1:5654/db/tql/alarm-input-get.tql?name=%s&time=%f&value=%f", value(0), value(1), value(2)), nil)
doHttp("POST", strSprintf("http://127.0.0.1:5654/db/tql/alarm-input-post.tql"), strSprintf(`%s,%.0f,%.0f`, value(0), value(1), value(2)), "Content-Type: text/csv", "X-Custom-Header: notification")
Running the TQL Agent
The command works on both Linux and Windows. Set the URL to the correct location of append.tql.
If you cloned the GitHub repository directly into Neo, run:
$ python3 run-agent-tql.py http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql monitoring --servername linuxThe output now includes CSV data being sent to TQL. The ### marker separates debugging output showing the JSON-to-CSV conversion.
$ python3 run-agent-tql.py http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql monitoring --servername linux
http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql?table=monitoring
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713673816, 0.0], ['DU-linux', 1713673816, 154170302464], ['DT-linux', 1713673816, 538854957056], ['DR-linux', 1713673816, 384684654592], ['NI-linux', 1713673816, 0], ['NO-linux', 1713673816, 0]]}}
###
CPU-linux,1713673816,0.0
DU-linux,1713673816,154170302464
DT-linux,1713673816,538854957056
DR-linux,1713673816,384684654592
NI-linux,1713673816,0
NO-linux,1713673816,0
{'success': True, 'reason': 'success', 'elapse': '6.961687ms', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713673820, 0.1], ['DU-linux', 1713673820, 154170302464], ['DT-linux', 1713673820, 538854957056], ['DR-linux', 1713673820, 384684654592], ['NI-linux', 1713673820, 1156], ['NO-linux', 1713673820, 1156]]}}
###
CPU-linux,1713673820,0.1
DU-linux,1713673820,154170302464
DT-linux,1713673820,538854957056
DR-linux,1713673820,384684654592
NI-linux,1713673820,1156
NO-linux,1713673820,1156
{'success': True, 'reason': 'success', 'elapse': '2.676484ms', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}Data collection is now in place. Next, build a dashboard to display it.
Building a Dashboard
Create a dashboard
Click + in Neo’s tab area, then select DASHBOARD from the menu to open a blank dashboard.
Add a chart to the empty dashboard
Click Create New Panel on the initial dashboard screen to open the panel editor.
You can now add charts.
CPU Usage
Select a Line chart, then choose the table and tag as shown below.
Click Apply to preview the chart. When it looks right, click Save in the upper-right corner to add it to the dashboard.
CPU Gauge — Windows
Use a gauge to show CPU utilization. Follow the settings in the image and select last value so the gauge displays the latest measurement.
CPU DIFF
This chart shows changes in CPU utilization. Set value to diff to visualize changes during the selected measurement period.
Network Traffic (IN/OUT)
Select the table and tags in the same way as for CPU Usage.
Chart selection
Calculation settings
Divide value by 1024 to display KiB instead of bytes (value / 1024).
Remaining Disk Space
This chart displays remaining disk capacity in real time. The Linux and Windows capacities differ greatly in the example, making their lines look almost flat. Displaying one machine at a time makes increases and decreases easier to see.
Chart selection
Calculation settings
Click the calculation icon highlighted in red and apply value / 1024 / 1024 / 1024 to each series. This converts bytes to GiB. Without the conversion, the chart displays long byte counts.
Alert History (Scatter Chart)
This scatter chart shows alerts from the last five minutes, using the alarm_history log table.
Log tables have an automatically assigned ingestion timestamp named _ARRIVAL_TIME. You can choose either the user-defined TIME column or _ARRIVAL_TIME for the chart. The original example recommends _ARRIVAL_TIME for performance, but these timestamps represent different events, so select the one that matches your purpose.
View the Complete Dashboard
The charts are arranged as shown and saved in DASHBOARD.dsh in the public machbase/neo-apps repository. Click the link icon in the upper-right corner to obtain the dashboard’s URL and open it separately in another browser tab.
Open the Dashboard through Its Own URL
The final screen shows the dashboard in a separate browser view. The time settings display the last five minutes of data and refresh every three seconds.
[Quick Setup] Use the System-Monitoring App in Neo
This section describes the fastest way to display system metrics with Neo.
Install Machbase Neo
This demonstration uses Neo 8.0.17. The videos below explain downloading, installing, and starting the version used in the guide.
Install and start on Windows
Install and start on Linux/macOS
Download neo-apps into Neo
Neo can clone source code directly from GitHub. The following steps assume that Neo is installed and running.
Click the third icon at the upper left to open Git Clone. Enter https://github.com/machbase/neo-apps.git and click OK to download the application.
Check the Installed Package
After cloning, a top-level neo-apps directory appears. Open its server-mon subdirectory to see the files shown below. The repository also includes the Python agent source, although those files are not displayed in Neo’s file view.
Create the Tables with the Worksheet
Open setup.wrk to display the worksheet shown below. Worksheets are Neo’s document format for combining explanatory content and executable work.
You can execute SQL directly from the worksheet.
Place the cursor in the first CREATE TAG TABLE statement and click the play button at the upper right to create the metrics table.
After it succeeds, place the cursor in the CREATE TABLE alarm_history statement and run it to create the second table.
Start the TQL Agent
The main walkthrough explains the agent in detail. Here, we use the TQL version so alert detection runs as well.
This example assumes that Neo runs on Linux under WSL2 and names that machine linux. A second agent runs on the Windows host with the name windows.
Install the Python Dependencies
Install the external packages used by the agent. argparse, logging, platform, and socket are part of Python’s standard library and do not need separate installation.
pip3 install psutil requests
Linux
In the server-mon source directory, run the following commands.
Check the directory contents
~/neo/current/neo-apps/server-mon$ ls
DASHBOARD.dsh agent_core.py append.tql mon.sql run-agent-json.py run-agent-tql.py run.sh setup.wrkStart the agent
~/neo/current/neo-apps/server-mon$ python3 run-agent-tql.py http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql monitoring --servername linux
http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql?table=monitoring
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713678506, 0.1], ['DU-linux', 1713678506, 154355449856], ['DT-linux', 1713678506, 538854957056], ['DR-linux', 1713678506, 384499507200], ['NI-linux', 1713678506, 0], ['NO-linux', 1713678506, 0]]}}
###
CPU-linux,1713678506,0.1
DU-linux,1713678506,154355449856
DT-linux,1713678506,538854957056
DR-linux,1713678506,384499507200
NI-linux,1713678506,0
NO-linux,1713678506,0
{'success': True, 'reason': 'success', 'elapse': '2.824225ms', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-linux', 1713678510, 0.1], ['DU-linux', 1713678510, 154355449856], ['DT-linux', 1713678510, 538854957056], ['DR-linux', 1713678510, 384499507200], ['NI-linux', 1713678510, 1156], ['NO-linux', 1713678510, 1156]]}}
###
CPU-linux,1713678510,0.1
DU-linux,1713678510,154355449856
DT-linux,1713678510,538854957056
DR-linux,1713678510,384499507200
NI-linux,1713678510,1156
NO-linux,1713678510,1156
{'success': True, 'reason': 'success', 'elapse': '4.977728ms', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}
.....The output confirms successful collection.
Windows
Directory contents
C:\Users\ratse\work\neo-apps\server-mon>dir
C 드라이브의 볼륨에는 이름이 없습니다.
볼륨 일련 번호: 9CFC-DC76
C:\Users\ratse\work\neo-apps\server-mon 디렉터리
2024-04-21 오후 02:50 <DIR> .
2024-04-20 오후 12:33 <DIR> ..
2024-04-20 오후 12:33 3,578 agent_core.py
2024-04-21 오후 02:50 431 append.tql
2024-04-21 오후 02:50 15,439 DASHBOARD.dsh
2024-04-20 오후 12:33 312 mon.sql
2024-04-20 오후 12:33 1,622 run-agent-json.py
2024-04-21 오후 02:50 2,358 run-agent-tql.py
2024-04-20 오후 12:33 51 run.sh
2024-04-21 오후 02:50 12,474 setup.wrk
2024-04-20 오후 12:33 <DIR> __pycache__
8개 파일 36,265 바이트
3개 디렉터리 20,532,850,688 바이트 남음Start the agent
C:\Users\ratse\work\neo-apps\server-mon>python3 run-agent-tql.py http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql monitoring --servername windows
http://127.0.0.1:5654/db/tql/neo-apps/server-mon/append.tql?table=monitoring
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-windows', 1713678697, 13.4], ['DU-windows', 1713678697, 1835372167168], ['DT-windows', 1713678697, 1922094321664], ['DR-windows', 1713678697, 86722154496], ['NI-windows', 1713678697, 0], ['NO-windows', 1713678697, 0]]}}
###
CPU-windows,1713678697,13.4
DU-windows,1713678697,1835372167168
DT-windows,1713678697,1922094321664
DR-windows,1713678697,86722154496
NI-windows,1713678697,0
NO-windows,1713678697,0
{'success': True, 'reason': 'success', 'elapse': '5.638746ms', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}
{'data': {'columns': ['name', 'time', 'value'], 'rows': [['CPU-windows', 1713678701, 5.2], ['DU-windows', 1713678701, 1835372167168], ['DT-windows', 1713678701, 1922094321664], ['DR-windows', 1713678701, 86722154496], ['NI-windows', 1713678701, 279462], ['NO-windows', 1713678701, 12224643]]}}
###
CPU-windows,1713678701,5.2
DU-windows,1713678701,1835372167168
DT-windows,1713678701,1922094321664
DR-windows,1713678701,86722154496
NI-windows,1713678701,279462
NO-windows,1713678701,12224643
{'success': True, 'reason': 'success', 'elapse': '925.549µs', 'data': {'message': 'append 6 rows (success 6, fail 0)'}}
.....Open the Dashboard
Setup is complete. The supplied dashboard is preconfigured for the machine names linux and windows, so it should display their data immediately.
Click DASHBOARD.dsh on the left to open it.
Default theme
This screenshot was taken about three minutes after startup, so the charts are not yet full. They refresh every three seconds with live data.
Try the infographic theme
The repository also includes DASHBOARD-infographic.dsh, which provides the alternative theme shown below.
Conclusion
Using Neo 8.0.17, this walkthrough covers metric collection, alert processing, and visualization for system monitoring. The demonstration uses only two machines. The original article describes deployments with more than 100,000 metric types and over 100 billion stored records on an 8-core, 16GB server; actual capacity depends on the workload and configuration.
A production deployment also needs consideration of rollup queries for long-term visualization, backups and mounts, and integration with third-party systems.
As a first step, this example provides a practical introduction to collecting and using system metrics with Machbase Neo.
We look forward to seeing further applications built on these ideas.
