Query Multiple Fields

[ThePeltonian]

View in #flux on Slack

@stuart_pelton: Hello all,

Currently monitoring Windows services across a lot of servers some of the servers have the same Windows service but its startup mode is disabled so not running - so when setting up alerting it alerts on the Windows service that is disabled - I have tried to say in the query below basically not where the startup mode = 4 (disabled)

Has anyone got an example I can use?

Thanks in advance

from(bucket: "telegraf")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "win_services")
  |> filter(fn: (r) => r["service_name"] == "PROCESS FRAMEWORK")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_field"] == "startup_mode" and r["_value"] != 4)
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> yield(name: "last")

@Etienne_Crinier: In your third filter you filter the field state so in your 4th filter you haven’t the field startup_mode i think this is the problem

@stuart_pelton: Hi thanks for the response, I have tried to swap them over, but I have also noticed that the query is also wrong…

What i want is to just return the “State” (ie running or stopped) Windows services that are not in disabled startup_mode

Am kind of lost how to do this
If this was in SQL it would be i suppose like:

Select state
From telegraf
Where service_name = PROCESS FRAMEWORK
and startup_mode != 4
Any takers for the above? any guidance on this would be appreciated

[Anaisdg]

Hello @ThePeltonian,
This worked for me.

import "influxdata/influxdb/schema"
from(bucket: "noaa")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "h2o_feet" or r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees" or r["_field"] == "water_level")
  |> filter(fn: (r) => r["location"] == "coyote_creek")
  |> group()
  |> schema.fieldsAsCols()
  |> filter(fn: (r) => r.water_level != 7.5)
  |> yield(name: "mean")

Using the noaa sample dataset

Sample data | InfluxDB Cloud Documentation

please let me know if it works for you.

[ThePeltonian]

@Anaisdg

|> schema.fieldsAsCols()

Hi @Anaisdg

Thankyou for the reply, gave me something else to go at, I kept getting errors when trying the above but I have kinda got it to work and pull back nearly how I want it but with a few issues

from(bucket: "telegraf") |> range(start: v.timeRangeStart, stop: v.timeRangeStop) |> filter(fn: (r) => r["_measurement"] == "win_services") |> filter(fn: (r) => r["_field"] == "state" or r["_field"] == "startup_mode") |> filter(fn: (r) => r["service_name"] == "PROCESS FRAMEWORK") |> filter(fn: (r) => r["host"] =~ "Server1") |> pivot( rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value" ) |> filter(fn: (r) => r.startup_mode != 4) |> drop(columns: ["startup_mode"]) |> yield(name: "last")

When the results come back I only have state = 4 showing on the legend, how can i get it to show the full details?

[Anaisdg]

@ThePeltonian,
Hmm I’m not sure what are the full details you are expecting? Why did you end up dropping the startup_mode?

[ThePeltonian]

Hi @Anaisdg,

The result I want to show in the results table & legend is state to show that it is in state 4 (running) as will be alerting on if this drops below 4 as this will be not running - the issue we have is that sometimes there are services installed and in stopped state - but the startup_mode = 4 (disabled).

Before adding the pivot the results would pull back the full data for state including service name, host etc.