Skip to content

Commit 8c71abc

Browse files
jsorianojsoriano
authored
Remove datasource option from SQL module and add tests (#15686)
Remove datasource option from SQL module. This option was intended to set the DSN of a database connection, and we were ignoring the hosts setting. In other SQL modules we are using the values in hosts as DSNs, do here the same for consistency. Host is redacted when we cannot parse it as it can contain passwords. StandardizeEvent is exposed in mbtest.Fetcher interface so we can more easily check contents of events in tests. Add integration tests of the module with MySQL and PostgreSQL. Add real data.json with data from MySQL and PostgreSQL.
1 parent 2657f71 commit 8c71abc

12 files changed

Lines changed: 279 additions & 42 deletions

File tree

‎metricbeat/docs/modules/sql.asciidoc‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@ This file is generated! See scripts/mage/docs_collector.go
88

99
beta[]
1010

11-
This is the sql module that fetches metrics from a SQL database. You can define driver, datasource and SQL query.
11+
This is the sql module that fetches metrics from a SQL database. You can define driver and SQL query.
1212

1313

1414

@@ -26,10 +26,9 @@ metricbeat.modules:
2626
metricsets:
2727
- query
2828
period: 10s
29-
hosts: ["localhost"]
29+
hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"]
3030
3131
driver: "postgres"
32-
datasource: "user=myuser password=mypassword dbname=mydb sslmode=disable"
3332
sql_query: "select now()"
3433
3534
----

‎metricbeat/mb/testing/fetcher.go‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ package testing
2020
import (
2121
"testing"
2222

23+
"github.com/elastic/beats/libbeat/beat"
2324
"github.com/elastic/beats/libbeat/common"
2425
"github.com/elastic/beats/metricbeat/mb"
2526
)
@@ -32,6 +33,7 @@ type Fetcher interface {
3233
FetchEvents() ([]mb.Event, []error)
3334
WriteEvents(testing.TB, string)
3435
WriteEventsCond(testing.TB, string, func(common.MapStr) bool)
36+
StandardizeEvent(mb.Event, ...mb.EventModifier) beat.Event
3537
}
3638

3739
// NewFetcher returns a test fetcher from a Metricset configuration
@@ -73,6 +75,10 @@ func (f *reportingMetricSetV2Fetcher) WriteEventsCond(t testing.TB, path string,
7375
}
7476
}
7577

78+
func (f *reportingMetricSetV2Fetcher) StandardizeEvent(event mb.Event, modifiers ...mb.EventModifier) beat.Event {
79+
return StandardizeEvent(f, event, modifiers...)
80+
}
81+
7682
type reportingMetricSetV2FetcherError struct {
7783
mb.ReportingMetricSetV2Error
7884
}
@@ -96,6 +102,10 @@ func (f *reportingMetricSetV2FetcherError) WriteEventsCond(t testing.TB, path st
96102
}
97103
}
98104

105+
func (f *reportingMetricSetV2FetcherError) StandardizeEvent(event mb.Event, modifiers ...mb.EventModifier) beat.Event {
106+
return StandardizeEvent(f, event, modifiers...)
107+
}
108+
99109
type reportingMetricSetV2FetcherWithContext struct {
100110
mb.ReportingMetricSetV2WithContext
101111
}
@@ -118,3 +128,7 @@ func (f *reportingMetricSetV2FetcherWithContext) WriteEventsCond(t testing.TB, p
118128
t.Fatal("writing events", err)
119129
}
120130
}
131+
132+
func (f *reportingMetricSetV2FetcherWithContext) StandardizeEvent(event mb.Event, modifiers ...mb.EventModifier) beat.Event {
133+
return StandardizeEvent(f, event, modifiers...)
134+
}

‎x-pack/metricbeat/metricbeat.reference.yml‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -971,10 +971,9 @@ metricbeat.modules:
971971
metricsets:
972972
- query
973973
period: 10s
974-
hosts: ["localhost"]
974+
hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"]
975975

976976
driver: "postgres"
977-
datasource: "user=myuser password=mypassword dbname=mydb sslmode=disable"
978977
sql_query: "select now()"
979978

980979

‎x-pack/metricbeat/module/sql/_meta/config.yml‎

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2,9 +2,8 @@
22
metricsets:
33
- query
44
period: 10s
5-
hosts: ["localhost"]
5+
hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"]
66

77
driver: "postgres"
8-
datasource: "user=myuser password=mypassword dbname=mydb sslmode=disable"
98
sql_query: "select now()"
109

‎x-pack/metricbeat/module/sql/_meta/docs.asciidoc‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,3 +1,3 @@
1-
This is the sql module that fetches metrics from a SQL database. You can define driver, datasource and SQL query.
1+
This is the sql module that fetches metrics from a SQL database. You can define driver and SQL query.
22

33

‎x-pack/metricbeat/module/sql/docker-compose.yml‎

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,12 @@
1+
version: '2.3'
2+
3+
services:
4+
mysql:
5+
extends:
6+
file: ../../../../metricbeat/module/mysql/docker-compose.yml
7+
service: mysql
8+
9+
postgresql:
10+
extends:
11+
file: ../../../../metricbeat/module/postgresql/docker-compose.yml
12+
service: postgresql

‎x-pack/metricbeat/module/sql/query/_meta/data.json‎

Lines changed: 25 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,26 +1,30 @@
11
{
2-
"@timestamp":"2016-05-23T08:05:34.853Z",
3-
"beat":{
4-
"hostname":"beathost",
5-
"name":"beathost"
2+
"@timestamp": "2017-10-12T08:05:34.853Z",
3+
"event": {
4+
"dataset": "sql.query",
5+
"duration": 115000,
6+
"module": "sql"
67
},
7-
"metricset":{
8-
"host":"localhost",
9-
"module":"sql",
10-
"name":"query",
11-
"rtt":44269
8+
"metricset": {
9+
"name": "query",
10+
"period": 10000
1211
},
13-
"sql":{
14-
"metrics":{
15-
"numeric":{
16-
"mynumericfield":1
17-
},
18-
"string":{
19-
"mystringfield":"abc"
20-
}
12+
"service": {
13+
"address": "172.22.0.3:3306",
14+
"type": "sql"
15+
},
16+
"sql": {
17+
"driver": "mysql",
18+
"metrics": {
19+
"numeric": {
20+
"table_rows": 6
21+
},
22+
"string": {
23+
"engine": "InnoDB",
24+
"table_name": "sys_config",
25+
"table_schema": "sys"
26+
}
2127
},
22-
"driver":"postgres",
23-
"query":"select * from mytable"
24-
},
25-
"type":"metricsets"
28+
"query": "select table_schema, table_name, engine, table_rows from information_schema.tables where table_rows \u003e 0;"
29+
}
2630
}

‎x-pack/metricbeat/module/sql/query/_meta/data_postgres.json‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
{
2+
"@timestamp": "2017-10-12T08:05:34.853Z",
3+
"event": {
4+
"dataset": "sql.query",
5+
"duration": 115000,
6+
"module": "sql"
7+
},
8+
"metricset": {
9+
"name": "query",
10+
"period": 10000
11+
},
12+
"service": {
13+
"address": "172.22.0.2:5432",
14+
"type": "sql"
15+
},
16+
"sql": {
17+
"driver": "postgres",
18+
"metrics": {
19+
"numeric": {
20+
"blk_read_time": 0,
21+
"blk_write_time": 0,
22+
"blks_hit": 1923,
23+
"blks_read": 111,
24+
"conflicts": 0,
25+
"datid": 12379,
26+
"deadlocks": 0,
27+
"numbackends": 1,
28+
"temp_bytes": 0,
29+
"temp_files": 0,
30+
"tup_deleted": 0,
31+
"tup_fetched": 1249,
32+
"tup_inserted": 0,
33+
"tup_returned": 1356,
34+
"tup_updated": 0,
35+
"xact_commit": 18,
36+
"xact_rollback": 0
37+
},
38+
"string": {
39+
"datname": "postgres",
40+
"stats_reset": "2020-01-21 11:23:56.53"
41+
}
42+
},
43+
"query": "select * from pg_stat_database"
44+
}
45+
}

‎x-pack/metricbeat/module/sql/query/dsn.go‎

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
// Copyright Elasticsearch B.V. and/or licensed to Elasticsearch B.V. under one
2+
// or more contributor license agreements. Licensed under the Elastic License;
3+
// you may not use this file except in compliance with the Elastic License.
4+
5+
package query
6+
7+
import (
8+
"net/url"
9+
10+
"github.com/go-sql-driver/mysql"
11+
12+
"github.com/elastic/beats/metricbeat/mb"
13+
)
14+
15+
// ParseDSN tries to parse the host
16+
func ParseDSN(mod mb.Module, host string) (mb.HostData, error) {
17+
// TODO: Add support for `username` and `password` as module options
18+
19+
sanitized := sanitize(host)
20+
21+
return mb.HostData{
22+
URI: host,
23+
SanitizedURI: sanitized,
24+
Host: sanitized,
25+
}, nil
26+
}
27+
28+
func sanitize(host string) string {
29+
// Host is a standard URL
30+
if url, err := url.Parse(host); err == nil && len(url.Host) > 0 {
31+
return url.Host
32+
}
33+
34+
// Host is a MySQL DSN
35+
if config, err := mysql.ParseDSN(host); err == nil {
36+
return config.Addr
37+
}
38+
39+
// TODO: Add support for PostgreSQL connection strings and other formats
40+
41+
return "(redacted)"
42+
}

‎x-pack/metricbeat/module/sql/query/query.go‎

Lines changed: 9 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -10,21 +10,22 @@ import (
1010
"strings"
1111
"time"
1212

13+
"github.com/jmoiron/sqlx"
1314
"github.com/pkg/errors"
1415

1516
"github.com/elastic/beats/libbeat/common"
1617
"github.com/elastic/beats/libbeat/common/cfgwarn"
1718
"github.com/elastic/beats/metricbeat/mb"
18-
19-
"github.com/jmoiron/sqlx"
2019
)
2120

2221
// init registers the MetricSet with the central registry as soon as the program
2322
// starts. The New function will be called later to instantiate an instance of
2423
// the MetricSet for each host defined in the module's configuration. After the
2524
// MetricSet has been created then Fetch will begin to be called periodically.
2625
func init() {
27-
mb.Registry.MustAddMetricSet("sql", "query", New)
26+
mb.Registry.MustAddMetricSet("sql", "query", New,
27+
mb.WithHostParser(ParseDSN),
28+
)
2829
}
2930

3031
// MetricSet holds any configuration or state information. It must implement
@@ -33,9 +34,8 @@ func init() {
3334
// interface methods except for Fetch.
3435
type MetricSet struct {
3536
mb.BaseMetricSet
36-
Driver string
37-
Datasource string
38-
Query string
37+
Driver string
38+
Query string
3939
}
4040

4141
// New creates a new instance of the MetricSet. New is responsible for unpacking
@@ -44,9 +44,8 @@ func New(base mb.BaseMetricSet) (mb.MetricSet, error) {
4444
cfgwarn.Beta("The sql query metricset is beta.")
4545

4646
config := struct {
47-
Driver string `config:"driver"`
48-
Datasource string `config:"datasource"`
49-
Query string `config:"sql_query"`
47+
Driver string `config:"driver"`
48+
Query string `config:"sql_query"`
5049
}{}
5150

5251
if err := base.Module().UnpackConfig(&config); err != nil {
@@ -56,7 +55,6 @@ func New(base mb.BaseMetricSet) (mb.MetricSet, error) {
5655
return &MetricSet{
5756
BaseMetricSet: base,
5857
Driver: config.Driver,
59-
Datasource: config.Datasource,
6058
Query: config.Query,
6159
}, nil
6260
}
@@ -65,7 +63,7 @@ func New(base mb.BaseMetricSet) (mb.MetricSet, error) {
6563
// format. It publishes the event which is then forwarded to the output. In case
6664
// of an error set the Error field of mb.Event or simply call report.Error().
6765
func (m *MetricSet) Fetch(report mb.ReporterV2) error {
68-
db, err := sqlx.Open(m.Driver, m.Datasource)
66+
db, err := sqlx.Open(m.Driver, m.HostData().URI)
6967
if err != nil {
7068
return errors.Wrap(err, "error opening connection")
7169
}

0 commit comments

Comments
 (0)