Metrics filtering - combine namepass and tagpass

[Giovanni_Luisotto]

Hello

I’m gathering SQL server related metrics (but that’s not important, the problem can be applied to any input) using Telegraf, and I’ve been asked to set up a filter on a subset of points.

The input configuration runs different queries and writes several measurements, I have to filter only one measurement and keep only certain values (based on a tag value)
To do so, I should apply in cascade namepass and tagpass which is not possible since them both work on the whole input.

to put it as a list:

1. fetch the data
2. apply a filter on a specific measurement
   2.1 on this measurement, filter a specific tag value
3. do all this in a single input plugin

Example

Let’s say that I want to:
apply a filter on the sqlserver_requests measurements and keep only the “AdventureWorks2016” (database_name) related points

Sample input

sqlserver_requests,database_name=AdventureWorks2016,sql_instance=QDLP03:SQL2017,query_hash=aaa Somefield=10
sqlserver_requests,database_name=AdventureWorks2016,sql_instance=QDLP03:SQL2017,query_hash=bbb Somefield=2
sqlserver_requests,database_name=Northwind,sql_instance=QDLP03:SQL2017,query_hash=ccc Somefield=4
sqlserver_requests,database_name=Northwind,sql_instance=QDLP03:SQL2017,query_hash=ddd Somefield=6
sqlserver_cpu,sql_instance=QDLP03:SQL2017 other_process_cpu=8i,sqlserver_process_cpu=12i,system_idle_cpu=80i 

Desired Output

sqlserver_requests,database_name=AdventureWorks2016,sql_instance=QDLP03:SQL2017,query_hash=aaa Somefield=10
sqlserver_requests,database_name=AdventureWorks2016,sql_instance=QDLP03:SQL2017,query_hash=bbb Somefield=2
sqlserver_cpu,sql_instance=QDLP03:SQL2017 other_process_cpu=8i,sqlserver_process_cpu=12i,system_idle_cpu=80i 

note that only a subset of points from the sqlserver_requests measurements has been filterd

Is it possible to do this by combining namepass and tagpass without splitting the input configuration?

current “solution”

As of now, the only solution I have is to create 2 separated input configuration, one with all the metric except the one that I want to filter and another one in which namepass and *tagpass are applied (or… since it just fetch one measurement, just tagpass is enough)

## fetch all the metrics that do NOT need filtering
[[[inputs.sqlserver]]

  {...plugin config...}
  ## do NOT include "SqlRequests"
  include_query = ['VolumeSpace','QueryStats','Cpu']


[[[inputs.sqlserver]]

  {...plugin config...}
  ##include only queries to be filterd
  include_query = ['SqlRequests']
  
  ## Filter measurement name
  namepass = ['sqlserver_requests']
  ##apply additional filter on tag
  [inputs.sqlserver.tagpass]
    database_name = ['AdventureWorks2016']

Question

1. Is it possible to obtain the same result without splitting the input configuration in two?
2. Can namepass and tagpass be combined to filter only a subset of points?

[Anaisdg]

Hello @Giovanni_Luisotto,
Yes they can be combined.
Why do you need to join the two configurations?
Have you see this documentation yet? It could be useful if you haven’t.

influxdata/telegraf/blob/master/docs/CONFIGURATION.md#metric-filtering

However please note due to the way TOML is parsed, tagpass and tagdrop parameters must be defined at the end of the plugin definition, otherwise subsequent plugin config options will be interpreted as part of the tagpass/tagdrop tables.

[Giovanni_Luisotto]

I’d like to merge them to avoid excessive duplication.

In fact, every time I split the configuration, the input is actually duplicated and some filters added to fetch just a subset of data.

So far, looks like that’s not possible, butt that’s not an issue

[mhall119]

Are you trying to get all metrics from VolumeSpace,QueryStats, and Cpu, but only AdventureWorks2016 metrics from SqlRequests?

[Giovanni_Luisotto]

Exactly, and I also want to do so in a single configuration of the sqlserver input plugin (or any plugin, the question itself can be applied to any input)

[mhall119]

Yeah, I don’t think that’s possible with the set of plugins currently available, it looks like your setup is the best solution available

[mhall119]

Your only other option might be to use two output plugins and do your filtering there instead

[ssoroka]

This is possible using processors, as long as you can define a set of selectors to pick the records you want to drop. Note also that removing all the fields from a metric will drop the entire metric, and you can use this to your advantage:

[[inputs.sqlserver]]

  {...plugin config...}

[[processors.override]] # placeholder for tag directives
  order = 1 # order is important for chaining processors

  # in processors selectors affect whether the processor considers the metric, 
  # not whether it drops or keeps the metric.
  
  # only consider this metric name for this rule
  namepass = ["sql_server_requests"] 
  
  fielddrop = ["*"] # remove all fields to drop the metric

  # tagpass must be last
  [processors.override.tagpass] 
    # consider only metrics that match this tag rule
    database_name = "Northwind"

[Giovanni_Luisotto]

Sadly it’s not working.

If I use just the following, all the metrics of the specified measurement are dropped

[[processors.override]]
  order = 1 
  namepass = ["sqlserver_query_stats"] 
  
  fielddrop = ["*"] # remove all fields to drop the metric

But when the tagpass (or tagdrop) section is added no filter is applied and all the points are sent to the output.

[[processors.override]]
  order = 1 
  namepass = ["sqlserver_query_stats"] 
  
  fielddrop = ["*"]

  [processors.override.tagpass]
    database_name = ["AdventureWorks2016"]

[ssoroka]

hmm. It should work. Tagpass should work as a selector to make sure the metric is considered (considered meaning the fielddrop modifier is applied to the metric) if it matches that rule. Tagdrop would be the opposite. if the tagdrop matches, this fielddrop will not be applied to the metric (and it will continue on unedited). Here’s a link to the list of selectors and modifiers if you need it.

If I change namepass to namepass = ["sqlserver_requests"], it works with your example input above. Maybe it’s just a small naming thing.

[Giovanni_Luisotto]

Thanks, it was just a mistake in the configuration. (I was about to try some fancy stuff using the glob pattern strings but the processor is way easier to use, maintain and explain)

For anyone having similar requirements, here is a summary with practical examples:

Template

[[processors.override]]
  order = 1 
  namepass = ["__MeasurementName__"] 
  
  fielddrop = ["*"]

  ##[processors.override.tagpass] or [processors.override.tagdrop]
  [processors.override.tagdrop]  
    __TagKey__= ["__TagValue__"]

1. The processor will consider only a specific measurement(s) - see namepass
2. fielddrop will delete all the fields, thus deleting the points
3. this process will be applied only on metrics matching tagpass or tagdrop
   3.1 tagpass - Matched values will be dropped (will be included in the processor)
   3.2 tagdrop - Matched values will NOT be dropped (will be excluded from the processor)

• Use tagpass when you know what you want to remove
• Use tagdrop when you know what you want to keep

Sample

This config filters the measurement “sqlserver_database_io” and keeps only the points in which the tag “database_name” has value “AdventureWorks2016”

[[processors.override]]
  order = 1 
  namepass = ["sqlserver_database_io"] 
  
  fielddrop = ["*"]

  [processors.override.tagdrop]
    database_name = ["AdventureWorks2016"]

Thanks @ssoroka