I am trying to merge 3 lookup files having them join on one field but keep all additional fields and records(that don't join) in the output. I have tried join type=outer but that appears to only keep all records from one side of the join.
File 1:
Hostname, IP, MAC
A,192.168.1.1,MACC
D,192.168.1.2,MACF
G,192.168.1.3,MACI
File 2:
Hostname, Owner, Location
A,John,Boston
J,Tom,NewYork
M,Fred,Cleveland
File 3:
Hostname,Switch,Port
A,B0K-mdfa,FA/02
J,B0K-mdfa,FA/03
X,B1K-mdfb,FA/03
I want the results to have:
Hostname,IP,MAC,Owner,Location,Switch,Port
A,192.168.1.1,MACC,John,Boston,B0K-mdfa,FA/02
D,192.168.1.2,MACF,,,,,
G,192.168.1.3,MACI,,,,,
J,,,Tom,NewYork,B0K-mdfa,FA/03
M,,,Fred,Cleveland,,,
X,,,,,B1K-mdfb,FA
Basically, this is what I have been trying:
| inputlookup File1 | join type=outer Name [|inputlookup File2 ] | join type=outer Name [|inputlookup File3 ]
What happens is what I would expect, I get all records from file 1, those that join to it from File 2 and then those that join to that from file 3 when I want all records from all 3 files basically merged on Name. I tried using append but that won't work either. Any ideas?
Thanks,
-Bob
Why not just use the lookup files the way they're intended to be used, as lookups?
|inputlookup File1 | lookup File2 Hostname | lookup File3 Name
EDIT: Updated version which gets all the Hostnames to begin with:
|inputlookup File1 | fields Hostname | append [|inputlookup File2 | fields Hostname] | append [|inputlookup File3 Hostname] | dedup Hostname | lookup File1 Hostname | lookup File2 Hostname | lookup File3 Hostname
Extending answer from Ayn ->
|inputlookup File1 | fields Hostname |
append [|inputlookup File2 | fields
Hostname] | append [|inputlookup File3
Hostname] | dedup Hostname | join
type=outer Hostname [|inputlookup
File1] | join type=outer Hostname
[|inputlookup File2]| join type=outer
Hostname [|inputlookup File3]
I thought using append and then piping it to transaction Name but for some reason that doesn't return any results?
Why not just use the lookup files the way they're intended to be used, as lookups?
|inputlookup File1 | lookup File2 Hostname | lookup File3 Name
EDIT: Updated version which gets all the Hostnames to begin with:
|inputlookup File1 | fields Hostname | append [|inputlookup File2 | fields Hostname] | append [|inputlookup File3 Hostname] | dedup Hostname | lookup File1 Hostname | lookup File2 Hostname | lookup File3 Hostname
I was just taking that approach.
Thanks!!!
-Bob
Fair enough - updated my answer with an approach that will grab all the hostnames first of all.
That is only going to give me the records that match from file 1 in file 2 and file 3. I need the records from all 3 files merged, regardless if they are found in the other files.