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!

SignalFlow: What? Why? How?

What is SignalFlow? Splunk Observability Cloud’s analytics engine, SignalFlow, opens up a world of in-depth ...

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...