Splunk Search
Highlighted

Pass earliest/latest in pipeline

Communicator

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.

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Legend

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

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Communicator

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

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Legend

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

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Communicator

Thanks, will study and give a try.

Bye.
/st

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Esteemed Legend

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

Re: Pass earliest/latest in pipeline

Communicator

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

Rgds
/st

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Esteemed Legend

Did you actually try my search?

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Communicator

Hi, yes, tried and see infomaxtime = +infinity and infomintime = 0.000. thanks a lot.

0 Karma
Highlighted

Re: Pass earliest/latest in pipeline

Communicator

Why don't you fintune your Table

Try this

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

0 Karma