Create a table of top 5 IPs and sort count by descending order

  • What Grafana version and what operating system are you using? Grafana V11.4 on Ubuntu

  • What are you trying to achieve?

I am trying to build a dashboard that monitors Crowdsec kernel log. Idea is to make a table that
lists top 3 IP’s (by aggregation) that tried to access any port on my server.

The log format is :

2025-01-04T00:42:38.638415-05:00 peronalserver kernel: crowdsec drop: IN=dni6p3 OUT=docker0 MAC=<mac id> SRC=127.0.0.1 DST=127.0.0.2 LEN=44 TOS=0x00 PREC=0x00 TTL=238 ID=00000 PROTO=TCP SPT=00000 DPT=80 WINDOW=00000 RES=0x00 SYN URGP=0 

Example:

IP = Count

1.1.1.1 = 32

2.2.2.2 = 12

3.3.3.3 = 5

  • How are you trying to achieve it?
    Using Promtail to read Kern.log and running this query in Panel Add

    sum by (ip) (

  count_over_time({service_name="system_logs"} 
  |= "crowdsec drop" 
  | pattern `<_>SRC=<ip> <_>` 
  | __error__=""
  [$__range])
)
  • What happened?

Its creating a table thats is displaying data with timestamps for each count of the selected IP, but it’s not aggregating all IPs or limiting the display to the top 3…

Output Table:

Time = 127.0.0.1 (column header)

2025-01-04 01:20:00 = 1

2025-01-04 02:40:00 = 1

2025-01-04 03:20:00 = 1

2025-01-04 04:00:00 = 1

<Drop down list to select other IP’s>

  • What did you expect to happen?

a table that
lists top 3 IP’s (by aggregation) that tried to access any port on my server.

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

None

Can someone please help me figure this out? Many thanks!

Welcome to forum @humbledasher007

What if you wrapped all that with

topk(5

This is the new query I am using :

topk(5
sum by(ip) (
  count_over_time({service_name="system_logs"} 
  |= "crowdsec drop" 
  | regexp `SRC=(?P<ip>[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})` [$__range])
) > 0
)

Now there is an error: parse error at line 2, col 1: syntax error: unexpected SUM

Look up at the documentation for that function proper use syntax

This doesn’t look like PromQL (probably why topk didn’t work), what language and datasource are you using?