topic I am using a lookup table and I only want the results that have a match in the look up table. in Splunk Search

I am using a lookup table and I only want the results that have a match in the look up table.

jamesfdally — Tue, 23 Jan 2018 17:38:13 GMT

lookup Down.csv node AS host, BBB AS Circuit
Table host,Circuit,msg,_time,node, BBB

I only want events to hit the table that have a match from the lookup.
Currently I am getting all events and the ones that do match have the fields populated with data and the other events are in the table with blanks.

Re: I am using a lookup table and I only want the results that have a match in the look up table.

493669 — Tue, 23 Jan 2018 17:43:17 GMT

you can use join command...if you can provide sample events then i can helpwith query

Re: I am using a lookup table and I only want the results that have a match in the look up table.

mayurr98 — Tue, 23 Jan 2018 17:57:18 GMT

If you want to save search processing time and if you want it run fast. I would suggest you to add one more column in csv say marker and put value 1 in that for every row.

Then run below search

lookup Down.csv node AS host, BBB AS Circuit 
OUTPUT marker | search marker=* | Table host,Circuit,msg,_time,node, BBB

This will give your only common values between lookup and raw data...if you already have something in lookup file which is not in raw data then you can OUTPUT that field to raw data and then search for that field which will give you only match events .

Let me know if this helps!

Re: I am using a lookup table and I only want the results that have a match in the look up table.

elliotproebstel — Tue, 23 Jan 2018 18:18:36 GMT

There's actually no good reason to add another field into the lookup, assuming there already exists any column in the lookup table that is populated for every row. In fact, you will always know for sure that the fields node and BBB will exist, so you could just as easily do this without modifying the lookup table:

<your search>
| lookup Down.csv node AS host, BBB AS Circuit 
| where isnotnull(node) 
| Table host,Circuit,msg,_time,node, BBB

Re: I am using a lookup table and I only want the results that have a match in the look up table.

elliotproebstel — Tue, 23 Jan 2018 18:44:33 GMT

In addition to the approach suggested by @mayurr98, there is also the option of using the fields in the lookup table as input into the base search to limit the result set to events that have corresponding entries in the lookup table. Here's how to do that:

your base search [ | inputlookup Down.csv | fields node BBB | format ]

Note that there is no | between your base search and the subsearch. What will come out of the subsearch will look like this:

((node="value1" AND BBB="val1") OR (node="value2" AND BBB="val2") OR (node="value3" AND BBB="val3")...)

With this approach, the base search will filter down to only events that have corresponding entries in the lookup file. If you still need to lookup the events in your lookup file to OUTPUT values from other columns in the csv, you can append the lookup as usual after the base search.

Re: I am using a lookup table and I only want the results that have a match in the look up table.

jamesfdally — Tue, 23 Jan 2018 21:02:34 GMT

Thank you for all the input. I am using the isnotnull option after the lookup, but I was hoping to use the lookup table as a filter.
My base search results need to be filtered by 2 fields in the lookup table. If they match then I need only the raw events that match and add some data from the lookup table to the raw events that are left over.

Re: I am using a lookup table and I only want the results that have a match in the look up table.

elliotproebstel — Thu, 25 Jan 2018 21:10:40 GMT

That's exactly what my answer below enables you to do. Have you tried it? If it's not working as you expected, I can help you troubleshoot.

Re: I am using a lookup table and I only want the results that have a match in the look up table.

jamesfdally — Tue, 30 Jan 2018 14:46:02 GMT

Thanks for all the help!

Re: I am using a lookup table and I only want the results that have a match in the look up table.

jamesfdally — Tue, 30 Jan 2018 14:47:19 GMT

This works also

Re: I am using a lookup table and I only want the results that have a match in the look up table.

elliotproebstel — Tue, 30 Jan 2018 15:31:47 GMT

Glad we found you two working solutions!