topic Re: lookup multiple field but append the missing value in Getting Data In

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

LearningGuy — Thu, 15 Jun 2023 00:34:40 GMT

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

Re: lookup multiple field but append the missing value

richgalloway — Tue, 13 Jun 2023 16:24:21 GMT

The general form for that is

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

Re: lookup multiple field but append the missing value

LearningGuy — Tue, 13 Jun 2023 17:02:30 GMT

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

Re: lookup multiple field but append the missing value

richgalloway — Tue, 13 Jun 2023 17:46:46 GMT

Read the CSV instead of searching for Table B.

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

Re: lookup multiple field but append the missing value

LearningGuy — Tue, 13 Jun 2023 18:55:13 GMT

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

Re: lookup multiple field but append the missing value

richgalloway — Tue, 13 Jun 2023 19:56:19 GMT

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.

Re: lookup multiple field but append the missing value

LearningGuy — Wed, 14 Jun 2023 18:02:50 GMT

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

Re: lookup multiple field but append the missing value

richgalloway — Wed, 14 Jun 2023 19:08:06 GMT

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.