Flux query help

[ThePeltonian]

Hello, using influxv2 with telegraf to monitor windows servers.
Trying to get my head around how to do alerting based on the provided query.

This is what I have so far…

This is query 1 where it fetches the “state” of the service which when running = 4
from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services”)
|> filter(fn: (r) => r[“_field”] == “state”)
|> filter(fn: (r) => r[“service_name”] == “SplunkForwarder”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Query 2 - this pulls back the startup type of the service = this should be value 2

from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services”)
|> filter(fn: (r) => r[“_field”] == “startup_mode” and r._value == “2”)
|> filter(fn: (r) => r[“service_name”] == “SplunkForwarder”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

The above queries work ok but show both values in the graph and if the alert is triggered it shows the service that is stopped but also ALL the services that have the startup value of 2

Ideally I would like it just to state the Non running service

I tried to use pivot() from the below article with no success as it didnt seem to pull back any data

How do I select multiple field columns with flux?

@scott Is this correct for value-fields (no-tag fields)? I am trying to execute a simple select via Chronograf but it does not seem to be working the way you explained. Assumed I would have inserted data with insert test a=10,b=40 insert test a=20,b=30 insert test a=20,b=40 Now I want to retrieve BOTH a and b variables of the second datapoint. This is very simple in influx: select a,b from test where a>10 AND b=30 But I have absolutely no idea how to acomplish this in flux? At least via…

from(bucket: “telegraf”)

|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services” and r._field =~ “state”|“startup_mode”/ )
|> pivot(rowKey:[“_time”], columnKey: [“_field”], valueColumn: “_value”)
|> filter(fn: (r) => r.state > 1 and r.startup_mode == 2)
|> filter(fn: (r) => r[“service_name”] == “SplunkForwarder”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Can anyone point me in the right direction for this, I feel like i am close … but forever chasing the proverbial cigar…

Any help would be greatly appreciated

[ThePeltonian]

Ok I have done the following:

Query A

from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services” and r[“_field”] == “state”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Query B

from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services”)
|> filter(fn: (r) => r[“_field”] == “startup_mode” and r._value == “2”)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

As I am using Grafana for visualisations and alerting - I sued a Series Override for “Startup_mode” hidden series - this omitted it from the graph and also the alert.

Now to the next tweak - within the alert I receive the following:

[Alerting] Service Alert

Service Down Service is set to automatic but not currently in running state.

	##### Metric name	##### Value
—	—
##### state {display_name=ASP.NET State Service, host=lo1wpcewtweb01, service_name=aspnet_state}	##### 1.000

Where we have the Value of 1.000 is it possible to change that within the query so it shows as “Stopped” ?
I have had a look at " Conditionally transform column values with map()" but with no success, my query is below but it takes a long time to execute and no change on the results

from(bucket: “telegraf”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “win_services” and r[“_field”] == “state”)
|> map(fn: (r) => ({
r with
level:
if r._value == 1 then “stopped”
else if r._value == 2 then “pending”
else “running”
})
)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)

Below is what is shown in the legend

win_services state {display_name=“ASP.NET State Service”, host=“MyServer”, service_name=“aspnet_state”} Current 1.000

If anyone could guide on this please?

[Anaisdg]

Hello @ThePeltonian,
First, congrats on getting such a deep understanding of Flux and giving it a try. Have you tried mapping with 1.0? instead of 1? It might be a type mismatch issue.
This works for me for example:

import "experimental/array"
table1 = array.from(rows: [{_time: 2020-01-01T00:00:00Z, _value: 1.0, _measuremtn: "table1"},{_time: 2020-01-02T00:00:00Z, _value: 2.0 , _measuremtn: "table1"}])
table1  |> map(fn: (r) => ({
    r with
    level:
      if r._value == 1.0 then "critical"
      else "normal"
    })
  )