1748069 Members
5600 Online
108758 Solutions
New Discussion юеВ

Filter for SAP ALOG File

 
SOLVED
Go to solution
Volker Borowski
Honored Contributor

Filter for SAP ALOG File

Hi script guys,
I have a file with the following structure (This one comes from a SAP ALOG Transport file).

D57K901438 20041102100542
D57K901438 20041102100543
D57K901438 20041102100554
D57K901438 20041102100636
D57K901442 20041102170907
D57K901442 20041102170926
D57K901442 20041102170932
D57K901442 20041102170952
D57K901442 20041102171019
D57K901442 20041102171020
D57K901442 20041102171022
D57K901481 20041102171035
D57K901481 20041102171037
D57K901481 20041102171129
D57K901481 20041102171130
D57K901481 20041102171131
D57K901481 20041102171147
D57K901479 20041102171712
D57K901479 20041102171714
D57K901479 20041102171736
D57K901479 20041102171737
D57K901479 20041102171738

I need to eliminate all lines but the first and the last on belonging to the first column key. It may differ how many lines one key has, but they are in sequence, sorted by keyfield (which is a SAP-Transport ID) and Timestamp.

Target content after filtering lines should look as follows:

D57K901438 20041102100542
D57K901438 20041102100636
D57K901442 20041102170907
D57K901442 20041102171022
D57K901481 20041102171035
D57K901481 20041102171147
D57K901479 20041102171712
D57K901479 20041102171738

Or even better :-) I can do this one in EXCEL from the previous, but if anyone has a match for this, I need to subtract the timestamp from the second entry from the first one of a specific key.

D57K901438 took 54 sec
D57K901442 took 75 sec
D57K901481 took 72 sec
D57K901479 took 26 sec

Thanks for help
Volker
6 REPLIES 6
Singaram
Advisor

Re: Filter for SAP ALOG File

Hi volker

The following script will work for your first level requirement.

for I in `cut -d" " -f1 $1 | sort -u`
do
grep $I $1 | head -1
grep $I $1 | tail -1
done

For date computations continue with EXCEL

Singaram
Rodney Hills
Honored Contributor

Re: Filter for SAP ALOG File

Here is a perl program that should work-

#!/usr/bin/perl
$prevkey="";
while(<>) {
chomp;
($key,$dt)=split(" ",$_);
if ($prevkey <> $key) {
print $prevline,"\n" unless $prevkey;
print $_,"\n";
}
$prevline=$_;
$prevkey=$key
}
print $prevline,"\n";

Run it by entering "perl prog.pl yourdata"

HTH

-- Rod Hills
There be dragons...
Rodney Hills
Honored Contributor

Re: Filter for SAP ALOG File

#!/usr/bin/perl
use Time::Local;
while(<>) {
chomp;
($key,$dt)=split(" ",$_);
($yr,$mth,$day,$hh,$mm,$ss)=unpack("a4a2a2a2a2a2",$dt);
$time=timelocal($ss,$mm-1,$hr,$day,$mth,$yr-1900);
$hold{$key}[1]=$time;
$hold{$key}[0]=$time unless $hold{$key}[0];
}
foreach $key (sort keys %hold) {
printf "%s %d secs\n",$key,$hold{$key}[1]-$hold{$key}[0];
}

Here is a perl script that will also calculate the time difference.

HTH

-- Rod Hills
There be dragons...
Singaram
Advisor
Solution

Re: Filter for SAP ALOG File

volker

The following shell script will report with date computation (Level 2 request).

Singaram

# This function returns the epoch time.
# arguments: $1 = YYYYMMDDHHMMSS
get_epoht()
{
typeset -i JDD
typeset -i JTT
YER=`echo $1 | cut -c1-4`
MON=`echo $1 | cut -c5-6`
DAY=`echo $1 | cut -c7-8`
HOR=`echo $1 | cut -c9-10`
MIN=`echo $1 | cut -c11-12`
SEC=`echo $1 | cut -c13-14`

JDD=$(($DAY-32075+1461*($YER+4800+($MON-14)/12)/4+367*($MON-2-($MON-14)/12*12)/1
2-3*(($YER+4900+($MON-14)/12)/100)/4))
JTT=$((($JDD * 86400) + ($HOR * 3600) + ( $MIN * 60) + $SEC))
echo $JTT
}


for I in `cut -d" " -f1 $1 | sort -u`
do
startjtt=$(get_epoht `grep $I $1 | head -1 | cut -d" " -f2`)
endjtt=$(get_epoht `grep $I $1 | tail -1 | cut -d" " -f2`)
echo $I 'took ' `expr $endjtt - $startjtt ` ' Secs '
done

Hein van den Heuvel
Honored Contributor

Re: Filter for SAP ALOG File

I like Rodney's approach of gathering all start and stop times before reporting.
It avoids dealing with end-conditions.

Here is another perl solution which allows for crossing day boundaries, but assumes all jobs take less than a day:

while (1) {
$_ = <>;
if (/(\w+)\s+\d{8}(..)(..)(..)/) {
$time = ($2*60+$3)*60+$4;
$key = $1;
} else {
$key = "";
}
if ($key eq $start_key) {
$end_time = $time;
} else {
$elapsed = $end_time - $start_time;
$elapsed += 86400 if ($elapsed < 0);
print "$start_key took $elapsed sec\n" if ($start_key);
$start_key = $key;
$start_time = $time;
}
last unless ($key);
}


If you don't car about the last one,
or can end the file with"
"XXXXXXXXXX 12345678123456"
then this simplyfies to:

while (<>) {
/(\w+)\s+\d{8}(..)(..)(..)/;
$time = ($2*60+$3)*60+$4;
$key = $1;
if ($key eq $start_key) {
$end_time = $time;
} else {
$elapsed = $end_time - $start_time;
$elapsed += 86400 if ($elapsed < 0);
print "$start_key took $elapsed sec\n" if ($start_key);
$start_key = $key;
$start_time = $time;
}
}

(my first cut :-).

Hein.


Volker Borowski
Honored Contributor

Re: Filter for SAP ALOG File

Singram,

sorry for the 2 points in the first solution, should have been a 6er. Seems to be a mouse-wheel problem. Please give me a spare to correct this one.


Thanks to all of you for the solutions.
Evaluation shows, that there might be additional (repeated at later times) entries in my source file :-( Now this results in some real funny import times.

The seems to happen, when transports are manually re-selected for repeated imports. Not sure how to deal with this, I am working on this and will present a final solution at the end (May be other SAPers are interested in this kind of transport statistic as well).

The reason I rated the pure shell-solution a little bit higher is, that I myself have no idea of perl, and I may need to include some other enhancements. But what I saw from the perl solutions was working fine as well. May be I'll switch to these some time later.

Thanks a lot
Volker