topic Re: Best Way to search using a lookup table? in Splunk Search

Best Way to search using a lookup table?

gohar — Tue, 24 May 2011 17:14:40 GMT

I'm running a search across a bunch of data, say syslogs, that has a lot of different source_IPs.

I make a lookup table of name [ip_list]

src_ip
10.10.10.1
10.10.10.2
10.10.10.3

What is the best way to search across all of my data and ONLY show items from lookup tables that are NOT match with field

Re: Best Way to search using a lookup table?

ziegfried — Tue, 24 May 2011 18:09:58 GMT

<search terms> NOT [ | inputlookup <your lookup> ]

eg.

* NOT [ | inputlookup ip_list ]

To inspect which search string is generated by the subsearch, you can execute

| inputlookup ip_list | format

Re: Best Way to search using a lookup table?

gohar — Mon, 28 Sep 2020 09:36:31 GMT

Not working. What I am trying is

I want to compare my field(source_IPs) with lookup file(ip_list) and generates those IPs from lookup file that are not matched with source_IPs field.

Also, can I trim my desired output by using stats command that will show only IPs

Re: Best Way to search using a lookup table?

gohar — Mon, 28 Sep 2020 09:36:34 GMT

source="/export/home/azubair/Audit_Report" inputlookup ip_list NOT [ | fields source_IPs ]

No output

Re: Best Way to search using a lookup table?

ziegfried — Wed, 25 May 2011 12:21:59 GMT

What is the name of the column you want to compare it with in the lookup?

Re: Best Way to search using a lookup table?

gohar — Wed, 25 May 2011 13:05:02 GMT

there is only 1 column in ip_list with the name "ip"

Re: Best Way to search using a lookup table?

ziegfried — Mon, 28 Sep 2020 09:36:39 GMT

source="/export/home/azubair/Audit_Report" NOT [ | inputlookup ip_list | fields ip | rename ip as source_IPs ] | stats count by source_IPs

Re: Best Way to search using a lookup table?

gohar — Mon, 28 Sep 2020 09:36:47 GMT

thanks man. It shows ips of the field source_IPs that are not matched with my lookup table.
I want the other way round, means want IPs from my lookup table that are not matched with field source_IPs

Re: Best Way to search using a lookup table?

ziegfried — Mon, 28 Sep 2020 09:36:50 GMT

Ah, I get it 😉 Here you go:

Re: Best Way to search using a lookup table?

gohar — Mon, 28 Sep 2020 09:36:55 GMT

thanks man, run with the flow.
just for knowledge,your previous command worked well by showing all events without "|stats count by source_IPs" but when we append stats it process 14% of my file and display no results. Is stats command take too much processing??

source="/export/home/azubair/Audit_Report" NOT [ | inputlookup ip_list | fields ip | rename ip as source_IPs ] | stats count by source_IPs