Join 2 lookups match fields

[nathanluke86]

Hello,

I am looking to join 2 lookups and match the field "AccountName" from lookup1 with user field in lookup 2.

I have 269 results in lookup 1 and 250 results in lookup 2.

When I match the fields and join the lookups I lose the 19 results that dont have a match.

How can I do this a keep the 19 results so I can manually update these

TIA

[richgalloway]

Please tell us more about the problem you are trying to solve so we can help you find a solution.

[nathanluke86]

@richgalloway

basically I want to join two lookups and combine the fields from both by matching on a user field

lookup1 has fields user, ip, mac
lookup2 has fields user, workstation, guid, sid

I want to match the user field and then create a new lookup as below:

lookup with fields user, ip, mac, workstation, guid, sid.

I can join these by using |eval matchfield user but when I do this I lose 19 results from lookup1 as there is no user match in lookup2

lookup1 has 269 users
lookup2 has 250 users (missing 19 users)

I need to create the new lookup but also keep the 19 users that were not matched.

hope that makes sense

[acfecondo75 · Accepted solution]

Hello nathanluke86!

If you want to get the results from both lookups, try something like this:

| inputlookup lookup1.csv
| append
[|inputlookup lookup2.csv]

then to get only one row per user, you could add something like this to the end:

| stats values(*) as * by user

[nathanluke86]

Thanks @acfecondo75

used the above but changed append to appendcols