topic Re: Join 2 lookups match fields in Splunk Search

Join 2 lookups match fields

nathanluke86 — Mon, 18 May 2020 13:02:08 GMT

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

acfecondo75 — Mon, 18 May 2020 13:32:38 GMT

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

richgalloway — Mon, 18 May 2020 13:35:53 GMT

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

nathanluke86 — Mon, 18 May 2020 13:55:09 GMT

@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

nathanluke86 — Mon, 18 May 2020 14:36:42 GMT

Thanks @acfecondo75

used the above but changed append to appendcols