awk to sum column field from duplicate row/lines


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers awk to sum column field from duplicate row/lines
# 1  
Old 06-25-2013
awk to sum column field from duplicate row/lines

Hello,
I am new to Linux environment , I working on Linux script which should send auto email based on the specific condition from log file. Below is the sample log file

Code:
Name    m/c        usage
abc         xxx         10 
abc         xxx         20 
abc         xxx         5 
xyz         ddd         15
xyz         ddd         15
xyz         ddd         15
pqr         111         5
pqr         111         5

1st column is user name and last column is usage, I like to sort above file for the condition if total usage from any one user e.g. abc >25 then trigger email to user and cc to admin id.

I could able to find duplicate entries and email to those found entries

Code:
awk '{a[$1]++}END{for(x in a)if(a[x]>1)for(i=1;i<=a[x];i++)print x}' tmpfile > p3temp.txt
                awk '!_[$0]++' p3temp.txt > p4temp.txt
                while read MAIL; do     mailx -s "xxx!"    –c  admin@email.com  "$MAIL"  –c   < tmpfile; done < p4temp.txt


Output I am getting
Code:
abc 
xyz
pqr

and auto email to all three users

But one thing is which not right is that email to user “pqr” even though this user have duplicate entries but total usage ( 5+5)= 10 which I like to ignore because its less than 25. I need you help to adding for column from duplicate rows to make email notification.


Thanks in advance for your help

Last edited by Scrutinizer; 06-25-2013 at 03:07 AM.. Reason: code tags
# 2  
Old 06-25-2013
I don't understand what you are trying to achieve, don't you mean a[$1]+=$3 instead of a[$1]++?
This User Gave Thanks to Scrutinizer For This Post:
# 3  
Old 06-27-2013
Srutinizer- Thanks lot for pointing out that.

Code:
awk '{a[$1]+=$3}END{for(i in a){print i, a[i]}}' test > output1 
awk '{if ($2 > 25) print $1}' output1 > output2               #### Sorting usage  >25


Below is output-
Output1
Code:
pqr 10
abc 35
xyz 45

output2 (This contain only those ids which have license usage >25)
Code:
abc
xyz

After filtering of the users name, I like to send email to these ids. Email address is strored in another file e.g. file name email-list.txt as below.

Code:
pqr patt.rich@mnc.com 
abc adam.char@mnc.com
xyz xevior.zoy@mnc.com"]xevior.zoy@mnc.com

Please guide me on how to grab email id from other file.

Thanks again.

Last edited by Scrutinizer; 06-28-2013 at 02:19 AM.. Reason: code tags, took out email tags
# 4  
Old 06-27-2013
You could do the whole thing in one awk program:
Code:
awk '
        NR == FNR {
                A[$1] += $3
                next
        }
        {
                E[$1] = $2
        }
        END {
                for ( k in A )
                {
                        if ( A[k] > 25 )
                        {
                                cmd = "echo message body | mailx -s 'subject' " E[k]
                                system ( cmd )
                        }
                }
        }
' input.txt email.txt

This User Gave Thanks to Yoda For This Post:
# 5  
Old 06-28-2013
That’s just wonderful. I knew I am being very amateur in that coding.
One last help on “Message body” I want my message body content same as input file (in this case “input”)
Thanks lot.
# 6  
Old 06-28-2013
You can cat the file:
Code:
cmd = "cat input.txt | mailx -s 'subject' " E[k]

# 7  
Old 06-29-2013
UUOC! Better directly mail the file:
Code:
cmd = "< input.txt mailx -s 'subject' " E[k]

or
Code:
cmd = "mailx -s 'subject' " E[k] " < input.txt"

This User Gave Thanks to MadeInGermany For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (12 Replies)
Discussion started by: as7951
12 Replies

3. UNIX for Dummies Questions & Answers

Unique values in a row sum the next column in UNIX

Hi would like to ask you guys any advise regarding my problem I have this kind of data file.txt 111111111,20 111111111,50 222222222,70 333333333,40 444444444,10 444444444,20 I need to get this file1.txt 111111111,70 222222222,70 333333333,40 444444444,30 using this code I can... (6 Replies)
Discussion started by: reks
6 Replies

4. Shell Programming and Scripting

Awk: print lines with one of multiple pattern in the same field (column)

Hi all, I am new to using awk and am quickly discovering what a powerful pattern-recognition tool it is. However, I have what seems like a fairly basic task that I just can't figure out how to perform in one line. I want awk to find and print all the lines in which one of multiple patterns (e.g.... (8 Replies)
Discussion started by: elgo4
8 Replies

5. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

6. UNIX for Dummies Questions & Answers

awk solution to duplicate lines based on column

Hi experts, I have a tab-delimited file with one column containing values separated by a comma. I wish to duplicate the entire line for every value in that comma-delimited field. For example: $cat file 4444 4444 4444 4444 9990 2222,7777 6666 2222 ... (3 Replies)
Discussion started by: torchij
3 Replies

7. Shell Programming and Scripting

awk transpose row into 2 field column

Need to transpose every 2 fields of a row into a single 2 field column. input 4 135 114 76 217 30 346 110 5 185 115 45 218 85 347 125 6 85 116 130 220 65 352 95 11 30 117 55 221 42 355 75 16 72 118 55 224 37 357 430 17 30 119 55 225 40 358 62 21 52 120 65 232 480 360 180 ....... (8 Replies)
Discussion started by: sdf
8 Replies

8. Shell Programming and Scripting

counting lines containing two column field values with awk

Hello everybody, I'm trying to count the number of consecutive lines in a text file which have two distinctive column field values. These lines may appear in several line blocks within the file, but I only want a single block to be counted. This was my first approach to tackle the problem (I'm... (6 Replies)
Discussion started by: origamisven
6 Replies

9. Shell Programming and Scripting

duplicate row based on single column

I am a newbie to shell scripting .. I have a .csv file. It has 1000 some rows and about 7 columns... but before I insert this data to a table I have to parse it and clean it ..basing on the value of the first column..which a string of phone number type... example below.. column 1 ... (2 Replies)
Discussion started by: mitr
2 Replies

10. Shell Programming and Scripting

Delete a row that has a duplicate column

I'm trying to remove lines of data that contain duplicate data in a specific column. For example. apple 12345 apple 54321 apple 14234 orange 55656 orange 88989 orange 99898 I only want to see apple 12345 orange 55656 How would i go about doing this? (5 Replies)
Discussion started by: spartan22
5 Replies
Login or Register to Ask a Question

Featured Tech Videos