awk to subtract from values in file

Shell Programming and Scripting
1

data.txt:

0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,734--734
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,736--736
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742

I'm using the below code to get specific values:

gawk -F, '{$3=strftime("%a %b %d %T %Y,%s",$3)}1' OFS=, nodatetime_mq_conn_open_error  | gawk -F"," '/Oct 10 04.*2015,/,0 {print $10"-"$4"_"$11}'

when i run the above awk code, i get this:

0-1444665949_733--734
0-1444666249_734--734
0-1444666549_734--735
0-1444666849_735--735
0-1444667149_735--735
0-1444667449_735--735
0-1444667749_735--736
0-1444668049_736--736
2-1444668349_736--742
0-1444668649_742--742

What i need to do now, is, i want to be able to subtract the two numbers at the end so that when i run the awk code, it gives me the diference between the two numbers. something like this:

0-1444665949_1
0-1444666249_0
0-1444666549_1
0-1444666849_0
0-1444667149_0
0-1444667449_0
0-1444667749_1
0-1444668049_0
2-1444668349_6
0-1444668649_0

also, i want to be able to get the difference between values on two consecutive lines. for instance,

0,mq_conn_open_error,1444668049,736,/PROD/GAP/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,736--736
2,mq_conn_open_error,1444668349,742,/PROD/GAP/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742

i want to have a separate awk command like the one above that will subtract the bolded value of a specific column in a log line from the same column in the line before it. so basically, the awk code would have to read the output given to it 2 lines at a time and do the substraction, then move on to the next two lines. hope this makes sense.

2

Hello SkySmart,

Could you please try following.

awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){split($NF, B,"--");print $1 "-" $3 "_" B[2]-B[1]}}'  Input_file

Output will be as follows.

0-1444667449_0
0-1444667749_1
0-1444668049_0
2-1444668349_6
0-1444668649_0

NOTE: In this example input you have shown dates which are 12 Oct so only I have taken that in my code, you can put it as per your need.

EDIT: Adding a non one-liner form of solution now for same.

awk -F, '{
                A=strftime("%a %b %d %T %Y,%s",$3);
                if(A ~ /Oct 12.*2015/){
                                                        split($NF, B,"--");
                                                        print $1 "-" $3 "_" B[2]-B[1]
                                      }
         }
        '  Input_file

Thanks,
R. Singh

1 Like
3

For your second requirement, try

awk 'NR>1 {print $8 - last} {last = $8} END {print $8 - last}' FS="," file
0
85
0
0
0
85
0
566
0
0
1 Like
4 
awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){split($NF, B,"--");print $1 "-" $3 "_" B[2]-B[1]}}'  Input_file

The above command works beautifully. however, i'd like for it to pull out all records it finds in the log from the point the search pattern is found, to the end of file.

right now, it only shows the line containing the search pattern.

-sh-4.1$ 
-sh-4.1$ awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12 13:55.*2015/){split($NF, B,"--");print $1 "-" $3 "_" B[2]-B[1]} }' input_file.txt
0-1444683349_0
-sh-4.1$ 
-sh-4.1$
5

Hello SkySmart,

If I understood correctly your requirement, you need to print all the records from the point where first match happens for example Oct 12.*2015 in this example, you can mention dates as per your need. Following may help you in same then.
Let's say we have following Input_file:

cat Input_file
0,mq_conn_open_error,1744665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1644666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,734--734
0,mq_conn_open_error,1844666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1944666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,2244667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,736--736
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,734--734
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,736--736
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,734--734
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1544667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1644667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,735--735
0,mq_conn_open_error,1744667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736
0,mq_conn_open_error,1844668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,736--736
2,mq_conn_open_error,1944668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742
0,mq_conn_open_error,2044668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742

So if you will see the dates and times in Input_file it will look as follows.

awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);print A}' Input_file
 

Mon Apr 14 17:25:49 2025,1744665949
Sat Feb 12 06:44:09 2022,1644666249
Thu Jun 15 03:22:29 2028,1844666549
Sat Aug 16 13:14:09 2031,1944666849
Sat Feb 16 17:39:09 2041,2244667149
Mon Oct 12 12:30:49 2015,1444667449
Mon Oct 12 12:35:49 2015,1444667749
Mon Oct 12 12:40:49 2015,1444668049
Mon Oct 12 12:45:49 2015,1444668349
Mon Oct 12 12:50:49 2015,1444668649
Mon Oct 12 12:05:49 2015,1444665949
Mon Oct 12 12:10:49 2015,1444666249
Mon Oct 12 12:15:49 2015,1444666549
Mon Oct 12 12:20:49 2015,1444666849
Mon Oct 12 12:25:49 2015,1444667149
Mon Oct 12 12:30:49 2015,1444667449
Mon Oct 12 12:35:49 2015,1444667749
Mon Oct 12 12:40:49 2015,1444668049
Mon Oct 12 12:45:49 2015,1444668349
Mon Oct 12 12:50:49 2015,1444668649
Mon Oct 12 12:05:49 2015,1444665949
Mon Oct 12 12:10:49 2015,1444666249
Mon Oct 12 12:15:49 2015,1444666549
Mon Oct 12 12:20:49 2015,1444666849
Wed Dec 12 21:12:29 2018,1544667149
Sat Feb 12 07:04:09 2022,1644667449
Mon Apr 14 17:55:49 2025,1744667749
Thu Jun 15 03:47:29 2028,1844668049
Sat Aug 16 13:39:09 2031,1944668349
Mon Oct 16 23:30:49 2034,2044668649

So as you can see above when it will match date Oct 12.*2015 it will print from there to till rest of the lines.
Following code may help then for same.

awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){Q=1};if(Q){split($NF, B,"--");print $1 "-" $3 "_" B[2]-B[1]}}'  Input_file

Output will be as follows then.

0-1444667449_0
0-1444667749_1
0-1444668049_0
2-1444668349_6
0-1444668649_0
0-1444665949_1
0-1444666249_0
0-1444666549_1
0-1444666849_0
0-1444667149_0
0-1444667449_0
0-1444667749_1
0-1444668049_0
2-1444668349_6
0-1444668649_0
0-1444665949_1
0-1444666249_0
0-1444666549_1
0-1444666849_0
0-1544667149_0
0-1644667449_0
0-1744667749_1
0-1844668049_0
2-1944668349_6
0-2044668649_0

Kindly do let me know if you have any queries on same.

Thanks,
R. Singh

1 Like
6

Your command worked magnificently! thank you.

one final question.

i have this command (provided above by RudiC to address my second problem of subtracting values on two different consecutive lines):

gawk 'NR>1 {print $8 - last"-"$3"_"$8"--"last","$10} {last = $8} END {print $8 - last"-"$3"_"$8"--"last","$10}' FS="," Input_file

It works. but, i want it to pull out all records found in this file, from a certain point, to the end of the file, then perform the subtractions.

so, i know it will involve something like this:

gawk 'NR>1 {A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12 03:55.*2015/){Q=1};if(Q){split($NF, B,"--") {print $8 - last"-"$3"_"$8"--"last","$10} {last = $8} END {print $8 - last"-"$3"_"$8"--"last","$10}' FS="," Input_file

but this isn't working.

7

Hello SkySmart,

Let's say we have following Input_file as follows.

0,mq_conn_open_error,1744665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1644666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,714--734
0,mq_conn_open_error,1844666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1944666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,715--735
0,mq_conn_open_error,2244667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,705--735
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,705--735
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,702--736
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,705--736
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,706--742
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,711--742
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,722--734
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,723--734
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,727--735
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,725--735
0,mq_conn_open_error,1444667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,729--735
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,730--735
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,733--736
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,719--736
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,700--742
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,702--742
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,700--734
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,733--735
0,mq_conn_open_error,1544667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,722--735
0,mq_conn_open_error,1644667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,720--735
0,mq_conn_open_error,1744667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736
0,mq_conn_open_error,1844668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,733--736
2,mq_conn_open_error,1944668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742
0,mq_conn_open_error,2044668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742

Following the code which may help in same then.

awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){Q=1};if(Q){split($NF, B,"--");print B[2]-B[1]}}' Input_file

Output will be as follows.

30
34
31
36
31
12
11
8
10
6
5
3
17
42
40
34
1
1
2
13
15
1
3
6
0

EDIT: Adding a command by which we can get the respective line and difference too.

 awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){Q=1};if(Q){split($NF, B,"--");print $0  OFS B[2]-B[1]}}'  Input_file

Output will be as follows.

0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,705--735 30
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,702--736 34
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,705--736 31
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,706--742 36
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,711--742 31
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,722--734 12
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,723--734 11
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,727--735 8
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,725--735 10
0,mq_conn_open_error,1444667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,729--735 6
0,mq_conn_open_error,1444667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,730--735 5
0,mq_conn_open_error,1444667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,733--736 3
0,mq_conn_open_error,1444668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,719--736 17
2,mq_conn_open_error,1444668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,700--742 42
0,mq_conn_open_error,1444668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,702--742 40
0,mq_conn_open_error,1444665949,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,700--734 34
0,mq_conn_open_error,1444666249,734,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62022,0,733--734 1
0,mq_conn_open_error,1444666549,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,734--735 1
0,mq_conn_open_error,1444666849,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,733--735 2
0,mq_conn_open_error,1544667149,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,722--735 13
0,mq_conn_open_error,1644667449,735,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62107,0,720--735 15
0,mq_conn_open_error,1744667749,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,735--736 1
0,mq_conn_open_error,1844668049,736,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62192,0,733--736 3
2,mq_conn_open_error,1944668349,742,/PROD/G/cicsitlp/sys/unikixmain.log,72K,mq_conn_open_error,62758,2,736--742 6
0,mq_conn_open_error,2044668649,742,/PROD/G/cicsitlp/sys/unikixmain.log,64K,mq_conn_open_error,62758,0,742--742 0

Thanks,
R. Singh

1 Like
8

Try

awk -F, '{A=strftime("%a %b %d %T %Y,%s",$3);if(A ~ /Oct 12.*2015/){Q=1};if(Q){split($NF, B,"--");print $0  OFS B[2]-B[1], $8 - last}} {last = $8} END {print $8 - last}'  Input_file
1 Like