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

[AKG1_old1]

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).

[somesoni2]

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...

[AKG1_old1]

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

[niketn]

@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.

[AKG1_old1]

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

[niketn · Accepted solution]

@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.

[AKG1_old1]

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