Display counts of values that occur n times in Table?

[eggy]

Let’s say I have this query in loki

{tenant_id="tenantId"} 
|= `ERROR` 
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
| pattern `<_> name=<name> `
| label_format name=`{{ .name }}` 

I want to have the data presented in a Table. When I Transform the data, I can do a Group By (date) and I can go to Calculate → All Values and then I can see all Values i.e.

[ "2024-05-14", "2024-05-14", "2024-05-14", "2024-05-13"]

Now I want to have a count, where if the same value occurs 3 times, increment it by one. so for the example above, the count will 1 one

For the example below the count will be 2

[ "2024-05-14", "2024-05-14", "2024-05-14", "2024-05-13", "2024-05-12", "2024-05-12", "2024-05-12"]

I am struggling to achieve this, can someone please help me

[joachimschiewek]

Hi eggy,

what ever you do with loki,
after that you have to transform the data.

first - “Extract fields” by labels
then - “Filter fields by name”
then - “Convert the field type” because from loki you get only text

then you can give us an exacly example whats in the table view.

Jo

[eggy]

group by

So for instance, I’d like another column (let’s say called fail count), that contains the logic "if a date (string, even) is repeated 3 times, increment the count by 1)

so

["2024-05-15","2024-05-15","2024-05-15"]

would produce 1.

but

["2024-05-15"]

would produce 0 .

however

["2024-05-15","2024-05-15","2024-05-15", "2024-05-14","2024-05-14","2024-05-14"]

would produce 2 and so on

[joachimschiewek]

It confuses me a little bit that it is always the same date.
How am I supposed to assign a count?

I am searching for the key…

[eggy]

This is the raw query

{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`

date is a label_format

[yosiasz]

check this out here for some grouping syntax

Cheat Sheet - Loki - Seb's IT blog

try to do your aggregation in the data source and leave the visualization to grafana is my motto

[eggy]

If i aggregate the data with Loki as such

 count by (date, websiteId) (
        count_over_time(    
{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
            [$__range]
        )
    ) > 2

I am unable to see the data in Transform when I try to extract the values

[yosiasz]

well you might not have any data > 2

what do you see when you do

count by (date, websiteId) (
        count_over_time(    
{tenant_id="tenantId", cluster_name="clusterName"}
|= `ERROR`
| label_format date=`{{ __timestamp__ | date "2006-01-02"}}`
            [$__range]
        )
    )

show us the data view

[eggy]

There are matches but the ‘label {}’ are not coming up in the ‘source’ when i extract fields.

If i exclude the ‘count by’ and the ‘count_over_time’ I am able to see the ‘label {}’ in the Source

[eggy]

Ok i managed to do it.

I was displaying the values as part of a range Type instead of an instant .

I also added all the values I wanted to see in the table to the count by (date, websiteId) so I could see it in the table.

With regards to the count I done a transformation, in the group by i set the failures Calculate as Count.

Thanks all