Splunk Search

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

bmkaiser
Explorer

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
1 Solution

cramasta
Builder

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

View solution in original post

bmkaiser
Explorer

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

| EVAL Dept=MVDEDUP(Dept)

JWellsBNSF
Engager

This was incredibly helpful. Thank you.

0 Karma

cramasta
Builder

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

cramasta
Builder

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

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

0 Karma

bmkaiser
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...

Cloud Platform | Discontinuing support for TLS version 1.0 and 1.1

Overview Transport Layer Security (TLS) is a security communications protocol that lets two computers, ...

New Customer Testimonials

Enterprises of all sizes and across different industries are accelerating cloud adoption by migrating ...