Sorting Data

[Allanm]

I have some stats from the wget command I ran last night to troubleshoot a network latency issue. I am attaching the results in an excel sheet.

There are 3 sheets , the two sheets have results and the last sheet has a sample of results the way I want it to present. Is there a way to format the data the way I want it to be presented.

Thanks,
Allan.

[Hein van den Heuvel]

fyi... I can not 'see' the zip attachment. It does not download.

Irrespective of that, this question is posted in an HPUX forum which natively does not grow excel speadsheets.

The original layout of the the data may well influence the right choice of tools to help you solve the problem.

How is the data originally delivered?
How would the output idealy be presented?
Fixed width columns, Comma-Seperated-Values, XML, ...

Please retry a reply with the zip file, or better still a (single) .TXT file showing a good chunk of the original input and desired output?

Hein.


Hein.

[Allanm]

Here is the script that I am using:

There are two script, here is the one which I use from a remote server:

#!/bin/bash
export http_proxy="http://proxy"
export https_proxy="http://proxy"
exec > /tmp/wget_output.txt 2>&1

while true; do
echo -n "> " && date
echo -n "web1 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web1.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web2 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web2.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web3 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web3.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web4 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web4.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web5 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web5.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web6 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web6.com/testweb.html 2>&1 |grep user|awk '{print $3}'
echo -n "web7 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web7.com/testweb.html 2>&1 |grep user|awk '{print $3}'
sleep 1
done | sed 's/elapsed//'

Here is the one which I used locally on the web server:

#!/bin/bash
exec > /tmp/wget_output.txt 2>&1

while true; do
echo -n "> " && date
echo -n "web1 ";/usr/bin/time wget -q --no-check-certificate -O /dev/null https://web1.com/testweb.html 2>&1 |grep user|awk '{print $3}'
sleep 1
done | sed 's/elapsed//'

Through the two scripts I compare as to what the issue is, through script one I am trying to look for network latency and through script two I am trying to look for apache issues.

I am attaching a word file with the screen shots of the excel sheet that I attached earlier, so you can derive as to which way I want the data to be presented and then I can work on getting a graph from that data.

I am not good at perl but I am wondering if there can a perl script which can make my life easier.

[Allanm]

And just FYI I was able to open the attachments through IE and not through Firefox.

[Hein van den Heuvel · Accepted solution]

>> And just FYI I was able to open the attachments through IE and not through Firefox.

And I could not read get to the ZP file with IE 6.0, but it worked with chrome.

>>... testweb.html 2>&1 |grep user|awk '{print $3}'

First a side comment to get that out of my system:
Why pipe from grep to awk?
awk will be happy to do the filter:
.. testweb.html 2>&1 |awk '/user/{print $3}'

More importantly... don't print it just yet.
Stick it in a variable and glue that with a comma (or tab) to a variable for a line being build.
At the end of the while loop, print the whole line with seperators and all!

>> I am attaching a word file with the screen shots

An other side command to get it out of my system. Why hide a PICTURE of a spreadsheet in a non-HPUX WORD format file when the DATA for the spreadsheet is just plain old text? Worse than Useless!
If it is text, present it as text pretty please! Leave Microsoft out of it!


>> two sheets have results and the last sheet has a sample of results the way I want it to present

Unfortunately the example output failed to use real data.
So it is not clear whether the column for 'web1' should come from the first dataset, or the second or should just be 0:00.14 !

But enough nit-picking already!

Here is a starting point.
This is a PERL script to read the second file and produce an excel-ready CSV file like:

#perl test.pl test.txt
Date,web1,web2,web3,web4,web5,web6,web7
Tue Jan 20 16:17:49 PST 2009,0:00.14,0:00.07,0:00.08,0:00.08,0:00.09,0:00.06,0:00.07
Tue Jan 20 16:17:51 PST 2009,0:00.07,0:00.07,0:00.09,0:00.11,0:00.07,0:00.06,0:00.07

-------------------- test.pl ---------
use strict;
use warnings;
my (@values, @names);
my $header = -1;

push @names, 'Date'; # provide first column name
while (<>) {
chomp;
if ( /\s\d\d\d\d$/ ) { # date line has 4 digits at end.
print join (',',@names), "\n" unless $header++;
print join (',',@values), "\n";
@values = ($_); # array becomes just the date line.
}
if ( /^(\S+)\s+(\d+:\d\d\.\d\d)/ ) {
push @names, $1 unless $header;
push @values, $2; # add values to array
}
}
print join (',',@values), "\n"; # last line.


Now if you wanted to COMBINE the data from the two files, then you may want to run the above twice and use the 'join' command, or a csv tool to combine the results.

Or you can augment the script to read two streams and keep them in sync, skipping ahead one or the other to catch up if need be... but that's like 'work'.

If you promiss to just deal with a day at a time or so (86400 sample?) then you could easily use two perl associative array, keyed by time&date to combine the data.
Just fill the arrays and print at the end, The 'remote' array can just have whole lines or read up on 'Arrays of Arrays'.

Before having some nice reader here dive into that, please provide more clear details.

1) a plain TEXT sample for 3 or 4 corresponding seconds for input and desired output.

2) what to do when a timestamp is missing in either file, or when a column value is missing in the remote file.

3) Don't you need an avg, min, max, and time for those as you are processing the data anyway?

Enjoy!
Hein.

[Allanm]

Thanks Hein for the help Hein!!

1) a plain TEXT sample for 3 or 4 corresponding seconds for input and desired output. - attached

2) what to do when a timestamp is missing in either file, or when a column value is missing in the remote file. - no need to deal with this condition

3) Don't you need an avg, min, max, and time for those as you are processing the data anyway? - not really needed but if its relatively simple then yes.

[Hein van den Heuvel]

Well, from the txt file it looks like you do NOT need to combine the two datasets.
If that's the case, then perl script I first publishes already solved the problem.
You can let it loose on both inputs.

Here is is again with a TAB as seperator, and collecting MAX values as you go, and with indent spaces in the reply.

( Any more bells and wissles is work... send money first! ;-)


Use as: # perl test.pl test.txt > test.tsv

------------------- test.pl -------------
use strict;
use warnings;
my $seperator = "\t";
my ($i, @values, @names, @max);
my $header = -1;

push @names, 'Date'; # provide first column name

push @max, 'Maximums:';
while (<>) {
chomp;
if ( /\s\d\d\d\d$/ ) { # date line has 4 digits at end.

print join ($seperator,@names), "\n" unless $header++;
print join ($seperator,@values), "\n";
@values = ($_); # array becomes just the date line.

$i = 0;
}
if ( /^(\S+)\s+(\d+:\d\d\.\d\d)/ ) {
if ( $header ) {
$i++;
$max[$i] = $2 if $2 gt $max[$i];
} else {
push @max, $2;
push @names, $1;
}
push @values, $2;
}
}
print join ($seperator,@values), "\n";
print join ($seperator,@max), "\n";

[Allanm]

Works great !!!

Thanks so much Hein, I have assigned 10 points each for your posts.

Allan