How to find event status changed

[atpsplunk11]

Hello everyone!

We have a log file contains the following information, status 0 means server is up, 1 means down:
Date/time Server Status
2019/02/11 120000 server1 1
2019/02/11 120000 server2 0
2019/02/11 123000 server1 0

This file contains many servers' status generated by a cron job. I want to write a Splunk query/search to show all servers which were down and for how long. My desire output would be similar to the following
Server From To Duration
server1 2019/02/11 120000 2019/02/11 123000 30

Thus I would find a server status is "1", then need to find the immediate status "0" for the same server to calculate the outage time. How do I write this search query?

Since a server could be down for a long period, this log file could have multiple entries for same server continuously, such as
2019/02/11 120000 server1 1
2019/02/11 120000 server2 0
2019/02/11 120300 server1 1
2019/02/11 120300 server2 0
2019/02/11 130000 server1 0

Any help is appreciated!

[Tedesco1 · Accepted solution]

Try something like this:

Notes:
1) I'm assuming your date/time field is called "_time"; you didn't specify. I'm also assuming your date/time field is in the Splunk standard epoch time. If it's not, it's easy enough to change this to epoch time using the eval command and strptime function.

2) You didn't list your search / filter criteria or how you are accessing the data, so the first line is vague.

3) This assumes your data is up-to-date to the time of running this search. If you are missing data close to the time of search, some of the durations will not calculate properly (as they calculate based on the current time).

4) I generally use all lowercase... everything below is lowercase except for what you specified above.

 index=foo sourcetype=bar, etc. 
 | sort 0 Server -_time 
 | autoregress Server as last_Server
 | streamstats count(eval(Status=0 OR (NOT Server=last_Server))) as unique_count
 | delta _time as Duration
 | search Status=1
 | eval Duration=if(isnull(Duration) OR (NOT Server=last_Server),now() - _time,abs(Duration))
 | stats earliest(_time) as From sum(Duration) as Duration by Server unique_count
 | sort 0 -From
 | eval To=strftime(From+Duration,"%c"), From=strftime(From,"%c")
 | fields Server From To Duration

[atpsplunk11]

This solution works well for me. I am able to get data from logs with multiple outage from different servers. Thank you.