topic Re: How to remove a duplicate from a lookup or only return one of the results? in Splunk Search

How to remove a duplicate from a lookup or only return one of the results?

bmkaiser — Thu, 03 Sep 2015 17:12:11 GMT

I am performing a lookup on a table that contains data that I don't manage and cannot change. The lookup is returning duplicates for some people because they're listed twice in the source table. How do I either return only one of the results from the lookup or remove the duplicate from only the affected records? My data looks something like this:

Name     Dept
----------------------
Joe       IT
          IT
----------------------
Mary      IT
----------------------
Bob      Ops
----------------------

I don't want to remove all occurrences of IT. I only want to remove the duplicates from Joe's row. Or perhaps there's a different way I can get the information so that it is only returning one result. The lookup portion is pretty standard and looks like this:

| LOOKUP name AS first_name OUTPUT department AS Dept

Re: How to remove a duplicate from a lookup or only return one of the results?

cramasta — Tue, 29 Sep 2020 07:13:26 GMT

Create a lookup definition.

manager > lookups > lookup definitations

check advanced options then change the maximum matches to 1

so instead of

| LOOKUP mylookup.csv name AS first_name OUTPUT department AS Dept
you use
| LOOKUP mylookupdefinition name AS first_name OUTPUT department AS Dept

Re: How to remove a duplicate from a lookup or only return one of the results?

cramasta — Thu, 03 Sep 2015 17:44:51 GMT

you can also dedup the lookup file by running the a query as such.

| inputlookup mylookup.csv | dedup name | outputlookup mylookup.csv

Re: How to remove a duplicate from a lookup or only return one of the results?

bmkaiser — Thu, 03 Sep 2015 17:55:01 GMT

Thank you for your help! I like this because I'd rather never have the data in the first place.

Re: How to remove a duplicate from a lookup or only return one of the results?

bmkaiser — Fri, 04 Sep 2015 12:51:21 GMT

I did also figure out how to remove the duplicate using mvdedup:

| EVAL Dept=MVDEDUP(Dept)

Re: How to remove a duplicate from a lookup or only return one of the results?

JWellsBNSF — Thu, 21 Nov 2019 20:05:08 GMT

This was incredibly helpful. Thank you.