Adding a custom metric (db_size) / query format

[TomClqncy]

Hello, I'm trying to add a custom metric in the config (Grafana agent yaml file) to collect db_size for the different databases.
Here is the query which works in the SQL console:
SELECT DB_NAME(6) AS [Database], CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS FileSizeGB FROM sys.master_files
However, we got that error
level=error integration=mssql msg="Invalid metric description." err="[mssqlintegration,collector=mssql_standard,query=mssql_db_size] Missing values for the requested columns: [\"DB_NAME\" \"Size of Databases\"]"
Then, I tried to change the query to : SELECT DB_NAME(6) AS [Database], CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS FileSizeGB GROUP BY DB_NAME
And I got that error now : level=error integration=mssql msg="Invalid metric description." err="[mssqlintegration,collector=mssql_standard,query=mssql_db_size] mssql: Invalid column name 'size'."

It would be great to get some help on how to write the query to get the db_size correctly. Thx a lot for support

[burningalchemist]

Hi @TomClqncy, let me have a look and come back to you soon.

[burningalchemist]

Just a quick note:

• first error comes from sql_exporter; the second one comes from the mssql driver, so there might be something wrong (or a limitation) from the mssql driver side.

Could you also post here the config for that query? Missing values for the requested columns is a sign they possibly should be explicitly set in the config.

[TomClqncy]

You replied fast, THANK YOU 👍
Is that what you asked me for?
integrations: mssql: enabled: true scrape_interval: 60s scrape_integration: true instance: ${COMPUTERNAME} connection_string: 'sqlserver://xxxx:xxxx@127.0.0.1:1433' # replace with your connection string metric_relabel_configs: - action: keep regex: mssql_available_commit_memory_bytes|mssql_batch_requests_total|mssql_buffer_cache_hit_ratio|mssql_build_info|mssql_checkpoint_pages_sec|mssql_connections|mssql_deadlocks_total|mssql_io_stall_seconds_total|mssql_kill_connection_errors_total|mssql_log_growths_total|mssql_os_memory|mssql_os_page_file|mssql_page_fault_count_total|mssql_server_target_memory_bytes|mssql_server_total_memory_bytes source_labels: - __name__ query_config: collector_name: mssql_standard min_interval: 30s metrics: - metric_name: mssql_db_count type: counter help: 'Number of dbs on Host.' key_labels: - DB_NAME value_label: dbs values: - Number of Databases query: | SELECT count(name) as NumOfDBs, state_desc FROM master.sys.databases where database_id > 4 group by state_desc

[burningalchemist]

Yeah, this part specifically (this is from sql_exporter):

    metrics:
    - metric_name: mssql_db_count
      type: counter 
      help: 'Number of dbs on Host.' 
      key_labels: 
      - DB_NAME
      value_label: dbs 
      values: 
      - Number of Databases 
      query: |
        SELECT count(name) as NumOfDBs, state_desc FROM master.sys.databases where database_id > 4 group by state_desc

Values list should be a set of column names. So if in the query you put ... as NumOfDBs, then in values list we should also have specifically NumOfDBs for the example.

Same goes for key_labels (it should also be a column in the result set).

[TomClqncy]

So this way?
query_config: collector_name: mssql_standard min_interval: 30s metrics: - metric_name: mssql_db_size type: gauge help: 'Size of dbs on Host.' key_labels: - DB_NAME value_label: dbs values: - FileSizeGB query: | SELECT DB_NAME(6) AS [Database], CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS FileSizeGB

[burningalchemist]

Yeah, it looks better to me. Show me what the error returns if there's one. If only I had access to MSSQL instance, I'd test on my end.

Also - key_labels - since you use AS Database - change DB_NAME to Database for the alias used.

[TomClqncy]

We are here now but still doesn't work. I've built a VM on GCP with a SQL Server. I can share access if you want
SELECT DB_NAME(6) AS [Database], CAST(size * 8. / 1024 / 1024 AS decimal(12,4)) AS [FileSizeGB] FROM sys.master_files

[burningalchemist]

We can hop on a call, what do you think?

[TomClqncy]

LinkedIn request sent to communicate 1:1 ;)

[burningalchemist]

Hey @TomClqncy, did it work out for you eventually? I sent you a couple of messages, but I guess you probably haven't read them. 🙂

[TomClqncy]

Hello @burningalchemist , sorry for following up lately. Still on the table. Update to come on Monday (LinkedIn message sent to you).
Cheers