How to use PromQL operators to limit/filter query values?

Grafana Prometheus
1

I have HTTP request logs with client IP addresses and I have a panel with the following query from Loki’s Prometheus data source (http://localhost:3100/loki):

topk(5, sum by (client_ip)(rate({host="webserver.local",job="nginx_access",client_ip=~".+"}[1m])))

This query shows a time series graph with the top 5 Client IP addresses with the most requests/minute.

Within this data there are some lower values that I want to exclude. For example: some IP addresses have 1 request/minute values.

How do can I use PromQL operators to filter the query values to only include the IP adresses that have greater than 5 requests/minute ?

2

Hello johhny! In PromQL you can use the basic comparison operators. Therefore just using > 5 should solve your problem. Your query would look like this: topk(5, sum by (client_ip (rate({host="webserver.local",job="nginx_access",client_ip=~".+"}[1m]))) > 5 I have recreated similar problem on grafana.play.

Here is the query without comparison operator:

image
image2880×1656 443 KB

And here is with the comparison operator:

image
image2874×1570 354 KB

2 Likes
3

@ivanahuckova, Thank You very much for the https://play.grafana.org tip, I did not know it existed!

As for the PromQL, with the help of Grafana Slack members I have recently learned that Loki’s Prometheus endpoint (localhost:3100/loki) uses LogQL, not PromQL.

LogQL currently does not support the comparison operators, but there are plans to implement it in the future (https://github.com/grafana/loki/pull/1848).

4

@johhny thank you so much for sharing your findings! Also, I am very glad that you were able to solve your problems at trough our community Slack. :slightly_smiling_face:

5

@ ivanahuckova, I need to filter text results for a query
sql_pc_Databases_WriteTransactions_over_sec
It returns 20 Databases in the resultset as follows :slight_smile: and need to exclude half of them, how can this be done? Thanks
something like
counter_instance <> "admin, AdventureWorks2012"
or
counter_instance NOT IN "admin, AdventureWorks2012"

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“Admin”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“Admin”, exported_instance=“SQL2K12\SC2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AdventureWorks2012”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AdventureWorks2016CTP3”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AdventureWorksDW2012”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AdventureWorksDemo”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AdventureWorksLT2012”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AppMng_Service_DB_ad70559509ad4123823dc1827f8bf2cc”, exported_instance=“SQL2K12\SC2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“AppServiceDB”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“BAMArchive”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“BAMPrimaryImport”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“BAMStarSchema”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

sql_pc_Databases_WriteTransactions_over_sec{alias=“sqlserverOn2K12”, counter_instance=“BTS13AlertsApplication”, exported_instance=“SQL2K12”, instance=“salam7:9966”, job=“sql_servers”}

1 Like
6

Did you find a solution?

7

No, nobody responded