Filter query results

Hi

We are working on the Grafana queries that pull the data from Influx db. It pulls the data successfully but we are unable to create a dashboard with the query based on the searching for a particular text in the query output.
for eg.

SELECT “message” FROM “three_day”.“logs”

and the “message” results to multiple rows like below in addition to other type of logs
TIME=2016-09-20T18:15:31.187GMT|Level=INFO|USER=XXXX|ID=12345|ACTION="XYZ"
other log information
other log informatin
TIME=2016-09-20T18:15:31.187GMT|Level=INFO|USER=XXXX|ID=12345|ACTION="XYZ"
other log informatin
other log informatin

How can i filter the results only for results contain “TIME” or “ID=12345”?

Thanks.

This sounds like a normal InfluxDB query. Here is an example from the Grafana Play site:

http://play.grafana.org/dashboard/db/influxdb-templated-queries?panelId=1&fullscreen&edit&orgId=1

The Query Inspector shows the real query sent to InfluxDB:

SELECT mean("value") FROM "logins.count" WHERE ("datacenter" =~ /^Asia$/ AND "hostname" =~ /^(server\\5|server\\second\\slash)$/) AND time >= now() - 1h GROUP BY time(1m)

Note that Grafana if not in raw mode, automatically adds a time clause to the query.

1 Like

Hi Daniel

Thanks for the response.

In your example query, for eg. WHERE (“datacenter” =~ /^Asia$/ , the value Asia, America etc. are coming from KEY “datacenter”=Asia or America or Europe or Africa

In my case, the values are part of the message and need to use something like ‘datacenter’ CONTAINS ‘Asia’:
datacenter=The traffic comes from Asia
datacenter=The traffic comes from America
datacenter=The traffic comes from Europe
datacenter=The traffic comes from Africa

And I needs to assign only the Asia, America, Europe, Africa to the 'datacenter" KEY.

Hope you get my case. Thanks.

Hi Daniel, do you have any comments for the above query? Thanks

Not sure I understand. Why doesn’t a normal where clause work for you?

I don’t understand this, you can query fields if that’s what you mean:

Or do you mean you want to do a partial match?

~= is the regex operator and can do partial matches. For example: "datacenter" =~ /^A/ would match anything starting with A.

Or do you mean that you want to do this:

where "datacenter" = 'Asia' OR "datacenter" = 'Africa'

Hi Daniel

thanks for the response. Sorry for not clear in my response.

I agree with you but my use case is different.

Here is the flow:
Application send json data(key/values) to Kafka
Kafka sends the key/values to Influx DB for
eg.
“message”/“This is a test data created by john”
“message”/“This is a test data created by admin”

The “message” is indexed
By default, Grafana displays the “message” values through the query.
eg. select message from logs
The output of the above query is -
“This is a test data created by john”
“This is a test data created by admin”

But I need to filter the query results here. For eg. I just want the only the rows with “john”

Hope I am clear.

thanks for your time.