Select COUNT question

Good afternoon,

I am trying to graph some new metrics. The data looks like this:

show measurements
name: measurements


show series from messages where (“host” =~ /myhost/)

SELECT count(“message”) FROM “messages” WHERE (“host” =~ /myhost/ AND “type” =~ /service_notification/) AND time > now() -8h
name: messages
time count

1522243635297572386 3

Returns 3 rows.

If I look into it a bit deeper I see the 3 messages.

SELECT distinct(“message”) FROM “messages” WHERE (“host” =~ /myhost/ AND “type” =~ /service_notification/) AND time > now() -8h
name: messages
time distinct

1522243679978667319 CRITICAL:
CRIT - 71 processes (ok from 1 to 60)(!!) 37342.1 MB virtual, 5331.7 MB physical, 198.3% CPU, youngest running for 112 sec, oldest running for 18 days
1522243679978667319 WARNING:
WARN - 67 processes (ok from 1 to 60)(!) 35202.5 MB virtual, 4952.9 MB physical, 160.4% CPU, youngest running for 172 sec, oldest running for 18 days
1522243679978667319 OK:
OK - 39 processes 20074.9 MB virtual, 2538.6 MB physical, 41.7% CPU, youngest running for 232 sec, oldest running for 18 days

Here is my ask:
How can I do a select count to only calculate results that are WARNING or CRITICAL? I don’t care about the OK notifications. I just want to trend the number of WARNING and CRITICAL notifications for a given host, in this case, ‘myhost’ and the result would be 2, not 3.

It looks like you’re storing logs as string values inside a field. Field values cannot be used in a query.

The best approach would be to use the logparser plugin to extract data from those logs before sending it to InfluxDB. “CRITICAL”, “WARNIN”, and “OK” could be tags, which would allow you to use them in a query.

It would be helpful if your logs were in a structured format, as this makes them easier to parse, however the “logparser” plugin supports grok filters and regex as well. It seems like you have other metrics in those strings as well; it might make sense to parse them into separate measurements.