Add custom metrics to expose MSSQL server hostname

[barywhyte]

I am not sure weather I should call this improvement suggestion or weather this is by design. I am adding the following custom metrics to mssql_standard.collector.yml file. While other custom metrics such as mssql_last_elapsed_time etc are working well, I can't seem to be able to add the following metric to expose database hostname. Below was my first attempt:

- metric_name: mssql_hostname
  type: gauge
  help: 'Database server hostname'
  key_labels:
    - hostname
  query: |
    SELECT @@SERVERNAME AS hostname

Which produced error as it cannot convert string hostname to numeric gauge. Then, I tried other variations and the last that nearly worked was:

- metric_name: mssql_hostname
    type: gauge
    help: 'Database server hostname'
    key_labels:
      - hostname
    values:
      - 1
    query: |
      SELECT @@SERVERNAME AS hostname

Which produced error:
Error gathering metrics: [from Gatherer #1] [, collector="mssql_standard", query="mssql_hostname"] column(s) ["1"] missing from query result

I used 1 as a placeholder value, hoping that I can retrieve the hostname by whatever means using PromQL.

I am wondering if there is another way to expose servername/hostname on which MSSQL runs to Prometheus. I am unable to find MSSQL metrics that exposes hostname as part of its label or other attributes

[burningalchemist]

Hey @barywhyte, for this purpose there's a new field static_value which serves exactly as a placeholder if you just need to extract a string label. This feature is not released yet, but will be available in the upcoming v0.13.

As a workaround, I suggest you to construct a query so it returns two columns: the hostname string and some static value. Something like this:

SELECT @@SERVERNAME AS hostname, 1 as value;

Then you specify value in the values field in the config.

[barywhyte]

This fixed it for me! Thank you @burningalchemist. Looking forward to the new version v0.13.