topic Pass earliest/latest in pipeline in Splunk Search

Pass earliest/latest in pipeline

stwong — Tue, 29 Sep 2020 13:12:07 GMT

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.

Re: Pass earliest/latest in pipeline

gcusello — Fri, 10 Mar 2017 10:27:11 GMT

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

Re: Pass earliest/latest in pipeline

woodcock — Fri, 10 Mar 2017 15:29:08 GMT

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)

Re: Pass earliest/latest in pipeline

stwong — Fri, 10 Mar 2017 16:30:38 GMT

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

Re: Pass earliest/latest in pipeline

stwong — Fri, 10 Mar 2017 16:32:47 GMT

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

Rgds
/st

Re: Pass earliest/latest in pipeline

gcusello — Fri, 10 Mar 2017 16:45:56 GMT

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

Re: Pass earliest/latest in pipeline

puneethgowda — Fri, 10 Mar 2017 16:57:20 GMT

Why don't you fintune your Table

Try this

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

Re: Pass earliest/latest in pipeline

puneethgowda — Fri, 10 Mar 2017 17:00:07 GMT

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

Re: Pass earliest/latest in pipeline

woodcock — Fri, 10 Mar 2017 21:06:02 GMT

Did you actually try my search?

Re: Pass earliest/latest in pipeline

stwong — Tue, 29 Sep 2020 13:12:26 GMT

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

Re: Pass earliest/latest in pipeline

stwong — Sat, 11 Mar 2017 11:47:01 GMT

Thanks, will study and give a try.

Bye.
/st

Re: Pass earliest/latest in pipeline

woodcock — Sat, 11 Mar 2017 15:33:42 GMT

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.

Re: Pass earliest/latest in pipeline

stwong — Mon, 13 Mar 2017 03:25:40 GMT

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.

Re: Pass earliest/latest in pipeline

stwong — Mon, 13 Mar 2017 03:27:10 GMT

Thanks, will give it a try.

Rgds

Re: Pass earliest/latest in pipeline

woodcock — Mon, 13 Mar 2017 19:21:51 GMT

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