Making timeseries from logs

[PiotrJS]

Hi,

I would like to generate a simple line chart showing a connection status which goes from 0(closed) to 1(open). Quite simple but...

I tried something like below but it did not work as I want. I think the problem I have is that the connection metric does not stay at value "1" till "closed" is emitted.

data record(timestamp = toTimestamp("2024-08-17T09:30:00.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-18T09:31:00.000-0400"), msg ="closed"),
     record(timestamp = toTimestamp("2024-08-19T09:30:00.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-20T09:31:00.000-0400"), msg ="closed"),
     record(timestamp = toTimestamp("2024-08-21T09:31:30.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-21T22:32:00.000-0400"), msg ="closed")
| makeTimeseries {
   opened=countIf(msg=="opened",default: 0),
   closed=countIf(msg=="closed",default: 0) }
| fieldsAdd connection=opened[]-closed[]

 Any help would much appreciated.

Thank you

[krzysztof_hoja]

I would be possible if we had a function to calculate cumulative sum for given array. Because we do not have such function we can use arrayMovingSum which will work for timeseries no longer then 60 elements. Result of query with such addition:

data record(timestamp = toTimestamp("2024-08-17T09:30:00.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-18T09:31:00.000-0400"), msg ="closed"),
     record(timestamp = toTimestamp("2024-08-19T00:00:00.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-19T02:00:00.000-0400"), msg ="closed"),
     record(timestamp = toTimestamp("2024-08-19T09:30:00.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-20T09:31:00.000-0400"), msg ="closed"),
     record(timestamp = toTimestamp("2024-08-21T09:31:30.000-0400"), msg ="opened"),
     record(timestamp = toTimestamp("2024-08-21T22:32:00.000-0400"), msg ="closed")
| makeTimeseries {
   opened=countIf(msg=="opened",default: 0),
   closed=countIf(msg=="closed",default: 0) }, 
   from: toTimestamp("2024-08-17T00:00:00.000-0400"), to: toTimestamp("2024-08-22T00:00:00.000-0400"), interval:2h
| fieldsAdd opened=arrayMovingSum(opened, 60), closed=arrayMovingSum(closed, 60)

 looks like this:

Now substation

| fields connection=opened[]-closed[], interval, timeframe

should give expected effect:

Please to that this method has some limitations:

• changes from "Closed" to "Open" and back to "Closed" in same interval is invisible.
• lost event/log line will produce results hard to explain

Kris