[inputs.sqlserver] exclude_query not honored

[vidkun]

Relevant telegraf.conf:

query_version = 2
exclude_query = [ 'WaitStatsCatagorized' , 'DatabaseIO' , 'DatabaseProperties' , 'CPUHistory' , 'DatabaseSize' , 'DatabaseStats' , 'MemoryClerk' , 'VolumeSpace' , 'Schedulers' , 'AzureDBResourceStats' , 'AzureDBResourceGovernance' , 'SqlRequests' ]

System info:

PS C:\Program Files\telegraf> .\tele
Telegraf 1.12.4 (git: HEAD 9a4f08e)

Windows 2016 & 2012 R2
SQL Server 2016 and 2017

Steps to reproduce:

1. Add sqlserver input plugin config
2. Start telegraf service or run telegraf.exe --test
3. Watch all the SQL Server metrics come through

Expected behavior:

I expect queries defined in the exclude_query option to be excluded from data collected and forwarded.

Actual behavior:

It appears as if all metrics are being collected and forwarded regardless of exclude_query config. Even if you define all query types in the exclude_query option, you still get SQL Server metrics pulled back when running telegraf.

Additional info:

 Disk\ Read\ IO\ Throttled/sec\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Read\ IO\ Throttled/sec\ |\ internal\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Read\ IO/sec\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Read\ IO/sec\ |\ internal\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ Bytes/sec\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ Bytes/sec\ |\ internal\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ IO\ Throttled/sec\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ IO\ Throttled/sec\ |\ internal\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ IO/sec\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Disk\ Write\ IO/sec\ |\ internal\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=0i 1572537597000000000
 Max\ memory\ (KB)\ |\ default\ |\ Resource\ Pool\ Stats,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ counters value=5976880i 1572537597000000000

[glinton]

There is a typo in your config - WaitStatsCatagorized -> WaitStatsCategorized, this would only affect that one query and would end up collecting stats for it.

It's unclear to me if the content of Additional Info is what you are expecting, or what is being returned. It should be returned as your query version is 2 and you are not excluding the PerformanceCounters query (though I'm not certain which set's Max memory).

Can you post the output of telegraf.exe --config \path\to\your\modified\telegraf.conf --test?

[vidkun]

Updated the exclude_query config to:

exclude_query = [ 'PerformanceCounters' , 'WaitStatsCategorized' , 'DatabaseIO' , 'DatabaseProperties' , 'CPUHistory' , 'DatabaseSize' , 'DatabaseStats' , 'MemoryClerk' , 'VolumeSpace' , 'Schedulers' , 'AzureDBResourceStats' , 'AzureDBResourceGovernance' , 'SqlRequests' ]

Ran:
PS C:\Program Files\telegraf> .\telegraf.exe --test --config .\telegraf.conf

And got the results in the attached testrun.txt.
testrun.txt

Edit: If it's not clear, that exclude_query includes all query types. So expected results would be that the --test would return zero MSSQL related metrics. As shown in the attached file, that is not the case.

[danielnelson]

Can you add ServerProperties to your list and recheck? It looks like we just need to document this query.

[vidkun]

Alright... stupid user error. Apparently, I overlooked the exclude_query config being under [inputs.sqlserver.tags] instead of just the main input stanza. My apologies.

That said, I did update the config to also include your ServerProperties and re-ran the --test. For some reason I'm still getting just this one single sqlserver related result:

> Performance\ metrics,host=host,index=dbmetrics,servername=server:MSSQLSERVER2017,type=Performance\ metrics Available\ physical\ memory\ (bytes)=3834626048i,Average\ pending\ disk\ IO=0i,Average\ runnable\ tasks=0i,Average\ tasks=7i,Buffer\ pool\ rate\ (bytes/sec)=1199i,Connection\ memory\ per\ connection\ (bytes)=296550i,Memory\ grant\ pending=0i,Page\ File\ Usage\ (%)=43i,Page\ lookup\ per\
 batch\ request=40440i,Page\ split\ per\ batch\ request=4i,Point\ In\ Time\ Recovery=0i,Readahead\ per\ page\ read=84i,Signal\ wait\ (%)=15i,Sql\ compilation\ per\ batch\ request=43i,Sql\ recompilation\ per\ batch\ request=1i,Total\ target\ memory\ ratio=27i 1573051091000000000

This is with this exclude_query setting:

exclude_query = [ 'ServerProperties' , 'PerformanceCounters' , 'WaitStatsCategorized' , 'DatabaseIO' , 'DatabaseProperties' , 'CPUHistory' , 'DatabaseSize' , 'DatabaseStats' , 'MemoryClerk' , 'VolumeSpace' , 'Schedulers' , 'AzureDBResourceStats' , 'AzureDBResourceGovernance' , 'SqlRequests' ]

Edit: Nevermind. Fixed that too. Related to query version. This is resolved now.