Splunk Search

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

AKG1_old1
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

niketn
Legend

@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

somesoni2
Revered Legend

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
Builder

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

niketn
Legend

@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

AKG1_old1
Builder

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

niketn
Legend

@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!!!"

AKG1_old1
Builder

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

Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...