How to find all the hosts which transition from pass to fail over a period of time?

[macadminrohit]

Hi,

I have following events coming from a csv file on different hosts which logs the events into splunk,

"HOST",SA","20180212-11:58:54.338","SUCCESS","1","1","0"

We need to find all the hosts in which have transitioned from FAILURE to SUCCESS over the period of time, I think it can be done since the FAILURE events might have also been indexed in the past.

[micahkemp]

You can use streamstats to compare previous values, but unless you run events through sort, first, they'll be in reverse chronological order, so you'd probably want to look for events that transitioned from FAILURE to SUCCESS in reverse chronological order. Something like:

| makeresults | eval host="S2F2S", status="SUCCESS"
 | append [| makeresults | eval _time=999, host="S2F2S", status="FAILURE"]
 | append [| makeresults | eval _time=998, host="S2F2S", status="SUCCESS"]
 | append [| makeresults | eval _time=997, host="F2S", status="SUCCESS"]
 | append [| makeresults | eval _time=996, host="F2S", status="FAILURE"]
 | append [| makeresults | eval _time=995, host="S2F", status="FAILURE"]
 | append [| makeresults | eval _time=995, host="S", status="SUCCESS"]
 | append [| makeresults | eval _time=994, host="S2F", status="SUCCESS"]
 | append [| makeresults | eval _time=993, host="F2S2F", status="FAILURE"]
 | append [| makeresults | eval _time=992, host="F2S2F", status="SUCCESS"]
 | append [| makeresults | eval _time=991, host="F2S2F", status="FAILURE"]

 | streamstats current=false last(status) AS more_recent_status BY host
 | search more_recent_status=FAILURE status=SUCCESS

| streamstats window=1 current=false last(status) AS more_recent_status BY host
| search more_recent_status=FAILURE status=SUCCESS

This finds the SUCCESS events that are followed by a FAILURE event (which would be above it in the search results).

[macadminrohit]

I tried this but i am not getting what i looking for. Actually i need the list of hosts which transitioned from FAILURE to SUCCESS and as far as i could understand your query above, i see :

It will look for previous event and check for status field in that , and then search for all the events which had stream of events transitioning from SUCCESS to FAILURE. i tried to flip the field values in the search but it still doesnt work.

[micahkemp]

You are correct that I had my search flipped. I've added changed that and added a run anywhere search that should show that it works that way, though.

[macadminrohit]

It still didnt return anything.
I know there are hosts which transitioned from Failure to SUCCESS during the time interval.index=net sourcetype=csv NOT Date
[| inputlookup servers.csv
| search zone=EST
| table host] ProjectName="DBversion"
| fillnull value=0 Total_TestCases_Executed,Success_TestCases
| fillnull value=null HostName,ProjectName,OverallStatus | streamstats window=1 current=false last(OverallStatus) AS more_recent_status BY HostName | search more_recent_status=SUCCESS OverallStatus=FAILURE |
fields - Cnt _time

[micahkemp]

I added a test case and correction to my search in the answer. window=1 was causing the search to only find transitions that were right next to each other, so I removed it.

[macadminrohit]

Still need to try this, will let you know how it goes.