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?
You would need to explore streamstats search command.
See these posts with similar requirements
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:
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 | ...
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.