Splunk Search

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

Builder

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

alt text

alt text

0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

SplunkTrust
SplunkTrust

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

Builder

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

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Builder

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

SplunkTrust
SplunkTrust

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

Builder

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