- Metricbeat Reference: other versions:
- Metricbeat overview
- Quick start: installation and configuration
- Set up and run
- Upgrade Metricbeat
- How Metricbeat works
- Configure
- Modules
- General settings
- Project paths
- Config file loading
- Output
- Kerberos
- SSL
- Index lifecycle management (ILM)
- Elasticsearch index template
- Kibana endpoint
- Kibana dashboards
- Processors
- Define processors
- add_cloud_metadata
- add_cloudfoundry_metadata
- add_docker_metadata
- add_fields
- add_host_metadata
- add_id
- add_kubernetes_metadata
- add_labels
- add_locale
- add_network_direction
- add_nomad_metadata
- add_observer_metadata
- add_process_metadata
- add_tags
- community_id
- convert
- copy_fields
- decode_base64_field
- decode_json_fields
- decode_xml
- decode_xml_wineventlog
- decompress_gzip_field
- detect_mime_type
- dissect
- dns
- drop_event
- drop_fields
- extract_array
- fingerprint
- include_fields
- rate_limit
- registered_domain
- rename
- script
- translate_sid
- truncate_fields
- urldecode
- Autodiscover
- Internal queue
- Logging
- HTTP endpoint
- Regular expression support
- Instrumentation
- metricbeat.reference.yml
- How to guides
- Modules
- ActiveMQ module
- Aerospike module
- Apache module
- App Search module
- AWS module
- AWS billing metricset
- AWS cloudwatch metricset
- AWS dynamodb metricset
- AWS ebs metricset
- AWS ec2 metricset
- AWS elb metricset
- AWS kinesis metricset
- AWS lambda metricset
- AWS natgateway metricset
- AWS rds metricset
- AWS s3_daily_storage metricset
- AWS s3_request metricset
- AWS sns metricset
- AWS sqs metricset
- AWS transitgateway metricset
- AWS usage metricset
- AWS vpn metricset
- AWS Fargate module
- Azure module
- Azure app_insights metricset
- Azure app_state metricset
- Azure billing metricset
- Azure compute_vm metricset
- Azure compute_vm_scaleset metricset
- Azure container_instance metricset
- Azure container_registry metricset
- Azure container_service metricset
- Azure database_account metricset
- Azure monitor metricset
- Azure storage metricset
- Beat module
- Ceph module
- Ceph cluster_disk metricset
- Ceph cluster_health metricset
- Ceph cluster_status metricset
- Ceph mgr_cluster_disk metricset
- Ceph mgr_cluster_health metricset
- Ceph mgr_osd_perf metricset
- Ceph mgr_osd_pool_stats metricset
- Ceph mgr_osd_tree metricset
- Ceph mgr_pool_disk metricset
- Ceph monitor_health metricset
- Ceph osd_df metricset
- Ceph osd_tree metricset
- Ceph pool_disk metricset
- Cloudfoundry module
- CockroachDB module
- Consul module
- Coredns module
- Couchbase module
- CouchDB module
- Docker module
- Dropwizard module
- Elasticsearch module
- Elasticsearch ccr metricset
- Elasticsearch cluster_stats metricset
- Elasticsearch enrich metricset
- Elasticsearch index metricset
- Elasticsearch index_recovery metricset
- Elasticsearch index_summary metricset
- Elasticsearch ml_job metricset
- Elasticsearch node metricset
- Elasticsearch node_stats metricset
- Elasticsearch pending_tasks metricset
- Elasticsearch shard metricset
- Envoyproxy module
- Etcd module
- Google Cloud Platform module
- Golang module
- Graphite module
- HAProxy module
- HTTP module
- IBM MQ module
- IIS module
- Istio module
- Jolokia module
- Kafka module
- Kibana module
- Kubernetes module
- Kubernetes apiserver metricset
- Kubernetes container metricset
- Kubernetes controllermanager metricset
- Kubernetes event metricset
- Kubernetes node metricset
- Kubernetes pod metricset
- Kubernetes proxy metricset
- Kubernetes scheduler metricset
- Kubernetes state_container metricset
- Kubernetes state_cronjob metricset
- Kubernetes state_daemonset metricset
- Kubernetes state_deployment metricset
- Kubernetes state_node metricset
- Kubernetes state_persistentvolumeclaim metricset
- Kubernetes state_pod metricset
- Kubernetes state_replicaset metricset
- Kubernetes state_resourcequota metricset
- Kubernetes state_service metricset
- Kubernetes state_statefulset metricset
- Kubernetes state_storageclass metricset
- Kubernetes system metricset
- Kubernetes volume metricset
- KVM module
- linux module
- Logstash module
- Memcached module
- MongoDB module
- MSSQL module
- Munin module
- MySQL module
- NATS module
- Nginx module
- Openmetrics module
- Oracle module
- PHP_FPM module
- PostgreSQL module
- Prometheus module
- RabbitMQ module
- Redis module
- Redis Enterprise module
- SQL module
- Stan module
- Statsd module
- System module
- System core metricset
- System cpu metricset
- System diskio metricset
- System entropy metricset
- System filesystem metricset
- System fsstat metricset
- System load metricset
- System memory metricset
- System network metricset
- System network_summary metricset
- System process metricset
- System process_summary metricset
- System raid metricset
- System service metricset
- System socket metricset
- System socket_summary metricset
- System uptime metricset
- System users metricset
- Tomcat module
- Traefik module
- uWSGI module
- vSphere module
- Windows module
- ZooKeeper module
- Exported fields
- ActiveMQ fields
- Aerospike fields
- Apache fields
- App Search fields
- AWS fields
- AWS Fargate fields
- Azure fields
- Beat fields
- Beat fields
- Ceph fields
- Cloud provider metadata fields
- Cloudfoundry fields
- CockroachDB fields
- Common fields
- Consul fields
- Coredns fields
- Couchbase fields
- CouchDB fields
- Docker fields
- Docker fields
- Dropwizard fields
- ECS fields
- Elasticsearch fields
- Envoyproxy fields
- Etcd fields
- Google Cloud Platform fields
- Golang fields
- Graphite fields
- HAProxy fields
- Host fields
- HTTP fields
- IBM MQ fields
- IIS fields
- Istio fields
- Jolokia fields
- Jolokia Discovery autodiscover provider fields
- Kafka fields
- Kibana fields
- Kubernetes fields
- Kubernetes fields
- KVM fields
- linux fields
- Logstash fields
- Memcached fields
- MongoDB fields
- MSSQL fields
- Munin fields
- MySQL fields
- NATS fields
- Nginx fields
- Openmetrics fields
- Oracle fields
- PHP_FPM fields
- PostgreSQL fields
- Process fields
- Prometheus fields
- Prometheus typed metrics fields
- RabbitMQ fields
- Redis fields
- Redis Enterprise fields
- SQL fields
- Stan fields
- Statsd fields
- System fields
- Tomcat fields
- Traefik fields
- uWSGI fields
- vSphere fields
- Windows fields
- ZooKeeper fields
- Monitor
- Secure
- Troubleshoot
- Get help
- Debug
- Common problems
- "open /compat/linux/proc: no such file or directory" error on FreeBSD
- Metricbeat collects system metrics for interfaces you didn’t configure
- Metricbeat uses too much bandwidth
- Error loading config file
- Found unexpected or unknown characters
- Logstash connection doesn’t work
- Publishing to Logstash fails with "connection reset by peer" message
- @metadata is missing in Logstash
- Not sure whether to use Logstash or Beats
- SSL client fails to connect to Logstash
- Monitoring UI shows fewer Beats than expected
- Dashboard could not locate the index-pattern
- Contribute to Beats
SQL module
editSQL module
editThis functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.
The SQL module allows you to execute custom queries against an SQL database and store the results in Elasticsearch.
This module supports the databases that you can monitor with Metricbeat, including:
- PostgreSQL
- MySQL
- Oracle
- Microsoft SQL
- CockroachDB
To enable the module, run:
metricbeat module enable sql
After enabling the module, open modules.d/sql.yml
and set the required
fields:
-
driver
-
The driver can be any driver that has a Metricbeat module, such as
mssql
orpostgres
. -
sql_query
- The single query you want to run.
-
sql_response_format
-
Either
variables
ortable
:-
variables
- Expects a two-column table that looks like a key/value result. The left column is considered a key and the right column the value. This mode generates a single event on each fetch operation.
-
table
- Expects any number of columns. This mode generates a single event for each row.
-
Example: capture Innodb-related metrics
editThis sql.yml
configuration shows how to capture Innodb-related metrics that
result from the query SHOW GLOBAL STATUS LIKE 'Innodb_system%'
in a MySQL
database:
- module: sql metricsets: - query period: 10s hosts: ["root:root@tcp(localhost:3306)/ps"] driver: "mysql" sql_query: "SHOW GLOBAL STATUS LIKE 'Innodb_system%'" sql_response_format: variables
The SHOW GLOBAL STATUS
query results in this table:
Variable_name | Value |
---|---|
Innodb_system_rows_deleted |
0 |
Innodb_system_rows_inserted |
0 |
Innodb_system_rows_read |
5062 |
Innodb_system_rows_updated |
315 |
Results are grouped by type in the result event for convenient mapping in
Elasticsearch. For example, strings
values are grouped into sql.strings
, numeric
into sql.numeric
, and so on.
The example shown earlier generates this event:
{ "@timestamp": "2020-06-09T15:09:14.407Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "service": { "address": "172.18.0.2:3306", "type": "sql" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 1272810 }, "sql": { "driver": "mysql", "query": "SHOW GLOBAL STATUS LIKE 'Innodb_system%'", "metrics": { "numeric": { "innodb_system_rows_updated": 315, "innodb_system_rows_deleted": 0, "innodb_system_rows_inserted": 0, "innodb_system_rows_read": 5062 } } }, "metricset": { "name": "query", "period": 10000 }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "name": "elastic", "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "488431bd-bd3c-4442-ad51-0c50eb555787", "id": "670ef211-87f0-4f38-8beb-655c377f1629" } }
Example: query PostgreSQL and generate a "table" result
editThis sql.yml
configuration shows how to query PostgreSQL and generate
a "table" result. This configuration generates a single event for each row
returned:
- module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"] driver: "postgres" sql_query: "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" sql_response_format: table
The SELECT query results in this table:
datid | datname | blks_read | blks_hit | tup_returned | tup_fetched | stats_reset |
---|---|---|---|---|---|---|
69448 |
stuff |
8652 |
205976 |
1484625 |
53218 |
2020-06-07 22:50:12 |
13408 |
postgres |
0 |
0 |
0 |
0 |
|
13407 |
template0 |
0 |
0 |
0 |
0 |
Because the table contains three rows, three events are generated, one event for each row. For example, this event is created for the first row:
{ "@timestamp": "2020-06-09T14:47:35.481Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "service": { "address": "localhost:5432", "type": "sql" }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "1bffe66d-a1ae-4ed6-985a-fd48548a1971", "id": "670ef211-87f0-4f38-8beb-655c377f1629", "name": "elastic" }, "sql": { "metrics": { "numeric": { "tup_fetched": 53350, "datid": 69448, "blks_read": 8652, "blks_hit": 206501, "tup_returned": 1.491873e+06 }, "string": { "stats_reset": "2020-06-07T20:50:12.632975Z", "datname": "stuff" } }, "driver": "postgres", "query": "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 14076705 }, "metricset": { "name": "query", "period": 10000 } }
Example: get the buffer catch hit ratio in Oracle
editThis sql.yml
configuration shows how to get the buffer cache hit ratio:
- module: sql metricsets: - query period: 10s hosts: ["oracle://sys:Oradoc_db1@172.17.0.3:1521/ORCLPDB1.localdomain?sysdba=1"] driver: "oracle" sql_query: 'SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS' sql_response_format: table
The example generates this event:
{ "@timestamp": "2020-06-09T15:41:02.200Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "sql": { "metrics": { "numeric": { "hit ratio": 0.9742963357937117, "physical_reads": 17161, "db_block_gets": 122221, "consistent_gets": 545427 }, "string": { "name": "DEFAULT" } }, "driver": "oracle", "query": "SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (physical_reads / (db_block_gets + consistent_gets)) \"Hit Ratio\" FROM V$BUFFER_POOL_STATISTICS" }, "metricset": { "period": 10000, "name": "query" }, "service": { "address": "172.17.0.3:1521", "type": "sql" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 39233704 }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "id": "670ef211-87f0-4f38-8beb-655c377f1629", "name": "elastic", "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "49e00060-0fa4-4b34-80f1-446881f7a788" } }
Example: get the buffer cache hit ratio for MSSQL
editThis sql.yml
configuration gets the buffer cache hit ratio:
- module: sql metricsets: - query period: 10s hosts: ["sqlserver://SA:password@localhost"] driver: "mssql" sql_query: 'SELECT * FROM sys.dm_db_log_space_usage' sql_response_format: table
The example generates this event:
{ "@timestamp": "2020-06-09T15:39:14.421Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "sql": { "driver": "mssql", "query": "SELECT * FROM sys.dm_db_log_space_usage", "metrics": { "numeric": { "log_space_in_bytes_since_last_backup": 524288, "database_id": 1, "total_log_size_in_bytes": 2.08896e+06, "used_log_space_in_bytes": 954368, "used_log_space_in_percent": 45.686275482177734 } } }, "event": { "dataset": "sql.query", "module": "sql", "duration": 40750570 }, "metricset": { "name": "query", "period": 10000 }, "service": { "address": "172.17.0.2", "type": "sql" }, "agent": { "id": "670ef211-87f0-4f38-8beb-655c377f1629", "name": "elastic", "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "3da88889-036e-47cb-a88b-275037fa2bc9" }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" } }
Example: launch two or more queries
editTo launch two or more queries, specify the full configuration for each query. For example:
- module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"] driver: "postgres" sql_query: "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" sql_response_format: table - module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"] driver: "postgres" sql_query: "SELECT * FROM pg_catalog.pg_tables pt WHERE schemaname ='pg_catalog'" sql_response_format: table
Example configuration
editThe SQL module supports the standard configuration options that are described in Modules. Here is an example configuration:
metricbeat.modules: - module: sql metricsets: - query period: 10s hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"] driver: "postgres" sql_query: "select now()" sql_response_format: table
Metricsets
editThe following metricsets are available:
On this page