Host Case Sensitive

InfluxDB 2
1

Hello,

I have setup Grafana Dashboard that pulls hosts from InfluxDBV2 with a custom list of windows hosts within the variables, my problem is that the HOSTS can be either uppercase or lowercase so when setting the variable I have to put in both Upper and Lowercase versions - this is a bit messy as on the dropdown it shows both and 1 does not work, how can i get around this?

The custom list is

server1,server2,server3 etc

I cant see where i can change this to be case insensitive

piped query for data

from(bucket: “bucket”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “sql_cpu”)
|> filter(fn: (r) => r[“host”] =~ /${comp:pipe}/)
|> filter(fn: (r) => r["_field"] == “Percent_Processor_Time” or r["_field"] == “Percent_User_Time” or r["_field"] == “Percent_Privileged_Time”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Has anyone got any ideas to get around this issue?

2

I’m not familiar with InfluxDB 2.0 however if I was to do this with Telegraf and InfluxDB 1.x then I’d use the string processor for Telegraf.

Strings

We have pretty much same use case regarding the naming. A lot of the systems we monitor have been confiigured by other people who have used different naming conventions throughout.

We force all tags in to uppercase to be honest to make it easier to manage.

3

@ThePeltonian You can just force the correct case in your query using the strings package. This is a lot easier if the correct case is lower. You’ll also need to use the regexp to convert the forced-lowercase host value into a usable regexp type:

import "strings"
import "regexp"

hostValue = strings.toLower(v: "${comp:pipe}")
hostRegex = regexp.compile(v: hostValue)

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sql_cpu")
  |> filter(fn: (r) => r["host"] =~ hostRegex)
  |> filter(fn: (r) => r["_field"] == "Percent_Processor_Time" or r["_field"] == "Percent_User_Time" or r["_field"] == "Percent_Privileged_Time")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
4

Hi Scott thanks for that, when implement the above it does not work - on the selection I choose an uppercase host nothing comes back but when changing the hostValue = strings.toLower(v: “${comp:pipe}”) to hostValue = strings.toUpper(v: “${comp:pipe}”) it pulls back the uppercase selected host metrics

I think the issue is that the “Hosts” are in InfluxDB as mixed case some lower some upper so unable to predict which.

import “influxdata/influxdb/schema”
schema.tagValues(bucket: “mssql”, tag: “host”)

This is one of the queries to pull back host tags - this is used on certain dashboards

But mainly I use similar to the one posted - @philb do you have an example?

Would it be such as

Convert a tag value to uppercase

[[processors.strings.uppercase]]
tag = “host”

5

Have just tried the above within the telegraf.conf - failed start, I tried within the Global / Agent tags and within the outputs and inputs sections all failed to start.

6

I’m not sure about the Telegraf issue, but in the Flux filter, you can force lower case on both operands of the predicate expression:

// ...
  |> filter(fn: (r) => strings.toLower(v: r["host"]) =~ hostRegex)
7

@ThePeltonian I just learned something new in Flux today. You can add flags to a regular expression to make it case-insensitive. The only catch is that you can only do it if you’re using the regexp package (for now at least).

Try this:

import "strings"
import "regexp"

hostRegex = regexp.compile(v: "($i)${comp:pipe}")

from(bucket: "bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sql_cpu")
  |> filter(fn: (r) => r["host"] =~ hostRegex)
  |> filter(fn: (r) => r["_field"] == "Percent_Processor_Time" or r["_field"] == "Percent_User_Time" or r["_field"] == "Percent_Privileged_Time")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")
8

Hi @scott, sadly cant seem to get that to work within Grafana :frowning: :sweat:

I think i am in need of sorting this out within the InfluxDB itself to sort this out sadly.

I have a few issues with the SQLServer side of things about settings etc but the above is a main one;

9

Thats close, but it should be

[[processors.strings]]
  ## Convert a tag value to uppercase
   [[processors.strings.uppercase]]
     tag = "host"

Also, I’m not sure if its the formatting on the forums or not but the double quotes don’t seem right.

[[processors.strings.uppercase]]
tag = “host”

try with “host” instead.

I place that at the very end of my main configuration file with all the connection details in and its applied to all my metrics, however I force all tags to uppercase so i just use the wildcard

[[processors.strings]]
  ## Convert a tag value to uppercase
   [[processors.strings.uppercase]]
     tag = "*"

As for Grafana, maybe that regex function works in influx but not Grafana. I don’t know about influx 2.0 though so its just a vage guess.

EDIT: Actually, i think you might have included that full processor block. I just saved this post and the first section of the definition is formatted in bold text.

It might be better to wrap config snippets in the code formatting

image
image1475×281 20.6 KB

10

Perfect!!!

used that at the end then its started to add them in as uppercase - thankyou so much

Just need to sort the sql inputs now :slight_smile:

11

Awesome, glad its sorted :slight_smile:

Regarding SQL inputs, are you collecting performance data from SQL server or are you wanting to query a SQL source and write it to Infux?

12

collecting from sql server,

I have a couple of servers that have old sql 2005 database’ that have been upgraded to 2012 (i think its 2012) but telegraf starts but then stops when it gets to collecting that data.

I can add in the base configuration but not the Inputs.sql

I was wondering about using the database_type = sql_server but cant find any examples of telegraf configs with it in.

13

Sorry for the delay.

It should be simple enough to set up. does telegraf output any errors when connecting?

If i recall, you just need to create a user with the correct permissions and set up the connection string in the config. I can’t remember if I had to make any firewall changes or network settings in the SQL server instance itself.

14

Hi @philb sorry been away from work, no errors the config is the same as the ones on the other servers - firewall is open etc as I can add in the basic Telegraf config without the SQL additions and it worked.

I have read that Telegraf has issues with older versions of SQL so was wondering if the upgraded db;s had some remnants left in there somewhere that causes Telegraf to stop