topic Re: How to exclude duplicate events based on a field value in another event? in Splunk Search

How to exclude duplicate events based on a field value in another event?

russell120 — Mon, 30 Sep 2019 16:48:29 GMT

Hi, I have an "asset discovery" type of query that uses a CSV and 4+ indexes, and produces tens of thousands of results look similar to this:

id     device     serial  origin
111  routerAlpha  12345   sales
     routerAlpha  12345
222  switchBeta   78787   devops
     switchBeta   78787   
    desktopSigma  44444   
    desktopSigma  44444   
    serverTheta   55555

How do I:
1) Remove all events that have a serial with a null id IF the same serial appears again in an event with a numerical id
2) Then, deduplicate all other events that have a serial # with a null id

The result should look like this:

id     device     serial  origin
111  routerAlpha  12345   sales
222  switchBeta   78787   devops
    desktopSigma  44444   
    serverTheta   55555

Re: How to exclude duplicate events based on a field value in another event?

jacobpevans — Mon, 30 Sep 2019 20:22:27 GMT

Greetings @russell120,

This run-anywhere search should give you what you need. Fillnull is necessary so that stats does not drop the null entries. Then, you count the number of unique device rows. If there are multiple device rows AND id is null, then drop those. You can use whatever you want instead of "-", and you can use replace to change back to null if necessary at the end.

           | makeresults | eval id="111"   , device="routerAlpha" ,  serial="12345" , origin="sales"
| append [ | makeresults | eval id=null()  , device="routerAlpha" ,  serial=null()  , origin=null()   ]
| append [ | makeresults | eval id="222"   , device="switchBeta"  ,  serial="78787" , origin="devops" ]
| append [ | makeresults | eval id=null()  , device="switchBeta"  ,  serial="78787" , origin=null()   ]
| append [ | makeresults | eval id=null()  , device="desktopSigma",  serial="44444" , origin=null()   ]
| append [ | makeresults | eval id=null()  , device="desktopSigma",  serial="44444" , origin=null()   ]
| append [ | makeresults | eval id=null()  , device="serverTheta" ,  serial="55555" , origin=null()   ]
| fillnull value="-"
| stats      count by id device serial origin
| eventstats count by device
| search NOT(count>1 id="-")
| fields   - count
| sort                id device serial

Re: How to exclude duplicate events based on a field value in another event?

russell120 — Tue, 01 Oct 2019 17:46:07 GMT

Hey @jacobevans ,

Thanks! This works perfectly for the example I provided, but perhaps I simplified my real data a bit too much. My issue is that I have a multivalue field.

If origin was a multivalue field and the values sales and devops were replaced with sales marketing and devops tech, respectively, what edit would we need in your solution for it to work?

Since I have a multivalue field, the stats count by line (line 9) makes a 2nd copy of each event to separate the multivalues into their own a single value.

Re: How to exclude duplicate events based on a field value in another event?

jacobpevans — Tue, 01 Oct 2019 18:49:18 GMT

Try inserting | mvexpand origin between these lines:

| fillnull value="-"
| stats      count by id device serial origin

So that it now looks like this:

            | makeresults | eval id="111"   , device="routerAlpha" ,  serial="12345" , origin="sales"
 | append [ | makeresults | eval id=null()  , device="routerAlpha" ,  serial=null()  , origin=null()   ]
 | append [ | makeresults | eval id="222"   , device="switchBeta"  ,  serial="78787" , origin="devops" ]
 | append [ | makeresults | eval id=null()  , device="switchBeta"  ,  serial="78787" , origin=null()   ]
 | append [ | makeresults | eval id=null()  , device="desktopSigma",  serial="44444" , origin=null()   ]
 | append [ | makeresults | eval id=null()  , device="desktopSigma",  serial="44444" , origin=null()   ]
 | append [ | makeresults | eval id=null()  , device="serverTheta" ,  serial="55555" , origin=null()   ]
 | fillnull value="-"
 | mvexpand origin
 | stats      count by id device serial origin
 | eventstats count by device
 | search NOT(count>1 id="-")
 | fields   - count
 | sort                id device serial

Without testing, I believe that should work.

Re: How to exclude duplicate events based on a field value in another event?

russell120 — Wed, 02 Oct 2019 13:14:11 GMT

Thanks a lot! I'll accept your original answer as the answer as it is the solution to the original question. I appreciate your help.

Re: How to exclude duplicate events based on a field value in another event?

jacobpevans — Wed, 02 Oct 2019 21:15:23 GMT

You got it. Thank you for accepting the answer!