SQL Metrics cant be merged

[dcst55]

#6731 # Relevant telegraf.conf:

[global_tags]
[agent]
  interval = "10s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  collection_jitter = "0s"
  flush_interval = "60s"
  flush_jitter = "0s"
  precision = ""
  debug = true
  quiet = false
  logfile = "/telegraf/telegraf.log"
  logfile_rotation_interval = 86400
  logfile_rotation_max_archives = 2
  hostname = ""
  omit_hostname = false


###############################################################################
#                                  OUTPUTS                                    #
###############################################################################


[[outputs.file]]
    files = ["C:/telegraf/output/metrics.json"]
    data_format = "json"
[[aggregators.merge]]
    drop_original = true


###############################################################################
#                                  INPUTS                                     #
###############################################################################


[[inputs.win_perf_counters]]
    [[inputs.win_perf_counters.object]]
        ObjectName = "Processor"
        Instances = ["*"]
        Counters = [
            "% Idle Time",
            "% Interrupt Time",
            "% Privileged Time",
            "% User Time",
            "% Processor Time",
            "% DPC Time",
            ]
        Measurement = "win_cpu"
        IncludeTotal=true

    [[inputs.win_perf_counters.object]]
        ObjectName = "LogicalDisk"
        Instances = ["*"]
        Counters = [
            "% Idle Time",
            "% Disk Time",
            "% Disk Read Time",
            "% Disk Write Time",
            "Current Disk Queue Length",
            "% Free Space",
            "Free Megabytes",
            ]
        Measurement = "win_disk"
        IncludeTotal=true

    [[inputs.win_perf_counters.object]]
        ObjectName = "PhysicalDisk"
        Instances = ["*"]
        Counters = [
            "Disk Read Bytes/sec",
            "Disk Write Bytes/sec",
            "Current Disk Queue Length",
            "Disk Reads/sec",
            "Disk Writes/sec",
            "% Disk Time",
            "% Disk Read Time",
            "% Disk Write Time",
            ]
        Measurement = "win_diskio"

    [[inputs.win_perf_counters.object]]
        ObjectName = "Network Interface"
        Instances = ["*"]
        Counters = [
            "Bytes Received/sec",
            "Bytes Sent/sec",
            "Packets Received/sec",
            "Packets Sent/sec",
            "Packets Received Discarded",
            "Packets Outbound Discarded",
            "Packets Received Errors",
            "Packets Outbound Errors",
            ]
        Measurement = "win_net"
        IncludeTotal=true

    [[inputs.win_perf_counters.object]]
        ObjectName = "System"
        Counters = [
            "Context Switches/sec",
            "System Calls/sec",
            "Processor Queue Length",
            "System Up Time",
            ]
        Instances = ["------"]
        Measurement = "win_system"

    [[inputs.win_perf_counters.object]]
        ObjectName = "Memory"
        Counters = [
            "Available Bytes",
            "Cache Faults/sec",
            "Demand Zero Faults/sec",
            "Page Faults/sec",
            "Pages/sec",
            "Transition Faults/sec",
            "Pool Nonpaged Bytes",
            "Pool Paged Bytes",
            "Standby Cache Reserve Bytes",
            "Standby Cache Normal Priority Bytes",
            "Standby Cache Core Bytes",
            ]
        Instances = ["------"]
        Measurement = "win_mem"

    [[inputs.win_perf_counters.object]]
        ObjectName = "Paging File"
        Counters = [
            "% Usage",
            ]
        Instances = ["_Total"]
        Measurement = "win_swap"

[[inputs.cpu]]
    percpu = false
    totalcpu = true
    collect_cpu_time = false
    report_active = false
    fielddrop = ["usage_iowait", "usage_irq", "usage_nice", "usage_softirq", "usage_steal", "usage_system", "usage_user"]

[[inputs.disk]]
    fielddrop = ["inodes*"]
    tagexclude = ["mode", "path"]

[[inputs.mem]]
    fieldpass = ["swap_cached", "swap_free", "swap_total", "total", "used", "used_percent", "available", "available_percent", "name"]

[[inputs.sqlserver]]
    servers = ["Server=<SQL_SERVER_IP>;Port=1433;app name=telegraf;log=1;"]
    query_version = 2
    exclude_query = []

    [inputs.sqlserver.tagpass]
        counter = ["Lock Wait Count", 
                "Lock Wait Time", 
                "CPU Usage (time)", 
                "CPU usage %", 
                "Requests completed/sec", 
                "Queued requests", 
                "Errors/sec", 
                "Logins/sec", 
                "Logouts/sec", 
                "Transactions/sec",
                "User Connections",
                "Backup/Restore Throughput/sec"
        ]
        scheduler_id = ["*"]
        wait_category = ["*"]
        hardware_type = ["*"]
        statement_text = ["*"]

System info:

Telegraf Version 1.13.3
Windows Server 2019
Microsoft SQL Server 2017

Steps to reproduce:

1. Start telegraf.
2. show in output File.

Expected behavior:

A more efficient listing of metrics like this:

{"fields":{
	"name":"sqlserver_performance",
	"tags":{
		"counter":
        "Lock Wait Time": 343,
        "Lock Wait Count": 7,
		"database_name":"master",
		"host":"MSSQL-SA",
		"instance":"internal",
		"object":"SQLServer:Workload Group Stats",
		"sql_instance":"MSSQL-SA:INSTANCE01"},
        "timestamp":1583146670}

Actual behavior:

Very inefficient metric listing:

{"fields":{
	"value":7},
	"name":"sqlserver_performance",
	"tags":{
		"counter":"Lock Wait Count",
		"database_name":"master",
		"host":"MSSQL-SA",
		"instance":"internal",
		"object":"SQLServer:Workload Group Stats",
		"sql_instance":"MSSQL-SA:INSTANCE01"},
	"timestamp":1583146670}

{"fields":{
	"value":343},
	"name":"sqlserver_performance",
	"tags":{
		"counter":"Lock Wait Time",
		"database_name":"master",
		"host":"MSSQL-SA",
		"instance":"internal",
		"object":"SQLServer:Workload Group Stats",
		"sql_instance":"MSSQL-SA:INSTANCE01"},
	"timestamp":1583146670}

Additional info:

[danielnelson]

Only metrics which have the same measurement+tagset can be merged. Try to apply the pivot processor before the merge aggregator, this will use the counter tag as the name for the value field:

[[processors.pivot]]
  ## Tag to use for naming the new field.
  tag_key = "counter"
  ## Field to use as the value of the new field.
  value_key = "value"