Difference between counts

1

Hello,

I tried to use difference() to find the offset between current & previous counts but seems not working properly.

from(bucket: "Dialout")
  |> range(start: -30s, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> difference(nonNegative: false, columns: ["_value"])

Can someone give me a hint?

Regards,
Minh

2

Welcome @mdang

Can you clarify what you mean by “previous count”? The count of your value 30 seconds ago?

I presume that “current count” means the count of the most recent values in the database?

3

Thanks for looking into this.

Correct. My goal is to get the diff between the most recent count vs 30s ago in the database.

4

I think a union will be needed. Something like this…

FirstPeriod1 = from(bucket: "Dialout")
  |> range(start: -31s, stop: now())
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> set(key: "newValue", value: "first_value_of_period1")
  |> yield(name: "first_period1")

 LastPeriod1 = from(bucket: "Dialout")
  |> range(start: -60s, stop: -30s)
  |> filter(fn: (r) => r["_measurement"] == "show interface status")
  |> filter(fn: (r) => r["source"] == "device")
  |> filter(fn: (r) => r["_field"] == "state")
  |> filter(fn: (r) => r["_value"] == "connected")
  |> last()  
  |> unique(column: "TABLE_interface")  
  |> group(columns: ["host", "_measurement"], mode:"by")  
  |> count()
  |> set(key: "newValue", value: "last_value_of__period1")
  |> yield(name: "last_period1")

Period1_Union = union(tables: [FirstPeriod1, LastPeriod1])
  |> pivot(rowKey:["_time"], columnKey: ["newValue"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with period1_difference: r.last_value_of_period1 - r.first_value_of_period1 }))    
  |> yield(name: "Period1_Union")