script output to columns with headers 2

[tagyourit]

netstat -an | grep tcp | awk '{print $6}' | sort | uniq -c | sort -nr

294 FIN_WAIT_2
173 ESTABLISHED
 83 TIME_WAIT
 33 LISTEN
  9 CLOSED
  3 CLOSING
  2 CLOSE_WAIT
  1 SYN_RCVD

Would like the output to be in the following format instead

FIN_WAIT_2 ESTABLISHED TIME_WAIT LISTEN CLOSED CLOSING CLOSE_WAIT SYN_RCVD
294        172         83        33     9      3       2          1

I figured initial write of this file would create headers, then subsequent writes would only add numbers for stats collected a row at a time.

 

[LKBrwnDBA]

Try something like this:

netstat -an | grep tcp |awk '{x[$6]++;} END {asorti(x);
 for(i in x){printf "%s ",i};print ""
 for(i in x){printf "%s ",x[i]};print ""
}'

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tagyourit]

This is on AIX 6 using KSH. I changed awk to gawk and getting the columns and some numbers.

4 5 6 7 1 2 3
FIN_WAIT_2 LAST_ACK LISTEN TIME_WAIT CLOSED CLOSE_WAIT ESTABLISHED

 

[FlorianAwk]

printf is your solution.

netstat -an | grep tcp |awk '{x[$6]++;} END {
 for(i in x){printf "%s ",i};print "";
 for(i in x){printf "%s ",x[i];print ""}
}' |awk '{printf "%15s%15s%15s%15s%15s%15s\n",$1,$2,$3,$4,$5,$6}'

15 is the size you want to apply to each field.

Good luck.

 

[tagyourit]

Here is a more fully developed script to account for states that had 0 connections. Data was being shifted around otherwise. Still not sure how to get the format I'm looking for in one go without scrubbing the data.

dt=`date +"%m%d%y%H%M%S"`
netstat -an | grep tcp | awk '{print $6}' | sort | uniq -c | sort -nr > /tmp/tcpconn.out
for state in SYN_SENT SYN_RECEIVED TIME_WAIT LISTEN FIN_WAIT_2 ESTABLISHED CLOSE_WAIT CLOSED LAST_ACK FIN_WAIT_1 CLOSING
do
k=`grep $state /tmp/tcpconn.log`
if [ "$k" == "" ]
then
echo "0 $state" >> /tmp/tcpconn.log
fi
done
while read line
do
echo "$dt $line" >> /tmp/logs/tcpconn.out
done < /tmp/tcpconn.log

which gives us

050313094000 480 FIN_WAIT_2
050313094000 322 ESTABLISHED
050313094000 101 TIME_WAIT
050313094000 33 LISTEN
050313094000 9 CLOSED
050313094000 2 LAST_ACK
050313094000 1 CLOSE_WAIT
050313094000 0 SYN_SENT
050313094000 0 SYN_RECEIVED
050313094000 0 FIN_WAIT_1
050313094000 0 CLOSING

But this is still what I'm trying to achieve

TIME[tab][tab]FIN_WAIT_2[tab]ESTABLISHED[tab]TIME_WAIT[tab]LISTEN[tab]CLOSED[tab]LAST_ACK[tab]CLOSE_WAIT[tab]SYN_SENT[tab]SYN_RCVD[tab]FIN_WAIT_1[tab]CLOSING
093500[tab][tab]294[tab][tab][tab]172[tab][tab][tab]83[tab][tab][tab]33[tab][tab]19[tab][tab]3[tab][tab][tab]2[tab][tab][tab]1[tab][tab][tab]0[tab][tab][tab]0[tab][tab][tab]0
094000[tab][tab]194[tab][tab][tab] 63[tab][tab][tab]33[tab][tab][tab]93[tab][tab]41[tab][tab]8[tab][tab][tab]1[tab][tab][tab]3[tab][tab][tab]0[tab][tab][tab]0[tab][tab][tab]0
094500[tab][tab]341[tab][tab][tab]112[tab][tab][tab]45[tab][tab][tab]13[tab][tab]7[tab][tab]11[tab][tab][tab]0[tab][tab][tab]7[tab][tab][tab]0[tab][tab][tab]0[tab][tab][tab]0 
095000[tab][tab]498[tab][tab][tab]252[tab][tab][tab]71[tab][tab][tab]49[tab][tab]12[tab][tab]2[tab][tab][tab]5[tab][tab][tab]1[tab][tab][tab]0[tab][tab][tab]0[tab][tab][tab]0

 

[FlorianAwk]

I suggest:

$ cat init.txt 
050313094000 480 FIN_WAIT_2 
050313094000 322 ESTABLISHED 
050313094000 101 TIME_WAIT 
050313094000 33 LISTEN 
050313094000 9 CLOSED 
050313094000 2 LAST_ACK 
050313094000 1 CLOSE_WAIT 
050313094000 0 SYN_SENT 
050313094000 0 SYN_RECEIVED 
050313094000 0 FIN_WAIT_1 
050313094000 0 CLOSING
$ cat horizontalToVertical.awk 
#!/bin/bash

awk '(NR==1){
        time=substr($1,7,6)
} 

{
        for (i=2;i<=NF;i++) 
                a[i]=a[i]" "$i
} 

END{
        for (i in a) 
                if (i==3) #header
                        print "TIME",a[i]
                else
                        print time,a[i]
} ' init.txt |awk '{
        for (i=1;i<=NF;i++) 
                printf "%13s",$i
        print ""
}'
$ ./horizontalToVertical.awk init.txt >result.txt 
$ cat result.txt 
       094000          480          322          101           33            9            2            1            0            0            0            0
         TIME   FIN_WAIT_2  ESTABLISHED    TIME_WAIT       LISTEN       CLOSED     LAST_ACK   CLOSE_WAIT     SYN_SENT SYN_RECEIVED   FIN_WAIT_1      CLOSING
$

If you want to stack many logs, I suggest you erase the printing of headers in the script, and get ready a file with headers in it. So, you can send all lines into it and have a good file.

Other idea: split the script above into 2 scripts: one to have a re-usable csv file, and one to make a beautifull display. If you do so, the first line will become "#!/usr/bin/awk" and you will be able to keep only awk commands.

I hope this is clear.

 

[LKBrwnDBA]

Your "Trying to achieve" results do not match your posted data.
Here is a starter:

TMPDIR=/tmp
LOGDIR=/tmp/log
dt=`date +"%m%d%y%H%M%S"`
netstat -an | grep tcp |awk 'BEGIN {split("CLOSE_WAIT,CLOSED,CLOSING,ESTABLISHED,FIN_WAIT_1,FIN_WAIT_2,LAST_ACK,LISTEN,SYN_RECEIVED,SYN_SENT,TIME_WAIT",tx,","); for(x in tx){t[tx[x]]=0}}{t[$6]++} END {for(x in t) print t[x],x}'|sort -nr| xargs -i+ echo $dt +

Which produces:

==> ./m2
050313143251 68 ESTABLISHED
050313143251 39 LISTEN
050313143251 2 CLOSE_WAIT
050313143251 2 CLOSED
050313143251 0 TIME_WAIT
050313143251 0 SYN_SENT
050313143251 0 SYN_RECEIVED
050313143251 0 LAST_ACK
050313143251 0 FIN_WAIT_2
050313143251 0 FIN_WAIT_1
050313143251 0 CLOSING

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Or better this:

TMPDIR=/tmp
LOGDIR=/tmp/log
dt=`date +"%m%d%y%H%M%S"`
netstat -an | grep tcp |awk 'BEGIN {split("CLOSE_WAIT,CLOSED,CLOSING,ESTABLISHED,FIN_WAIT_1,FIN_WAIT_2,LAST_ACK,LISTEN,SYN_RECEIVED,SYN_SENT,TIME_WAIT",tx,",");for(x in tx){t[tx[x]]=0}}{t[$6]++} END {
for(x in t){printf "%12s ",x};print ""; 
for(x in t){printf "%12s ",t[x]};print ""}'

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tagyourit]

LKBrwnDBA, thanks a bunch. For some reason, my data was not parsing right with the other awk's, but yours ( both of them ) sorted the data quite well. Thank you.

 

[tagyourit]

Now my brain is thinking...in a single script, could I run the initial bit to create the headers one time, then only print the numbers after? Something silly like the following

while true
do
clear
date
echo
netstat -an | grep tcp |awk 'BEGIN {split("CLOSE_WAIT,CLOSED,CLOSING,ESTABLISHED,FIN_WAIT_1,FIN_WAIT_2,LAST_ACK,LISTEN,SYN_RECEIVED,SYN_SENT,TIME_WAIT",tx,",");for(x in tx){t[tx[x]]=0}}{t[$6]++} END
{
for(x in t){printf "%12s ",x};print "";
for(x in t){printf "%12s ",t[x]};print ""}'
sleep 60
done

And we could watch connections come and go 'live' but also, would be easy to just push this to a file with something like tee and have an Excel ready data file as well.

 

[LKBrwnDBA]

Try this:

dt=`date +"%m%d%y%H%M%S"`
echo "Date: $dt"
echo ""|awk 'BEGIN{split("CLOSE_WAIT,CLOSED,CLOSING,ESTABLISHED,FIN_WAIT_1,FIN_WAIT_2,LAST_ACK,LISTEN,SYN_RECEIVED,SYN_SENT,TIME_WAIT",tx,",");for(x in tx){t[tx[x]]=0}} END {
for(x in t){printf "%12s ",x};print "";
}'
while true
do
  netstat -an | grep tcp |awk 'BEGIN {split("CLOSE_WAIT,CLOSED,CLOSING,ESTABLISHED,FIN_WAIT_1,FIN_WAIT_2,LAST_ACK,LISTEN,SYN_RECEIVED,SYN_SENT,TIME_WAIT",tx,",");for(x in tx){t[tx[x]]=0}}{t[$6]++} END {
  #for(x in t){printf "%12s ",x};print "";
  for(x in t){printf "%12s ",t[x]};print "";
  }'
  sleep 30
done

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tagyourit]

Giddyup! That works perfectly. Amazing work LKBrwnDBA, thanks a ton.