Loki group by label value

[thyseus]

Hi there,

i want to aggregate by label value over the following demo input:

2023-01-01 A: foo B: bar C: TWO
2023-01-02 A: foo B: bar C: TWO
2023-01-03 A: foo B: bar C: ONE

becoming a table that count the occurences of C: like:

TWO: 2
ONE: 1

Is this possible with promQL or do i need to parse the data via a custom script ?

[tonyswumac]

You can use pattern for this (assuming the log stays in the same format):

pattern "<_> <_>: <a_value> <_>: <b_value> <_>: <c_value>" | line_format "{{.c_value}}"

This will get you just the last value, you can then wrap around this with a metrics function like count_over_time.

Link for analyzer: LogQL Analyzer | Grafana Loki documentation

[thyseus]

Thanks a lot for that quick answer !

Actually the pattern is the part i already managed to do by using regexp, not pattern in promtail:

expression: '^(?P<timestamp>\S+).*GEO.*(?P<browser>(Chrome|Firefox)+).*C: (?P<countryExpected>\S+).* I: (?P<ip>\S+).* L: (?P<countryActual>\S+).* S: (?P<city>\S+) V: (?P<geoService>\S+).*$'

The label is scraped properly out of the log lines.

Now, the count_over_time() part is the part i do not understand.
Could you please give an example how i can GROUP BY a label value (thats sql language...) ?

[tonyswumac]

Using your example, it would be something like this (not tested):

sum by (city) (
  count_over_time(
    {<SELECTOR>}
      | expression: '^(?P<timestamp>\S+).*GEO.*(?P<browser>(Chrome|Firefox)+).*C: (?P<countryExpected>\S+).* I: (?P<ip>\S+).* L: (?P<countryActual>\S+).* S: (?P<city>\S+) V: (?P<geoService>\S+).*$' 
    [5m]
  )
)

Basically, count_over_time returns a set of metrics for all labels from your expression, and sum aggregates by a label provided.

[thyseus]

Awesome! Will test that in office tomorrow and will report. Thanks a lot. Would have taken days for me to figure that out

[thyseus]

Works perfect. Since my log lines are already put into “labels” by promtail, my final (grafana) code is even smaller:

sum by (geoService) (
  count_over_time(
    {context="vpn"}[$__range]
  )
)

Thanks again, hopefully this helps other people, too !