Select last active by node

Hi guys,

Sorry for my powerless english.

I stored data below, 2 nodes by backend, active is state (fields), stored data every 10m
i tried to count number of active node by back for last time group by time(10m) and by back name

time                active app back   node  slot
----                ------ --- ----   ----  ----
1498719600000000000 1      ABC abc_s1 node1 s1
1498719601000000000 1      ABC abc_s1 node2 s1
1498719602000000000 1      DEF def_s1 node1 s1
1498719603000000000 1      DEF def_s1 node2 s1
1498719604000000000 1      GHI ghi_s1 node1 s1
1498719605000000000 1      GHI ghi_s1 node2 s1
1498719606000000000 1      JKL jkl_s1 node1 s1
1498719607000000000 1      JKL jkl_s1 node2 s1
1498719608000000000 1      MNO mno_s1 node1 s1
1498719609000000000 1      MNO mno_s1 node2 s1
1498719610000000000 1      PQR pqr_s1 node1 s1
1498719611000000000 1      PQR pqr_s1 node2 s1
1498719612000000000 1      STU stu_s1 node1 s1
1498719613000000000 1      STU stu_s1 node2 s1
1498719614000000000 1      VWX vwx_s1 node1 s1
1498719615000000000 1      VWX vwx_s1 node2 s1
1498719616000000000 1      XYA xya_s1 node1 s1
1498719617000000000 1      XYA xya_s1 node2 s1
1498720217000000000 1      ABC abc_s1 node1 s1
1498720218000000000 1      XYA xya_s1 node2 s1
1498720218000000000 1      XYA xya_s1 node1 s1
1498720218000000000 1      PQR pqr_s1 node2 s1
1498720218000000000 1      PQR pqr_s1 node1 s1

I want result like this :
abc_s1 : 2
xya_s1 : 2

i tried sum count and last function without good result

For information, in grafana, i want display singlestat like this
BACK name
Number of active
Number of request

With Green if active = 2, orange if active = 1, red if active = 0

Please help me :slight_smile: perhaps my method is wrong

best regards

I’d think last("active") should work. You may want to post the query that doesn’t work and what error you get.

This is a working uptime singlestat for me:

Is “back” a tag?

1 Like

Yes back is a tag, backend of my haproxy
i found almost query but time is 1970 :frowning:
select mean(time), sum(active) from (select time, back, last(active) as active from alive group by back,node) group by back, active

alive
active:, back:abc_s1

time sum
1970-01-01T00:00:00Z 2

1 Like

@dosy07 I think you need a group by time in there?