Display counts of values that occur n times in Table?

Let’s say I have this query in loki

|= `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

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.


Screenshot from 2024-05-15 13-39-48
group by
Screenshot from 2024-05-15 13-43-07

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)



would produce 1.



would produce 0 .


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

would produce 2 and so on

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

I am searching for the key…

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

check this out here for some grouping syntax

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

If i aggregate the data with Loki as such

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

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

well you might not have any data > 2

what do you see when you do

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

show us the data view


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

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

1 Like