基于Prometheus/OpenSearch/Grafana/Kibana实现Oracle SQL查询与Linux命令监控的方案咨询

Hey there! Great question—you’re absolutely right to look for a more streamlined alternative to custom Java code for this monitoring work. The cloud-native tools you’re targeting have solid, purpose-built integrations for exactly these use cases. Let’s break down the best approaches for both your Oracle SQL and Linux command monitoring needs:

Oracle SQL Query Monitoring (Active Users, etc.) #

Option 1: Prometheus + Oracle Exporter #

• Use a mature Oracle exporter (like the widely adopted oracle-database-exporter) to bridge your Oracle database with Prometheus.
• You can configure custom SQL queries directly in the exporter’s config file—for example, to track active users:

  SELECT COUNT(*) AS active_users FROM v$session WHERE status = 'ACTIVE'
  

• The exporter will run these queries on a schedule, convert the results into Prometheus-compatible metrics, and expose them via an HTTP endpoint. Prometheus then scrapes this endpoint to collect the data.
• Once the metrics are in Prometheus, you can build dashboards in Grafana to visualize trends in active users, query latency, or any other custom metrics you define.

Option 2: OpenSearch + Logstash/Beats #

• If you prefer storing this data in OpenSearch for Kibana visualization, use Logstash with the Oracle input plugin or Filebeat’s Oracle module (where available).
• Configure the tool to run your scheduled SQL queries, format the results as structured events, and send them to OpenSearch. For example, you can set up a cron-like schedule to pull active user counts every minute.
• In Kibana, you can create line charts, gauges, or tables to display real-time or historical counts of active users, with filtering and time-range controls built right in.

Linux Command Output Monitoring (netstat/grep, etc.) #

Option 1: Prometheus + Node Exporter + Custom Shell Scripts #

• Node Exporter is the go-to for system metrics, but it also supports a textfile_collector feature that lets you add custom metrics.
• Write a simple shell script to run your netstat/grep commands and output results in Prometheus metrics format. For example, to count established connections:

  #!/bin/bash
  ESTABLISHED_COUNT=$(netstat -an | grep ESTABLISHED | wc -l)
  echo "server_established_connections $ESTABLISHED_COUNT" > /var/lib/node_exporter/textfile_collector/connections.prom
  

• Make the script executable, then use cron to run it at your desired interval (e.g., every 30 seconds). Node Exporter will automatically pick up the .prom file and expose the metric to Prometheus.
• From there, Grafana can pull this metric to build dashboards showing connection trends, process counts, or any other custom command outputs.

Option 2: OpenSearch + Filebeat/Metricbeat #

• To send Linux command outputs to OpenSearch, use Filebeat’s exec input—configure it to run your commands on a schedule and send the output as structured logs to OpenSearch.
• Alternatively, use Metricbeat with a custom module to parse command outputs into formal metrics (instead of raw logs), which makes it easier to build aggregated visualizations in Kibana.
• For example, you can set up Filebeat to run netstat -s | grep "segments retransmitted" every minute, send that count to OpenSearch, and then create a Kibana dashboard tracking retransmission rates over time.

All these approaches are far more maintainable than a custom Java program—they leverage community-supported tools, require minimal custom code, and integrate seamlessly with your target monitoring stack.

备注：内容来源于stack exchange，提问作者user3069309