topic Re: Search Items in a Lookup Table NOT present in Index in Splunk Search

How to search Items in a Lookup Table NOT present in Index?

djn12313 — Fri, 05 Aug 2022 14:21:59 GMT

Hi all -

Relatively new to Splunk and have already attempted a number of methods from forums to perform this search to no avail.

I have a single Index of events and a single lookup table containing reference data. Events are tied to the Lookup Table via the source.item_id value in the event stream and the lookup_id field in the Lookup Table. I'm trying to find items that exist in the Lookup table that do NOT exist in the event stream and then list the lookup_output field (from the Lookup Table) .

The cleanest method seems to be something along these lines:

Running each search independently seems to return the correct results. I opted to use "format" command to return a 'clean' list of the of source_item_ids.

The problem I'm running into is the results returned are always every value in the Lookup Table. Which I know is not right. Any thoughts / help appreciated.

Re: Search Items in a Lookup Table NOT present in Index

masonmorales — Wed, 01 Apr 2015 15:08:20 GMT

Can you tell us the field names you are using in both your base search and the lookup table? Or post the actual search?

Re: Search Items in a Lookup Table NOT present in Index

djn12313 — Wed, 01 Apr 2015 16:08:12 GMT

It looks nearly identical to what I posted.

| inputlookup folders | fields "Item ID", Path | search NOT [search index=folderevents | dedup event_id | table source.item_id | format]

Re: Search Items in a Lookup Table NOT present in Index

masonmorales — Wed, 01 Apr 2015 16:16:44 GMT

Without seeing your data, I don't know which fields are supposed to match up, but I am guessing you aren't getting the expect results because your field names between the inputlookup and your data do not match.

Re: Search Items in a Lookup Table NOT present in Index

somesoni2 — Wed, 01 Apr 2015 17:25:58 GMT

Try this

| inputlookup folders | fields "Item ID", Path | search NOT [search index=folderevents | dedup event_id | table source.item_id  | rename source.item_id as "Item ID"| format]

Re: Search Items in a Lookup Table NOT present in Index

djn12313 — Mon, 28 Sep 2020 19:22:31 GMT

Renaming the "Item ID" (what I also referred to in the original post as "lookup_id") to source.item_id ended up resolving this for me.

Revised search looks like:

 | inputlookup folders | fields "Item ID", Path |  rename "Item ID" as source.item_id | search NOT [search index=folderevents | dedup event_id | table source.item_id | format]

Re: Search Items in a Lookup Table NOT present in Index

djn12313 — Wed, 01 Apr 2015 18:38:54 GMT

It's strange. I tried renaming the field in the sub search first and couldn't get it to work. For some reason, however, it did work when I changed it in the inputlookup search per my comment above.

Re: Search Items in a Lookup Table NOT present in Index

masonmorales — Wed, 01 Apr 2015 19:33:22 GMT

Good to hear!

Re: Search Items in a Lookup Table NOT present in Index

Taruchit — Wed, 27 Apr 2022 15:37:40 GMT

Hello All,

I have a usecase of fetching list of hosts in an index but not listed in the lookup table.

For example: -
indexA has 5 hosts: host1. host2. host3, host4, host5

In lookup table, we have two columns: -
index
host

index	host
indexA	host1
indexA	host3

Thus, as the result I need host2, host4, host5.

It would be great to receive any inputs that helps to build the logic for the solution.

Thank you

Re: Search Items in a Lookup Table NOT present in Index

Taruchit — Wed, 18 May 2022 11:34:12 GMT

Hello @djn12313

Your SPL works successfully to fetch records present in lookup table and not in index.

Can you please share if the same query can be extended to fetch the max(_time) of the events returned by the SPL? That means, displaying the events along with the time when they were last present in the index.

Thank you

Re: Search Items in a Lookup Table NOT present in Index

Taruchit — Fri, 05 Aug 2022 12:35:04 GMT

Hi @djn12313 ,

I tried using the approach you shared in the above post, however, I am still getting results which are common in search results and lookup table.

In the index, field names are orig_host and orig_index, thus, used rename.

Thus, please share how I can get those results which are present in lookup table and not fetched in Splunk logs.

Thank you