Search two lookup tables for matching field values

[marktechuk]

Hi trying to search two lookup tables for matching fields values, both tables have the same fields.
Just looking to compare my output UserFromTest1 and UserFromTest2 for a match.

| inputlookup test1.csv UserName as User OutputNew User as UserFromTest1
| inputlookup test2.csv UserName as User OutputNew User as UserFromTest2

Thanks

[rahulbhatia · Accepted solution]

| inputlookup Test1.csv
| fields UserName, Count | rename Count as Count1
| join type=inner UserName
[| inputlookup Test2.csv
| fields UserName, Count | rename Count as count2]

It will show you the list of UserName's which are present in both the table, i have added count column to show the 2 different count value for the same UserName

[marktechuk]

Worked great, thanks

[wmyersas]

You need to |where after the lookups (and I'd suggest case-insensitivizing it):

<first part of search>
| inputlookup test1.csv UserName as User OutputNew User as UserFromTest1
| inputlookup test2.csv UserName as User OutputNew User as UserFromTest2
| eval UserFromTest2=upper(UserFromTest2)
| eval UserFromTest1=upper(UserFromTest1)
| where UserFromTest2=UserFromTest1
| <rest of search goes here>