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
The general form for that is
<<your search for Table A>>
| append [ search <<your search for Table B ]
| stats values(*) as * by Name Role
How do I do it if Table B is in CSV file? Thanks
Read the CSV instead of searching for Table B.
<<your search for Table A>>
| append [ | inputlookup TableB.csv ]
| stats values(*) as * by Name Role
The general form for that is
<<your search for Table A>>
| append [ search <<your search for Table B ]
| stats values(*) as * by Name Role
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
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.
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
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.