sqlexporter.yaml

global:
  scrape_timeout_offset: 250ms
  min_interval: 0s
  max_connections: 3
  max_idle_connections: 3

target:
  data_source_name: "postgres://crate@localhost:5432/sys?sslmode=disable"
  collectors: [health_collector]

collectors:
  - collector_name: health_collector
    metrics:
      - metric_name: cluster_underreplicated_shards
        type: gauge
        help: "Underreplicated shards by table"
        key_labels:
          - health
          - severity
          - table
          - schema
        values:
          - shards
        query: |
          SELECT lower(health) AS "health",
                 severity,
                 lower(table_name) AS "table",
                 lower(table_schema) AS "schema",
                 sum(underreplicated_shards) as "shards"
          FROM sys.health
          GROUP BY 1, 2, 3, 4
      - metric_name: cluster_missing_shards
        type: gauge
        help: "Missing shards by table"
        key_labels:
          - health
          - severity
          - table
          - schema
        values:
          - shards
        query: |
          SELECT lower(health) AS "health",
                 severity,
                 lower(table_name) AS "table",
                 lower(table_schema) AS "schema",
                 sum(missing_shards) as "shards"
          FROM sys.health
          GROUP BY 1, 2, 3, 4
      - metric_name: cluster_health
        type: gauge
        help: "CrateDB cluster health"
        key_labels:
          - name
        values:
          - severity
        query: |
          SELECT sys_cluster.name as "name",
                 max(sys_health.severity) as "severity"
          FROM sys.health sys_health, sys.cluster sys_cluster
          GROUP BY 1

$ ./sql_exporter -config.file sqlexporter.yaml
I0624 17:37:05.558089   11464 main.go:52] Starting SQL exporter (version=0.5, branch=master, revision=fc5ed07ee38c5b90bab285392c43edfe32d271c5) (go=go1.11.3, user=root@f24ba5099571, date=20190114-09:24:06)
I0624 17:37:05.558493   11464 config.go:18] Loading configuration from sqlexporter.yaml
I0624 17:37:05.558958   11464 main.go:67] Listening on :9399

$ http :9399/metrics
HTTP/1.1 200 OK
Content-Encoding: gzip
Content-Length: 225
Content-Type: text/plain; version=0.0.4; charset=utf-8
Date: Mon, 24 Jun 2019 20:17:11 GMT

# HELP cluster_health CrateDB cluster health
# TYPE cluster_health gauge
cluster_health{name="cr8-crate-run784392651"} 2
# HELP cluster_missing_shards Missing shards by table
# TYPE cluster_missing_shards gauge
cluster_missing_shards{health="yellow",schema="doc",severity="2",table="t1"} 0
# HELP cluster_underreplicated_shards Underreplicated shards by table
# TYPE cluster_underreplicated_shards gauge
cluster_underreplicated_shards{health="yellow",schema="doc",severity="2",table="t1"} 1