sql_exporter - column(s) ["serviceRequest1" "serviceResponseCode1"] missing from query result

Question

I have issues with sql_exporter. A very simple SQL query does not work.

DB query and output (SQL Server 2022)

SELECT [serviceRequest], [serviceResponseCode]
FROM [ccc].[bbb].[aaa]
WHERE [serviceDisplayName] = 'UploadDoc'

Output: URI and HTTP_CODE

http://example.org/OTCS/llisapi.dll 200
http://example.org/OTCS/llisapi.dll 200
http://example.org/OTCS/llisapi.dll 200
http://example.org/OTCS/llisapi.dll 200

Very simple: I wish to make a rule if not 200 and return the HTTP code and URI

Config

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

target:
  data_source_name: 'sqlserver://x:xx:1433'
  collectors: [YYY]

collector_files: 
  - "YYY.collector.yml"

and

collector_name: YYY

metrics:
  - metric_name: upload_document
    type: gauge
    help: 'Upload Document with REST API'
    key_labels:
      - serviceRequest1
      - serviceResponseCode1
    value_label: UploadDocHTTPcode
    values: 
      - serviceRequest
      - serviceResponseCode
    query: |
      SELECT [serviceRequest], [serviceResponseCode]
      FROM [ccc].[bbb].[aaa]
      WHERE [serviceDisplayName] = 'UploadDoc'

Error

I0831 09:31:17.132976    6824 main.go:67] Listening on :9399
I0831 09:31:23.247900    6824 sql.go:88] Database handle successfully opened with driver sqlserver.
I0831 09:31:23.280905    6824 promhttp.go:38] Error gathering metrics: [from Gatherer #1] [, collector="YYY", query="upload_document"] column(s) ["serviceRequest1" "serviceResponseCode1"] missing from query result

Answer 1

Documentation for sql_exporter is rather lacking, but general idea can be found here:

• key_labels are expected to be names of columns to be used as labels in created metric. In your case it should be a single serviceRequest.
• values - names of columns that contain value of metric to be created. In your case it should be serviceResponseCode.
• value_label name of the label, used if multiple columns are listed in values section, to distinguish between metrics corresponding to each of them. In this case it is not needed.

So to create metric

upload_document{serviceRequest="http://example.org/OTCS/llisapi.dll"} 200

you need configuration like

collector_name: YYY

metrics:
  - metric_name: upload_document
    type: gauge
    help: 'Upload Document with REST API'
    key_labels:
      - serviceRequest
    values: [serviceResponseCode]
    query: |
      SELECT DISTINCT [serviceRequest], [serviceResponseCode]
      FROM [ccc].[bbb].[aaa]
      WHERE [serviceDisplayName] = 'UploadDoc'

Also please notice that it is expected for metric to contain unique set of labels. I'm not sure if sql_exporter ensures this, so I added a DISTINCT. But depending on you data structure, you might need to rework this query entirely to get only last result, not every unique pair.