topic Re: How can we exclude rows which are present in another table/lookup ? in Splunk Search

How can we exclude rows which are present in another table/lookup ?

AKG1_old1 — Thu, 01 Feb 2018 18:52:37 GMT

Hi,

I am using one search query to extract list of data and I want to exclude those rows which are present in one csv file(lookup).

Re: How can we exclude rows which are present in another table/lookup ?

niketn — Thu, 01 Feb 2018 19:07:29 GMT

@agoyal, refer to @somesoni2's answer https://answers.splunk.com/answers/612603/how-to-search-what-values-are-missing-in-my-lookup.html of marking the results coming from index vs lookup and then you can add a filter to only those coming from index.

Following is a run anywhere example (instead of first pipe | tstats you can have your first search pulling data from index)

| tstats count where index=_internal by sourcetype
| eval from="data"
| append[ | inputlookup sourcetypelist.csv 
    | table sourcetype
    | eval count=0 
    | eval from="lookup"]
| stats values(from) as from sum(count) as Total by sourcetype
| search from="data" AND from!="lookup"

You can also use outer join (or left) with |inputlookup as your first command and index search as second.

Re: How can we exclude rows which are present in another table/lookup ?

somesoni2 — Thu, 01 Feb 2018 19:13:47 GMT

Generally, in this type of cases, you can just use lookup table to filter your data upfront, so that all you get is data no in lookup, like this

your base search NOT [| inputlookup yourlookup.csv | table Fields For Filter]
|..aggregation commans...

Re: How can we exclude rows which are present in another table/lookup ?

AKG1_old1 — Thu, 01 Feb 2018 20:17:58 GMT

Thank you @niketnilay @somesoni2 . It worked for me. 🙂

Re: How can we exclude rows which are present in another table/lookup ?

AKG1_old1 — Thu, 01 Feb 2018 20:20:37 GMT

@somesoni2: thank you !! my base search(tstats) display results in table. Not sure how to use "NOT" after that.

Re: How can we exclude rows which are present in another table/lookup ?

niketn — Fri, 02 Feb 2018 01:25:32 GMT

@agoyal , by base search @somesoni2 meant the first pipe with index and sourcetype where you will apply
second search for field values not in the lookup file i.e. NOT [| inputlookup ....]
Unless your main search is on metadata fields, this is better approach where your events can be filtered in the base search itself. So do try out this approach (if your main query is not tstats) and confirm query performance.

PS: Table generating command or transforming commands can be placed only after your base search.

Re: How can we exclude rows which are present in another table/lookup ?

AKG1_old1 — Fri, 02 Feb 2018 15:10:55 GMT

Thank you for clarification. my main query is tstats. Other approach with append fits for my requirement. Thanks Again 🙂