How to simply sum on a per ip basis apache logs

Grafana Loki
1

I’ve been trying to simply compile data for instance number of requests per ip from basic apache logs.

This is the format of the apache logs:

198.20.000.000 - - [19/Feb/2021:03:08:01 +0000] "GET /favicon.ico HTTP/1.1" 200 15086 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:80.0) Gecko/20100101 Firefox/80.0"
104.187.000.000 - - [19/Feb/2021:03:08:09 +0000] "POST /user/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0 HTTP/1.1" 200 11453 "https://www.xxx-app.com/user-activity/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36 Edg/88.0.705.68"
104.187.000.000 - - [19/Feb/2021:03:08:32 +0000] "POST /user/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0 HTTP/1.1" 200 11451 "https://www.xxx-app.com/user-activity/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36 Edg/88.0.705.68"
104.187.000.000 - - [19/Feb/2021:03:08:54 +0000] "POST /user/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0 HTTP/1.1" 200 11404 "https://www.xxx-app.com/user-activity/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36 Edg/88.0.705.68"
104.187.000.000 - - [19/Feb/2021:03:09:19 +0000] "POST /user/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0 HTTP/1.1" 200 11393 "https://www.xxx-app.com/user-activity/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36 Edg/88.0.705.68"
104.187.000.000 - - [19/Feb/2021:03:09:40 +0000] "POST /user/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0 HTTP/1.1" 200 11436 "https://www.xxx-app.com/user-activity/run?hash=8ccb60702d04ddb3a0e1f6685a571fb0" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36 Edg/88.0.705.68"
66.249.000.000 - - [19/Feb/2021:03:16:22 +0000] "GET / HTTP/1.1" 302 - "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"
66.249.000.000 - - [19/Feb/2021:03:16:23 +0000] "GET /login HTTP/1.1" 200 6150 "-" "Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)"

Then I have the following query:

sum by(ipps) (count_over_time({job="varlogs"} | regexp `(?P<ipps>\S+) (?P<identd>\S+) (?P<user>\S+) \[(?P<timestamp>[\w:\/]+\s[+\\-]\d{4})\] "(?P<action>\S+)\s?(?P<path>\S+)?\s?(?P<protocol>\S+)?" (?P<status>\d{3}|-) (?P<size>\d+|-)\s?"?(?P<referer>[^\"]*)"?\s?"?(?P<useragent>[^\"]*)?"?` [$__interval]))

The results is the following:

2023-03-03 15:24:20              3

However this does not display the ips and their total

Is there a way with loki to simply display a table such as:

ip address      number of requests
2

Looks like it shoud be working. I did verify your regex looks good, but just curious if you remove the aggregation does the log parse correctly?

If you are running this in Grafana a screenshot would be nice.