Grafana Agent (v0.39) - how to add 'query_config' block for mssql_config?

Grafana Alloy
1

The latest version of Grafana Agent (v0.39) embedded custom metrics for MSSQL integration. I tried the below configuration with adding ‘query_config’ section in integrations_config, and then Grafana Agent Service failed to start. If I removed ‘query_config’ section, it worked.

mssql:
	enabled: true
	connect_string:  "sqlserver://<USERNAME>:<PASSWORD>@<HOST>:<PORT>"
	query_config: 
	  - metric_name: mssql_deadlocks_total 
	   type: counter 
	   help: 'Number of lock requests that resulted in a deadlock.' 
	   values: [cntr_value] 
	  query: | 
     SELECT cntr_value FROM sys.dm_os_performance_counters WITH (NOLOCK) WHERE counter_name = 'Number of Deadlocks/sec' AND instance_name = '_Total'
2
3

I just followed the doc you posted, adding ‘query_config’ block in the config file, but failed.

4

image
image715×510 34.4 KB

can you try something like this:

query_config:
collector_name: mssqlmi_clerk

	# Similar to global.min_interval, but applies to the queries defined by this collector only.
	#min_interval: 0s

	metrics:
	  #
	  # Collected from sys.dm_os_memory_clerks
	  #
	  - metric_name: mssqlmi_clerk_size_kilobytes
		type: gauge
		help: 'Memory Clerk'
		key_labels:
		  - clerk_type
		values: [size_kb]
		query_ref: mssqlmi_clerk

	queries:
	  - query_name: mssqlmi_clerk
		query: |
		  SELECT
				  mc.[type] AS [clerk_type]
				  ,SUM(mc.[pages_kb]) AS [size_kb]
		  FROM sys.[dm_os_memory_clerks] AS mc WITH (NOLOCK)
		  GROUP BY
				   mc.[type]
		  HAVING
				  SUM(mc.[pages_kb]) >= 1024
		  OPTION(RECOMPILE);
5

I wanted to post here as I was not able to find this information anywhere else. There are two important things to keep in mind for using custom query metrics:

  1. You may add your metrics to the standard list by copying the original mssql_standard collector block (available here) to a yaml file and appending your entries to the existing list. This new file can then be fetched using a local.file block assigned to the query_config variable:
prometheus.exporter.mssql "integrations_mssql" {
 connection_string = "sqlserver://user:pass@127.0.0.1:1433"
 query_config = local.file.mssqlqueries.content
} 
local.file "mssqlqueries" {
  filename = "./mssqlqueries.yml"
}
  1. Because the query_config variable only supports a single collector_name and metrics entries, multiple query files (e.g., one for cluster-specific metrics and a separate for custom application metrics) require individual exporter, relabel, scrape, etc blocks for each yaml file:
# Note that this first definition includes the default SQL metrics 
# and does not require a local.file definition
prometheus.exporter.mssql "integrations_mssql" {
  connection_string = "sqlserver://user:pass@127.0.0.1:1433"
}

discovery.relabel "integrations_mssql" {
	targets = prometheus.exporter.mssql.integrations_mssql.targets
	rule {
		target_label = "job"
		replacement  = "integrations/mssql"
	}
}

prometheus.scrape "integrations_mssql" {
	targets    = discovery.relabel.integrations_mssql.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/mssql"
}

# Additional SQL metrics defined in yaml files
prometheus.exporter.mssql "integrations_app_sql" {
  connection_string = "sqlserver://user:pass@127.0.0.1:1433"
  query_config = local.file.app_sql_queries.content
}
local.file "app_sql_queries" {
  filename = "./mssql_appdb.collector.yml"
}

discovery.relabel "integrations_app_sql" {
	targets = prometheus.exporter.mssql.integrations_app_sql.targets
        rule {
		target_label = "job"
		replacement  = "integrations/mssql"
	}
}

prometheus.scrape "integrations_app_sql" {
	targets    = discovery.relabel.integrations_app_sql.output
	forward_to = [prometheus.remote_write.metrics_service.receiver]
	job_name   = "integrations/mssql"
}
1 Like