How to check time difference between a series of events?

[sh254087]

Hello!

I am trying to find system uptime and here's the scenario:

The monitoring/status check log returns fields like - InstanceID, timestamp, Count.

There's a status check event every 5mins. System is up when 'Count >=5' and down otherwise. 

Looking to know how I can come up with the Downtime value either in actual time as mentioned in sample data in the table or in seconds.

Can someone please help with this?

InstanceID	timestamp	SampleCount	Difference	Downtime
insA	2023-02-21T16:00:00Z	5	0
insA	2023-02-21T16:05:00Z	4	0
insA	2023-02-21T16:10:00Z	2	00:05
insA	2023-02-21T16:15:00Z	5	00:10	00:10
insA	2023-02-21T16:20:00Z	5	0
insA	2023-02-21T16:25:00Z	5	0
insA	2023-02-21T16:30:00Z	4	0
insA	2023-02-22T01:35:00Z	2	09:05
insA	2023-02-22T01:40:00Z	5	09:10	09:10
insA	2023-02-22T01:45:00Z	5	0
insA	2023-02-22T01:50:00Z	5	0
insA	2023-02-22T01:55:00Z	5	0
insA	2023-02-22T02:00:00Z	5	0
insA	2023-02-22T02:05:00Z	5	0
insA	2023-02-22T02:10:00Z	4	0
insA	2023-02-22T02:15:00Z	3	00:05
insA	2023-02-22T02:20:00Z	5	00:10	00:10
insA	2023-02-22T02:25:00Z	5	0

[ITWhisperer · Accepted solution]

Assuming already in time order, you could try something like this

| eval up=if(SampleCount >= 5, 1, null())
| streamstats window=2 global=f range(_time) as diff by up InstanceID
| eval downtime=if(diff-300>0,tostring(diff-300,"duration"),null())

[sh254087]

@ITWhisperer This just did it for me. Thanks a lot.