Splunk Search
Highlighted

Search Items in a Lookup Table NOT present in Index

Engager

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.itemid value in the event stream and the lookupid 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 sourceitemids.

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.

Tags (3)
0 Karma
Highlighted

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

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
Highlighted

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

Engager

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
Highlighted

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

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

Highlighted

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

Engager

Renaming the "Item ID" (what I also referred to in the original post as "lookupid") to source.itemid 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]
0 Karma
Highlighted

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

Influencer

Good to hear!

0 Karma
Highlighted

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

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
Highlighted

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

Engager

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