Find missing elements in database with repeating cycle

I have in my database a series of file records with metadata on the file’s cycle and the counter for that cycle. Something like the table below:

Filename Cycle Count in Cycle
file1 A 1
file2 A 2
file3 A 3
file4 A 4
file5 B 1
file6 B 2
file7 B 3
file8 B 4
file9 C 1
file10 C 2
file11 C 3
file12 C 4

Essentially I am monitoring new files being produced by the system, and adding them to the database with the appropriate metadata (cycle and count _in_cycle). Files are produced in cycles and each cycle has a certain amount of files.

One of the things that I need to do is make sure that no files are missing in each cycle.
Is it possible to detect, for example, that cycle ‘B’ is missing the file with count 2?

Thank you.

Hello @mvaladas,
You could group by cycle, and then sum the count in cycle and make an alert if the count is less than 10 for example in the case above. That’s pretty much about it.

from(bucket: "your_bucket_name")
  |> range(start: -1h)  // Adjust the time range as needed
  |> filter(fn: (r) => r._measurement == "your_measurement_name")
  |> group(columns: ["cycle"])
  |> sum(column: "_value")
  |> yield(name: "summed_values")

Thank you. I will it.

1 Like