0

I have 2 csv files. File1 is an existing list of private IP address & its hostname. File2 is a daily report which has 8 columns in which 2 containing the private IP. I want to compare file2 with with file1 by matching field 4 and field 7 of file2 with field 2 of file1. Then, upon matching, I want to append field 3 and field 6 of file2 according to the matches of field 4 and field 7 with field 2 of file1.

File1.csv

PC1,192.168.3.1
PC2,192.168.3.2
PC3,192.168.3.3

File2.csv (Has about 50 lines)

Port,Type,S_Host,S_IP,Port,D_Host,D_IP,Port
2,tcp,N/A,192.168.3.1,2,N/A,192.168.3.2,8
3,tcp,N/A,192.168.3.2,2,N/A,192.168.3.3,3

I need to do a bash script to automate file2.

Desired output:

Port,Type,S_Host,S_IP,Port,D_Host,D_IP,Port
2,tcp,PC1,192.168.3.1,2,PC2,192.168.3.2,8
3,tcp,PC2,192.168.3.2,2,PC3,192.168.3.3,3
Improve this question
8
  • 3
    Welcome to SO, on SO we encourage users to add their efforts which they have put in order to solve their own problems so kindly do add the same in your question and let us know then. Commented May 8, 2020 at 4:31
  • 1
    Hint: You can use join command. Commented May 8, 2020 at 4:34
  • Please add your desired output (no description) for that sample input to your question (no comment). Commented May 8, 2020 at 4:48
  • added my desired output :) Commented May 8, 2020 at 5:16
  • 1
    Read file1 into an array in awk (indexed by IP) then process file2 and lookup the hostname using the IP from fields 4 & 7 and set fields 3 & 6 equal to the resulting hostnames and print the record. Commented May 8, 2020 at 5:43

1 Answer 1

Reset to default
1

If your input files look like this, i.e. the first version, with spaces after the comma:

File1.csv

Host, IP
PC1, 192.168.3.1
PC2, 192.168.3.2
PC3, 192.168.3.3

and:

File2.csv

Port, Type, S_Host, S_IP, Port, D_Host, D_IP, Port
2, tcp, N/A, 192.168.3.1, 2, N/A, 192.168.3.2, 8
3, tcp, N/A, 192.168.3.2, 2, N/A, 192.168.3.3, 3

Try:

#!/bin/bash
awk '
  BEGIN {FS = ", "; OFS = ", "}
  (FNR == NR) && (NR > 1) {hh[$2] = $1}
  NR > FNR {
    if (FNR == 1) 
      print; 
    else 
      print $1, $2, hh[$4], $4, $5, hh[$7], $7, $8;
  }
' File1.csv File2.csv

This is the ouput I get:

Port, Type, S_Host, S_IP, Port, D_Host, D_IP, Port
2, tcp, PC1, 192.168.3.1, 2, PC2, 192.168.3.2, 8
3, tcp, PC2, 192.168.3.2, 2, PC3, 192.168.3.3, 3

Also, if the IP is a public IP, I need to do a whois search instead to get the OrgName

I suggest you to post another question about this second topic. It is like in professional emails: one item = one question.

Improve this answer
Sign up to request clarification or add additional context in comments.

20 Comments

I'm only getting empty spaces at the 3rd and 6th column Pierre.
Somebody upvoted my answer but you say there is an issue about empty spaces. Does it work or not? And if not, what happens exactly? With the input files you gave after removing the blanks, I get the desired output (see my edited answer with the output).
It's not working. The column 3 and 6 returns empty in the output.
@scriptdummy: Are there white spaces after the comma in your csv files?
Yes Pierre there are white spaces after the comma in my csv files
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.