How to achieve lookup multiple field but append the missing value?

[LearningGuy]

How do I perform lookup multiple field but append the missing value.   Thanks
For example:
Table A:
Name        Role              IP
PaloAlto   Firewall       192.168.1.1
Cisco         Router         192.168.1.2

Table B
Name          Role           ConsoleIP
PaloAlto     Firewall    192.168.10.1
Cisco           Router      192.168.10.2
Siemens     Switch      192.168.10.3

Table A lookup table B results:
Name        Role              IP                                ConsoleIP
PaloAlto   Firewall       192.168.1.1           192.168.10.1
Cisco         Router         192.168.1.2           192.168.10.2
Siemens     Switch      <empty>                 192.168.10.3

[LearningGuy]

How do I do it if Table B is in CSV file?  Thanks

[richgalloway]

Read the CSV instead of searching for Table B.

<<your search for Table A>>
| append [ | inputlookup TableB.csv ]
| stats values(*) as * by Name Role

[richgalloway · Accepted solution]

The general form for that is

<<your search for Table A>>
| append [ search <<your search for Table B ]
| stats values(*) as * by Name Role

[LearningGuy]

Hello,
What is the reason you used "stat"?   
Does the CSV (Table B) need to have same exact field name as the Table A when using your search?
When I ran your command, it merged Name with multiple roles into 1 row
I need both tables are combined using 2 column name and role as  a key because the table has no unique ID, so it's kinda like a lookup, but if I use lookup, it won't catch value that's not on Table A (Siemens and Switch)

Thanks

[richgalloway]

The stats command regroups the results based on the values in the named fields (Name and Role, in this case).  It's how the two search results get put together into a single result set.

If done right, you should see one row for each pair of Name and Role values in either Table A or Table B.

[LearningGuy]

Does the CSV (Table B) need to have same exact field name as the Table A when using your search?
It worked after I changed the field name (Name and Role) on Table B in CSV file to match the field names on Table A.
Is it possible to do it without matching the field names (Name and Role) on both Table A and Table B?

Please confirm this.  Thanks

[richgalloway]

To get the right results, yes, Table A and Table B must share at least one field name.  If they don't share a field then stats cannot group the results.