Pass earliest/latest in pipeline

[stwong]

Hi,

Sorry for the newbie question. We want to calculate percentage of time between 2 events over the entire search period. We use transaction and get the sum of time between each pair of events:

| transaction dest service startswith="CRITICAL;SOFT;1" endswith=OK | stats sum(duration) as total_downtime by dest

But we've no idea how to pass the earliest(_time) and latest(_time) of so that we can do the calculation like

percentage = (total_downtime/(latest-earliest))*100

Would anyone please help?
Thanks a lot.

[woodcock]

Try this:

... | search OK OR "CRITICAL;SOFT;1" | streamstats count(eval(searchmatch("CRITICAL;SOFT;1"))) AS sessionID BY dest service | stats range(_time) AS duration min(_time) AS earliest BY dest service sessionID | stats sum(duration) as total_downtime min(earliest) AS earliest BY dest | eventstats min(earliest) AS earliest | percentDown = 100 * (total_downtime)/(now() - earliest)

Using search time parameters would be wrong because 0 from all time would be from 1977. This uses the oldest _time value from all events returned in the search and now() as the time frame.

[stwong]

Thanks, will give it a try.

Rgds

[woodcock]

Don't forget to upvote helpful answers and close the question by clicking Accept on the best one.

[puneethgowda]

Host 1 2 minutes
Host 2 5 minutes
Host 3 40 minutes

Like this you can show what is the downtime of each server in minutes

[stwong]

Hello, thanks. I'm able to get the downtime of each server now. Just hope to get the percentage of downtime of each host over the entire search period (time between the first and last records for all hosts, or better to get the start/end time of time picker). Thanks.

[puneethgowda]

Why don't you fintune your Table

Try this

Host|now vs Latest max(transaction time in minutes)

[woodcock]

Ditch transaction; try this:

... | search OK OR "CRITICAL;SOFT;1" | streamstats count(eval(searchmatch("CRITICAL;SOFT;1"))) AS sessionID BY dest service | stats range(_time) AS duration by dest service sessionID | stats sum(duration) as total_downtime by dest | addinfo | percentDown = 100 * (total_downtime)/(info_max_time - info_min_time)

[stwong]

Hi, thanks. Tried addinfo before but seems add earliest/latest time for transaction instead of the first search in the pipe line.

Rgds
/st

[woodcock]

Did you actually try my search?

[stwong]

Hi, yes, tried and see info_max_time = +infinity and info_min_time = 0.000. thanks a lot.

[gcusello]

Hi stwong,
try something like this:

your_search
| transaction dest service startswith="CRITICAL;SOFT;1" endswith=OK 
| stats earliest(_time) AS Earliest latest(_time) AS Latest sum(duration) as total_downtime by dest 
| eval percentage = (total_downtime/(Latest-Earliest))*100

Bye.
Giuseppe

[stwong]

Hi Giuseppe,

Thanks. Seems this returns time period of transaction. Can I get the time span for "your_search" ? It's Nagios log and logs status of all hosts. Some are okay and some have down/up status change. We hope to get the percentage of downtime of each host (period betwen down/up) over the entire period.

Bye,
/st

[gcusello]

In this way you have the first and the latest events of your results.
to have earliest and latest you should follow this answer:
https://answers.splunk.com/answers/334498/how-to-use-eval-on-a-token-from-a-time-picker-and.html

Bye.
Giuseppe

[stwong]

Thanks, will study and give a try.

Bye.
/st