We currently have this search to calculate the percentage of time for a status using transaction, but can it be improved or use stats instead?

[smudge797]

We have a way of calculating the percentage of time the status is in the “OK” state by using transaction to find the intervals of time it was “OK” compared to when it was in “WARNING” state.
(As the logs we found for the device we were searching for only had “OK” or “WARNING” events). This is what we have, but I'm wondering if it could be improved or if stats could be used instead?

index=icinga_logs sourcetype=icinga hu1secsoudc1 "*status_interface*" 
| rex "(?i)^(?:[^;]*;){2}(?P<status>[^;]+)" 
| transaction startswith=(status="OK") endswith=(status="WARNING")
| stats sum(duration) AS TotalGood 
| append [search index=icinga_logs sourcetype=icinga hu1secsoudc1 "*status_interface*" 
| rex "(?i)^(?:[^;]*;){2}(?P<status>[^;]+)" 
| transaction startswith=(status="WARNING") endswith=(status="OK") 
| stats sum(duration) AS TotalBad] 
| append [search index=icinga_logs sourcetype=icinga hu1secsoudc1 "*status_interface*" 
| rex "(?i)^(?:[^;]*;){2}(?P<status>[^;]+)" | head 1 
| eval TotalGood = if(status=="OK" OR status=="STOPPED", now()-_time, 0) 
| eval TotalBad = if(status="WARNING", now()-_time, 0)] | stats sum(TotalGood) AS TotalGood sum(TotalBad) AS TotalBad | eval Total = (TotalGood+TotalBad) | eval Percentage = (TotalGood/Total)*100 | table Percentage

[woodcock]

Try this:

index=icinga_logs sourcetype=icinga hu1secsoudc1 "*status_interface*"
| rename COMMENT "This solution presumes every event has a 'status' field which is either 'OK', 'WARNING' or 'STOPPED'"
| rex "(?i)^(?:[^;]*;){2}(?P<status>[^;]+)" 
| reverse
| streamstats current=t count(eval(status="OK")) AS GoodSessionID count(eval(status="WARNING")) AS BadSessionID
| eventstats latest(status) AS lastGoodStatus latest(_time) AS lastGoodTime by GoodSessionID
| eventstats latest(status) AS lastBadStatus latest(_time) AS lastBadTime by BadSessionID
| eval lastGoodTime=if(lastGoodStatus="WARNING, lastGoodTime, now())
| eval lastBadTime=if(lastBadStatus="WARNING", now(), lastBadTime)
| where status!="STOPPED"
| rename COMMENT "Keep only 'OK' and 'WARNING' events"
| eval goodDuration= if(status="OK", lastGoodTime - _time, 0)
| eval badDuration= if(status="WARNING", lastBadTime - _time, 0)
| stats sum(goodDuration) AS TotalGood sum(badDuration) as TotalBad
| eval Total = (TotalGood+TotalBad) | eval Percentage = (TotalGood/Total)*100 | table Percentage

[cmerriman]

could you use
|stats range(_time) as duration by status
and possibly one other identifying 'by' clause instead of the transactions?