Netstat TCP status data logging script

I have a script that performs netstat -an calls to show TCP status for two ports (8080 and 5555). I have it print it out onto one row into a log file every minute. This is all fine and dandy but due to the nature of the traffic the status values change often. I need to grab the counts of these statuses and be able to plug them into excel and plot a graph for each status. I need static data so that means i also need the statuses that don't show up (the counts that equal 0). With sort | uniq -c it only picks up positive results obviously. My question is how can I fill in the blank for the statuses that don't show up so I can have full data?

Here is my script(it is in a while loop to run till 2pm):

#!/bin/bash
TS=$(date '+%Y-%m-%d %H:%M:%S')
LOG=_$(hostname)_TCP.log
LOGTS=$(date '+%Y%m%d')
HR=$(date '+%H')

while [ "$HR" != "14" ]; do
TS=$(date '+%Y-%m-%d %H:%M:%S')
        echo "$(echo $TS) $(printf "Port 8080 ")( $(netstat -an | grep 8080 | awk '{print $6}' | sort -k1 | uniq -c | awk '{print $2" " $1 ","}' |  xargs)) $(printf "Port 5555 ")( $(netstat -an | grep 5555 | awk '{print $6}' | sort -k1 | uniq -c | awk '{print $2" " $1 ","}' |  xargs)) " | tee -a $LOGTS$LOG
#       sleep 3600
        sleep 60
        HR=$(date '+%H')
done

echo "Past 14:00 so script is finished"

This is my current results:

2015-08-13 09:55:27 Port 8080 ( ESTABLISHED 7, FIN_WAIT2 1, LISTEN 1,) Port 5555 ( CLOSE_WAIT 1, ESTABLISHED 2,)
2015-08-13 09:56:27 Port 8080 ( ESTABLISHED 1, LISTEN 1,) Port 5555 ( CLOSE_WAIT 1, ESTABLISHED 1,)

As you can see I can get the counts nicely. But if i import it into excel the data will not be uniform and I will have to fill in blanks for the no counts in order to be able to plot the graph. Unless there is another method or way to do this nicely with excel?

My idea is possibly use an array with the tcp statuses to keep like a table of result hits and also count the zeros. Is this the right way of thinking?

Sorry for the long post. Thank you in advance.

Solution

I made a bash script that convert your file output :

2015-08-13 09:55:27 Port 8080 ( ESTABLISHED 7, FIN_WAIT2 1, LISTEN 1,) Port 5555 ( CLOSE_WAIT 1, ESTABLISHED 2,)
2015-08-13 09:56:27 Port 8080 ( ESTABLISHED 1, LISTEN 1,) Port 5555 ( CLOSE_WAIT 1, ESTABLISHED 1,)

to :

2015-08-13,09:55:27,8080,7,,,,1,,,,,1,,5555,2,,,,,,,1,,,,0
2015-08-13,09:56:27,8080,1,,,,,,,,,1,,5555,1,,,,,,,1,,,,1

with the definition of all the session state you can have in linux system for each port you have :

declare -a arr=("ESTABLISHED" "SYN_SENT" "SYN_RECV" "FIN_WAIT1" "FIN_WAIT2" "TIME_WAIT" "CLOSED" "CLOSE_WAIT" "LAST_ACK" "LISTEN" "CLOSING")

The last number in each line is a line count variable.

Usage :

netstat_format.sh your_output.txt formatted_output.txt

Full code of netstat_format.sh :

#!/bin/bash
#title         :format_netstat.sh
#author        :Bertrand Martel
#date          :13/08/2015

#declare a list of all session state you may find in linux system
declare -a arr=("ESTABLISHED" "SYN_SENT" "SYN_RECV" "FIN_WAIT1" "FIN_WAIT2" "TIME_WAIT" "CLOSED" "CLOSE_WAIT" "LAST_ACK" "LISTEN" "CLOSING")

IFS=$'\n'     #line delimiter
set -f        #Disable file name generation (globbing)
count_line=0  #line counter

#empty your output file
cp /dev/null "$2"

for i in $(cat "$1"); do

    #test="2015-08-13 09:55:27 Port 8080 ( ESTABLISHED 7, FIN_WAIT2 1, LISTEN 1,) Port 5555 ( CLOSE_WAIT 1, ESTABLISHED 2,)"
    main_part=$i

    new_line=""

    #extract first,second and fourth column with ' ' delimiter
    date_val=`echo $main_part | cut -d' ' -f1`
    time_val=`echo $main_part | cut -d' ' -f2`
    port_val=`echo $main_part | cut -d' ' -f4`

    #append these fields to new line output var
    new_line="$date_val,$time_val,$port_val"

    for i in {0..10}
    {
        #here extract all that is between parenthesis and process it independently with replacing "," with ' ', looking for session state in arr defined in the beginning.
        #  awk '{print $2}' => will finally print the second argument eg the value of the key found in arr
        result=`echo $main_part | awk -v FS="([(]|[)])" '{print $2}'  | sed 's/,/ /g' | grep -o "${arr[i]} [^ ]*" | awk '{print $2}'`
        if [ -z "$result" ]; then
            result=""
        fi
        new_line="$new_line,$result"
    }

    #cut all before " Port"
    second_part=`echo $main_part | sed 's/.*) Port //'`

    #second port in line
    port2_val=`echo $second_part | cut -d' ' -f1`

    #add port2 value to line output
    new_line="$new_line,$port2_val"

    for i in {0..10}
    {
        result=`echo $second_part | awk -v FS="([(]|[)])" '{print $2}'  | sed 's/,/ /g' | grep -o "${arr[i]} [^ ]*" | awk '{print $2}'`
        if [ -z "$result" ]; then
            result=""
        fi
        new_line="$new_line,$result"
    }

    #############################################

    #cut all before " Port"
    third_part=`echo $second_part | sed 's/.*) Port //'`

    #second port in line
    port3_val=`echo $third_part | cut -d' ' -f1`

    #add port2 value to line output
    new_line="$new_line,$port3_val"

    for i in {0..10}
    {
        result=`echo $third_part | awk -v FS="([(]|[)])" '{print $2}'  | sed 's/,/ /g' | grep -o "${arr[i]} [^ ]*" | awk '{print $2}'`
        if [ -z "$result" ]; then
            result=""
        fi
        new_line="$new_line,$result"
    }

    ############################################

    #add line count
    new_line="$new_line,$count_line"

    #increment line count
    count_line=$((count_line+1))

    #append content of new line to output file
    echo $new_line >> "$2"
done

cat "$2"

I created a gist when you can take the file :

https://gist.github.com/bertrandmartel/5f1c0c0c84db44e85ca8#file-netstat_format-sh

Nevertheless, it only processes 2 series of Port XXX (....) strings, if you expect to have more you have to modify the script a little