Get duration of certain field value InfluxDB

1

Hello!

I have this on my database:

time running

2022-09-16 09:00:00 False

2022-09-16 09:05:00 False

2022-09-16 09:10:00 True

2022-09-16 09:40:00 True

2022-09-16 10:00:00 False

2022-09-16 12:00:00 True

2022-09-16 12:10:00 False

I want to get this result:

Duration True: 1h
Duration False: 2h 10 min

1h = 30 min (09:10-09:40) + 20 min (09:40-10:00) + 10 min (12:00-12:10)
2h 10 min = 5 min (09:00-09:05) + 5 min (09:05-09:10) + 120 min (10:00-12:00)

It doesn’t matter if I use Flux or InfluxQL.

I have this csv to extract the number of events of True and False, but I am more interested in the duration of each value:

import "csv"

csvData = "#group,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,string,string,string,string,string,string,string,string,string,string,string

#default,_result,,,,,,,,,,,,,,,,,

,result,table,_start,_stop,_time,_value,_field,_measurement,dest_ip,dest_port,event_type,flow_id,host,in_iface,path,proto,src_ip,src_port

,,0,2022-09-16T09:00:00.00000000Z,2022-09-16T09:00:00.00000000Z,2022-09-16T09:00:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,1437107460025591,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,111.111.111.111,36766

,,0,2022-09-16T09:05:00.00000000Z,2022-09-16T09:05:00.00000000Z,2022-09-16T09:05:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,2166347159271520,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,222.222.222.222,34387

,,0,2022-09-16T09:10:00.00000000Z,2022-09-16T09:10:00.00000000Z,2022-09-16T09:10:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,321927463147576,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,110.110.110.110,11751

,,0,2022-09-16T09:40:00.00000000Z,2022-09-16T09:40:00.00000000Z,2022-09-16T09:40:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,333555236012237,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,100.100.100.100,13488

,,0,2022-09-16T10:00:00.00000000Z,2022-09-16T10:00:00.00000000Z,2022-09-16T10:00:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,334429991280534,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,180.180.180.180,53958

,,0,2022-09-16T12:00:00.00000000Z,2022-09-16T12:00:00.00000000Z,2022-09-16T12:00:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,33606186887911,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,180.180.180.180,45018

,,0,2022-09-16T12:10:00.00000000Z,2022-09-16T12:10:00.00000000Z,2022-09-16T12:10:00.00000000Z,False,PLC,suricata-alerts,123.123.123.123,80,alert,1554565580150037,opnsense.home.arpa,re0,/var/log/suricata/eve.json,TCP,150.150.150.150,511"

csv.from(csv: csvData)

|> group(columns: ["_value"])|> set(key: "Count", value: "my_Count")|> count(column: "Count")```

Can someune help me with the query?

Thank you!

2

Hello @mmmmgggg,
Yes! I’m so sorry for the delay. Sometimes questions fall through the cracks. Next time you dont hear back, please tag me.
Thank you for providing sample input data you rock!
You would do:

import "csv"
csvData = "#group,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE

#datatype,string,long,dateTime:RFC3339,dateTime:RFC3339,dateTime:RFC3339,string,string,string,string,string,string,string,string,string,string,string,string,string

#default,_result,,,,,,,,,,,,,,,,,

,result,table,_start,_stop,_time,_value,_field,_measurement,dest_ip,dest_port,event_type,flow_id,host,in_iface,path,proto,src_ip,src_port

,,0,2022-09-16T09:00:00.00000000Z,2022-09-16T09:00:00.00000000Z,2022-09-16T09:00:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,1437107460025591,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,111.111.111.111,36766

,,0,2022-09-16T09:05:00.00000000Z,2022-09-16T09:05:00.00000000Z,2022-09-16T09:05:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,2166347159271520,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,222.222.222.222,34387

,,0,2022-09-16T09:10:00.00000000Z,2022-09-16T09:10:00.00000000Z,2022-09-16T09:10:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,321927463147576,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,110.110.110.110,11751

,,0,2022-09-16T09:40:00.00000000Z,2022-09-16T09:40:00.00000000Z,2022-09-16T09:40:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,333555236012237,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,100.100.100.100,13488

,,0,2022-09-16T10:00:00.00000000Z,2022-09-16T10:00:00.00000000Z,2022-09-16T10:00:00.00000000Z,False,PLC,suricata-alerts,192.168.0.14,80,alert,334429991280534,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,180.180.180.180,53958

,,0,2022-09-16T12:00:00.00000000Z,2022-09-16T12:00:00.00000000Z,2022-09-16T12:00:00.00000000Z,True,PLC,suricata-alerts,192.168.0.14,80,alert,33606186887911,opnsense.home.arpa,re1_vlan742^,/var/log/suricata/eve.json,TCP,180.180.180.180,45018

,,0,2022-09-16T12:10:00.00000000Z,2022-09-16T12:10:00.00000000Z,2022-09-16T12:10:00.00000000Z,False,PLC,suricata-alerts,123.123.123.123,80,alert,1554565580150037,opnsense.home.arpa,re0,/var/log/suricata/eve.json,TCP,150.150.150.150,511"

csv.from(csv: csvData)
|> stateDuration(unit: 1m, fn: (r) => true)

Using stateDuration:

This doc does a good example of highlighting other like functions too that are worth knowing about.

1 Like