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

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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...