Splunk Search

Best Way to search using a lookup table?

Explorer

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

Tags (2)
0 Karma
1 Solution

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

View solution in original post

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

View solution in original post

Explorer

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

0 Karma

Influencer

Ah, I get it 😉 Here you go:

| inputlookup ip_list | fields ip | rename ip as source_IPs | search NOT [ search source="/export/home/azubair/Audit_Report" | dedup source_IPs | fields source_IPs ]

0 Karma

Explorer

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

0 Karma

Influencer

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

0 Karma

Explorer

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

0 Karma

Influencer

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

0 Karma

Explorer

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

No output

0 Karma

Explorer

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

0 Karma