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 (2)
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
Contributor

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

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
Contributor

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

Taruchit
Contributor

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.

|inputlookup table.csv |fields index, host | search NOT [search index="xxx" |rename orig_* AS * | dedup host | table index, ssc_division, host | format]

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

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