Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk Matching Columns - Am I missing something?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk Matching Columns - Am I missing something?
# 1  
Old 10-02-2018
awk Matching Columns - Am I missing something?
I am using awk to match columns and output based on those matches. For some reason it is not printing matching columns, am I missing something?

Operating system - windows with cygwin.

Command that I am using:

Code:
sed 's/[[:space:]]*,[[:space:]]*/,/g' $tempdir/file1 > $tempdir/file1.$$ && awk -F, 'FNR==NR{f2[$1]=$2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next} FNR==1{print $0, "POE Admin,POE Oper,POE Power,POE Watts,POE Device,POE Class" ;next} {print $0,($2 in f2)?f2[$2]:"NA,NA,NA,NA,NA,NA,NA"}' OFS=, $tempdir/file2 $tempdir/file1.$$ > $tempdir/file3 && rm $tempdir/file1.$$ $tempdir/file1

file1

Code:
CDP NE Hostname,CDP NE IP,Platform,Capabilities,Local Interface,Remote Interface,Software,Software,Version,Release,VLAN,Admin IPhost1(SSI14450AJ5),1replaced,C5548P,Switch IGMP CVTA phone port ,Te1/1,Fe1/1,	Cisco Nexus Operating System (NX-OS) Software, Version 7.1(4)N1(1),1 1replaced,	
host2,10replaced,N5K-C5548P,Switch IGMP CVTA phone port ,Te1/2,Fe1/2,	Cisco Nexus Operating System (NX-OS) Software, Version 7.1(4)N1(1),1 10.replaced2,	
host3(SSI14450AJ5),10.replaced2,C5548P,Switch IGMP CVTA phone port ,Te1/5,Fe1/7,	Cisco Nexus Operating System (NX-OS) Software, Version 7.1(4)N1(1),572 replaced1,	
3560.local,1replaced2,cisco WS-C3560G-48PS,Router Switch IGMP ,Te4/1,Gi0/51,	Cisco IOS Software, C3560 Software (C3560-IPBASEK9-M), Version 12.2(55)SE11, RELEASE SOFTWARE (fc3),223 10replaced.2,	2960.local,10replaced5,cisco 48FPD-L,Switch IGMP ,Te1/9,Te1/0/1,	Cisco IOS Software, C2960S Software (C2960S-UNIVERSALK9-M), Version 15.2(2)E3, RELEASE SOFTWARE (fc3),501 1replaced.5,	
01.local..8,ciscPD-L,Switch IGMP ,Te2/6,Te1/0/1,	Cisco IOS Software, C2960S Software (C2960S-UNIVERSALK9-M), Version 15.0(2)SE10a, RELEASE SOFTWARE (fc3),501 10replaced.8,	
2960-.local,1replaced10,ciscPD-L,Switch IGMP ,Te2/12,Te1/0/1,	Cisco IOS Software, C2960S Software (C2960S-UNIVERSALK9-M), Version 15.0(2)SE10a, RELEASE SOFTWARE (fc3),501 10replaced.1,eplaced.local,10.eplaced,Cisco CISCO2911/K9,Router Switch IGMP ,Gi5/1,Gi0/0,	Cisco IOS Software, C2900 Software (C2900-UNIVERSALK9-M), Version 15.2(3)T, RELEASE SOFTWARE (fc1),	eplaced.local,10.replaced,cisco WS-C2960X-48FPD-L,Switch IGMP ,Te2/11,Te1/0/2,	Cisco IOS Software, C2960X Software (C2960X-UNIVERSALK9-M), Version 15.2(2)E3, RELEASE SOFTWARE (fc3),1 replaced4,	
Nreplaced.local,1replaced4,cisco WS-CreplacedPD-L,Switch IGMP ,Te2/10,Te1/0/1,	Cisco IOS Software, C2960X Software (C2960X-UNIVERSALK9-M), Version 15.2(2)E3, RELEASE SOFTWARE (fc3),1 1replaced4,replaced.log,Gi5/1,NWT-2821GW-01 Ge0/,connected,240,a-full,a-1000,10/100/1000-TX,VoiceGW-Primary,replaced.local,replaced246,Cisco CISCO2911/K9,Router Switch IGMP ,Gi5/1,Gi0/0,	Cisco IOS Software, C2900 Software (C2900-UNIVERSALK9-M), Version 15.2(3)T, RELEASE SOFTWARE (fc1),	
replaced.log,Gi5/2,2821GW-02 Ge0/,connected,240,a-full,a-1000,10/100/1000-TX,VoiceGW-Primary,replaced.local,replaced240.17,Cisco CISCO2911/K9,Router Switch IGMP ,Gi5/2,Gi0/0,	Cisco IOS Software, C2900 Software (C2900-UNIVERSALK9-M), Version 15.2(3)T, RELEASE SOFTWARE (fc1),	
replaced.log,Gi5/3,Metro Ethernet to,connected,trunk,a-full,a-1000,10/100/1000-TX,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,replaced.log,Gi5/4,Uplink to HQ-MDF-L,connected,trunk,a-full,a-1000,10/100/1000-TX,NA,HQ-MDF-L2-2960-01,replaced2,cisco WS-C2960S-48FPD-L,Switch IGMP ,Gi5/4,Gi1/0/48,	Cisco IOS Software, C2960S Software (C2960S-UNIVERSALK9-M), Version 12.2(55)SE3, RELEASE SOFTWARE (fc1),501 replaced1.2


file2
Code:
Interface,Admin,Oper,Power,(Watts),Device,Class
Gi5/1     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/2     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/3     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/4     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/5     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/6     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/7     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/8     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/9     ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/10    ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a  
Gi5/11    ,	auto   ,	ff        ,	0.0        ,	0.0        ,	n/a                 ,	n/a


Current output (I manually sanitized some fields, the columns that need to match were untouched, (Column 1 in file2 and column 2 in file1).


Code:
Hostname,Port,Name,Status,Vlan,Duplex,Speed,Type,VLAN Name,CDP NE Hostname,CDP NE IP,CDP NE Platform,CDP NE Capabilities,Local Interface,Remote Interface,CDP NE Software,CDP NE Software,CDP NE Version,CDP NE Release,CDP NE VLAN,CDP NE Admin IP,POE Admin,POE Oper,POE Power,POE Watts,POE Device,POE C.log,Te1/1,Trunk-,connected,trunk,full,10G,10GBase-CU 3M,NA,replaced,replaced,C5548P,Switch IGMP CVTA phone port,Te1/1,Fe1/1,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),1 replaced,,,NA,NA,NA,NA,NA,NA,NA
replaced,Te1/2,Trunk-to-replaced,trunk,full,10G,10GBase-CU 3M,NA,replaced,1replaced,N5K-C5548P,Switch IGMP Creplaced,Te1/2,Fe1/2,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),1 replaced,,,NA,NA,NA,NA,NA,NA,NA
h.log,Te1/3,Trunk-to-N5K-02,connected,trunk,full,10G,10GBase-CU 3M,NA,treplaced(SSI144305E6),replaced,N5K-C5548P,Switch IGMP replaced,Te1/3,Fe1/1,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),1 replaced,,,NA,NA,NA,NA,NA,NA,NAreplaced.log,Te1/4,Trunk-toreplaced,connected,trunk,full,10G,10GBase-CU 3M,NA,replaced6),replaced,N5K-C5548P,Switch IGMP replacede port,Te1/4,Fe1/2,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),1 replaced,,,NA,NA,NA,NA,NA,NA,NAreplaced.log,Te1/5,nexus replaced,connected,572,full,10G,10GBase-CU 3M,Dreplaced,replaced(SSI14450AJ5),1replaced,N5K-C5548P,Switch IGMP CVTA phone port,Te1/5,Fe1/7,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),572 replaced,,,NA,NA,NA,NA,NA,NA,NAreplaced.log,Te1/6,nexus to 4507 replaced,connected,572,full,10G,10GBase-CU 3M,Dreplaced,replaced,replaced,N5K-C5548P,Switch IGMP replaced,Te1/6,Fe1/7,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),572 1replaced2,,,NA,NA,NA,NA,NA,NA,NA
replaced.log,Te1/7,Uplink to replaced,notconnect,1,full,auto,No XCVR,default,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
replaced.log,Te1/8,Uplink treplaced,notconnect,1,full,auto,No XCVR,default,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
replaced.log,Gi5/1,replaced-01 Ge0/,connected,240,a-full,a-1000,10/100/1000-TX,Vreplacedimary,Hreplaced-01.replacedlocal,1replaced,Cisco CISCO291,Router Switch IGMP,Gi5/1,Gi0/0,Cisco IOS Software,C2900 Software (C2900-UNIVERSALK9-M),Version 15.2(3)T,RELEASE SOFTWARE (fc1),,NA,NA,NA,NA,NA,NA,NA
replaced,Gi5/2,replaced-02 Ge0/,connected,240,a-full,a-1000,10/100/1000-TX,Voicreplacedy,replaced.local,10replaced,Cisco Creplaced911/K9,Router Switch IGMP,Gi5/2,Gi0/0,Cisco IOS Software,C2900 Software (C2900-UNIVERSALK9-M),Version 15.2(3)T,RELEASE SOFTWARE (fc1),,NA,NA,NA,NA,NA,NA,NAreplaced.log,Gi5/3,Metro replacedo,connected,trunk,a-full,a-1000,10/100/1000-TX,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
replaced.log,Gi5/4,UplireplacedL,connected,trunk,a-full,a-1000,10/100/1000-TX,NA,HQ-MDF-L2-2960-01,1replaced,cisc-L,Switch IGMP,Gi5/4,Gi1/0/48,Cisco IOS Software,C2960S Software (C2960S-UNIVERSALK9-M),Version 12.2(55)SE3,RELEASE SOFTWARE (fc1),501 1replaced2,NA,NA,NA,NA,NA,NA,NA
Nreplaced.log,Gi5/5,MDF Phone,notconnect,520,auto,auto,10/100/1000-TX,VLreplaced22,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
replaced.log,Gi5/6,SEreplaced21,connected,525,full,1000,10/100/1000-TX,VLANreplacedeplaced-24,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA
replacedh.log,Gi5/7,PRINreplaced2,connected,525,full,1000,10/100/1000-TX,VLANreplaced4,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA,NA

Last edited by dis0wned; 10-02-2018 at 10:23 AM..
# 2  
Old 10-02-2018
From the data in the file samples posted none of the values in file2 field 1 match file1 field 2.
# 3  
Old 10-02-2018
Im sorry, I manually scrubbed the data and didnt realize I didnt include the matching fields, if you look at the output the field 2 is the same as input file1. The matches start at Gi5/1

------ Post updated at 01:24 PM ------

file1 Updated to reflect matches
# 4  
Old 10-02-2018
The sed is not working. Try:
Code:
awk -F, '{gsub("[ \t]*,[ \t]*", ",")} FNR==NR{f2[$1]=$2 OFS $3 OFS $4 OFS $5 OFS $6 OFS $7; next} FNR==1{print $0, "POE Admin,POE Oper,POE Power,POE Watts,POE Device,POE Class" ;next} {print $0,($2 in f2)?f2[$2]:"NA,NA,NA,NA,NA,NA,NA"}' OFS=, $tempdir/file2 $tempdir/file1

Last edited by rdrtx1; 10-02-2018 at 12:51 PM..
# 5  
Old 10-02-2018
That worked, I had to add
Code:
> $tempdir/file3 && rm $tempdir/file1.$$ $tempdir/file1

to the end of the command. One issue it created and Im not sure which part of the command is doing this but it is returning the interface that it is searching for and placing it on the line prior to the search output. Please see the output below:

Code:
Hostname,Port,Name,Status,Vlan,Duplex,Speed,Type,VLAN Name,CDP NE Hostname,CDP NE IP,CDP NE Platform,CDP NE Capabilities,Local Interface,Remote Interface,CDP NE Software,CDP NE Software,CDP NE Version,CDP NE Release,CDP NE VLAN,CDP NE Admin IP,POE Admin,POE Oper,POE Power,POE Watts,POE Device,POE Class
:Te1/1:
replaced.log,Te1/1,replaced-01,connected,trunk,full,10G,10GBase-CU 3M,NA,treplaced5),1replaced1,N5Kreplaced548P,Switch IGMP CVTA phone port,Te1/1,Fe1/1,Cisco Nexus Operating System (NX-OS) Software,Version 7.1(4)N1(1),1 1replaced,,,NA,NA,NA,NA,NA,NA,NA
:

Lastly, how were you able to determine that the sed portion of the command was not working properly? I'm trying to learn to fish. Thanks again for your help!!
# 6  
Old 10-02-2018
try removing print ":"$2":";
This User Gave Thanks to vgersh99 For This Post:
dis0wned
# 7  
Old 10-02-2018
I looked at the $tempdir/file1.$$ before it got deleted.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk merge matching columns

I know I'm not the first one asking this but my code still does not work: File 1: gi|1283| tRNAscan exon 87020 88058 . - . transcript_id "Parent=tRNA-Tyr5.r01"; gi|3283| tRNAscan exon 97020 97058 . + . transcript_id "Parent=tRNA-Tyr6.r01"; gi|4283| rRNAscan exon 197020 197058 . - . transcript_id... (5 Replies)
Discussion started by: 0sMoses
5 Replies

2. Shell Programming and Scripting

Merging multiple lines to columns with awk, while inserting commas for missing lines

Hello all, I have a large csv file where there are four types of rows I need to merge into one row per person, where there is a column for each possible code / type of row, even if that code/row isn't there for that person. In the csv, a person may be listed from one to four times... (9 Replies)
Discussion started by: RalphNY
9 Replies

3. Shell Programming and Scripting

awk - matching on 2 columns for differents lines

Given this file (I separated them in block to make my explanation clearer): 92157768877;Sof_deme_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 92157768877;Sof_trav_Fort_Email_am_%yyyy%%mm%%dd%;EMAIL;20/02/2015;1;0;0 91231838895;Sof_deme_faible_Email_am;EMAIL;26/01/2015;1 0;0... (1 Reply)
Discussion started by: Andy_K
1 Replies

4. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 Replies

5. Shell Programming and Scripting

awk to copy previous line matching a particular columns

Hello Help, 2356798 7689867 999 000 123678 20385907 9797 666 17978975 87468976 968978 98798 I am trying to have out put which actually look for the third column value of 9797 and then it insert line there after with first, second column value exactly as the previous line and replace the third... (3 Replies)
Discussion started by: Indra2011
3 Replies

6. Shell Programming and Scripting

Help with awk Matching columns from two files

Hello, I have two files as following: #bin chrom chromStart chromEnd name score strand observed 585 chr2 29442 29443 rs4637157 0 + C/T 585 chr2 33011 33012 rs13423995 0 + A/G 585 chr2 34502 34503 rs13386087 0 + ... (2 Replies)
Discussion started by: Homa
2 Replies

7. Shell Programming and Scripting

Find min.max value if matching columns found using AWK

Input_ File : 2 3 4 5 1 1 0 1 2 1 -1 1 2 1 3 1 3 1 4 1 6 5 6 6 6 6 6 7 6 7 6 8 5 8 6 7 Desired output : 2 3 4 5 -1 1 4 1 6 5 6 8 5 8 6 7 (3 Replies)
Discussion started by: vasanth.vadalur
3 Replies

8. Shell Programming and Scripting

awk - Matching columns between 2 files and reordering results

I am trying to match 4 colums (first_name,last_name,dob,ssn) between 2 files and when there is an exact match I need to write out these matches to a new file with a combination of fields from file1 and file2. I've managed to come up with a way to match these 2 files based on the columns (see below)... (7 Replies)
Discussion started by: ambroze
7 Replies

9. Shell Programming and Scripting

using command line arguments as columns for pattern matching using awk

Hi, I wish to use a column, as inputted by a user from command line, for pattern matching. awk file: { if($1 ~ /^8/) { print $0> "temp2.csv" } } something like this, but i want '$1' to be any column as selected by the user from command line. ... (1 Reply)
Discussion started by: invinclible0009
1 Replies

10. Shell Programming and Scripting

awk/sed search lines in file1 matching columns in file2

Hi All, as you can see I'm pretty new to this board. :D I'm struggling around with small script to search a few fields in another file. Basically I have file1 looking like this: 15:38:28 sz:10001 pr:14.16 15:38:28 sz:10002 pr:18.41 15:38:29 sz:10003 pr:19.28 15:38:30 sz:10004... (1 Reply)
Discussion started by: floripoint
1 Replies
Login or Register to Ask a Question