How to display systems that are offline based on Loki log queries in Grafana?

Question

"I'm using Loki to store logs and Grafana for visualization. I want to create a Grafana table that lists all systems that are considered offline. A system is considered offline if it has sent a "Timestamp" log in the mrs_error_list job in the past 7 days but not in the last minute. I am able to calculate the count of such systems using Loki queries but unable to list the actual systems.

I used the following query to count the number of offline systems:

(
  count(count by(system) (count_over_time({job="mrs_error_list"} |~ "Timestamp" [7d])))
)
- 
(
  count(count by(system) (count_over_time({job="mrs_error_list"} |~ "Timestamp" [1m])))
)

However, while this gives me the number of offline systems, I want to create a table that lists out these specific systems. I was thinking of subtracting the results from one query from the other, but I'm unsure how to approach this in Grafana.

Answer 1

You need unless operator for this.

vector1 unless vector2 results in a vector consisting of the elements of vector1 for which there are no elements in vector2 with exactly matching label sets. All matching elements in both vectors are dropped.

For your case:

count by(system) (count_over_time({job="mrs_error_list"} |~ "Timestamp" [7d]))
unless
count by(system) (count_over_time({job="mrs_error_list"} |~ "Timestamp" [1m]))

Here, first operand will return full list of systems that where present over last 7 days, and unless will exclude those, that were present over last one minute.