Splunk Search
Highlighted

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?

Path Finder

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
0 Karma
Highlighted

Re: 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?

Super Champion

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

0 Karma
Highlighted

Re: 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?

Esteemed Legend

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
0 Karma