Cannot distinct data in Grafana Loki

[elabkevin]

hi,

I want to find no of unique mbid in true_client_ip

My LogQl
count without (mbid) (sum by (mbid, true_client_ip) (count_over_time({true_client_ip=“1.64.33.76”}[10m])))

Result is incorrect

Log data

My expected result should be

How to do ? please help. Thanks

[tonyswumac]

Your first screenshot seems to show correct results.

Try removing the without, and see if the mbid is indeed identical.

[elabkevin]

Thanks for your reply.

but if I removed the “without” then it shows syntax error, could you please advice?

Thanks

[tonyswumac]

It would be:

count (
  sum by (mbid, true_client_ip) (
    count_over_time({true_client_ip="1.64.33.76"} [10m])
  )
)

[elabkevin]

Thanks for your reply.

I tried, but cannot get the correct result, below is the detail

Applied the LogQl, but 2 identical record (same mbid and true_client_ip) shown in the chart

here is the log the Logql retrieved

I want it can show like this

Please help. Thanks

[tonyswumac]

As I mentioned above, you already have what you want. You just need to transform your data from a time series into a 1-data-point table. The way you do this is by creating a table panel in your dashboard, and set the following:

1. Change Max data points to 1.
2. Set query type to Instant.
3. Add Organize fields transformation and hide the timestamp.

I don’t have your logs, but I will try to provide an example below using similar data. Hopefully it’s not confusing. You are looking for number of unique mbid for each client ip, and let’s say I am doing something similar and looking for number of unique containers in each AWS region. I have this query here:

count by (aws_region) (
  sum by (aws_region, container_id) (
    count_over_time({container_id=~".+"}[$__auto])
  )
)

And this produces following graph:

Now, I’ll create a new panel, but as a table, with the following changes:

This then presents the data in a table view:

[elabkevin]

Thanks for you reply and help.

I applied the setting same as you provided, but I got below 2 different results with different interval setting. I checked the official document but still cannot find the problem.

I set 10m as interval then I got 3, and do you know why the “Alert” tab page is gone?

I set 100m as interval then I got 1

here is the related log record, retrieved by executing the same query in “Explore” page. the log are created in same minute (time is highlighted below)

Could you please advice? Many Thanks

[tonyswumac]

1. Because you are setting the interval implicitly already via setting the number of data point to 1, you have to use $__auto in your query.

2. It’s better to use time series for alerting. We don’t generate alert from Grafana, we use Loki Ruler, so I am probably not going to be able to comment on this too much.

[elabkevin]

Thanks for your reply.

I use $__interval instead because $__auto is not recognized by Grafana Loki, but the result is still not correct, below is the setting

Here is the related Log record, mbid are blank, then I expect they will grouped together and counted as 1

and also I want to set an alert, so there is any LogQl and alert can achieve it in Grafana Loki.

Thanks