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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...