awk Matching Columns - Am I missing something?

Shell Programming and Scripting
1

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:

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

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

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).

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
2

From the data in the file samples posted none of the values in file2 field 1 match file1 field 2.

3

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

The sed is not working. Try:

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
5

That worked, I had to add

> $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:

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

try removing print ":"$2":";

1 Like
7

I looked at the $tempdir/file1.$$ before it got deleted.

8

And that fixed it. Thanks for your help!!

------ Post updated at 06:28 PM ------

Cool, thanks for the tip.