I have a script that pings several hosts and stores the response in a text file (see below)

Once this file is created, the intention is to populate a database with the values for 'packet loss', 'avg' and 'mdev', but first I have to extract this data.

avg=latency
mdev = jitter
packet loss = loss

My question is on how to extract the 'packet loss' 'avg' and 'mdev' values from the file, possibly in the form of .... Host# $avg $loss $jitter

Wed Dec  3 15:25:14 EST 2008
PING Host11 (10.0.201.51) 56(84) bytes of data.
--- Host11 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4010ms
rtt min/avg/max/mdev = 7.400/41.641/59.604/17.946 ms
.
Wed Dec  3 15:25:18 EST 2008
PING Host12 (10.0.202.51) 56(84) bytes of data.
--- Host12 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4016ms
rtt min/avg/max/mdev = 43.804/55.694/67.728/9.359 ms
.
Wed Dec  3 15:25:22 EST 2008
PING Host13 (10.0.205.51) 56(84) bytes of data.
--- Host13 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4007ms
rtt min/avg/max/mdev = 40.237/56.283/68.973/10.433 ms
.
Wed Dec  3 15:25:26 EST 2008
PING Host14 (10.0.201.52) 56(84) bytes of data.
--- Host14 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4007ms
rtt min/avg/max/mdev = 36.053/65.964/96.782/20.642 ms

What did you try so far?

awk '
        /^PING / {h=$2}
        /packet loss/ {pl=$6}
        /min\/avg\/max/ {
                split($4,a,"/")
                printf("Host = %s\navg = %s\nmdev = %s\npacket loss = %s\n\n", h, a[1], a[3], pl )
        }
' infile

Host = Host11
avg = 7.400
mdev = 59.604
packet loss = 0%

Host = Host12
avg = 43.804
mdev = 67.728
packet loss = 0%

Host = Host13
avg = 40.237
mdev = 68.973
packet loss = 0%

Host = Host14
avg = 36.053
mdev = 96.782
packet loss = 0%

If you are using ksh93, you can parse the file using ksh93 builtins

command exec 3< datafile

eof=$(3<#((EOF)))
if (( $(3<#((0))) != 0 ))
then
  print "not at start of file"
  exit 1
fi

while (( $(3<#((CUR))) < $eof ))
do
   3<#'PING*'
   read -A -u3 arr1
   3<#'*packet loss*'
   IFS=" %" read -A -u3 arr2
   IFS=" /" read -A -u3 arr3
   printf "%s %6.3f %3d %6.3f\n" ${arr1[1]} ${arr3[6]} ${arr2[5]} ${arr3[9]}
done

Host11  7.400   0 17.946
Host12 43.804   0  9.359
Host13 40.237   0 10.433
Host14 36.053   0 20.642

Thanks for all your help guys, here is what I have done so far

1 - a check-hosts script that uses a host-list.txt to ping konwn hosts

./check-hosts (script that pings hosts and generates the ping-results.txt)
rm -f ping-results.txt
cat hosts-list.txt |\
while read line
do
echo . >> ping-results.txt
date >> ping-results.txt
ping -c 5 -q $line >> ping-results.txt
echo $line
done

2 - host-list.txt

Host11
Host12
Host13
Host14

3 - I have modified the awk script (named extract-sla) from Zaxxon's response to read like this

awk '
/^PING / {h=$2}
/packet loss/ {pl=$6}
/min\/avg\/max/ {
split($4,a,"/")
printf("%s %s %s %s\n", h, a[2], a[4], pl )
}
' ping-results.txt

4 - Here is the new output from the results (which I can store into a file)

Host11 56.817 8.520 0%
Host12 55.031 8.485 0%
Host13 72.351 40.053 0%
Host14 32.590 9.705 0%

** Is there a way to get rid of the % sign since we already know it's a percentage.

5 - I would like to input these numbers into a database with a mysql command (the database and tables are already created)

Hostxx a b c

mysql -h 10.255.1.11 -u ping --password='ping' -D host_SLA -e "UPDATE device_list SET latency=a, jitter=b, loss=c WHERE host='Hostxx'";


Thanks for all your responses

For 4)
Pipe it to something like:

.... | tr -d '%'

Thanks for your input... I modified extract-sla script as follows

awk '
/^PING / {h=$2}
/packet loss/ {pl=$6}
/min\/avg\/max/ {
split($4,a,"/")
printf("a=%s b=%s c=%s d=%s\n", h, a[2], a[4], pl )
getline < "mysql-update-infile"
print
}
' ping-results-1.txt > mysql-update-tables

********

The above script produces a mysql-update-tables file in the format below, line by line (might not be the best way to do it, but it worked for me)

a=Host11 b=52.960 c=10.466 d=0%
/usr/bin/mysql -h 10.255.1.11 -u ping --password='ping' -D host_SLA -e "UPDATE device_list SET latency='$b', jitter='$c', loss='$d' WHERE host='$a'"

I then put all the pieces together using one script and cron the job.

** btw I did not have to delete the '%' in variable 'd'

Thanks for your help