How to find blocks where response time is 0 from ping data?

[dajomas]

Hello all,

I receive ping data into my Splunk environment. Everything is filtered so that I can plot the response times in a nice graph.

But what I would like to extract from this data are the blocks where the response time is 0 (which means there is no connection). I would like to retrieve the _time field for the first and the last event in that block so that I know during which intervals the connection is not available.

Can anyone point me in the direction I should be looking at?

Thanx!

[dajomas]

Thanx to @somesoni2 and @sundareshr .
Both your answers helped me reach my desired output.

[ppablo]

Hi @dajomas

Glad you got your expected output. Could you please resolve the post by clicking "Accept" directly below the answer that best helped solve your issue? Also, please be sure to comment below that answer with the final search you used get your expected results.

[sundareshr]

Try with the autoregress command, then compare the previous and current values. Once you have that, you can add logic to group by blocks where current and previous is 0. One approach could like this psuedo code

.. | reverse | autoregress pingtime as prevvalue | where pingtime>0 AND prevalue=0 | streamstats range(_time) as duration | ...

http://docs.splunk.com/Documentation/Splunk/6.1.7/SearchReference/Autoregress

[somesoni2 · Accepted solution]

You would need to explore streamstats search command.

See these posts with similar requirements

https://answers.splunk.com/answers/48557/transaction-trouble-with-ping-events.html
https://answers.splunk.com/answers/129766/service-down-time-stats.html
https://answers.splunk.com/answers/240689/how-to-calculate-downtime-based-on-the-amount-of-r.html
https://answers.splunk.com/answers/311027/how-to-identity-each-start-stop.html

[dajomas]

The scipt that generates the input records is:

date
/bin/ping $1 -c1 -R -n -v

The input records look like this:

Tue Jul 19 09:15:01 CEST 2016
PING 192.168.XXX.YYY (192.168.XXX.YYY) 56(124) bytes of data.
64 bytes from 192.168.XXX.YYY: icmp_seq=1 ttl=63 time=1.27 ms
RR:     192.168.XXA.YYA
        192.168.XXX.YYB
        192.168.XXX.YYY
        192.168.XXA.YYC
        192.168.XXA.YYA


--- 192.168.XXX.YYY ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 1.276/1.276/1.276/0.000 ms

And the output I needed is found with the following search

source="/var/log/ping/*" index="ping" sourcetype="ping" host=192.168.XXX.YYY PING 
 | fillnull 
 | reverse 
 | autoregress time as prevvalue 
 | search (time=0 AND prevvalue>0) OR (time>0 AND prevvalue=0)
 | autoregress time as time_1
 | autoregress _time as etime_1
 | autoregress prevvalue as prevvalue_1
 | eval etime = _time
 | eval secs = round((etime - etime_1) / 60) * 60
 | eval duration = tostring(secs,"duration")
 | rename etime as endtime, etime_1 as starttime
 | fieldformat starttime = strftime(starttime, "%Y-%m-%d %H:%M")
 | fieldformat endtime = strftime(endtime, "%Y-%m-%d %H:%M")
 | search endtime=* AND (prevvalue=0 AND time_1=0)
 | reverse
 | table starttime, endtime, duration

And the result looks like this: