I got a working script that calculates the time between two states, so to say the state duration. It is based on the elapsed() function.
var measurement = 'durations7'
var period = 1d
var every = 30m
var all_events = batch
|query('SELECT state, output FROM "azeti"."autogen"."State" WHERE sensor_guid = \'04245a5c-76a1-4065-8cdc-ea73597eb691\'')
.period(period)
.every(every)
var events = all_events
|eval(lambda: strContains("state", 'WARNING'))
.as('changed')
.keep('state', 'output')
var elapsed = all_events
|elapsed('state', 1m)
.as('duration')
events
|join(elapsed)
.as('changed', 'duration')
|log()
|influxDBOut()
.database('_azeti_analytics')
.measurement(measurement)
The data is saved into a new measurement. My issue now is that the fields are joined together with an offset so the duration shown for OK is actually the duration for the WARNING event.
How can i either offset the join or change my logic to join the duration to the right output fields?
date,changed.output,changed.state,duration.duration
11/5/2017 8:35:35 PM,POP WARNING - 1.737 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [RABCADMAUABSADAAMgAwADIAQwBBADAAMAAyADAALgBlAHUAcgBwAHIAZAAwADIALgBwAHIAbwBkAC4AbwB1AHQAbABvAG8AawAuAGMAbwBtAA==]],WARNING,475
11/5/2017 8:40:34 PM,POP OK - 0.046 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUAAxADkAMABDAEEAMAAwADMANQAuAEUAVQBSAFAAMQA5ADAALgBQAFIATwBEAC4ATwBVAFQATABPAE8ASwAuAEMATwBNAA==]],OK,4
11/5/2017 9:35:35 PM,POP WARNING - 1.242 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUABSADAANQAwADIAQwBBADAAMAAwADEALgBlAHUAcgBwAHIAZAAwADUALgBwAHIAbwBkAC4AbwB1AHQAbABvAG8AawAuAGMAbwBtAA==]],WARNING,55
11/5/2017 9:40:35 PM,POP OK - 0.045 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUAAxADkANABDAEEAMAAwADIAMQAuAEUAVQBSAFAAMQA5ADQALgBQAFIATwBEAC4ATwBVAFQATABPAE8ASwAuAEMATwBNAA==]],OK,5
11/5/2017 10:35:35 PM,POP WARNING - 1.863 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUABSADAANQAwADIAQwBBADAAMAAxADYALgBlAHUAcgBwAHIAZAAwADUALgBwAHIAbwBkAC4AbwB1AHQAbABvAG8AawAuAGMAbwBtAA==]],WARNING,55
11/5/2017 10:40:38 PM,POP OK - 0.057 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [VgBJADEAUABSADAANQAwADEAQwBBADAAMAAxADkALgBlAHUAcgBwAHIAZAAwADUALgBwAHIAbwBkAC4AbwB1AHQAbABvAG8AawAuAGMAbwBtAA==]],OK,5
11/6/2017 12:35:36 AM,POP WARNING - 1.145 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [SABFADEAUABSADAANwBDAEEAMAAwADIAMAAuAGUAdQByAHAAcgBkADAANwAuAHAAcgBvAGQALgBvAHUAdABsAG8AbwBrAC4AYwBvAG0A]],WARNING,114
11/6/2017 12:40:35 AM,POP OK - 0.051 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [VgBJADEAUAAxADgAOQAwADEAQwBBADAAMAAxADkALgBFAFUAUgBQADEAOAA5AC4AUABSAE8ARAAuAE8AVQBUAEwATwBPAEsALgBDAE8ATQA=]],OK,4
11/6/2017 4:40:38 AM,POP WARNING - 2.110 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [VgBJADEAUABSADAAOABDAEEAMAAxADIANAAuAGUAdQByAHAAcgBkADAAOAAuAHAAcgBvAGQALgBvAHUAdABsAG8AbwBrAC4AYwBvAG0A]],WARNING,240
11/6/2017 4:45:36 AM,POP OK - 0.273 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUABSADAAMQAwADIAQwBBADAAMAAyADEALgBlAHUAcgBwAHIAZAAwADEALgBwAHIAbwBkAC4AZQB4AGMAaABhAG4AZwBlAGwAYQBiAHMALgBjAG8AbQA=]],OK,4
11/6/2017 4:50:35 AM,POP WARNING - 2.831 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [TABPADIAUAAyADYANQBDAEEAMAAwADMAMAAuAEcAQgBSAFAAMgA2ADUALgBQAFIATwBEAC4ATwBVAFQATABPAE8ASwAuAEMATwBNAA==]],WARNING,4
11/6/2017 4:55:36 AM,POP OK - 0.056 second response time on port 110 [+OK The Microsoft Exchange POP3 service is ready. [QQBNADUAUABSADAANQAwADIAQwBBADAAMAAwADMALgBlAHUAcgBwAHIAZAAwADUALgBwAHIAbwBkAC4AbwB1AHQAbABvAG8AawAuAGMAbwBtAA==]],OK,5%
A screenshot of the data. E.g. “4” is actually the time between WARNING and OK so actually the duration for the WARNING event.
