Making timeseries from logs

PiotrJS — Wed, 21 Aug 2024 21:03:09 GMT

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.

Any help would much appreciated.

Thank you

Re: Making timeseries from logs

krzysztof_hoja — Wed, 28 Aug 2024 20:43:31 GMT

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

topic Making timeseries from logs in DQL

Making timeseries from logs

Re: Making timeseries from logs