Splunk Search

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

djn12313
Explorer

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:

| inputlookup mtylookuptable | fields lookup_id, lookup_output | search NOT [search index=myindex | dedup event_id | table source.item_id | format]

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.

Labels (1)
Tags (3)
0 Karma
1 Solution

masonmorales
Influencer

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.

View solution in original post

Taruchit
Explorer

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

indexhost
indexAhost1
indexAhost3

 

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

0 Karma

somesoni2
Revered Legend

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]
0 Karma

djn12313
Explorer

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.

0 Karma

masonmorales
Influencer

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.

djn12313
Explorer

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]

Taruchit
Explorer

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

0 Karma

masonmorales
Influencer

Good to hear!

0 Karma

masonmorales
Influencer

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

0 Karma

djn12313
Explorer

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]
0 Karma
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

<P style=" text-align: center; "><span class="lia-inline-image-display-wrapper lia-image-align-center" ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

<FONT size="5"><FONT size="5" color="#FF00FF">Get the latest news and updates from the Splunk Community ...